Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a single Enterprise Linux 6 system running MySQL 5.5. I am attempting to use LVM to capture a weekly filesystem snapshot.

However, as many of us have experienced, "FLUSH TABLES WITH READ LOCK;" can lead to a deadlock situation, create a backlog of queries, fill up the connection pool and effectively knock the system offline. In fact last night my "FLUSH TABLES WITH READ LOCK;" didn't complete until 8 hours after execution due to a long running query.

If I perform the MySQL snapshot using a script like this, can I somehow time out the "FLUSH TABLES WITH READ LOCK;". If it doesn't execute after a period of time I would like to kill it and avoid the inevitable deadlock.

mysql -u root <<-MYSQL_INPUT
FLUSH TABLES WITH READ LOCK;
SYSTEM /usr/local/bin/mysql-create-lvm-snapshot;
UNLOCK TABLES;
\q
MYSQL_INPUT
share|improve this question

2 Answers 2

up vote 1 down vote accepted

There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes.

However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM snapshot.

It is required if: - You are interested in point in time recovery (applying incremental restore via binlogs) - You have lots of non-transactional (ie MyISAM) tables being written to - You are running the snapshot against the master and want to create a slave based on the snapshot.

If your database is InnoDB oriented, and nothing interesting is hapenning with your MyISAM system tables (ie no one creating a procedure, GRANTing privileges etc.) then it's enough that you FLUSH only those tables.

All things InnoDB, you can just take the snapshot. There is no benefit in FLUSHing. InnoDB will make proper recovery anyhow.

The need for FLUSH TABLE comes from the requirement of getting the binary log file and position when you want to be able to rebuild a slave or otherwise use binary logs.

share|improve this answer
    
Thanks Shiomi. This database hosts a hodge-podge of MyISAM and InnoDB tables, so I believe I need to stay with the FLUSH TABLES WITH READ LOCK. –  Stefan Lasiewski Oct 25 '13 at 17:15

Mysql 5.6 introduced FLUSH TABLES FOR EXPORT which might be a better option for your use case if you use InnoDB exclusively (apart from the internal mysql database which is always MyISAM):

http://dev.mysql.com/doc/refman/5.6/en/flush.html

Unfortunately I cannot comment here (yet), but I did quite some research on the answer of Shlomi Noach above. I hope he is right, but I could not find a confirmation of his statement that taking a filesystem snapshot of InnoDB tables does never lead to data corruption.

From the comments of https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html:

For a filesystem snapshot of innodb, we find that setting innodb_max_dirty_pages_pct to zero; doing a 'flush tables with readlock'; and then waiting for the innodb state to reach 'Main thread process no. \d+, id \d+, state: waiting for server activity' is sufficient to quiesce innodb.

From http://www.mysqlperformanceblog.com/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/:

If you’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no-lock option with Percona Xtrabackup.

Which would imply that a flush/lock is not necessary if you could rule out administrative changes.

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.