I have a webserver with a MySQL database. It holds several databases serving my various projects. Since Upgrading Ubuntu to 16.04 it has given me a lot of trouble in general. This question specifically is about a situation that a table is locked with a table level lock with no obvious reason why the lock is not removed quickly.
I have a lot of stuck queries like so:
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
After a while of poking around and looking for nothing in performance_schema
such as table_handles
and metadata_locks
, I did not find much.
After a while I realized that it happens because of mysqldump
processes that I do periodically to dump one particular table. I had a dozen stuck mysqldump
processes (called by cron) like these:
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
(table name masked for privacy)
After killing mysqldump
processes, table locks were removed and everything went back to normal.
But why do mysqldump
processes lock things up and do not work?
Best Answer
Cause: table level lock during mysqldump which is the reason for slowness since the table is using MyISAM engine
Resolution: Convert MyISAM to Innodb, for row level locking,
Refer: myisam lock during mysqldump