Slow MySQL Updates/Inserts/Deletes

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.

Answers


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


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!


Need Your Help

disable button on master page

asp.net vb.net telerik

On my master page I have a rad menu.

How can I detect if a dependent is missing from an EF entity?

c# linq entity-framework-4.1 rdbms referential-integrity

Example situation (I have asked this more directly and received no feedback, so please excuse the abstract example but I am just trying to pose the question better):

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.