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

I'd come across this same issue, which turned out to be due to a trigger on the table. Any time an update was done on my table, the trigger would update another table which is were the delay was actually caused. Adding an index on that table fixed the issue. So try checking for triggers on the table.