Поведение INSERT… ON DUPLICATE KEY UPDATE в крайней ситуации

Несколько недель назад, я работал над проблемой клиента, который столкнулся с падением производительности БД и даже ее отказами, которые происходили приблизительно каждые 4 недели. Ничего особенного в окружении, в железе или запросах. В сущности, большей частью базы данных была одна таблица, в которой присутствовали, кроме прочего, INT AUTO_INCREMENT PRIMARY KEY и UNIQUE KEY.

Запросы, работающие с этой таблицей, почти все были типа INSERT ... ON DUPLICATE KEY UPDATE (далее — INSERT ODKU), где столбцы, перечисленные в INSERT, соответствовали столбцам с UNIQUE KEY. И выполнялись они с частотой, приблизительно 1500-2000 запросов в секунду, непрерывно 24 часа в сутки. Если вы хороши в математике, то наверное, уже догадались в чем дело.

Для целей дискуссии мы будем использовать следующую таблицу, как иллюстрацию к ситуации:

CREATE TABLE update_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL,
  host_id TINYINT UNSIGNED NOT NULL,
  last_modified TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY(username)
) ENGINE=InnoDB;

Теперь представим следующую последовательность событий:

(root@localhost) [test]> INSERT INTO update_test (username, host_id, last_modified) VALUES ('foo',3,NOW());
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> select * from update_test;
+----+----------+---------+---------------------+
| id | username | host_id | last_modified       |
+----+----------+---------+---------------------+
| 1  | foo      |       3 | 2012-10-05 22:36:30 |
+----+----------+---------+---------------------+

Ничего необычного, да? Мы вставили один ряд в пустую таблицу и если мы сделаем SHOW CREATE TABLE, то мы увидим что счётчик AUTO_INCREMENT сейчас имеет значение 2. Если мы сделаем INSERT ODKU в эту таблицу, то увидим следующее:

(root@localhost) [test]> insert into update_test (username,host_id) values ('foo',1) on duplicate key update last_modified=NOW();
Query OK, 2 rows affected (0.00 sec)

(root@localhost) [test]> select * from update_test;
+----+----------+---------+---------------------+
| id | username | host_id | last_modified       |
+----+----------+---------+---------------------+
|  1 | foo      |       3 | 2012-10-05 22:58:28 |
+----+----------+---------+---------------------+
1 row in set (0.00 sec)

И теперь, даже если мы не вставили новый ряд, наш счётчик AUTO_INCREMENT вырос до 3. Это, вообще-то, ожидаемое поведение. InnoDB проверяет ограничения в том порядке, в котором они были определены, и PRIMARY KEY всегда идёт первым. Поэтому MySQL проверяет наш INSERT, видит, что следующее значение AUTO_INCREMENT доступно и использует его, но потом, проверяет UNIQUE KEY и находит нарушение, поэтому вместо INSERT делает UPDATE. Если мы посмотрим счётчики handler status, мы можем увидеть, что был один запрос на вставку, который завершился неудачей, и один запрос на обновление, который прошёл успешно (это объясняет, почему изменены 2 ряда, а не 1).

(root@localhost) [test]> show status like 'handler%';
 *** some rows omitted ***
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_rollback           | 0     |
| Handler_update             | 1     |
| Handler_write              | 1     |
+----------------------------+-------+

В этом месте вы можете подумать — «Ну и что?». Давайте вернёмся к нашему клиенту. 1500 INSERT ODKU в секунду, непрерывно 24 часа в сутки. PRIMARY KEY их таблицы такой же, как я использовал в демонстрационной таблице — INT UNSIGNED. Считаем. Максимальное значение для INT UNSIGNED – это 4294967295. Делим это на 1500 запросов в секунду и делим на 86400, что является количеством секунд в сутках, и мы получаем 33.1 дней, или чуть больше чем 4 недели. Совпадение? Я так не думаю. Итак, что именно происходит, когда мы выходим за пределы значения? Некоторое поведение может вас удивить. Вернёмся к нашей демонстрационной таблице и вставим в нее ряд с максимальным значением для столбца с AUTO_INCREMENT, а потом вставим ещё один.

(root@localhost) [test]> insert into update_test (id,username,host_id) values (4294967295, 'bar', 10);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> flush status;
(root@localhost) [test]> insert into update_test (username,host_id) values ('baz', 10);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

(root@localhost) [test]> show status like 'handler%';
 *** some rows omitted ***
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_rollback           | 1     |
| Handler_write              | 1     |
+----------------------------+-------+

Итак, мы попытались вставить ряд и это не вышло, т.к. AUTO_INCREMENT уже имел максимальное значение и запрос не прошёл. Но, что случится если мы попробуем сделать INSERT ODKU? Сначала, посмотрим что у нас в таблице:

(root@localhost) [test]> select * from update_test;
+------------+----------+---------+---------------------+
| id         | username | host_id | last_modified       |
+------------+----------+---------+---------------------+
|          1 | foo      |       3 | 2012-10-05 22:58:28 |
| 4294967295 | bar      |      10 | NULL                |
+------------+----------+---------+---------------------+
2 rows in set (0.00 sec)

(root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW();
Query OK, 2 rows affected (0.00 sec)

Выглядит нормально, да? 2 ряда изменено, очевидно, что для ряда который соответствовал условию username = "foo", были обновлены host_id и last_modified, и мы можем радоваться. К сожалению, это не так:

(root@localhost) [test]> select * from update_test;
+------------+----------+---------+---------------------+
| id         | username | host_id | last_modified       |
+------------+----------+---------+---------------------+
|          1 | foo      |       3 | 2012-10-05 22:58:28 |
| 4294967295 | bar      |       7 | 2012-10-05 23:24:49 |
+------------+----------+---------+---------------------+
2 rows in set (0.00 sec)

Опа, обновлён был последний ряд, у которого id равен максимальному значению нашего AUTO_INCREMENT, а UNIQUE KEY на столбце username был проигнорирован.

Теперь мы можем легко понять в чем проблема клиента, чья база данных послужила вдохновением для этого поста. 1500 запросов в секунду, пытающихся заблокировать и обновить один и тот же ряд, ни к чему хорошему не приведут. Конечно, есть простое решение — изменить тип данных AUTO_INCREMENT-столбца c INT на BIGINT.

Оказывается, такое поведение документировано. Мануал говорит, что наш INSERT ODKU на таблице с несколькими уникальными индексами, будет эквивалентен запросу UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE id = 4294967295 OR username = "foo" LIMIT 1 и конечно оптимизатор скорее выберет PRIMARY, нежели вторичный UNIQUE.

Чему же мы здесь научились?

• Исчерпать AUTO_INCREMENT намного легче, чем кажется. Реальная таблица клиента содержала менее 500k рядов.
• Использование SIGNED типов для AUTO_INCREMENT — почти всегда плохая идея. Вы теряете половину диапазона доступных значений.
• Интуиция, подобно законам физики, часто подводит в крайних ситуациях.