Как известно, все системные администраторы делятся на две категории. Те, кто уже делают бэкапы и те, кто ещё нет.
Подобно им, администраторы БД также делятся на две категории, те, кто уже запускал процедуру удаления на большой БД с типом таблиц InnoDB, и те, кому это ещё предстоит.
Разумеется, в теории все знают, что из-за особенностей InnoDB, удаление может быть долгим, но это знание сродни тому, что «надо делать бэкапы». Многие осознают эти нехитрые истины, только наступив на грабли.
Для понимания, удаление 350М записей в таблице на 500М записей может занять более двух суток. Вторые грабли, на которые многие наступают, это попытка прибить запрос. Как мы все помним, InnoDB движок транзакционный, поэтому если вы попытаетесь прибить запрос, он попытается откатить изменения, а это может занять больше времени, чем выполнялся запрос.
Как сделать так, чтобы не было мучительно больно? Добро пожаловать под кат!
1. Если вы УЖЕ запустили удаление и теперь хотите приблизительно оценить, сколько уже сделано и сколько ещё осталось, используйте SHOW ENGINE INNODB STATUS; Получите длинный вывод. Ищите в нём вашу команду и смотрите undo log entries, это и будет количество уже обработанных записей.
Далее, в зависимости от того, сколько уже сделано, решаете, ждать до победного конца или прерывать запрос.
2. Если вы только планируете начать удаление, используйте LIMIT.
Количество записей подбирается эмпирически, скажем на не особо мощном сервере я использовал LIMIT 5000, на более мощном LIMIT 50000. Начните с небольших значений и увеличивайте их по необходимости.
Минусы решения:
а) надо руками запустить запрос необходимое количество раз (ну, или сделать текстовый файл с необходимым количеством строк с запросом и запускать из него.
б) каждый последующий запрос выполняется дольше, чем предыдущий
3. Используйте pt-archiver из комплекта percona-tools.
Я бы рекомендовал именно этот способ по ряду причин:
а) он быстрый;
б) его можно прервать в любой момент;
в) в нём можно наблюдать за прогрессом операции.
Пример:
В принципе, ключи довольно очевидны, тем не менее, пройдусь по ним:
Надеюсь, эта небольшая заметка показалась вам полезной (или хотя бы интересной).
Если вам есть, что добавить, поправить или просто высказаться — пишите!
Отдельной строкой хочу принести благодарность жж-пользователю merkwurdig, поднявшему обсуждение этой проблемы и заставившему меня вспомнить, как я бегал по таким же граблям, жж-пользовательнице svetasmirnova, которая всегда приходит на помощь при затруднениях с MySQL и сообществу ru_root, благодаря которому и появилась эта небольшая статья.
Подобно им, администраторы БД также делятся на две категории, те, кто уже запускал процедуру удаления на большой БД с типом таблиц 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, благодаря которому и появилась эта небольшая статья.
