Announcement

Announcement Module
Collapse
No announcement yet.

Xtrabackup freezes at unlocking the tables; with db locked, production server stops

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Xtrabackup freezes at unlocking the tables; with db locked, production server stops

    Hi there,

    My problem is this: Xtrabackup while backing up the whole database (InnoDB and MyISAM system tables), reaches the point where it is supposed to unlock the tables and finish the process, but doesn't do that and freezes, and the database remains locked. At this point, on break, the database crashes. This has been happening for last 3 days since the last MySQL update I applied.

    Details:
    Linux x64
    mysql: 5.6.15-63.0
    xtrabackup 2.1.8 compiled from percona-xtrabackup-2.1.8.tar.gz
    with: AUTO_DOWNLOAD="yes" ./utils/build.sh 5.6
    (compile without the "5.6" switch causes a "libiao" error at database start; it tries to call "libcrypt.so.11" which isn't there)

    The log of the whole thing happening:

    Code:
    /.../
    [01] Copying ./phpmyadmin/pma__navigationhiding.ibd to /backup/2014.03.08/phpmyadmin/pma__navigationhiding.ibd
    [01]        ...done
    xtrabackup: Creating suspend file '/backup/2014.03.08/xtrabackup_suspended_2' with pid '14135'
    >> log scanned up to (640660457184)
    
    140308 22:39:04  innobackupex: Continuing after ibbackup has suspended
    140308 22:39:04  innobackupex: Starting to lock all tables...
    140308 22:39:04  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
    140308 22:39:04  innobackupex: Connected to MySQL server
    140308 22:39:04  innobackupex: Connection to database server closed
    Query killing process is finished
    140308 22:39:04  innobackupex: All tables locked and flushed to disk
    
    140308 22:39:04  innobackupex: Starting to backup non-InnoDB tables and files
    innobackupex: in subdirectories of '/var/lib/mysql/mysqldb/db'
    innobackupex: Backing up files '/var/lib/mysql/mysqldb/db/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (78 files)
    >> log scanned up to (640660499850)
    innobackupex: Backing up file '/var/lib/mysql/mysqldb/db/stats/db.opt'
    innobackupex: Backing up file '/var/lib/mysql/mysqldb/db/stats/tables.frm'
    innobackupex: Backing up files '/var/lib/mysql/mysqldb/db/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
    >> log scanned up to (640660501221)
    innobackupex: Backing up files '/var/lib/mysql/mysqldb/db/phpmyadmin/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (16 files)
    140308 22:39:07  innobackupex: Finished backing up non-InnoDB tables and files
    
    140308 22:39:07  innobackupex: Waiting for log copying to finish
    
    xtrabackup: The latest check point (for incremental): '640643368160'
    xtrabackup: Stopping log copying thread.
    .>> log scanned up to (640660501221)
    
    xtrabackup: Creating suspend file '/backup/2014.03.08/xtrabackup_log_copied' with pid '14135'
    xtrabackup: Transaction log of lsn (640643368160) to (640660501221) was copied.
    So a successful ending is missing. Clearly, it freezes at the tables unlock.

    This is how the end should look like:
    Code:
    140308 22:42:40  innobackupex: All tables unlocked
    
    innobackupex: Backup created in directory '/backup/2014.03.08'
    140308 22:42:40  innobackupex: Connection to database server closed
    140308 22:42:40  innobackupex: completed OK!
    On break I get

    Code:
    DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009.
    innobackupex: Error:
    Error executing 'UNLOCK TABLES': DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009.
    ...which also confirms it stops at the tables unlock.

    Interestingly, if the httpd deamon is not running, the backup works just fine. The problem hence occurs only under load.

    I am lost at this. Please kindly advise.


  • #2
    Hi,

    As you said, problem occurs only under load means, backup should take more time then usual. Can you check wait_timeout value? and try to increase it and then check?
    It might be possible that process is getting timeout as the first error is
    " DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/bin/innobackupex line 3009."

    It seems, server killing the backup connection due to timeout.

    I would also suggest to read this http://www.percona.com/doc/percona-x...ved_ftwrl.html

    Comment

    Working...
    X