垃圾评论导致WordPress登录后台奇慢无比!

自从换了美国主机后,Wordpress后台打开就奇慢无比,调试过程:

  1. xhprof
    访问http://www.hemono.com/wp-admin/?_profile=1,结果啥也没记录下来:(
    通常的profile工具是注册一个shutdown函数,最后一次性的将结果写入磁盘;遇到这样的超时程序,需要能够实时的写入profile到存储设备,todo:让phpio支持实时记录
  2. Strace
    php-fpm进程比较多,如果使用strace-f 跟踪父进程id的话,输出的内容太杂乱,还是新建一个单独pid的测试环境:

    1. php-fpm
      新建一个debug-pool:/etc/php5/fpm/pool.d/debug.conf
      [debug]
      listen = 127.0.0.1:9001
      pm = static
      pm.max_children = 1
    2. nginx
      新建一个debug站点:
      listen:8080;
      fastcgi_pass 127.0.0.1:9001;
    3. 在htop里面找到pool-debug的进程id
      strace -s 1024 -p [debug-pool-pid]
    4. 然后在浏览器中访问http://localhost:8080,终端里面应该可以看到php-fpm的系统调用
      看了半天,发现程序在不断的执行SQL,但是strace无法定位到出错的PHP代码段,到是可以把strace显示的SQL拿到源代码目录去grep一下;
      在htop看到mysqld的cpu占用老高,把慢日志打开再说。
  3. 打开慢日志
    1. php-fpm 慢日志 /etc/php5/fpm/pool.d/debug.conf
      slowlog = /var/log/php-fpm.slow.$pool.log
      request_slowlog_timeout = 1 

      [09-Oct-2013 10:51:36]  [pool debug] pid 12263
      script_filename = /var/www/hemono.com/wp-admin/index.php
      [0x00007fc9e145ee28] mysql_query() /var/www/hemono.com/wp-includes/wp-db.php:1098
      [0x00007fc9e145ecc8] query() /var/www/hemono.com/wp-includes/wp-db.php:1379
      [0x00007fc9e145eb48] get_results() /var/www/hemono.com/wp-admin/includes/dashboard.php:612
      [0x00007fffc11dc080] wp_dashboard_recent_comments() unknown:0
      [0x00007fc9e145e8f0] call_user_func() /var/www/hemono.com/wp-admin/includes/template.php:963
      [0x00007fc9e145e7a0] do_meta_boxes() /var/www/hemono.com/wp-admin/includes/dashboard.php:206
      [0x00007fc9e145e688] wp_dashboard() /var/www/hemono.com/wp-admin/index.php:63
    2. mysql慢日志 /etc/mysql/my.cnf
      log_slow_queries        = /var/log/mysql/mysql-slow.log
      long_query_time = 1 

      # Query_time: 1.010872  Lock_time: 0.000149 Rows_sent: 50  Rows_examined: 78376
      SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT 0, 50;
      SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT 50, 50;
      SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT 100, 50;
      ……
    3. 最后还是靠慢日志定位到问题:
      /wp-admin/includes/dashboard.php

      function wp_dashboard_recent_comments() {
              global $wpdb;
      
              if ( current_user_can('edit_posts') )
                      $allowed_states = array('0', '1');
              else
                      $allowed_states = array('1');
      
              // Select all comment types and filter out spam later for better query performance.
              $comments = array();
              $start = 0;
      
              $widgets = get_option( 'dashboard_widget_options' );
              $total_items = isset( $widgets['dashboard_recent_comments'] ) && isset( $widgets['dashboard_recent_comments']['items'] )
                      ? absint( $widgets['dashboard_recent_comments']['items'] ) : 5;
              // 由于垃圾评论35000多条,导致迟迟不能获得5条有效的评论,只需加个条件 c.`comment_approved` != 'spam' 过滤掉spam评论
      +        while ( count( $comments ) < 5 && $possible = $wpdb->get_results( "SELECT * FROM $wpdb->comments c LEFT JOIN $wpdb->posts p ON c.comment_post_ID = p.ID WHERE c.`comment_approved` != 'spam' AND p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT $start, 50" ) ) {
      -        while ( count( $comments ) < 5 && $possible = $wpdb->get_results( "SELECT * FROM $wpdb->comments c LEFT JOIN $wpdb->posts p ON c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT $start, 50" ) ) {
      
                      foreach ( $possible as $comment ) {
                              if ( count( $comments ) >= $total_items )
                                      break;
                              if ( in_array( $comment->comment_approved, $allowed_states ) && current_user_can( 'read_post', $comment->comment_post_ID ) )
                                      $comments[] = $comment;
                      }
                      break;
                      $start = $start + 50;
              }

14 评论

  1. Pingback: [http://8601908.com]
  2. Pingback: [http://dsynet.com]
  3. Pingback: [http://body69.com]
  4. Pingback: nba 2k17 mt
  5. Pingback: albion online gold
  6. Pingback: fifa 17 coins
  7. Pingback: nab 2k16 mt coins
  8. Pingback: u4fifa
  9. Pingback: nba 2k16 mt
  10. Pingback: buy cheap fifa coins

发表评论