MySQL update taking(too) long time ....

Question

After some expected growth on our service all of the sudden some updates are taking extremely long time, these used to be pretty fast until the table reached about 2MM records, now they take about 40-60 seconds each.

update table1 set field1=field1+1 where id=2229230;
Query OK, 0 rows affected (42.31 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Here are the field types:

`id` bigint(20) NOT NULL auto_increment,
`field1` int(11) default '0',

Result from the profiling, for context switches which is the only one that seems to have high numbers on the results:

mysql> show profile context switches
    -> ;
+----------------------+-----------+-------------------+---------------------+
| Status               | Duration  | Context_voluntary | Context_involuntary |
+----------------------+-----------+-------------------+---------------------+
| (initialization)     | 0.000007  |                 0 |                   0 |
| checking permissions | 0.000009  |                 0 |                   0 |
| Opening tables       | 0.000045  |                 0 |                   0 |
| System lock          | 0.000009  |                 0 |                   0 |
| Table lock           | 0.000008  |                 0 |                   0 |
| init                 | 0.000056  |                 0 |                   0 |
| Updating             | 46.063662 |             75487 |               14658 |
| end                  | 2.803943  |              5851 |                 857 |
| query end            | 0.000054  |                 0 |                   0 |
| freeing items        | 0.000011  |                 0 |                   0 |
| closing tables       | 0.000008  |                 0 |                   0 |
| logging slow query   | 0.000265  |                 2 |                   1 |
+----------------------+-----------+-------------------+---------------------+
12 rows in set (0.00 sec)

The table is about 2.5 million records, the id is the primary key, and it has one unique index on another field (not included in the update).

It's a innodb table.

any pointers on what could be the cause ?

Any particular variables that could help track the issue down ?

Is there a "explain" for updates ?

EDIT: Also I just noticed that the table also has a :

`modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

Explain:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | table1        | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)

Answer 1

There's no way that query should take a long time, if id is really the primary key (unless you have lots and lots of ids equal to 2229230?). Please run the following two sqls and post the results:

show create table table1;
explain select * from table1 where id = 2229230;

Update: just to be complete, also do a

select count(*) from table1 where id = 2229230;

Answer 2

May I recommend as well:

OPTIMIZE TABLE table1;

Sometimes your tables just need a little love, if they've grown rapidly and you haven't optimized them in a while the indices may be a little crazy. In fact, if you've got the time (and you do), it never hurts to throw in

REPAIR TABLE table1;

Answer 3

Ok after a few hours of tracking down this one, it seems the cause was a "duplicate index", the create table that I was doing to answer Keith, had this strange combo:

• a unique key on fieldx
• a key on fieldx

the second one is obviously redundant and useless, but after I dropped the key all updates went back to < 1 sec.

+1 to Keith and Ivan as their comments help me finally track down the issue.