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

Xtrabackup locks tables/databases to write during backup?

bill_47820bill_47820 EntrantCurrent User Role Novice
Hello.
I have a server with installed mysql. One replication node is configured for this server.
The server has the following characteristics and application versions:
HDD
Intel (R) Xeon (R)
Mysql 5.7.22-0ubuntu0.16.04.1-log
Ubuntu Server 16.04
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
RAM is currently 60% free.

/var/lib/mysql directory occupies 20GB of disk space. Compressed backup occupies 2.9 GB.

On the master and on the slave, I configured daily backup using Xtrabackup.

I recently discovered that my application crashes with an UPDATE request timeout (there are a lot of them). The failure time is always the same and coincides with the backup time. Application work on the master.

Xtrabackup runs inside a bash script with the command

(xtrabackup --defaults-file=PATH_TO_DEFAULTS --backup --target-dir=DIR_TO_TARGET --stream=xbstream --parallel=8 2 >> PATH_TO_LOG_FILE) | (pigz) > PATH_TO_TARGET_FILE

no heavy operations at the time of the backup does not start

I moved the dump base to the local machine to experiment.
My local machine

8 GB
Intel Core i5
HDD

Mysql 5.7.26-0ubuntu0.16.04.1-log
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

cat /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
general_log = on
general_log_file=/var/log/mysql/all.log
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M

init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M

innodb_buffer_pool_size=3100M


[client]
;default-character-set = utf8mb4


I found out that 10 seconds after the start of backup, the time of INSERT/UPDATE requests increases to 1-3 minutes (20-70% of the backup time), and regardless of the database or table.

I tried to lower the priority using nice and ionice. With ionice -c2 -n7 requests continued INSERT/UPDATE for 50-60 seconds, which is still too much.
It also updated xtrabackup to xtrabackup version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a) but this did not help.

I did not find any errors in the disk subsystem.

Have the following questions:

Does Xtrabackup expose additional locks during backup?
Are there any problems with the hardware? But I do not believe that on two different systems one problem can be repeated and it is related to the hardware.
Maybe I’m missing some Mysql or Xtrabackup settings?

P.S. I had Mysql server 5.5.41 and xtrabackup version 2.4.9 and I had no such problems

Example log generated by Xtrabackup

190710 00:00:02 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' as '****' (using password: YES).
190710 00:00:02 version_check Connected to MySQL server
190710 00:00:02 version_check Executing a version check against the server...
190710 00:00:02 version_check Done.
190710 00:00:02 Connecting to MySQL server host: localhost, user: ****, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.22-0ubuntu0.16.04.1-log
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912
InnoDB: Number of pools: 1
190710 00:00:02 >> log scanned up to (1442416395566)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID NUMBER for DB/TABLE, old maximum was 0
190710 00:00:03 >> log scanned up to (1442416397727)
190710 00:00:04 >> log scanned up to (1442416411509)
190710 00:00:05 >> log scanned up to (1442416414806)
190710 00:00:06 >> log scanned up to (1442416415222)
190710 00:00:07 >> log scanned up to (1442416419410)
190710 00:00:08 >> log scanned up to (1442416422535)
190710 00:00:09 >> log scanned up to (1442416437869)
xtrabackup: Starting 8 threads for parallel data files transfer

A lot of lines like

190710 00:00:09 [01] Streaming FILE
190710 00:00:09 [03] ...done
190710 00:00:10 >> log scanned up to (1442416441036)

...

190710 00:02:28 [01] Streaming ./sys/sys_config.ibd
190710 00:02:28 [01] ...done
190710 00:02:29 >> log scanned up to (1442416709293)
190710 00:02:30 >> log scanned up to (1442416709293)
190710 00:02:31 >> log scanned up to (1442416709293)
190710 00:02:32 >> log scanned up to (1442416709293)
190710 00:02:33 >> log scanned up to (1442416709293)
190710 00:02:34 >> log scanned up to (1442416709293)
190710 00:02:34 [04] ...done

....

190710 00:03:03 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
190710 00:03:04 >> log scanned up to (1442416981570)
190710 00:03:05 >> log scanned up to (1442416981570)
190710 00:03:06 >> log scanned up to (1442416981570)
190710 00:03:07 >> log scanned up to (1442416981570)
190710 00:03:08 >> log scanned up to (1442416981570)
190710 00:03:09 >> log scanned up to (1442416981570)
190710 00:03:10 >> log scanned up to (1442416981570)
190710 00:03:11 >> log scanned up to (1442416981570)
190710 00:03:12 >> log scanned up to (1442416981570)
190710 00:03:13 >> log scanned up to (1442416981570)
190710 00:03:14 >> log scanned up to (1442417011053)
190710 00:03:15 >> log scanned up to (1442417067690)
190710 00:03:16 >> log scanned up to (1442417104634)
190710 00:03:17 >> log scanned up to (1442417190014)
190710 00:03:18 >> log scanned up to (1442417289947)
190710 00:03:19 >> log scanned up to (1442417339818)
190710 00:03:20 >> log scanned up to (1442417385144)
190710 00:03:21 >> log scanned up to (1442417420756)
190710 00:03:22 >> log scanned up to (1442417445338)
190710 00:03:23 >> log scanned up to (1442417457586)
190710 00:03:24 >> log scanned up to (1442417459900)
190710 00:03:25 >> log scanned up to (1442417467613)
190710 00:03:25 Executing FLUSH TABLES WITH READ LOCK...
190710 00:03:26 Starting to backup non-InnoDB tables and files
190710 00:03:26 [01] Streaming FILE.frm to <STDOUT>

....

190710 00:03:27 [01] ...done
190710 00:03:27 Finished backing up non-InnoDB tables and files
190710 00:03:27 [00] Streaming <STDOUT>
190710 00:03:27 [00] ...done
190710 00:03:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1442352534078'
xtrabackup: Stopping log copying thread.
.190710 00:03:27 >> log scanned up to (1442417516235)

190710 00:03:28 Executing UNLOCK TABLES
190710 00:03:28 All tables unlocked
190710 00:03:28 [00] Streaming ib_buffer_pool to <STDOUT>
190710 00:03:28 [00] ...done
190710 00:03:28 Backup created in directory 'BACKUP_DIR'
MySQL binlog position: filename 'mysql-bin.00001', position '1', GTID of the last change 'GTID_HERE'
190710 00:03:28 [00] Streaming <STDOUT>
190710 00:03:28 [00] ...done
190710 00:03:28 [00] Streaming <STDOUT>
190710 00:03:28 [00] ...done
xtrabackup: Transaction log of lsn (1442349921144) to (1442417516235) was copied.
190710 00:03:28 completed OK!

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello bill_47820

    Do all of your tables use InnoDB storage engine? I know that Percona XtraBackup has to take a lock during backup of MyISAM tables (for example). It does this after all the InnoDB tables have been backed up, see here for reference: https://www.percona.com/doc/percona-xtrabackup/2.3/how_xtrabackup_works.html

    Could you check on that, have a quick scan of that document page, and come back on that, please?

    Thanks!
  • bill_47820bill_47820 Entrant Current User Role Novice
    Hello lorraine.pocklington. Thanks for your reply.

    Using the following query, I got a list of MyISAM tables.
    SELECT table_name, table_schema, engine, table_comment FROM INFORMATION_SCHEMA.TABLES where ENGINE = 'MyISAM'

    My list of MyISAM tables

    TABLE1 MY_ARCHIVE_DB MyISAM
    TABLE2 MY_ARCHIVE_DB MyISAM
    columns_priv mysql MyISAM Column privileges
    db mysql MyISAM Database privileges
    event mysql MyISAM Events
    func mysql MyISAM User defined functions
    ndb_binlog_index mysql MyISAM
    proc mysql MyISAM Stored Procedures
    procs_priv mysql MyISAM Procedure privileges
    proxies_priv mysql MyISAM User proxy privileges
    tables_priv mysql MyISAM Table privileges
    user mysql MyISAM Users and global privilege

    TABLE1 and TABLE2 are not used in production. These are archived tables.
    The rest of the MyISAM tables are mysql system tables.

    All other tables that I work with have InnoDb engine

    I tried to exclude MY_ARCHIVE_DB and mysql from the backup using the command line option --databases-exclude

    Judging by the xtrabackup log, they were indeed excluded but this did not help.

    > I know that Percona XtraBackup has to take a lock during backup of MyISAM tables...

    Requests are starting to slow down at the stage of backup InnoDb tables
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Ack, thanks.

    I will check in with the XtraBackup team see if they have any suggestions/solutions for you.
  • bill_47820bill_47820 Entrant Current User Role Novice
    Thanks you. I will wait for an answer
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello again, I have an update from the engineers for you.

    Looking at how you're running PXB, you have the setting
    parallel=8
    
    .

    Could you try with
    parallel=1
    
    or without specifying the parallel option? The theory being that if you have slow IO it's possible that the 8 threads is saturating resources.

    Let me know if this changes things for you?
  • bill_47820bill_47820 Entrant Current User Role Novice
    Thanks. I'll try it in the next 2 days and inform of the results
  • bill_47820bill_47820 Entrant Current User Role Novice
    Looks like it works!
    And this is the only thing that I have not tried.
    Thank you very much.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Great, thanks for letting us know, I'll pass that message back to the team. Glad we could help :)
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.