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?
Attachments
Activity
This issue was fixed https://github.com/MariaDB/server/commit/f8268f3cce4577c28ab62e53293556d05a74fb1a and relates to the way mariabackup uses information tables. This fix reduces the number of queries and doesn't introduce new queries. The issue you are seeing isn't this one.
Increasing table_open_cache will always improve the querying of information_schema tables that reference ordinary tables. This is why the fix in this bug reduced this to occuring once.
The issue of increased backup time to this large factor is important, however its not this issue.
Is there any update on this issue ?
We experiencing the same issue with mysqldump and mariadb 10.5.12.
We have around 350.000 tables, with mariadb 10.1, the backup of all the 200 databases was taking around 30 min. Now it's around 6 hours.
We can see in wait states during regular queries not especially backup, that MariaDB is passing 20% in Statistics. When it was around 1% with MariaDB 10.1
We were able to reproduce this behavior on a local computer, increasing table_open_cache seems to improve by a lot the slow access to information_schema database.
For now we don't have enough RAM in this server to increase TABLE_OPEN_CACHE as much as our tables amount.
With TABLE_OPEN_CACHE set to 131072.