When xtrabackup backs up 100,000 table databases, the TABLESPACES file list query query is executed

MySQL & MariaDB Percona XtraBackup
1

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
스크린샷 2020-12-10 09.20.53.png1427×79 12.6 KB

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’;

2 Likes
2

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

The change was introduced to fix bug 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 ) 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

2 Likes
3

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.

2 Likes
4

There is a way to not run this query?

2 Likes
5

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 , and you might get asked to run some perf tools to gather some outputs/info about the issue for a workaround/fix

3 Likes
6

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,

2 Likes