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.