Issue :
When querying INFORMATION_SCHEMA.PARTITIONS table for a partition that does not exist, the query takes very long time to return:
For example, the following query would take long time for the table defined below:
SELECT 1 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'PartitionTable' AND PARTITION_NAME = 'P1493057460'
|
We have a partitioned table that is using RANGE partitioning. We use the the bigint(20) column 'utime' to represent the epoch timestamp of a record.
*************************** 1. row ***************************
|
Table: PartitionTable
|
Create Table: CREATE TABLE `PartitionTable` (
|
`rule_id` int(10) unsigned NOT NULL,
|
`rev` int(10) unsigned DEFAULT NULL COMMENT 'revision number',
|
`utime` bigint(20) unsigned DEFAULT '0',
|
`s0` varchar(128) NOT NULL,
|
`s1` varchar(128) NOT NULL,
|
`s2` varchar(128) DEFAULT NULL,
|
`s3` varchar(128) DEFAULT NULL,
|
`s4` varchar(128) DEFAULT NULL,
|
`s5` varchar(128) DEFAULT NULL,
|
`s6` varchar(128) DEFAULT NULL,
|
`s7` varchar(128) DEFAULT NULL,
|
`r0` text NOT NULL,
|
`r1` text NOT NULL,
|
`r2` text,
|
`r3` text,
|
`r4` text,
|
`r5` text,
|
KEY `match_all` (`rule_id`,`rev`,`s0`,`s1`,`s2`,`s3`,`s4`,`s5`,`s6`,`s7`,`utime`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (utime)
|
(PARTITION P1493056200 VALUES LESS THAN (1493056260) ENGINE = InnoDB,
|
PARTITION P1493056260 VALUES LESS THAN (1493056320) ENGINE = InnoDB,
|
PARTITION P1493056320 VALUES LESS THAN (1493056380) ENGINE = InnoDB,
|
PARTITION P1493056380 VALUES LESS THAN (1493056440) ENGINE = InnoDB,
|
PARTITION P1493056440 VALUES LESS THAN (1493056500) ENGINE = InnoDB,
|
PARTITION P1493056500 VALUES LESS THAN (1493056560) ENGINE = InnoDB,
|
PARTITION P1493056560 VALUES LESS THAN (1493056620) ENGINE = InnoDB,
|
PARTITION P1493056620 VALUES LESS THAN (1493056680) ENGINE = InnoDB,
|
PARTITION P1493056680 VALUES LESS THAN (1493056740) ENGINE = InnoDB,
|
PARTITION P1493056740 VALUES LESS THAN (1493056800) ENGINE = InnoDB,
|
PARTITION P1493056800 VALUES LESS THAN (1493056860) ENGINE = InnoDB,
|
PARTITION P1493056860 VALUES LESS THAN (1493056920) ENGINE = InnoDB,
|
PARTITION P1493056920 VALUES LESS THAN (1493056980) ENGINE = InnoDB,
|
PARTITION P1493056980 VALUES LESS THAN (1493057040) ENGINE = InnoDB,
|
PARTITION P1493057040 VALUES LESS THAN (1493057100) ENGINE = InnoDB,
|
PARTITION P1493057100 VALUES LESS THAN (1493057160) ENGINE = InnoDB,
|
PARTITION P1493057160 VALUES LESS THAN (1493057220) ENGINE = InnoDB,
|
PARTITION P1493057220 VALUES LESS THAN (1493057280) ENGINE = InnoDB,
|
PARTITION P1493057280 VALUES LESS THAN (1493057340) ENGINE = InnoDB,
|
PARTITION P1493057340 VALUES LESS THAN (1493057400) ENGINE = InnoDB,
|
PARTITION P1493057400 VALUES LESS THAN (1493057460) ENGINE = InnoDB) */
|
1 row in set (0.00 sec)
|
We have an application that inserts contantly into the table using prepared statments. Each partition will end up with around 200k records (ie, 200k entries inserted in one minutes)
Every hour we create 60 new partitions for the table, and then drop the all the partitions that are created more than two hours ago:
Partition Creation:
We first get the latest partition's upper limit, and store it in p_start_time
SELECT MAX(CAST(PARTITION_DESCRIPTION AS UNSIGNED)) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'PartitionTable';
|
We then have name of the new partition by CONCAT'ing 'P' and the timestamp. Next, we check the existence of a new partition. If the new partition does not exist ("SELECT 1" returns 0"), then we create the new partition.
SELECT 1 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'PartitionTable' AND PARTITION_NAME = CONCAT('P',p_start_time) INTO p_exists;
|
IF (p_exists < 1) THEN
|
SET @sql := CONCAT('ALTER IGNORE TABLE `test`.`PartitionTable` ADD PARTITION ( PARTITION P', p_start_time ,' VALUES LESS THAN (',p_start_time + 60,') );');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
We increament p_start_time value and repeat the above operation for 60 times, thus create 60 new partitions.
Partition Drop:
We then run
ALTER TABLE test.PartitionTable DROP PARTITION <partition_name>
|
repeatly to drop all partitions that are more than two hours old.
After upgrading from 10.1.15 to 10.1.19 and to 10.1.22, we are seeing that the query in the partition creation part
SELECT 1 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'PartitionTable' AND PARTITION_NAME = CONCAT('P',p_start_time)
|
now takes very long time to complete, more than 20 seconds or even longer.
However, once a partition is created, such query would return in no time (around 0.05 seconds or so when measured by bash time builtin function).
my.cnf:
[mysqld]
|
datadir=/var/mysql/data
|
socket=/var/lib/mysql/mysql.sock
|
user=mysql
|
skip-name-resolve
|
tmpdir=/var/tmp/mysql
|
innodb_fast_shutdown=0
|
sql_mode=
|
pid-file=/var/run/mysqld/mysqld.pid
|
|
auto_increment_offset=1
|
max_allowed_packet=67108864
|
long_query_time=0
|
innodb_write_io_threads=64
|
innodb_autoinc_lock_mode=2
|
max_prepared_stmt_count=65528
|
table_cache=2048
|
thread_cache_size=500
|
table_definition_cache=400
|
max_connections=8000
|
innodb_log_buffer_size=32M
|
event_scheduler=ON
|
innodb_flush_method=O_DIRECT
|
innodb_flush_log_at_trx_commit=2
|
innodb_thread_concurrency=0
|
key_buffer_size=128M
|
query_cache_type=0
|
innodb_file_per_table=1
|
myisam-recover=FORCE,BACKUP
|
bind_address=127.0.0.1
|
innodb_io_capacity=100
|
query_cache_size=0
|
innodb_log_file_size=512M
|
innodb_stats_on_metadata=OFF
|
auto_increment_increment=1
|
innodb_buffer_pool_size=8G
|
innodb_support_xa=false
|
|
|
[mysqld_safe]
|
pid-file=/var/run/mysqld/mysqld.pid
|
|