1

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.

1
  • Problem turned out to be in another place entirely; thanks for your help. Mar 15, 2011 at 12:49

1 Answer 1

2

--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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .