Поможет ли суррогатный ключ увеличить скорость вставки в таблицу?

Question

[antiiii]

Есть таблица:

ID char(32)
Date int
....
primary key (ID, Date)
пара неуникальных ключей
и партиции по Date по годам

ID - 32-символьный уникальный хеш.
Avg row length показывает 369.
На сейчас в таблице ~90 млн строк.
Данные из неё не удаляются, только вставляются новые.

Вставка в таблицу иногда занимает дикое количество времени (в среднем ~секунда на строку, на некоторых строках прыгает до 5-10 секунд) и иногда падает с Lock wait timeout exceeded.

Насколько я понимаю, происходит это из-за того, что для вставки нового ID движку нужно перестроить индекс, что на таких объемах накладно.

Я попробовал на тестовой машине - добавил суррогатный auto increment, пару (ID, Date) сделал просто unique - это ускорило вставку на ~40%, и самый медленный запрос упал с 5-10 секунд до 0.8-1 сек. На боевой машине и боевой базе протестировать эти изменения, понятно, не могу.

Собственно, спрашиваю только чтобы уточнить наверняка: правильно ли я решаю эту проблему и действительно ли суррогатный ключ в таких ситуациях даёт прирост скорости?

Comments

[galaxy]

Вставка в таблицу иногда занимает дикое количество времени (в среднем ~секунда на строку, на некоторых строках прыгает до 5-10 секунд) и иногда падает с Lock wait timeout exceeded.

кул стори че-то... Это что, myISAM?

Лишний индекс даже в теории не может увеличить скорость вставки, только замедлить.

[antiiii]

galaxy, нет, это Innodb.

> Лишний индекс даже в теории не может увеличить скорость вставки, только замедлить.
Тогда почему на тестовых данных стало быстрее?

[ThunderCat]

antiiii, Возможно объем данных в тестовой бд меньше, и соответственно тупо индексы перестраиваются быстрее. В идеале для быстрой вставки индексы наоборот надо сокращать, например созданием 2 таблиц и переносом пакетов из одной неиндексированной таблицы в индексированную, одной большой транзакцией, ну или другие танцы выполнять...
Еще вопрос - что заставило создавать именно чаровый ключ, еще и с такой длиной? Могу понять когда инт на 4млрд строк может накопится, статистика там разная, вся фигня, но уж в бигинт то точно все поместится...

[antiiii]

ThunderCat, > что заставило создавать именно чаровый ключ, еще и с такой длиной

Такие данные приходят из внешнего источника, и по этому ключу они дальше связываются с другими (и у меня, и в источнике) - поэтому пришлось плясать вокруг него.

А первичным ключом он стал 5 лет назад, когда в таблице было 900 тыс. строк и никто не думал, что она вырастет в 100 раз.

> объем данных в тестовой бд меньше, и соответственно тупо индексы перестраиваются быстрее
Данных там действительно меньше (в ~10 раз), но я же тестировал её в обоих вариантах: с ключом char(32) и с ключом int auto increment.

[FanatPHP]

antiiii, а почему вы решили что разница будет пропорциональной?

[antiiii]

FanatPHP, я посмотрел на цифры на одной машине и одинаковых объемах - но разных ключах, разделил одну на другую, получил 40% прироста скорости.

Или вы имеете в виду, пропорциональной на большем количестве строк? Тут я как раз не уверен, потому и задал этот вопрос.

[ThunderCat]

antiiii,

Такие данные приходят из внешнего источника, и по этому ключу они дальше связываются с другими (и у меня, и в источнике) - поэтому пришлось плясать вокруг него.

Что мешало оставить его вторичным ключом и добавить цифровой индекс, по нему и связывать внутри своей системы, а наружу общаться по этому строковому ключу? Понимаю что задним умом все гении, но такое решение выглядит логичнее чем использование длинного чар кея в качестве примари... Собственно и сейчас можно еще попробовать добавить в качестве примари бигинт и в связанных таблицах внести его как ключ. Да, это займет время, но структура хоть будет пошустрее.

[antiiii]

ThunderCat, так именно в этом и состоит вопрос: стоит ли убирать первичный ключ с char(32) и даст ли это прирост скорости на вставку? Потому что просто так сломать продакшн и потратить время на переделку всей базы я не могу.

Потому что по тестам выходит, что вроде бы да.
А по ответам выходит, что не должно.

Answer 1

[index0h]

Запросы делались по одному, без оборачивания в транзакцию (насколько я понимаю, если запрос один - то и транзакция бесполезна, независимо от кол-ва строк?).

Не совсем. При вставке без явной транзакции, во внутрянке она таки создаетется. С коммитом транзакции происходит перерассчет индексов. Т.е. вставляя много строк отдельными запросами вы будете пересчитывать индексы каждый раз. Обвернув все в транзакцию, или вставляя пачкой индексы будут пересчитываться реже, что ускорит выполнение.

Кроме этого, batch-запросы с auto increment имеют намного меньшее max время вставки: 1,5 сек против 11,4 (да, 11 секунд на вставку 20 строк).

Верно, это вполне ожидаемый результат.

правильно ли я решаю эту проблему и действительно ли суррогатный ключ в таких ситуациях даёт прирост скорости?

Не совсем. Индекс не ускоряет вставку, что обычный, что комплексный, он ускоряет поиск. Чем меньше индекс - тем быстрее он рассчитается.
Очень похоже на то, что у вас проблема несколько иного характера. Индекс хранится в ОЗУ, тормоза на вставке вероятно у вас за счет того, что индекс не влазит в буфер движка. Это значит, что при вставке БД занимается вытеснением из буфера одних индексов другими, а это уже работа с файловой системой, что очень медленно.
В таких ситуациях стоит двигаться в сторону уменьшения размера самого индекса.

Comments

[antiiii]

> Индекс хранится в ОЗУ, тормоза на вставке вероятно у вас за счет того, что индекс не влазит в буфер движка.

Выглядит действительно так: show table status показывает размер индекса в 17,8 ГБ, при том что innodb_buffer_pool_size = 15G.

То есть, тут два варианта:
1. либо докинуть ОЗУ;
2. либо уменьшить индекс, правильно?

[FanatPHP]

antiiii, и то и другое.

[index0h]

antiiii, FanatPHP говорит верно. Если у вас уже все прям так - лучше И докинуть ОЗУ И порезать индекс.
Как вариант - стоит в принципе часть данных вынести в архив с самым минимумом индексов, грубо говоря PK и все.

[index0h]

Размер буффера тоже иеет смысл приподнять

Answer 2

[antiiii]

Сделал ещё ряд тестов.

Точно так же, две таблицы:
- первичный ключ: char(32)+date, партицирование по годам + 3 дополнительных индекса по полям;
- первичный ключ: auto increment+date, партицирование по годам, уникальный ключ char32+date + 3 дополнительных индекса по полям;

В каждой таблице изначально 5 млн строк, в каждом тесте вставлялось по 100 000 новых.
Строки вставлялись по одной и пачками по 20 (среднее кол-во на продакшене за один вызов скрипта).
Запросы делались по одному, без оборачивания в транзакцию (насколько я понимаю, если запрос один - то и транзакция бесполезна, независимо от кол-ва строк?).

В результате получено ускорение в 2,6 раз:
- 0,010 сек с суррогатным и 0,026 сек с char(32) построчно;
- 0,003 и 0,007 по 20 строк за запрос.

Кроме этого, batch-запросы с auto increment имеют намного меньшее max время вставки: 1,5 сек против 11,4 (да, 11 секунд на вставку 20 строк).

Также при заполнении таблиц тестовыми данными (5 млн строк на каждую), auto increment показал в ~2.3 раза больше скорость и в целом скорость держалась +- стабильной, когда как для первичного char(32) ключа она постоянно падала вместе с заполнением таблицы.

В целом можно сказать, что да, суррогатный первичный ключ действительно сильно ускоряет вставку по сравнению с char(32).

Answer 3

[neol]

Если речь про Innodb, то

Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

https://dev.mysql.com/doc/refman/5.7/en/innodb-ind...

В вольном переводе каждый из ваших вторичных индексов будет содержать в себе значение из primary key (PK) и чем длиннее PK, тем больше все индексы в таблице.
Вполне вероятно, что при таких раскладах PK даже из 8 байтового bigint сильно выиграет у 36 байтового char+int (а то и 102, если с дуру сделали ID utf8mb4). Как минимум по занимаемому месту.

Answer 4

[rPman]

Если железом (индексы нужно размещать в отдельном таблеспейсе на отдельном физическом устройстве, желательно ssd) и настройками все что можно оптимизировал, то усложняй логику.

Например сделай дополнительную таблицу, в которую будешь набирать изменения до определенного количества (или лучше по времени, раз в сутки/час/...) и потом одним запросом переносить, пакетом перестройка индекса идет быстрее.

Логично что и поиск/чтение данных нужно производить сразу из двух этих таблиц, т.е. замедление но незначительное. Если допускать дублирование данных в таблицах, то перенос будет простым и не сильно нагружающим базу (не надо лочить таблицы)

p.s. решать задачу нужно исходя из того как данные читаешь и как в них делаешь поиск. Именно от этого зависит каким образом можно реорганизовать хранение или даже отказаться от mysql

к примеру один из способов (а у вас он прямо напрашивается - маленький пакет данных и временные ряды) - если чтение данных такое же последовательное как и запись (например запросить данные на интервале) то можно при записи упаковывать данные в одну запись, по какому-то временному критерию (например данные за сутки), сериализовав их, само собой текущий интервал данных хранить классическим способом, и по заполнению, переносить.