Xtrabackup locks tables/databases to write during backup?

MySQL & MariaDB Percona XtraBackup
1

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

…

190710 00:03:27 [01] …done
190710 00:03:27 Finished backing up non-InnoDB tables and files
190710 00:03:27 [00] Streaming
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
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
190710 00:03:28 [00] …done
190710 00:03:28 [00] Streaming
190710 00:03:28 [00] …done
xtrabackup: Transaction log of lsn (1442349921144) to (1442417516235) was copied.
190710 00:03:28 completed OK!

2

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: [URL]Percona XtraBackup

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

Thanks!

3

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

4

Ack, thanks.

I will check in with the XtraBackup team see if they have any suggestions/solutions for you.

5

Thanks you. I will wait for an answer

6

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?

7

Thanks. I’ll try it in the next 2 days and inform of the results

8

Looks like it works!
And this is the only thing that I have not tried.
Thank you very much.

9

Great, thanks for letting us know, I’ll pass that message back to the team. Glad we could help :slight_smile: