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

Hangs during backup

aubreyhollandaubreyholland EntrantInactive User Role Beginner
I'm using innobackupex to perform a full backup against a database running MySql 5.6.19. I've been performing these backups for a while, but all of a sudden in the middle of the backup both MySQL and the backup will seem to slow to a crawl. Neither process stops completely, but queries against the database become very slow and the log scan positions printed out by the backup start to change very infrequently. I am calling the backup like this:

/usr/bin/innobackupex --compress --no-timestamp --user=XXXX --password=XXXX --no-lock /database_backup/full 2> /var/log/xtrabackup.log

When this happens, attempts to kill the backup or stop mysql both result in a defunct process, and the only way I've found to get things running again is to reboot the machine running the database. Based on timestamps of files in the backup directory, it looks like the failure occurs after the files in the root directory of the backup have been created and it has begun copying files into the mysql directory. In particular, /database_backup/full/mysql/innodb_table_stats.ibd.qp is the last file that has been touched.

I'd really appreciate any advice or potential workarounds you can suggest, as we're running backup-free right now, and that's no good at all.

Thanks,
Aubrey

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi aubreyholland;

    From your general description, it sounds like the server might be running out of memory and then likely swaps and things go poorly at that point. Do you have trending data on your server stats like memory usage (including swap usage) and i/o wait? If so I would start there to see if that has been going up; you may have hit a critical threshold to where you swap now where before you did not.

    Another thing to check is to see what is actually running in MySQL when the backup starts to hang, to see if threads are getting backed up due to locking potentially. This can be seen through the processlist, or you could look at the Threads_running status variable to see if that spikes up. Having a lot of waiting threads will quickly kill a server and often requires a restart of MySQL (but not the host server itself; which is why I'm still leaning more towards a memory issue).

    -Scott
  • aubreyhollandaubreyholland Entrant Inactive User Role Beginner
    Hi Scott, thanks for your response. I've looked through our metrics around the time that we lost the database, and I haven't seen problems with memory or I/O wait. I think it's still possible that I'm missing something there, but I'm pretty sure those are not the issue. What we have seen is a bunch of threads backing up as you mention. Queries start having poor performance, which leads to a huge queue, and we've seen that in the process list. If we assume that's the issue, is there anything I can do about it? Is there any reason why doing the backup might lead to poor query performance?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi aubreyholland;

    Are all your tables InnoDB? The easiest way to see what is happening would be to run SHOW ENGINE INNODB STATUS \G while the issue is happening. That will show you if things are waiting on a lock, or what is going on. If queries are getting backed up, chances are something is blocking them (might be Xtrabackup), or your system is so bogged down that it cannot process the queries. But if you are not seeing any IOWAIT or swapping, then it seems unlikely that your system could be that bad off, unless it is a CPU issue, which is a possibility with compression I suppose.

    To get a backup completed, you might want to remove the --compress option to see if it will go through. You could also do a mysqldump at least just to have something in the interim (or mydumper for parallel export).

    And are you backing up the master database server? If so I would also recommend creating a slave, and backing up that as well (or just the slave).

    -Scott
  • wavecomwavecom Entrant Inactive User Role Beginner
    Hi
    .
    Im facing similar issue when i make backup. I have approx 1000 db´s, 40% innodb, rest myisam and total dirsize 12GB . In 1.2GB eg 10% -
    All querys innodb or myisam will be stucked with message Waiting for global read lock
    io is not utlized and iowait allmost nothing.
    My servers are running in vmware and enteprise fc storage..
    Backup itself continues working .
    i tried to fix memory to 1,5gb and throttling to 5. not helping...
    very same symptoms in different servers. tried in 3. Any idea for anyone ? Thanks!
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.