Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.17
-
None
-
CentOS Linux release 7.5.1804 (Core)
Linux fies1udb32.tellabs.fi 3.10.0-862.11.6.el7.x86_64 #1 SMP Tue Aug 14 21:49:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Description
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?
