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

mariabackup is very slow on databases with many tables

    XMLWordPrintable

    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

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration