Подобно им, администраторы БД также делятся на две категории, те, кто уже запускал процедуру удаления на большой БД с типом таблиц InnoDB, и те, кому это ещё предстоит.
Разумеется, в теории все знают, что из-за особенностей InnoDB, удаление может быть долгим, но это знание сродни тому, что «надо делать бэкапы». Многие осознают эти нехитрые истины, только наступив на грабли.
Для понимания, удаление 350М записей в таблице на 500М записей может занять более двух суток. Вторые грабли, на которые многие наступают, это попытка прибить запрос. Как мы все помним, InnoDB движок транзакционный, поэтому если вы попытаетесь прибить запрос, он попытается откатить изменения, а это может занять больше времени, чем выполнялся запрос.
Как сделать так, чтобы не было мучительно больно? Добро пожаловать под кат!
1. Если вы УЖЕ запустили удаление и теперь хотите приблизительно оценить, сколько уже сделано и сколько ещё осталось, используйте SHOW ENGINE INNODB STATUS; Получите длинный вывод. Ищите в нём вашу команду и смотрите undo log entries, это и будет количество уже обработанных записей.
---TRANSACTION 1 4141054098, ACTIVE 191816 sec, OS thread id 36004918272 updating or deleting, thread declared inside InnoDB 84
mysql tables in use 1, locked 1
686063 lock struct(s), heap size 88520688, undo log entries 229144332
MySQL thread id 56087872, query id 2202164550 1.1.1.2 database updating
DELETE
FROM table
WHERE UNIX_TIMESTAMP(moment) < 1498712335 - 365 * 86400
AND UNIX_TIMESTAMP(moment) > 0Далее, в зависимости от того, сколько уже сделано, решаете, ждать до победного конца или прерывать запрос.
2. Если вы только планируете начать удаление, используйте LIMIT.
Количество записей подбирается эмпирически, скажем на не особо мощном сервере я использовал LIMIT 5000, на более мощном LIMIT 50000. Начните с небольших значений и увеличивайте их по необходимости.
Минусы решения:
а) надо руками запустить запрос необходимое количество раз (ну, или сделать текстовый файл с необходимым количеством строк с запросом и запускать из него.
б) каждый последующий запрос выполняется дольше, чем предыдущий
3. Используйте pt-archiver из комплекта percona-tools.
Я бы рекомендовал именно этот способ по ряду причин:
а) он быстрый;
б) его можно прервать в любой момент;
в) в нём можно наблюдать за прогрессом операции.
Пример:
pt-archiver --source h=127.0.0.1,D=build4,t=b_iblock_element --optimize s --purge --where 'TAGS LIKE "%САПР%"' --limit 1000 --commit-each --progress 500 --charset "CP1251"В принципе, ключи довольно очевидны, тем не менее, пройдусь по ним:
--source — описывает подключение. Хост, база и таблица. При необходимости можно дописать логин и пароль (в примере я использую креды из ~/.my.cnf);
--optimize — оптимизирует исходную таблицу, либо ту, в которую переносятся данные. Поскольку в данном случае я не переношу, а удаляю данные, оптимизирую именно исходную (s) таблицу. В принципе, делать это необязательно;
--purge — изначально утилита предназначена для переноса данных в другую таблицу (или в файл). Но можно и просто удалять строки;
--where — обычное SQL-условие, по которому будут отбираться строки для удаления;
--limit 1000 — обрабатывать за раз 1000 строк (можно больше, зависит от производительности вашего сервера);
--commit-each — делать коммит после количества строк, указанного в --limit;
--progress 500 — выводить прогресс каждые 500 строк (опять же, имеет смысл подобрать этот параметр индивидуально);
--charset — кодировка. Если будет использоваться только ASCII-кодировка, проще указать --no-check-charset. Отдельно упомяну, что необходимо чтобы локаль консоли совпадала с указанным charset'ом, иначе ошибка не выведется, но и строки обработаны не будут.
Надеюсь, эта небольшая заметка показалась вам полезной (или хотя бы интересной).
Если вам есть, что добавить, поправить или просто высказаться — пишите!
Отдельной строкой хочу принести благодарность жж-пользователю merkwurdig, поднявшему обсуждение этой проблемы и заставившему меня вспомнить, как я бегал по таким же граблям, жж-пользовательнице svetasmirnova, которая всегда приходит на помощь при затруднениях с MySQL и сообществу ru_root, благодаря которому и появилась эта небольшая статья.
Комментарии (12)
evgenWebm
04.07.2017 00:41Хм. Я еще из категории которые уже делает бекапы, но еще не удалял чрезмерно много строк.
Но мысль такая.
Если удаление занимает столько много времени. Не дешевле перенести данные в другу бд и сделать подмену потом?
speller
04.07.2017 03:36Целостность данных при постоянной записи в базу не пострадает?
evgenWebm
04.07.2017 03:56Вроде обсуждаем сферического коня в вакууме)
Вон BasilioCat идентичную мысль сказал.speller
04.07.2017 04:09Так то да, проще всего сделать рядом временную таблицу с нужными данными, а потом подменить ею оригинальную. Но когда в базу постоянно идет запись, а таблицы еще и связаны внешними ключами — проблема удаления больших объемов становится весьма актуальной.
slavabars
04.07.2017 09:29Удаляю с выборкой по полю партиями (id >=1 and id <=5000). И все после того, как один раз решил почистить большой объем и уперся в производительность.
alexandersh123
04.07.2017 11:45очень странная задача… не могу себе вообще представить ситуацию, когда вообще может потребоваться в таблице на 500М строк удалять больше половины… это явно продакшн, 500М строк — видимо, активно работающий продакшн, удаление большей части таблицы явно связано с изменением логики… ну а если меняется логика, то под новую логику иногда лучше новую архитектуру использовать… на мой взгляд правильное решение в подобной ситуации, это подготовка новой таблицы, вставка в нее тех строк, которые в примере недо было не удалить, а оставить, в этом случае вставлять можно не сразу, а партиями… ну а потом замена одной таблицы на другую… а вообще очень странная задача
Naglec
04.07.2017 12:15Ну, вероятно, необходимо удалить какие-то устаревшие записи (например, за весь период до 01.01.2017). Либо что-то сильно много неожиданно наплодилось и теперь нужно удалить.
StraNNicK
04.07.2017 12:18Мне как-то в наследство проект достался, где сессии хранились в БД и хранились они по году (или по два, не помню уже). В общем таблица распухла до каких-то феерических значений и было принято решение очистить её, оставив данные за последний месяц.
Тогда-то я радостно по граблям в первый раз и проскакал.
А значения из статьи честно утянуты из этой заметки в ru_root. Она мне живо напомнила мой печальный опыт.
slavabars
06.07.2017 03:35Это может быть таблица логов, в моем случае это было так. Одно время мы копили вообще все действия, собирали их 5 лет. Потом решили что логи храним год, лишнее удалить.
dukei
04.07.2017 16:11Меня это долгое удаление спасло как-то. Смотрел я на активные процессы в базе, ибо тормозила она, и вдруг вижу там delete * from user. Я думаю, что за глюк, вроде проект работает нормально. Думаю, нет, не может такого запроса быть в продакшн, взял и убил его.
Оказалось, коллега ошибся и не в том окошке очистку тестового запустил. Понятно, что потом установили строгие регламенты, но факт остается фактом. Из-за медленности удаления ужасного исхода удалось избежать. Повезло :)
BasilioCat
Удаление (да и вообще модификация) записей через LIMIT в случае statement-based репликации — не самая хорошая идея. Лучше уж использовать диапазон значений первичного ключа (id >= 5000 AND id < 10000).
Также, в отдельных случаях может быстрее оказаться выбрать оставляемые записи в новую таблицу, на старую сделать truncate, и залить данные обратно. Или просто удалить таблицу, если в нее не ведется запись, а новую переименовать
Ну и конечно, можно таблицы партицировать, в том числе и по времени, и удалять ненужные партиции целиком.
mapron
Я когда столкнулся с задачей из статьи, там как раз были сотни миллионов записей и InnoDB, и удаление по времени — сделал партицирование — и это действительно решение, партиция дропается мгновенно. Так что +1 за последний совет.