occasional slow UPDATE using primary key

Question

I have an InnoDB table in MySQL 5.5.53 where simple updates like

UPDATE mytable SET acol = 'value' WHERE id = 42;

hang for several seconds. id is the primary key of the table.

If I enable query profiling using

SET profiling = 1;

then run the query and look at the profile, I see something like:

show profile;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000077 |
| checking permissions         | 0.000008 |
| Opening tables               | 0.000024 |
| System lock                  | 0.000008 |
| init                         | 0.000346 |
| Updating                     | 0.000108 |
| end                          | 0.000004 |
| Waiting for query cache lock | 0.000002 |
| end                          | 3.616845 |
| query end                    | 0.000016 |
| closing tables               | 0.000015 |
| freeing items                | 0.000023 |
| logging slow query           | 0.000003 |
| logging slow query           | 0.000048 |
| cleaning up                  | 0.000004 |
+------------------------------+----------+

That is, all the time is spent in end.

The documentation says:

• end

  This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

How can such a simple statement spend such a long time in this state?

Guenna

I am curious to know how many rows do you have in this table. and also do you have more updates/inserts/deletes or more selects in the application using this table? – Entree

Around 300000 rows, and I don't know the read/write ratio. – Guenna

Answer 1

It turns out that the problem is the query cache.

If I disable it with

SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = 0;

the problem goes away.

It must be invalidating query cache entries that causes the query to hang for such a long time.

Guenna