Slow UPDATE on MySQL/MariaDB Primary Key?

Question

MySQL

Request:

UPDATE `cart` SET `user_id` = NULL, `completed` = 0 WHERE `id` = 6948;
Query OK, 0 rows affected (1.21 sec)
Rows matched: 1  Changed: 0  Warnings: 0

It can be seen that 0 lines are affected, however, 1210 ms is awesome. SELECT of this row is always in 0ms.
Table size (6,354 rows).

describe cart;
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11)    | YES  | MUL | NULL    |                |
| completed | tinyint(1) | NO   |     | 0       |                |
+-----------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

show index from cart;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cart  |          0 | PRIMARY  |            1 | id          | A         |        6386 |     NULL | NULL   |      | BTREE      |         |               |
| cart  |          1 | user_id  |            1 | user_id     | A         |        2128 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Here is the profiling piece of that query that is at fault:

| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file   | Source_line |

| query end            | 2.502555 | 0.003000 |   0.000000 |                88 |                   8 |             0|           136 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        5093 |

Server version: 10.0.17-MariaDB-1~wheezy

Answer 1

In general, the problem was in OpenVZ and disk operations scheduling. The base turned out to be nothing to do with it, the OS didn’t give it a go.

Answer 2

Use profiling to see exactly what time is being spent on