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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.17
Fix Version/s: 10.2.19
Component/s: mariabackup
Labels:
None
Environment:
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mariabackup_slow_mysql_session.txt
106 kB
2018-09-14 10:11
screenshot-1.png
224 kB
2021-09-12 13:37

Activity

Ascending order - Click to sort in descending order

YURII KANTONISTOV created issue - 2018-09-14 10:25

Vladislav Vaintroub made changes - 2018-09-14 11:34

Field	Original Value	New Value
Assignee		Vladislav Vaintroub [ wlad ]

Elena Stepanova made changes - 2018-09-14 14:01

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?

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:

{noformat}
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 '%%/%%' |
{noformat}

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:
{noformat}
[mysql@fies1udb32 ~]$ du -skh /mysql_data
34G /mysql_data
{noformat}

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

{noformat}
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 |
...
{noformat}

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

Elena Stepanova made changes - 2018-10-14 17:36

Fix Version/s

10.2 [ 14601 ]

Vladislav Vaintroub made changes - 2018-10-31 16:13

Attachment

MDEV-17195.url [ 46656 ]

Vladislav Vaintroub made changes - 2018-10-31 16:13

Attachment

~~MDEV-17195~~.url [ 46656 ]

Vladislav Vaintroub made changes - 2018-10-31 16:21

issue.field.resolutiondate

2018-10-31 16:21:31.0

2018-10-31 16:21:31.363

Vladislav Vaintroub made changes - 2018-10-31 16:21

Fix Version/s		10.2.19 [ 23207 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Lionel Enkaoua made changes - 2021-09-12 13:37

Attachment

screenshot-1.png [ 58927 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89590 ]

MariaDB v4 [ 154929 ]

People

Assignee:: Vladislav Vaintroub

Reporter:: YURII KANTONISTOV

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-09-14 10:25

Updated:: 2021-09-13 01:29

Resolved:: 2018-10-31 16:21

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration