MySQL 5.7 застрял навсегда "в ожидании блокировки на уровне таблицы"

Question

У меня есть веб-сервер с базой данных MySQL. Он содержит несколько баз данных, обслуживающих мои различные проекты. После обновления Ubuntu до 16.04 это доставило мне много хлопот в целом. Этот вопрос, в частности, касается ситуации, когда таблица блокируется блокировкой на уровне таблицы без очевидной причины, по которой блокировка не удаляется быстро.

У меня много зависших запросов:

581723  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECT\n  *\nFROM inventory\nWHERE inventory_id = '21601'
581724  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECT\n  *\nFROM inventory\nWHERE inventory_id = '21592'
581725  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECT\n  *\nFROM inventory\nWHERE inventory_id = '21602'
581726  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECT\n  *\nFROM inventory\nWHERE inventory_id = '21596'
581729  algebrainc_ro   localhost   algebrainc  Query   10306   Waiting for table level lock    SELECT inventory_id, ebay_title FROM inventory WHERE ebay_id = '32999992936'
581730  algebrainc  localhost   algebrainc  Query   10282   Waiting for table level lock    SELECT \n  complete_status status,\n  ebay_transactions.inventory_id,\n  ebay_transactions.ebay_id, \n  quantity, purchase_price, \n  ebay_title \nFROM ebay_transactions, inventory \nWHERE \n  ebay_transactions.inventory_id = inventory.inventory_id \n  AND created_date > date_add( now(), interval -7 day )\nORDER BY \n  created_date

После того, как я покопался и ничего не искал в performance_schema таких как table_handles и metadata_locks , я не нашел много.

Через некоторое время я понял, что это происходит из-за процессов mysqldump которые я периодически выполняю для вывода одной конкретной таблицы. У меня было дюжина застрявших процессов mysqldump (вызываемых cron), таких как:

ichudov   1178  0.0  0.0  29004  3248 pts/46   Ss+  20:24   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts
ichudov   1394  0.0  0.0  29004   900 pts/26   Ss+  Jun23   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts
ichudov   2537  0.0  0.0  29004   920 pts/22   Ss+  Jun24   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts

(имя таблицы замаскировано для конфиденциальности)

После уничтожения процессов mysqldump блокировки таблиц были удалены, и все вернулось в нормальное состояние.

Но почему процессы mysqldump блокируют вещи и не работают?

Answer 1

Причина: блокировка уровня таблицы во время mysqldump, которая является причиной замедления, так как таблица использует движок MyISAM

Разрешение: конвертировать MyISAM в Innodb для блокировки на уровне строк,

ALTER TABLE 'table_name' ENGINE=INNODB;

См .: блокировка myisam во время mysqldump