Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17195

mariabackup is very slow on databases with many tables

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.17
    • 10.2.19
    • mariabackup
    • 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

          People

            wlad Vladislav Vaintroub
            ykantoni YURII KANTONISTOV
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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