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

Original author: Ernie Souhrada
  • Translation
Несколько недель назад, я работал над проблемой клиента, который столкнулся с падением производительности БД и даже ее отказами, которые происходили приблизительно каждые 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 — почти всегда плохая идея. Вы теряете половину диапазона доступных значений.
  • Интуиция, подобно законам физики, часто подводит в крайних ситуациях.

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 38

    +7
    А если бы не было в этой таблице автоинкрементного поля, то проблема бы и не возникла. В целом пример показательный.
      0
      Действительно, зачем тут autoincrement?
        0
        Наверное разработчик в свое время прочитал, что «к любой таблице нужно обязательно навесить суррогатный PK с автоинкрементом».
        0
        Ага, можно было бы вынести данные, которые обновляются в таблицу без автоинкримента.
        +2
        В других СУБД есть конструкции вроде UPDATE OR INSERT INTO… или MERGE а вместо AUTOINCREMENT юзать SEQUENCE.
          +1
          Могу вас поздравить с полезным опытом. BIGINT нормальный тип данных и следовало его учесть в архитектуре.
          А вобще вставка овер 1000 запросов в секунду плохая идея. Хорошая это накапливать 2 секунды данные и делать вставку одним запросом предварительно отключив ключи. И не забыть их включить потом. Это я вам из опыта говорю.
            +3
            Операция Удалить индексы / Вставить 3k записей / Создать индексы при табличке на 500k записей может привести к весьма неоднозначным последствиям. Например, если операция не уложится в 2 секунды и на нее наложится следующая. Хотя подход, конечно же, правильный. Вопрос лишь в требованиям к актуальности данных — может быть там была жесткая необходимость иметь в любой момент актуальные данные.
              0
              имеется таблица в 10 миллиардов записей, с обычными и полнотекстовыми индексами — если не отключать ключи перед вставкой, то можете себе представить время вставки пары тысяч новых строк.
                0
                Про 10 миллиардов не скажу, но на таблице в 50 млн. записей с тремя обычными индексами без отключения индексов запрос на вставку 1000 записей отрабатывает примерно за 200 мс.
                  0
                  с обычными и полнотекстовыми индексами

                  значит MyISAM
                  В InnoDb ALTER TABLE приводит к полному пересозданию таблицы.
                    0
                    И в 5.5+?
                      0
                      не в курсе. как-то попадалась информация, что часть модификаций планируется делать без пересоздания таблицы, какие изменения и в какой версии не помню. возможно что сейчас какие-то изменения происходят без пересоздания, сомневаюсь что все.
                        0
                        В 5.5+ создание и удаление индекса в InnoDB не приводит к пересозданию таблицы.
                        В 5.1, кстати, тоже, но только при использовании InnoDB Plugin.
                      +1
                      Мне одному тут кажется, что убрать что-то? Из:
                      * RDBMS
                      * 10 миллиардов записей
                      * полнотекстовые индексы
                        +2
                        * того кто все это проектировал
                        0
                        Что-то мне подсказывает что у Вас интенсивность запросов чуть поменьше чем 1500/сек.

                        Исходя из опыта, наиболее эффективным в таком случае является следующий подход (мало ли, вдруг кому интересно будет):
                        1. Сделать идентичную табличку, но без индексов вообще. Назовем ее tempA.
                        2. В течение разумного интервала времени заполняем эту табличку данными.
                        3. Переименовываем табличку tempA в tempB, создавая попутно пустую tempA
                        4. Создаем все необходимые индексы в tempB (на уже имеющихся данных)
                        5. Переименовываем tempB в рабочую таблицу.

                        Плюсы:
                        1. выборки идут из рабочей таблицы, в которую ничего не вставляется.
                        2. вставки идут в таблицу без индексов, причем не нужно создавать никаких буферов на время пересоздания индексов
                        3. переименование таблицы происходит практически мгновенно — переименование файлов, поэтому ни поток записи ни поток чтения практически не прерывается.
                        4. нагрузка на сервер становится более предсказуемой, меньше вероятность что сервер упадет при ее увеличении.

                        На что нужно обратить внимание: время накопления данных в tempA было ощутимо больше, чем время создания индекса в tempB. Этот момент лучше отслеживать по существованию таблицы tempB, если она существует то tempA в tempB не переименовывается и данные продолжают накапливаться в tempA.

                        Хотя это, конечно же, не панацея.
                        +1
                        Зачем их удалять? ALTER TABLE DISABLE KEYS имелось ввиду, я так понимаю
                          0
                          Увы, только удалять. DISABLE KEYS не работает с InnoDB и не распространяется на уникальные индексы:
                          «This feature can be activated explicitly for a MyISAM table. ALTER TABLE… DISABLE KEYS tells MySQL to stop updating nonunique indexes»
                      0
                      Отличный материал, спасибо.

                      Сдается мне что разработчик просто не знал что INSERT ODKU увеличивает счетчик AUTO_INCREMENT при апдейте существующей записи.
                        0
                        только для InnoDB, myisam поступает логически правильно.
                        0
                        Складывается впечатление, что ON DUPLICATE KEY UPDATE тот еще костыль.
                        Не в курсе как это сейчас, но года 1.5-2 назад имели проблему с этой конструкцией при обработке запросов в параллельных транзакциях, т.е. не в режиме autocommit. Проблема проявлялась при одновременном выполнении запросов с одинаковым значением уникального поля в двух разных транзакциях. Подробностей уже не помню, а по сути получалось что одна транзакция изменяла незафиксированные данные другой.
                          +1
                          И теперь, даже если мы не вставили новый ряд, наш счётчик AUTO_INCREMENT вырос до 3. Это, вообще-то, ожидаемое поведение.

                          Интересно, почему такое поведение считается ожидаемым. Т.е. объяснение из статьи понятно, но это скорее объяснение некоего неожиданного эффекта, а не наоборот
                            0
                            > Использование SIGNED типов для AUTO_INCREMENT — почти всегда плохая идея. Вы теряете половину диапазона доступных значений.

                            Разве?
                            Вот тут написано по-другому:
                            BIGINT[(M)] [UNSIGNED] [ZEROFILL]

                            A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.


                            Похоже в исходной статье опечатка, должно быть UNSIGNED.

                            И даже вот такая ненавязчивая рекомендация использовать правильный тип для первичного ключа:
                            SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
                              0
                              Может Вам так нагляднее будет:

                              18446744073709551615 - unsigned
                               9223372036854775807 - signed
                              

                              В положительном диапазоне unsigned в 2 раза больше чем signed. The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column (мануал) — следовательно, мы теряем половину диапазона.
                                0
                                Точно, перепутал названия Signed и Unsigned :)
                                  +1
                                  Рассуждение правильное, но если половина диапазона начинает играть роль, возможно, размерность типа выбрана неправильно. Либо вообще механизм работы с базой.
                                    0
                                    Скажем так, использовать UNSIGNED в автоинкрементных полях это все равно что смотреть в обе стороны, переходя одностороннюю дорогу. Вроде как и не зачем, а с другой стороны лишним не будет.
                                0
                                На будущее ещё один совет: всегда указывайте AUTO_INCREMENT=1 для создаваемых таблиц, если он там используется. На некоторых серверах я получил ошибку Failed to read auto-increment value from storage engine. К проблеме это не относится, но может испортить настроение.
                                  0
                                  Всегда нельзя.
                                  Как любая панацея такой подход может вызвать проблемы.
                                  Иногда бывают такие жуткие архитектуры где распределенная база подразумевает, что автоинкремент будет работать одинаково на разных серверах.
                                  Да, я знаю, что нужно в таких случаях использовать другое решение, например GUID, но если это уже готовое чужое решение, то нужно думать.
                                    0
                                    Ваше «всегда нельзя» означает именно «никогда не использовать»?
                                      0
                                      )))
                                      Да, немного не однозначно вышло.
                                      Просто у Вас было «всегда указывайте», я же хотел сказать, что не всегда, а обдуманно.
                                        0
                                        В зависимости от системы. Я не упоминал именно mysql подразумевая, что лишь о нём речь. Но вы правы, конечно.
                                  0
                                  >В сущности, большей частью базы данных была одна таблица
                                  >Запросы, работающие с этой таблицей, почти все были типа INSERT… ON DUPLICATE KEY UPDATE

                                  То есть база, по сути, представляет из себя один большой map. NOSQL спешит на помошь!
                                    0
                                    в 2017 году юзаю сервер MySQL 5.6.25 и в нём фича с приращением автоинкремента при INSERT ODKU видимо отсутствует. Пробовал и REPLACE, такое же поведение. Если обновляются существующие строки где ключи UNIQUE остаются прежними, то автоинкремент не увеличивается, а вот если вместе с тем вставляется новая строка, тогда автоинктремент увеличивает свое значение.

                                    INSERT INTO table (field_1, field_2) VALUES (1, foo), (2, bar), (3, baz) ON DUPLICATE KEY UPDATE field_2 = VALUES (field_2);
                                    
                                    REPLACE INTO table (field_1, field_2) VALUES (1, foo), (2, bar), (3, baz);
                                    

                                      0
                                      Вряд ли. Покажите схему.
                                        0
                                        создаю таблицу
                                        --
                                        -- База данных: `test_db`
                                        --
                                        
                                        -- --------------------------------------------------------
                                        
                                        --
                                        -- Структура таблицы `table_test`
                                        --
                                        
                                        CREATE TABLE `table_test` (
                                          `field_1` int(10) UNSIGNED NOT NULL COMMENT 'Уникальный идентификатор',
                                          `field_2` varchar(255) NOT NULL COMMENT 'Какое-то значение'
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Тестовая таблица';
                                        
                                        --
                                        -- Дамп данных таблицы `table_test`
                                        --
                                        
                                        INSERT INTO `table_test` (`field_1`, `field_2`) VALUES
                                        (1, 'foo'),
                                        (2, 'bar'),
                                        (3, 'baz');
                                        
                                        --
                                        -- Индексы таблицы `table_test`
                                        --
                                        ALTER TABLE `table_test`
                                          ADD PRIMARY KEY (`field_1`);
                                        
                                        --
                                        -- AUTO_INCREMENT для таблицы `table_test`
                                        --
                                        ALTER TABLE `table_test`
                                          MODIFY `field_1` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор', AUTO_INCREMENT=4;
                                        



                                        теперь делаю

                                        INSERT INTO table_test (field_1, field_2) VALUES (1, 'foo2'), (2, 'bar2'), (3, 'baz2') ON DUPLICATE KEY UPDATE field_2 = VALUES (field_2)
                                        


                                        в итоге значение автоинкремента не меняется
                                        --
                                        -- База данных: `test_db`
                                        --
                                        
                                        -- --------------------------------------------------------
                                        
                                        --
                                        -- Структура таблицы `table_test`
                                        --
                                        
                                        CREATE TABLE `table_test` (
                                          `field_1` int(10) UNSIGNED NOT NULL COMMENT 'Уникальный идентификатор',
                                          `field_2` varchar(255) NOT NULL COMMENT 'Какое-то значение'
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Тестовая таблица';
                                        
                                        --
                                        -- Дамп данных таблицы `table_test`
                                        --
                                        
                                        INSERT INTO `table_test` (`field_1`, `field_2`) VALUES
                                        (1, 'foo2'),
                                        (2, 'bar2'),
                                        (3, 'baz2');
                                        
                                        --
                                        -- Индексы таблицы `table_test`
                                        --
                                        ALTER TABLE `table_test`
                                          ADD PRIMARY KEY (`field_1`);
                                        
                                        --
                                        -- AUTO_INCREMENT для таблицы `table_test`
                                        --
                                        ALTER TABLE `table_test`
                                          MODIFY `field_1` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор', AUTO_INCREMENT=4;
                                        



                                        Если я не правильно понял на счет схемы, то подскажите как её посмотреть
                                          0
                                          Статья описывает такое поведение.
                                          Т.е. может возникнуть ситуация, когда вставка не работает потому, что автоинкремент дошел до максимума, хотя в таблице всего пара записей.
                                      0
                                      Я понял в чем у вас ситуация. Запросом упоминаются не все UNIQUE поля, в частности не упоминается поле id, которое автоинктрементное и уникальное.

                                      Соответственно происходит накрутка счетчика и если id упомянуть в запросе, то прироста нет.

                                      Как бы там ни было, мне ваша статья позволила более детально понять происходящее, а нашел я её когда потребовалось делать UPDATE множества строк, не в цикле, а одним запросом.

                                      Получается отлично. Спасибо вам за статью.

                                      Only users with full accounts can post comments. Log in, please.