Details
High 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)