Happy New Year everyone,
I’m hoping someone can help me out. I’m trying to use Xtrabackup to stream about 2500 production databases from the server to a backup storage server (i.e. not a MySQL server, just storage). We’ve been doing this for a while using NFS and it’s no longer a viable solution because of the write speed to NFS and the load NFS imposses on the system. So I’m attempting to switch from using xtrabackup_56 directly to innobackupex and streaming with xbstream to my storage server.
On my development box it works great. On production, not so much. After a few hours of running MySQL stops accepting connections and when I do a processlist on it every query is in a state of “Waiting for global read lock” adn the process that I think is from xtrabackup is in a sleep state. Any suggestions on fixing this? From what I’ve read I’m thinking that --lock-wait-quiry-type=update would be the way to go? If I kill off the root process that’s sleeping in MySQL, things are all good again. Any thoughts? I’m running xtrabackup 2.1.9 (yes, I need to upgrade it, but I can’t right now unless it’s absolutely required).
this is the xtrabackup command that’s running on the system.
xtrabackup_56 --defaults-group=mysqld --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --throttle=16 --parallel=4 --stream=xbstream