Recent Topics

1 Oct 25, 2007 08:46    

My b2evolution Version: Not Entered

I've got a 1.9.3 install that's producing some slow queries, from 12 to 23 seconds. I'm capturing the queries in my slow query log:

SELECT DISTINCT evo_comments.* FROM evo_comments INNER JOIN evo_posts ON comment_post_ID = post_ID  INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = 2 AND comment_type IN ('comment','trackback','pingback')  AND comment_status IN ('published') AND  (  ( post_status = "private" AND post_creator_user_ID = 1 )  OR post_status IN ('published','protected') ) ORDER BY comment_date DESC LIMIT 20;  

I've tried to speed things up a bit by creating a couple of indexes in evo_comments, now my explain looks like this (sorry for the formatting):

+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys                            | key          | key_len | ref                               | rows  | Extra                                        |
+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | evo_comments | range  | comment_post_ID,comment_type,comment_idx | comment_type | 1       | NULL                              | 18532 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | evo_posts    | eq_ref | PRIMARY,post_status,post_creator_user_ID | PRIMARY      | 4       | b2mm.evo_comments.comment_post_ID |     1 | Using where; Distinct                        |
|  1 | SIMPLE      | evo_postcats | ref    | PRIMARY,catpost                          | PRIMARY      | 4       | b2mm.evo_posts.post_ID            |     2 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | othercats    | eq_ref | PRIMARY,cat_blog_ID                      | PRIMARY      | 4       | b2mm.evo_postcats.postcat_cat_ID  |     1 | Using where; Distinct                        |
+----+-------------+--------------+--------+------------------------------------------+--------------+---------+-----------------------------------+-------+----------------------------------------------+
4 rows in set (0.00 sec)

Does anyone have any suggestions in terms of indexes or optimization to get these query times down?

2 Oct 26, 2007 06:22

I'm not sure if this is related but I have to assume the MYSQL server is overwhelmed. When my server is a little bit busy I get mysql errors in the sidebar. It starts in the sidebar directly where the Admin link is in the skin. What is this about? :

      An unexpected error has occured!

      If this error persits, please report it to the administrator.

      Go back to home page
      Additional information about this error:

      MySQL error!

      MySQL server has gone away(Errno=2006)

      Your query:

      SELECT * 
                    
      FROM evo_groups 
                   
      ORDER BY grp_name

      An unexpected error has occured!

      If this error persits, please report it to the administrator.

      Go back to home page
      Additional information about this error:

      MySQL error!

      MySQL server has gone away(Errno=2006)

      Your query: Session::dbsave()

      UPDATE evo_sessions SET
              sess_data = NULL,
              sess_ipaddress = '70.91.122.133',
              sess_key = 'sg7eXmMMMcLDy964r6wWOyL2YH7DpypW',
              sess_lastseen = '2007-10-26 00:13:23',
              sess_user_ID = 3
            
      WHERE sess_ID = 1478143

3 Nov 06, 2007 05:59

I believe I solved the problem. In the sidebar, I had a number of included php files that are not part of b2. I think these were taking so long that the mysql server would "go away" waiting.

I changed the way I call these with ajax calls that fire after the page is loaded and everything seems to be fine.


Form is loading...