# Xtrabackup locks tables/databases to write during backup? **URL:** \ **Category:** Percona XtraBackup\ **Created:** [July 15, 2019, 3:28am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115 "2019-07-15T03:28:42Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![bill\_47820](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@bill\_47820](https://forums.percona.com/u/bill_47820)\ **Post date:** [July 15, 2019, 3:28am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/1 "2019-07-15T03:28:42Z")
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! ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 16, 2019, 7:15am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/2 "2019-07-16T07:15:40Z")
Hello [bill\_47820](https://www.percona.com/forums/member/43052-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](https://www.percona.com/doc/percona-xtrabackup/2.3/how_xtrabackup_works.html%5B/URL%5D) Could you check on that, have a quick scan of that document page, and come back on that, please? Thanks! ---
**Author:** ![bill\_47820](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@bill\_47820](https://forums.percona.com/u/bill_47820)\ **Post date:** [July 17, 2019, 6:30am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/3 "2019-07-17T06:30:54Z")
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 ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 17, 2019, 7:00am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/4 "2019-07-17T07:00:45Z")
Ack, thanks. I will check in with the XtraBackup team see if they have any suggestions/solutions for you. ---
**Author:** ![bill\_47820](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@bill\_47820](https://forums.percona.com/u/bill_47820)\ **Post date:** [July 17, 2019, 7:20am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/5 "2019-07-17T07:20:09Z")
Thanks you. I will wait for an answer ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 17, 2019, 7:37am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/6 "2019-07-17T07:37:02Z")
Hello again, I have an update from the engineers for you. Looking at how you’re running PXB, you have the setting ```auto parallel=8 ``` . Could you try with ```auto 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? ---
**Author:** ![bill\_47820](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@bill\_47820](https://forums.percona.com/u/bill_47820)\ **Post date:** [July 17, 2019, 10:26am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/7 "2019-07-17T10:26:52Z")
Thanks. I’ll try it in the next 2 days and inform of the results ---
**Author:** ![bill\_47820](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@bill\_47820](https://forums.percona.com/u/bill_47820)\ **Post date:** [July 19, 2019, 2:02am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/8 "2019-07-19T02:02:03Z")
Looks like it works! And this is the only thing that I have not tried. Thank you very much. ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 19, 2019, 6:06am UTC](https://forums.percona.com/t/xtrabackup-locks-tables-databases-to-write-during-backup/7115/9 "2019-07-19T06:06:40Z")
Great, thanks for letting us know, I’ll pass that message back to the team. Glad we could help 🙂