Приветствую! У каждого разработчика рано или поздно наступает момент, когда появляется необходимость работать с большими базами данных. В мире таблиц весом более 5 гигабайт действуют немного иные законы "физики", нежели в маленьких табличках: приходится заботиться о тех вещах, о которых раньше даже и не задумывался. Сегодня я поделюсь трюком, который поможет быстро удалить много данных с таблицы MySQL с движком InnoDB.
Проблема
У нас на руках имеется: Таблица с сообщениями `messages` за последние 10 дней. Каждый день в неё сваливается примерно миллион новых записей. По прошествии 10-ти дней у нас скрипт вызываемый кроном перетаскивает сообщения в таблицу `messages_history`. Нам поставили задачу уменьшить срок хранения сообщений в основной таблице с 10 дней до трёх.
Решение "В лоб"
//Собираем все сообщения
$oldMessages = Message::findAll(new DBWhereParamsType([
'where' => [
'time <=' => Carbon::now()->subDays(self::LIFETIME_OF_MESSAGES)->format(),
],
]));
//Собираем ID всех сообщений, которые достали
$oldMessagesIDs = [];
foreach ($oldMessages as $oldMessage) {
$oldMessagesIDs[] = $oldMessage->id;
}
//Вставляем полученные старые сообщения в таблицу с историей сообщений
$res = MessageHistory::bathInsert($oldMessages);
//Если insert прошёл успешно, удаляем из основной таблицы старые сообщения
if ($res === true) {
//Эта конструкция под капотом собирает запрос вида
// DELETE * FROM `messages` WHERE `id` IN (1, 2, 3...)
MessageD::deleteWhere(new DBWhereParamsType([
'where' => [
'id' => $idsList,
],
]));
}Вроде бы всё логично. Запускаем с лимитом 50000, и что же мы видим?
Удаление заняло более минуты. Но почему же так? Давайте разбираться
Блокировка таблиц
При удалении из таблицы InnoDB происходит её блокировка на время выполнения транзакции. Когда мы посылаем большой запрос на удаление базе, она блокирует таблицу `messages`, и это довольно сильно затягивает процесс, так как при каждом обращении других скриптов к этой таблице будет создаваться её слепок.
Что с этим делать?
DELETE - это один из немногих случаев, когда оптимизация должна идти не на уменьшение количества запросов, а наоборот, на их увеличение и разбивку на маленькие. И поможет нам в этом php функция array_chunk. Она разобьёт наш $oldMessagesIDs на чанки, по которым будет удобно пройтись foreach'ом:
if ($res === true) {
//Разбиваем массив на чанки
$oldMessagesIDsChunks = array_chunk($oldMessagesIDs, 100);
//Проходимся по каждому из них
foreach ($oldMessagesIDsChunks as $idsList) {
MessageD::deleteWhere(new DBWhereParamsType([
'where' => [
'id' => $idsList,
],
]));
}
}Запускаем с лимитом 50000...
Вуаля! Итак, давайте подытожим, что у нас случилось:
Таблица не блокируется одной транзакцией на долгое время
Среднее время выполнения скрипта уменьшилось примерно в 5 раз
Надеюсь, этот небольшой трюк поможет Вам однажды настроить скрипт удаляющий большие объёмы данных :-) Удачи!
Комментарии (43)
baldr
17.11.2022 13:24+9А где транзакция-то? Это где такой код пишут?
Я правильно понял что вы засасываете в память из базы десяток миллионов записей, причем в виде ORM-обьектов, а потом вставляете их в другую таблицу в той же базе? Ничего тут оптимизировать нельзя? Подсказка: SQL.
snobit
17.11.2022 14:46+6Это же слишком сложно, написать INSERT ... SELECT.
Я бы предложил вынести все в микросевис, который будет удалять сообщения. Если сообщений станет много, можно в кубере поднять десяток инстансов и все.
michael_v89
17.11.2022 20:35+1INSERT… SELECT на больших нагрузках может вызывать взаимные блокировки и сбои транзакций.
MaryRabinovich
17.11.2022 13:29-1Оптимизируем больше - можно сразу только айдишники выбирать. Вместо того, чтобы выбрать сначала записи полностью, а дальше вытаскивать в массив айдишники через foreach, выбрать сразу только колонку ID.
(хотя нет, это бессмысленно - вы всё равно выбираете сообщения полностью, чтобы переложить в архив)
polar_yogi
17.11.2022 13:36+5Почитайте хотя бы https://mariadb.com/kb/en/big-deletes/
И про isolation levels тоже.
А то такую фигню пишете что даже комментарий сложно написать.baldr
17.11.2022 13:39+9Все сеньоры релоцировались и не успели рассказать джунам про транзакции, но забыли на столе книжку про ORM.
vasilisc
17.11.2022 13:52InnoDB не блокирует таблицу. Почитайте что действительно происходит при удалении большого количества записей.
mafia8
17.11.2022 14:03Что мешает удалить таблицу messages и снова создать её?
baldr
17.11.2022 14:36Во-первых, DDL-операции в MySQL вызывают неявный коммит, то есть транзакция закроется и все пишущие клиенты получат ошибки.
Во-вторых, насколько я понял, надо не все записи удалить, а только часть старше нескольких дней.
varanio
17.11.2022 14:09+5Почему нельзя сделать просто запрос DELETE ... WHERE time < ...
IgorAlentyev
19.11.2022 16:24Потому что нужно еще и перенести сообщения в другую таблицу. И суть проблемы не изменится, операция удаления все еще будет боттлнеком.
baldr
19.11.2022 17:12Нет, не будет. Проблема не в удалении, а в условии "where id in (...)" - когда передаются 50к идентификаторов и база начинает все их искать сканированием, пусть даже и по индексу.
Давайте я еще раз объясню как должен выглядеть правильный способ, полностью на стороне базы в SQL:
открываем транзакцию с уровнем изоляции REPEATABLE READ
выполняем INSERT INTO history_table (...) SELECT ... FROM messages_table WHERE message_time < '2022-11-01' (дата для примера, можно смещение от сегодняшней взять)
Выполняем DELETE FROM messages_table WHERE message_time < '2022-11-01'
COMMIT
Всё! Не надо копировать миллионы записей из базы на сервер, переводить их в объекты или массивы на PHP, потом из них выбирать id, потом из них формировать какие-то слайсы и новые SQL-запросы на миллионы вставок, а потом еще и пачку удалений по id.. Про транзакции в статье не сказано ни слова и я вообще не уверен что они там были.
Автор очень мудро не появляется больше в комментариях и не пытается научить нас еще каким-то новым "PHP-трюкам".
IgorAlentyev
19.11.2022 17:47Ну, вы ошибаетесь. Проблема из за лога транзакций при удалении, который пишется на диск. Почитайте остальные комментарии, там есть ссылка на сайт mariadb, где описывается эта проблема и как с ней бороться.
То что вы описали действительно увеличит скорость, но несущественно, относительно времени именно удаления, так что это экономия на спичках.
Akina
17.11.2022 14:50+7А что мешает сделать таблицы партиционированными, например, посуточно? Тогда вместо удаления записей просто удаляем партицию - практически мгновенная операция.
Но самое главное - непонятно, зачем вообще мучать PHP, когда всё описанное элементарно и гораздо менее нагрузочно выполняется на стороне MySQL.
FanatPHP
17.11.2022 15:35А диагностику проблемы он правильно описал? Про создание слепка, которое замедляет удаление?
Akina
17.11.2022 17:36Вот даже не вчитывался в эти подробности. Привык, что те, кто работает из языка или фреймворка, уровень СУБД просто "пробежали по верхам".
На сколько это верно, и верно ли вообще - надо копать internals. Но мне сентенция о создании слепка на каждый чих кажется крайне сомнительной.
IgorAlentyev
20.11.2022 12:15Так может быть стоит разобраться, о чем идет речь, прежде чем писать своё мнение? Если так сделать, то окажется что и PHP и ORM тут вообще не при чем, и проблема как раз таки в том, как работает DELETE в MySql и его форках.
IgorAlentyev
19.11.2022 16:25Что вам пхп плохого сделал? Там боттлнек в удалении, где и так sql.
IgorAlentyev
20.11.2022 12:12Господа дизлайкеры, прежде чем миносовать, рекомендую ознакомиться с постом, комментариями и вот этой ссылкой - https://mariadb.com/kb/en/big-deletes/, где написано, в чем заключается проблема и какие есть варианты решения.
v3shin
17.11.2022 22:45Не понял про лимит 50000: вы же, вроде, в коде не количество прописываете, а дату.
А если добавлять разбивку по 100 сообщений, почему бы сразу не запускать скрипт с лимитом в 100?
yeahga
17.11.2022 22:46Берём id последнего сообщения на удаление (если они по порядку), далее:
DELETE FROM messages WHERE id <= ?;
baldr
18.11.2022 13:35А вот это антипаттерн. Не надо считать id только растущим. Даже если это обычно так в большинстве случаев. Где-то может быть долгая транзакция, которая выделила себе id раньше всех, а закрылась позже всех. Где-то база может решить оптимизировать индексы и последовательности..
Akina
18.11.2022 19:55Я бы даже сказал - не надо считать, что значение id не может быть присвоено явным заданием, минуя генератор.
yeahga
18.11.2022 22:38+1Спасибо, буду иметь в виду. А что, если пометить флагом сообщения на перенос в историю, далее смотреть на этот флаг?
UPDATE `messages` SET `to_history` = 1 WHERE `time` <= ?; INSERT INTO `messages_history` SELECT * FROM `messages` WHERE `to_history` = 1; DELETE FROM message WHERE `to_history` = 1;baldr
19.11.2022 01:23Так иногда делают, но нужно еще учитывать такие вещи как движок БД.
В PostgreSQL из-за механизма MVCC операция UPDATE создаст новую строку взамен старой. Старая будет не видна для новых транзакций, но все еще будет занимать место на диске. Это проблема Bloating для Postgres. После удаления строк место освободится только после autovacuum (а может и нет) или ручного запуска vacuum.
В MySQL если старый движок MyISAM (что вряд ли), то там таких проблем не будет (нет транзакций - нет проблем. Или есть, но другие). Как это работает в InnoDB - я уже не знаю точно, но подозреваю что тоже не бесплатно.
Однако, нужно определиться с тем какую проблему вы решаете этим. Боитесь что во время копирования придут новые строки и влезут в диапазон дат на удаление? Напрасно боитесь если вы все делаете в транзакции с правильным уровнем изоляции.
У вас две транзакции? Тогда все еще сложнее - а что если этот флаг уже стоит у части строк - предыдущий запуск скрипта пытался отправить сообщения в историю, выбрал их, но что-то пошло не так... Или отправил в историю, а удалить не смог..
yeahga
19.11.2022 03:00нужно еще учитывать такие вещи как движок БД
Исходил из того, что в статье речь о MySQL.
выбрал их, но что-то пошло не так
При следующем выполнении скопирует. Можно использовать
INSERT IGNOREна всякийотправил в историю, а удалить не смог
Аналогично, удалит в дугой раз
Напрасно боитесь
Согласен
LeMaX
18.11.2022 14:16Удаляем блоками или разделами...
innodb не блокирует таблицу, но при больших кол-вах записей на удаление расходует ресурсы и долбит журнал увеличивая ввод/вывод.
ilyaplot
19.11.2022 15:59Сделайте партиционирование сообщений по дате и удаляйте партиции, а не записи в таблицах.
miruzzy
20.11.2022 07:34Поправьте, если ошибаюсь
Разве такая конструкция не подойдёт?
With tt as( delete from messages returning *) insert into mess_history select * from tt
PS пишу с телефона , возможны ошибки
akamap
в deleteWhere нельзя указать то же условие, что и в findAll ?
lexspeedkb Автор
В данном случае нет, так как сообщения постоянно добавляются в БД, и вполне может быть ситуация, когда в это условие добавятся новые вхождения за те миллисекунды, пока происходила обработка данных. Да, именно в примере с сообщениями это вряд ли возможно, но это всего лишь один пример)
Asper1
Используйте в фильтрах фиксированное время для обработки сообщений, тогда новые сообщения никак не будут влиять на обработку.
michael_v89
Будут, если новое сообщение пришло в ту же секунду, что и последнее, полученное в SELECT, но после выполнения SELECT.
Зачем использовать фиксированное возрастающее время для идентификации записей, если можно использовать фиксированный возрастающий идентификатор записи?
baldr
Потому что идентификатор записи не обязан отвечать за порядок вставки, а отвечает за уникальность, в первую очередь.
В один прекрасный день придет сисадмин, посмотрит что INTEGER скоро переполнится и сделает
ALTER TABLE tablename AUTO_INCREMENT = 1Akina
Это если Вы говорите о программной генерации идентификатора или об идентификаторе типа UUID(). Если же речь идёт об автоинкрементном генераторе, то он обязан гарантировать соблюдение двух правил-условий, а именно то, что следующее сгенерированное им для поля значение будет (1) больше предыдущего сгенерированного (2) больше максимального в этом поле.
Допустимость несоблюдения любого из этих условий немедленно приводит к потенции генерации уже существующего в таблице значения и, соответственно, к возникновению ошибки дублирования. Некоторые СУБД вообще блокируют прямое присвоение значения автоинкрементному полю.
Кстати, из этих же требований проистекает и то, что "дыры" в значении автоинкремента для двух последовательно сгенерированных значений - штатное событие. Ибо неразрывность - не гарантируется.
Но отчасти Вы правы. Порядок генерации значений и порядок их записи в таблицу (точнее, порядок моментов обращения к генератору и порядок моментов выполнения фиксации транзакций) имеют право не совпадать.
Если автоинкремент обрабатывается правильно, то введённое админом значение будет скорректировано (увеличено), чтобы выполнялось правило 2..
baldr
Вот, кстати, не факт что у автора статьи в базе id был целочисленный автоинкремент, а не тот же uuid. В таком случае бессмыссленно здесь спорить о том что ему нужно по "id < X" фильтровать. Впрочем, сдается мне что там int, все-таки.
Верно. Поменял вручную - можешь получить по рукам (потом, при вставке).
Это какие, например? MySQL, MSSQL, Postgres, Oracle - все имеют штатный механизм для (ручного) сброса счетчика.
Что касается автоинкремента - конкретно он, да - является частным случаем генератора уникального идентификатора и, видимо, расти будет только вверх. Я точно помню что давно читал что-то про внутренние механизмы БД по использованию пустых промежутков для автогенерации primary key, но не могу найти это нигде и уже начинаю сомневаться в том что это имеет смысл.
В ANSI SQL вроде как есть определение счетчика, который может "зацикливаться", с конструкцией вроде такого:
id integer GENERATED ALWAYS AS IDENTITY (start with 42 increment by 10 cycle)Но такой синтаксис, видимо, не поддерживается MySQL. Статья по ссылке выше немного тяжеловата для чтения, я нашел ссылку на нее на SO - там чуть понятнее.
Сейчас лень проверять, но, по-моему, при попытке вставить строку в таблицу в PostgreSQL, если в таблице уже есть строка с таким уникальным id, который предлагает sequence, то база выругается на ошибку, строка вставлена не будет, но счетчик у sequence увеличится.
michael_v89
Так и не надо чтобы он отвечал за порядок вставки. В статье описано удаление по списку id, независимо от порядка.
Akina
Статья в качестве СУБД использует MySQL. Который вполне допускает прямое присвоение в автоинкрементное поле, в том числе значение менее текущего атрибута таблицы, если оно "свободно". Если вставить такую запись во время выполнения процесса очистки - код, ориентирующийся на значение id по диапазону, эту запись смахнёт в корзину и не почешется. Чтобы этого не произошло, код должен "вытянуть" на себя все значения, подлежащие удалению, и потом возвращать их в запросе на удаление очередного чанка.
michael_v89
Но в статье нет удаления по диапазону id, как это связано с темой обсуждения?
Ну так в статье так и делается, а человек говорит, что вместо этого надо использовать время в запросе на удаление.