[#MDEV-17195] mariabackup is very slow on databases with many tables

mariabackup becomes very slow if the MariaDB server has many schemes/many tables and using --lock-ddl or --lock-ddl-per-table option.

mariabackup executes per each table information_schema query like below, depending on the server it takes 0.5-1.0+ second time:

MariaDB [(none)]> show profiles;

+----------+------------+-------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                           |

+----------+------------+-------------------------------------------------------------------------------------------------+

|        1 | 1.10146659 | SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE = 52974 AND NAME LIKE '%%/%%' |

We have servers with as many as 234440 InnoDB tables
=> just querying information schema will last more than 117220 seconds
=> daily backup lasts at least 33 hours, actual time is about 3x days.

Data itself is about 34GB and its copying/archiving lasts only some minutes:

[mysql@fies1udb32 ~]$ du -skh /mysql_data

34G     /mysql_data

Slowness locates in "Filling schema table" phase, see attached mariabackup_slow_mysql_session.txt for details:

MariaDB [(none)]> show profiles;

+----------+------------+-------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                           |

+----------+------------+-------------------------------------------------------------------------------------------------+

|        1 | 1.10146659 | SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE = 52974 AND NAME LIKE '%%/%%' |

|        2 | 0.94333337 | SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE = 52974 AND NAME LIKE '%%/%%' |

|        3 | 0.85380770 | SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE = 52974 AND NAME LIKE '%%/%%' |

+----------+------------+-------------------------------------------------------------------------------------------------+

...

MariaDB [(none)]> SHOW PROFILE FOR QUERY 3;

...

| Filling schema table           | 0.759816 |

...

I guess mariabackup could make bulk query to information_schema to get all the metadata it needs in one go?

[MDEV-17195] mariabackup is very slow on databases with many tables Created: 2018-09-14 Updated: 2021-09-13 Resolved: 2018-10-31
Status:	Closed
Project:	MariaDB Server
Component/s:	mariabackup
Affects Version/s:	10.2.17
Fix Version/s:	10.2.19

[MDEV-17195] mariabackup is very slow on databases with many tables Created: 2018-09-14 Updated: 2021-09-13 Resolved: 2018-10-31