Uploaded image for project: 'Percona XtraBackup'
  1. Percona XtraBackup
  2. PXB-1698

Percona Xtrabackup scanning all rows of partitioned tables when using --lock-ddl-per-table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Pending Release
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.4.12
    • Fix Version/s: 2.4.13, 8.0.5
    • Component/s: None
    • Labels:
      None
    • Environment:

      Percona Xtrabackup 2.4.12
      Percona Server 5.7.22

      Description

      Hi,

      When using --lock-ddl-per-table option with Xtrabackup,  to perform MDL lock the tool issues a query for each table the has to be locked 

      SELECT * FROM <DBNAME>.<TABLE> LIMIT 1 ;

       

      In case if the table is partitioned, xtrabackup is trying to issue similar queries over every partition. Below is sample query for a table with 3 partitions.

      SELECT * FROM <DBNAME>.<TABLE>#P#P0 LIMIT 1;
      
      SELECT * FROM <DBNAME>.<TABLE>#P#P1 LIMIT 1;
      
      SELECT * FROM <DBNAME>.<TABLE>#P#P3 LIMIT 1;
      

       

       These are resulting full table scan on respective tables which take longer times in case of the table is huge and might result in timeout and backup failures. I have re produced this locally. Please see the steps I followed to re produce.

       

      • Created a test table with range partitioning.
      CREATE TABLE `test` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(50) DEFAULT NULL,
       `age` int(4) DEFAULT '0',
       PRIMARY KEY (`id`),
       KEY `age` (`age`)
      ) ENGINE=InnoDB AUTO_INCREMENT=400000 DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (id)
      (PARTITION P0 VALUES LESS THAN (100000) ENGINE = InnoDB,
       PARTITION P1 VALUES LESS THAN (200000) ENGINE = InnoDB,
       PARTITION P2 VALUES LESS THAN (300000) ENGINE = InnoDB,
       PARTITION P3 VALUES LESS THAN (400000) ENGINE = InnoDB) */ ;
      • Loaded some test data into this table.
      mysql> select count(*) from test ;
      +----------+
      | count(*) |
      +----------+
      | 399999 |
      +----------+
      1 row in set (0.09 sec)

       

      • Enabled slow query log and have run the backup script on only that database to keep it simple. 
      xtrabackup --version ; -uroot -pxxxx --socket=/var/lib/mysql1/mysqld.sock --lock-ddl-per-table --backup --databases <DBNAME> --datadir=/var/lib/mysql1/

       

      • Backup completed successfully. I looked into the SELECT queries on partitioned tables for the number rows that they retrieve. 
      SELECT * FROM DB1.test#P#P2 LIMIT 1;
      SELECT * FROM DB1.test#P#P0 LIMIT 1;
      SELECT * FROM DB1.test#P#P3 LIMIT 1;
      SELECT * FROM DB1.test#P#P1 LIMIT 1;

       

      Surprisingly only these queries are retrieving all the rows. Please see the below snippet.

      # Time: 2018-11-06T09:36:28.414246Z
      # User@Host: root[root] @ localhost [] Id: 13083
      # Schema: Last_errno: 0 Killed: 0
      # Query_time: 0.151925 Lock_time: 0.000171 Rows_sent: 399999 Rows_examined: 399999 Rows_affected: 0
      # Bytes_sent: 24130998
      SET timestamp=1541496988;
      SELECT * FROM DB1.test#P#P2 LIMIT 1;
      

      Suggested fix:

      It looks the query format used for partitioning itself is creating the problem. Please see below. I think we can ignore querying each partition when it comes to MDL lock as simple SELECT * FROM test LIMIT 1 will anyway lock the metadata.

       

      mysql> \P md5sum
      PAGER set to 'md5sum'
      mysql> SELECT * FROM test#P#P0 LIMIT 1;
       -> ;
      175c8e161f3e11b2c6b47dd108f1315b -
      399999 rows in set (0.18 sec)
      
      mysql> SELECT * FROM test PARTITION (P0) LIMIT 1;
      53c8fd558eb1485436f18ce42008f274 -
      1 row in set (0.00 sec)
      

       

       

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko
                Reporter:
                uday.varagani Uday Varagani
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: