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