Как удалить 1000+ баз данных с innodb_file_per_table=1 без зависания процесса MySQL?

Question

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

Через несколько месяцев базы данных должны быть очищены. Мы просто звоним DROP DATABASE customer_1 для каждой базы данных (предоставляя серверу MySQL 10 секунд между каждым оператором 'rest'), затем DROP USER 'customer_1'@'127.0.0.1'),

Время от времени вся база данных просто зависает. SHOW PROCESSLIST дает

Id     User       Command    Time    State         Info
[pid]  adm-user   Query      300     System lock   DROP DATABASE `customer_1`

Новые запросы не будут выполнены. Уничтожение соответствующего pid запроса приведет к Command=Killing, и все. Ничего не произошло. Демон MySQL также не может быть остановлен, потому что он все еще ожидает завершения запроса.

В результате мы отключили весь сервер, перезапустили его и заставили MySQL автоматически выполнять аварийное восстановление после сбоя, что прекрасно работает. После чего мы можем отбросить еще 10-30 баз данных, и тогда это событие повторяется.

Мы много читали на эту тему, в том числе:

• https://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/
• https://www.percona.com/blog/2009/06/16/slow-drop-table/
• https://dba.stackexchange.com/questions/41995/drop-database-locked-the-server

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

Наш my.cnf:

innodb_file_per_table   = 1
innodb_buffer_pool_size = 9G
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT
table_open_cache        = 200000
table_definition_cache  = 110000
innodb_flush_log_at_trx_commit = 2

Есть ли способ, с помощью которого мы можем ответственно отбрасывать базы данных, то есть без остановки сервера для других целей?

Я читал, что простое удаление всех файлов таблиц может сработать, а затем удалить базу данных, в которой MySQL должен просто удалить ссылки на базу данных.

Answer 1

Одна важная вещь, которую вы должны сделать, это использовать файловую систему XFS для вашего каталога данных MySQL.

Удаление больших файлов в файловой системе ext3 занимает слишком много времени, как вы, несомненно, читали в блоге Percona, на который вы ссылались. Использование XFS значительно ускоряет удаление большого файла, поэтому глобальный мьютекс сохраняется в течение более короткого времени.

Я бы также убрал таблицы по одному, чтобы еще больше сократить время удержания мьютекса. Затем, после того как вы удалили все таблицы, удалите базу данных.

База данных в MySQL вряд ли является физическим объектом вообще. Это подкаталог каталога данных MySQL и крошечный файл с именем db.opt это хранит несколько свойств базы данных как ее набор символов по умолчанию (это больше не даже отдельный файл в MySQL 8.0). После удаления всех таблиц удаление самой базы данных тривиально.

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

Я сделал это в некоторых условиях. Сделайте запрос DROP TABLE в RENAME TABLE, чтобы переместить таблицу в другую схему, к которой нет доступа ни у одного пользователя. Затем периодически запускайте скрипт, чтобы действительно отбрасывать таблицы, которые были в этой ручке более 7 дней. Это дает время для постепенного удаления страниц из буферного пула, поскольку данные из других таблиц вытесняют их. Кроме того, это также дает льготный период для пользователей, чтобы они могли передумать, если они решили, что они все-таки отказались от нужного им стола.

Answer 2

Рекомендации Билла Карвина кажутся разумными (хотя RENAME TABLE вызвал некоторые из тех же проблем, что и DROP TABLE в прошлом), но большая часть этого материала должна была быть исправлена: ошибка 51325 была исправлена ​​в 2011-12-20 в 5.6.4, а ошибка 64284 была исправлена ​​в 2012-08-09 в 5.6.6.

Возможно, вы столкнулись с чем-то связанным с ошибкой MySQL 91977, для которой предлагается обходной путь - отключить Adaptive Hash Index при удалении таблиц / баз данных.

SET GLOBAL innodb_adaptive_hash_index = OFF;
DROP TABLE ...
SET GLOBAL innodb_adaptive_hash_index = ON;

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

Вы можете обновить текущую версию MySQL 5.7.x до 5.7.25 и подать отчет об ошибке, если вы все еще можете воспроизвести проблему.

Answer 3

На основании этой цепочки комментариев:

Является ли решение на основе сценария жизнеспособным для вас? Например, создать скрипт php и запустить его? - treyBake 14 февраля в 17:33

@treyBake Да, конечно. У нас есть полный контроль над всем. - Сделал 5 минут назад

Вы можете сделать это через PHP (позвоните rm_databases.php например:

$tableName = 'customer_';

for ($i = 1; $i <= 300; $++)
{
    # set up db conn
    $conn = new \PDO(
        'mysql:dbname='. $tableName .$i. ';host=localhost;',
        'user',
        'pass'
     );

     # create the SQL statement
     $sql = 'DROP DATABASE IF EXISTS '. $tableName .$i. ';';

     # exec it
     $conn->prepare($sql);
     $conn->execute();
}

echo 'done!';

Тогда у вас есть выбор: либо запустить руководство, запустив:

php -f rm_databases.php

или вы можете настроить запуск cronjob каждые 3 месяца:

0 0 12 ? 1/3 MON#1 * php -f rm_databases.php

Это будет запускаться в первый понедельник каждые 3 месяца.

Примечание: это будет работать, если каждый клиентский БД имеет префикс customer_$i - но если это более динамично, чем бы это ни было изнурительно, возможно, стоит просто создать один мега-массив имен баз данных и пройтись по нему. Время начальной настройки будет больше, но как только вы закончите, добавление новых пользователей займет 2 секунды:

$databases = [
    'foo', 'bar3', 'foobar', 'treyisawesome', 'wp-firesf'
    # etc etc
];

foreach ($databases as $el)
{
   # set up db conn
   $conn = new \PDO(
       'mysql:dbname='. $el .';host=localhost;',
       'user',
       'pass'
    );

    # create the SQL statement
    $sql = 'DROP DATABASE IF EXISTS '. $el .';';

    # rest of the script stays the same
}

Answer 4

Вы должны указать ядру базы данных не делать эксклюзивную (глобальную) блокировку. Вы можете сделать это двумя способами:

1. Используйте предложение LOCK (NONE или SHARED).
2. Используйте ALTER ONLINE TABLE(так же, как LOCK = NONE).

ALTER TABLE tbl_name ДОБАВИТЬ ПЕРВИЧНЫЙ КЛЮЧ (столбец), ALGORITHM=INPLACE, LOCK=NONE;

После изменения всех таблиц или если вы создаете с общей блокировкой, вы сможете удалить все таблицы.