MySQL Bugs: #54736: MySQL performance drop after reboot

Bug #54736	MySQL performance drop after reboot
Submitted:	23 Jun 2010 12:45	Modified:	28 Oct 2010 9:03
Reporter:	Marc Brevoort	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	Ver 14.12 Distrib. 5.0.51a	OS:	Linux (ubuntu 8.04)
Assigned to:		CPU Architecture:	Any
Tags:	after, performance, reboot, restart, slow

Description:
I have an application which works on a mysql database (the dump is about 7.5 megabytes in size). It performs joins, group by and has subqueries.

Normally, queries execute in under a second. After rebooting the computer,
however, suddenly the queries execute very slowly (haven't actually measured
but I would say 30 seconds to a minute for a page refresh). Refreshing the
page again does not restore performance (hence I do not think it is a
caching problem). During the query, CPU usage maxes out.

Data is only selected from this database; its content has not changed from
creating it to after rebooting it.

It is worth mentioning that dropping the database, recreating and repopulating it restores performance. Only rebooting will cause the performance drop again; merely restarting mysqld has no impact.

Any ideas what could be causing this bizarre behaviour? What difference is there between a reboot and a service restart?

How to repeat:
See above.

Suggested fix:
You tell me!

Please, send your my.cnf file content, the results of

show table status;

in the problematic database and EXPLAIN results for typical query that becomes slow after reboot.

This may have something to do with filesystem cache, if your tables are MyISAM and your queries do not use indexes.

mysql> explain select sql_id,description,link,approved from (select filteredtable.* from (select SQL_Document.sql_id as sql_id,SQL_Document.sql_title as description,concat('view.php?id=',SQL_Document.sql_filehandle) as link,case sql_versioned when 0 then 'YES' else sql_approved_version end as approved from SQL_Document left join relation as r1 on (r1.srcid=SQL_Document.sql_id) left join relbind as rb1 on (r1.relid=rb1.relid) left join SQL_Tag as t1 on (r1.destid=t1.sql_id) where (1=1) and rb1.relname='doctags' and t1.sql_name='knowledgebase' and SQL_Document.sql_filehandle<>0 and coalesce(SQL_Document.sql_trashed,'0')<>1 order by sql_last_changed_time desc) as filteredtable left join relation on (filteredtable.sql_id=relation.srcid) left join relbind on (relation.relid=relbind.relid) left join SQL_Group on (relation.destid=SQL_Group.sql_id) where relbind.relname='readaccess' and SQL_Group.sql_name in ('admin')) as lx limit 10;
+----+-------------+--------------+-------+------------------------------------+----------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table        | type  | possible_keys                      | key      | key_len | ref                                            | rows | Extra                                                     |
+----+-------------+--------------+-------+------------------------------------+----------+---------+------------------------------------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2>   | ALL   | NULL                               | NULL     | NULL    | NULL                                           | 3807 |                                                           | 
|  2 | DERIVED     | SQL_Group    | const | sql_name,objindex                  | sql_name | 131     |                                                |    1 |                                                           | 
|  2 | DERIVED     | relbind      | ref   | relname                            | relname  | 67      |                                                |    1 | Using where; Using index                                  | 
|  2 | DERIVED     | <derived3>   | ALL   | NULL                               | NULL     | NULL    | NULL                                           | 3807 |                                                           | 
|  2 | DERIVED     | relation     | ref   | srcid,SIND,DIND,SRCINDEX,DESTINDEX | srcid    | 15      | filteredtable.sql_id,const,hubdb.relbind.relid |    1 | Using where; Using index                                  | 
|  3 | DERIVED     | rb1          | ref   | relname                            | relname  | 67      |                                                |    1 | Using where; Using index; Using temporary; Using filesort | 
|  3 | DERIVED     | t1           | ALL   | objindex                           | NULL     | NULL    | NULL                                           |  928 | Using where                                               | 
|  3 | DERIVED     | r1           | ref   | srcid,SIND,DIND,SRCINDEX,DESTINDEX | DIND     | 5       | hubdb.t1.sql_id                                |    5 | Using where                                               | 
|  3 | DERIVED     | SQL_Document | ref   | objindex                           | objindex | 5       | hubdb.r1.srcid                                 |    1 | Using where                                               | 
+----+-------------+--------------+-------+------------------------------------+----------+---------+------------------------------------------------+------+-----------------------------------------------------------+
9 rows in set (0.13 sec)

Please send me my.cnf files and your hardware configuration details.

Aside.  - Please disable the log parameter for slow queries 
        - Check the max_allowed_packet size

My my.cnf was previously attached to this ticket. 
As you can see, log_slow_queries was already disabled.

I've tried increasing 16M key_buffer and max_allowed
packet to 128M to no avail (also, as mentioned in my
original report, after a re-import of all data, 
things perform fine with the 16M settings).

Marc,

honestly, I would not exclude a hardware issue here.

How old is the machine?

Did I understand the following right:
yesterday it run fast
today you rebooted and it run slow
today evening you rebooted again and it run fast again?

This really sounds like a hardware device is dying.

Hello Susanne,

Thank you for the suggestion but I do not expect this to be a hardware problem. The performance issues *only* occur when running MySQL queries. No other software experiences performance problems nor crashes. Honestly, "This sounds like a hardware device is dying" sounds like taking the easy way out.

My previous detailed feedback shows in the output of the 'explain' commands that in one case an index is used whereas in the other case it is not. This should be a clear indicator that the problem is software-based. I'll gladly provide you with any further troubleshooting information needed- just let me know what you need. I'm just trying to do my small part to make MySQL the best product it can possibly be. It would be great if this sentiment is shared by the people maintaining it!

Hello Marc,

sorry, I oversaw that there was a difference in EXPLAIN.

Anyway, I also saw that you are using MySQL 5.0.51a.

This is a very old version.

We have had lots of indexing problems in past which we already fixed.

Maybe your problem is one of the side effect of other former bugs.

Our actual 5.0 version is 5.0.91

Our actual supported version is MySQL 5.1 and the actual version here is 5.1.50.

I would recommend that you first will update to 5.0.91 or even upgrade to 5.1.

There is a high chance that this will already fix your problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".