MySQL update taking(too) long time

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)

---

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;

---

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.

---

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;

---

What helped for me was changing the engine from 'innodb' to 'myisam'. My update query on a similar size dataset went from 100 ms to 0.1 ms.

Be aware that changing the engine type for an existing application might have consequences as InnoDB has better data integrity and some functions your application might be depending on.

For me it was worth losing InnoDB for a 1000 times speed increase on big datasets.

---

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.

---

Another thing to take into consideration when debugging update statements taking way too much time compared to the select statement version: are the update statements executed within a transaction?

In my case a transaction turned the update statement(s) from a blazingly fast execution time to a extremely slow one. The more rows affected the heavier the loss of performance. My statements work with user defined variables, but don't know if that part of the 'problem'.