Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Problem turned out to be in another place entirely; thanks for your help. –  Silver Dragon Mar 15 '11 at 12:49

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.