I have a large table (over 2 billion records) which is partitioned. Each partition contains roughly 500 million records. I have recently moved from physical hardware to AWS, i used a mysqldump to backup and restore the MySQL data. I have also recently created a new partition (p108). Querying data from old partitions (created on the old server) are running as normal, very quick, returning data in seconds. However querying records in the newly created partition (p108) is very slow - minutes.
show create table results
CREATE TABLE `termusage`
(
`id` BIGINT(20) NOT NULL auto_increment,
`terminal` BIGINT(20) DEFAULT NULL,
`date` DATETIME DEFAULT NULL,
`dest` VARCHAR(255) DEFAULT NULL,
`feattrans` BIGINT(20) DEFAULT NULL,
`cost_type` TINYINT(4) DEFAULT NULL,
`cost` DECIMAL(16, 6) DEFAULT NULL,
`gprsup` BIGINT(20) DEFAULT NULL,
`gprsdown` BIGINT(20) DEFAULT NULL,
`duration` TIME DEFAULT NULL,
`file` BIGINT(20) DEFAULT NULL,
`custcost` DECIMAL(16, 6) DEFAULT '0.000000',
`invoice` BIGINT(20) NOT NULL DEFAULT '99999999',
`carriertrans` BIGINT(20) DEFAULT NULL,
`session_start` DATETIME DEFAULT NULL,
`session_end` DATETIME DEFAULT NULL,
`mt_mo` VARCHAR(4) DEFAULT NULL,
`grps_rounded` BIGINT(20) DEFAULT NULL,
`gprs_rounded` BIGINT(20) DEFAULT NULL,
`country` VARCHAR(25) DEFAULT NULL,
`network` VARCHAR(25) DEFAULT NULL,
`ctn` VARCHAR(20) DEFAULT NULL,
`pricetrans` BIGINT(20) DEFAULT NULL,
PRIMARY KEY (`id`, `invoice`),
KEY `idx_terminal` (`invoice`, `terminal`),
KEY `idx_feattrans` (`invoice`, `feattrans`),
KEY `idx_file` (`invoice`, `file`),
KEY `termusage_carriertrans_idx` (`carriertrans`),
KEY `idx_ctn` (`invoice`, `ctn`),
KEY `idx_pricetrans` (`invoice`, `pricetrans`)
)
engine=innodb
auto_increment=17449438880
DEFAULT charset=latin1
/*!50500 PARTITION BY RANGE COLUMNS(invoice)
(PARTITION p103 VALUES LESS THAN (621574) ENGINE = InnoDB,
PARTITION p104 VALUES LESS THAN (628214) ENGINE = InnoDB,
PARTITION p106 VALUES LESS THAN (634897) ENGINE = InnoDB,
PARTITION p107 VALUES LESS THAN (649249) ENGINE = InnoDB,
PARTITION p108 VALUES LESS THAN (662763) ENGINE = InnoDB,
PARTITION plast VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
I created the partition p108 using the following query
ALTER TABLE termusage reorganize partition plast
INTO ( partition p108 VALUES less than (662763),
partition plast VALUES less than maxvalue )
I can see the file termusage#p#p108.ibd and looks to be "normal" and the data is there as i can get results from the query.
information_schema.PARTITIONS shows the following for the table - which indicates there is some kind of issue
Name Pos Rows Avg Data Length Method
p103 1 412249206 124 51124371456 RANGE COLUMNS
p104 2 453164890 133 60594061312 RANGE COLUMNS
p106 3 542767414 135 73562849280 RANGE COLUMNS
p107 4 587042147 129 76288098304 RANGE COLUMNS
p108 5 0 0 16384 RANGE COLUMNS
plast 6 0 0 16384 RANGE COLUMNS
How can i fix the partition ?
Explain for good query
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, t, p107, ref, idx_terminal,idx_feattrans,idx_file,idx_ctn,idx_pricetrans, idx_terminal, 17, const,const, 603, 100.00, Using index condition; Using temporary; Using filesort
Explain for poor query
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, t, p108, ALL, idx_terminal,idx_feattrans,idx_file,idx_ctn,idx_pricetrans, , , , 1, 100.00, Using where; Using temporary; Using filesort
For future readers, the issue was resolved by running ALTER TABLE ... ANALYZE PARTITION p108.
The table and index statistics that guide the optimizer to choose the best way to read the table were out of date. It's common to use ANALYZE to make sure these statistics are updated after a significant data load or delete.