Not the answer you need?
Register and ask your own question!

'FLUSH TABLES WITH READ LOCK' runs a very long time (several hours)

GerdGerd EntrantInactive User Role Beginner
Hello,
since two days I have the problem that the command 'flush tables with read lock' run several hours. Actual this night from 3:15am until 10:46am. During this time the database is not accessible and there is therefore a big trouble. I've got 27198 times the message 'log scanned up to (1705220767)'. Do you know what the number in the brackets is? Xtrabackup started on 04/28/2014 and has been run with no problems. On Monday, 06/16/2014, took 'flush tables ...' not even 1 second. I am using Xtrabackup 2.1.9. Does everbody knows what happen?
Thank you very much.
Best regards
Gerd

Comments

  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    That happens because you have a long running transaction until 10:46am. Because of that, Xtrabackup can't finish locking the tables so it stays waiting until that long transaction ends. There is a workaround to this problem in newest versions of Xtrabackup. This parameter has been implemented to configure a timeout on that waiting:

    http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--lock-wait-timeout

    Since you are using 2.1.9, you should be able to use it.

    In order to know what is preventing the table lock from finishing, you should run "SHOW PROCESSLIST" and "SHOW ENGINE INNODB STATUS\G" when the backup is locked waiting, and find the longest running query. That one is causing the problem.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.