Slow MySQL Updates/Inserts/Deletes

Question

I seem to be having slow inserts, updates and deletes on all tables on a specific database with MySQL. Not a lot of data in those tables (from 2k to 20k). Small number of columns (5-10), indexes (two of them), and no duplicate index issue. I'm running MySQL 5.0.45 with MyISAM.

I run the following query and it takes about 5-7 seconds:

UPDATE accounts SET updated_at = '2010-10-09 11:22:53' WHERE id = 8;

Selects seem to come back right away.

Explain gives me the following:

+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | accounts | index | NULL          | PRIMARY | 4       | NULL | 1841 | Using index | 
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

The profiler doesn't show any significant data for anything other than a seemingly high number of context switches:

+----------------------+----------+-------------------+---------------------+
| Status               | Duration | Context_voluntary | Context_involuntary |
+----------------------+----------+-------------------+---------------------+
| (initialization)     | 0.000057 |                 0 |                   0 | 
| checking permissions | 0.000008 |                 0 |                   0 | 
| Opening tables       | 0.000013 |                 0 |                   0 | 
| System lock          | 0.000005 |                 0 |                   0 | 
| Table lock           | 0.000005 |                 0 |                   0 | 
| init                 | 0.000061 |                 0 |                   0 | 
| Updating             | 0.000101 |                 0 |                   0 | 
| end                  | 7.957233 |              7951 |                   2 | 
| query end            | 0.000008 |                 0 |                   0 | 
| freeing items        | 0.000011 |                 0 |                   0 | 
| closing tables       | 0.000007 |                 1 |                   0 | 
| logging slow query   | 0.000002 |                 0 |                   0 | 
+----------------------+----------+-------------------+---------------------+

This might also help:

+----------------------+----------+-----------------------+---------------+-------------+
| Status               | Duration | Source_function       | Source_file   | Source_line |
+----------------------+----------+-----------------------+---------------+-------------+
| (initialization)     | 0.000057 | check_access          | sql_parse.cc  |        5306 | 
| checking permissions | 0.000008 | open_tables           | sql_base.cc   |        2629 | 
| Opening tables       | 0.000013 | mysql_lock_tables     | lock.cc       |         153 | 
| System lock          | 0.000005 | mysql_lock_tables     | lock.cc       |         162 | 
| Table lock           | 0.000005 | mysql_update          | sql_update.cc |         167 | 
| init                 | 0.000061 | mysql_update          | sql_update.cc |         429 | 
| Updating             | 0.000101 | mysql_update          | sql_update.cc |         560 | 
| end                  | 7.957233 | mysql_execute_command | sql_parse.cc  |        5122 | 
| query end            | 0.000008 | mysql_parse           | sql_parse.cc  |        6116 | 
| freeing items        | 0.000011 | dispatch_command      | sql_parse.cc  |        2146 | 
| closing tables       | 0.000007 | log_slow_statement    | sql_parse.cc  |        2204 | 
| logging slow query   | 0.000002 | dispatch_command      | sql_parse.cc  |        2169 | 
+----------------------+----------+-----------------------+---------------+-------------+

Additional info: It's running on a CentOS-5 VPS with 4 gigs of ram guaranteed. No index on the updated_at column and not triggers anywhere.

[New things that I tried]

1. Created a new table (using like) running innodb and inserted all records from one of the affected tables. (same problem)
2. Backed up the database and restored it to a different database within the same server instance. (same problem)
3. Restored that same backup to my local machine and I didn't have a problem.
4. Tried another database within the same mysql server instance that has the problem database and the other database (a Wordpress DB) ran updates/inserts/deletes just fine.
5. Restarted mysqld and restarted the entire server last night (same problem)
6. Updated MySQL to version 5.0.77 (same problem)
7. Deleted all indexes from one of the affected tables (same problem)

Any ideas what to look at next or what might be the issue? Seems to be more of a recent problem though I can't say when it started to show up exactly.

Answer 1

Finally found the answer. That database was somehow missing the MYD and MYI files and still running. Not sure how that's possible considering that the MYD file holds the data for MyISAM tables but that was causing the slow inserts/updates/deletes.

I ran an ALTER TABLE to set the engine to MyISAM (which it already was) and it recreated those files. Updates/inserts/deletes running fast again!

Answer 2

If you have variable length rows, you might need to run OPTIMIZE TABLE occasionally.