For years we’ve been successfully taking LVM snapshots (actually SAN snapshots) on our MySQL 5.1 slave using the following scripted method:
set global innodb_max_dirty_pages_pct = 0;
flush tables with read lock;
(wait for the server to go into “waiting for server activity” mode as seen in the “show engine innodb status” output)
LVM/SAN snapshot taken here
set global innodb_max_dirty_pages_pct = 90; (by default this is 90 in MySQL 5.1, but 75 in MySQL 5.6)
We got the directions above from Andrew Atkinson’s post at the bottom of this article: http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html
So… We are now upgrading to 5.6 but we have the following problem: Step 4 above never happens. It only goes to the generic “sleeping” state. I have never been able to quiesce the 5.6 server enough to see the “waiting for server activity” state. Is that still a valid state in Percona 5.6? I googled for an hour and could not find any solid info on that.
Can we safely change Step 4 to look for the global status of “innodb_buffer_pool_pages_dirty” to go down to 0 instead?