# When xtrabackup backs up 100,000 table databases, the TABLESPACES file list query query is executed **URL:** \ **Category:** Percona XtraBackup\ **Created:** [December 9, 2020, 10:43pm UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376 "2020-12-09T22:43:15Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![mysnoopy](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mysnoopy/32/16240_2.png) [@mysnoopy](https://forums.percona.com/u/mysnoopy)\ **Post date:** [December 9, 2020, 10:43pm UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/1 "2020-12-09T22:43:15Z")
hi When a DB with 100,000 tables is backed up with xtrabackup, the query is executed for a very long time(3,345sec) and the backup does not proceed. ![스크린샷 2020-12-10 09.20.53.png](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/ad0672247d662a28bc79cfeadf9d7b5c94122412.png) Can you tell me when the lookup query is executed? thank u SELECT T2.PATH, T2.NAME, T1.SPACE\_TYPE FROM INFORMATION\_SCHEMA.INNODB\_TABLESPACES T1 JOIN INFORMATION\_SCHEMA.INNODB\_TABLESPACES\_BRIEF T2 USING (SPACE) WHERE T1.SPACE\_TYPE = ‘Single’ & & T1.ROW\_FORMAT != ‘Undo’ UNION SELECT T2.PATH, SUBSTRING\_INDEX(SUBSTRING\_INDEX(T2.PATH, ‘/’, -1), ‘.’, 1) NAME, T1.SPACE\_TYPE FROM INFORMATION\_SCHEMA.INNODB\_TABLESPACES T1 JOIN INFORMATION\_SCHEMA.INNODB\_TABLESPACES\_BRIEF T2 USING (SPACE) WHERE T1.SPACE\_TYPE = ‘General’ & & T1.ROW\_FORMAT != ‘Undo’; ---
**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\ **Post date:** [December 15, 2020, 7:06am UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/2 "2020-12-15T07:06:54Z")
Hi mysnoopy, That query was introduced on PXB version 8.0.10 as shown on the following github commit [https://github.com/percona/percona-xtrabackup/blob/percona-xtrabackup-8.0.10/storage/innobase/xtrabackup/src/space\_map.cc#L63](https://github.com/percona/percona-xtrabackup/blob/percona-xtrabackup-8.0.10/storage/innobase/xtrabackup/src/space_map.cc#L63) The change was introduced to fix bug [https://jira.percona.com/browse/PXB-2124](https://jira.percona.com/browse/PXB-2124) , as you can check on github’s commit header. The function is used ( [https://github.com/percona/percona-xtrabackup/blob/68312d9a374320f942f95486d3a34b83d29f1cb6/storage/innobase/xtrabackup/src/xtrabackup.cc#L3956](https://github.com/percona/percona-xtrabackup/blob/68312d9a374320f942f95486d3a34b83d29f1cb6/storage/innobase/xtrabackup/src/xtrabackup.cc#L3956) ) to check which tablespaces to copy as per explained on the bug report Does the backup advance at some point? Even if log does not advance, do you see disk activity? if it dos not, I would suggest to: 1. Upgrade to latest PXB 8.0.22 to discard any bug 2. Check if there is any disk activity (iostat) related to the backup and/or disk saturation due to MySQL operation 3. Check SEIS (show engine innodb status) and error.log, searching for any performance issue, locking Regards ---
**Author:** ![mysnoopy](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mysnoopy/32/16240_2.png) [@mysnoopy](https://forums.percona.com/u/mysnoopy)\ **Post date:** [December 16, 2020, 8:32am UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/3 "2020-12-16T08:32:36Z")
Hi. CTutte Thanks for the advice. PXB 8.0.22 was released a few days ago. Let’s install it once again and test it. ---
**Author:** ![denis.moraes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/denis.moraes/32/2685_2.png) [@denis.moraes](https://forums.percona.com/u/denis.moraes)\ **Post date:** [January 26, 2021, 6:49pm UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/4 "2021-01-26T18:49:31Z")
There is a way to not run this query? ---
**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\ **Post date:** [February 1, 2021, 2:52pm UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/5 "2021-02-01T14:52:13Z")
Hi again, I have asked internally and it’s not possible to avoid the query, but taking 3.3 seconds shouldn’t be long. If you have issues with the backup, the developers told me you should consider creating a bug report on [https://jira.percona.com/projects/PXB/issues](https://jira.percona.com/projects/PXB/issues) , and you might get asked to run some perf tools to gather some outputs/info about the issue for a workaround/fix ---
**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\ **Post date:** [February 2, 2021, 10:35pm UTC](https://forums.percona.com/t/when-xtrabackup-backs-up-100-000-table-databases-the-tablespaces-file-list-query-query-is-executed/8376/6 "2021-02-02T22:35:48Z")
Hi @denis.moraes , welcome to the Percona Forums! Building on @CTutte 's answer, in the short-term you could also use pt-kill in an aggressive mode i.e. `--busy-time 1s --kill` which will obviously not stop the query from starting but will minimize how long it runs for. Let us know if this works for you,