Today I woke up with our production server down. Not happy.
We pinpointed the problem to a daily cronjob, that does a full mysqldump from the production database, to a remote server.
The SQL command were simply
mysqldump -u myuser -pmypassword mydatabase >outfile.sql
However, logging in to mysql admin console, and issuing a Show processlist; command displayed the following:
statistics select clickthrough_rate, i1.token, length(title) as len, p.id as pid, i1.category_id, title, c.name
155250 root localhost mydatauser Query 32164 Locked insert into srch_logs (log_id, api_session_id, query, category_filter, clickthrough_item_id) values
155251 root localhost mydatauser Query 32163 Locked insert into srch_logs (log_id, api_session_id, query, category_filter, clickthrough_item_id) values
155254 root localhost mydatauser Query 32145 Locked insert into srch_logs (log_id, api_session_id, query, category_filter, clickthrough_item_id) values
...[a lot of these, then]...
155941 root localhost mydatauser Query 26147 Locked LOCK TABLES `api_asin_cache` READ /*!32311 LOCAL */,`api_auto_pricesets` READ /*!32311 LOCAL */,`api
srch_logs is a normal MyIsam table, with a mere ~300K records.
My current best hypothesis, is that a web request issued concurrently to doing the mysqldump deadlocked both request. Can this happen?
Would running the mysqldump with –lock-tables=false fix this issue permanently?
Thank you for your time.
Best Answer
--lock-tables=false will probably stop this happening, but will potentially lead to an inconsistent backup being formed. That said, as MyISAM isn't transactionally controlled, it shouldn't make too much difference.
Another alternative may be to use a different storage engine (such as InnoDB) which uses a different locking model.