MySQL Forums
Forum List  »  Performance

Slow query on partitioned tables with over 200M rows
Posted by: Hong Yang
Date: February 11, 2014 05:10PM

I have a partitioned raw data table, one partition is created per day, and only the last 30 days of data is kept. Each partition of the table contains over 200M rows.
The following query is executed:
SELECT device_key, component_key
FROM `raw_data` as raw
WHERE
raw.fct_ts >= "2014-02-10 00:00:00"
AND raw.fct_ts < "2014-02-11 00:00:00"
AND raw.device_key =? AND raw.component_key = ?

The query timed out after 10 minutes of execution. Only when I replaced the search criteria raw.fc_ts range with a specific fct_ts value, the results came back immediately.

Here is table definition:
CREATE TABLE `raw_data` (
`device_key` int(11) NOT NULL COMMENT 'Foreign key for the device dimension table.',
`component_key` int(11) NOT NULL COMMENT 'Foreign key for a component dimension table.',
`fct_ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of the outputoctets__bytes measurement.',
`fct_resolution` int(10) unsigned NOT NULL COMMENT 'Seconds over which the outputoctets__bytes measurement was taken.',
`fct_value` double NOT NULL COMMENT 'Value of the outputoctets__bytes measurement.',
PRIMARY KEY (`device_key` , `component_key` , `fct_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (unix_timestamp(fct_ts)) (PARTITION p735638 VALUES LESS THAN (1392012000) ENGINE = InnoDB , PARTITION p735639 VALUES LESS THAN (1392098400) ENGINE = InnoDB , PARTITION pEOW VALUES LESS THAN MAXVALUE ENGINE = InnoDB)$$

EXPLAIN PARTITION-
'1', 'SIMPLE', 'raw', 'p735639', 'range', 'PRIMARY', 'PRIMARY', '12', NULL, '229101935', 'Using where; Using index'

EXPLAIN PLAN
'1', 'SIMPLE', 'raw', 'range', 'PRIMARY', 'PRIMARY', '12', NULL, '229101935', 'Using where; Using index'


The mysql(version 5.6) configuration
innodb_buffer_pool_size = 100G
# log file size should be 25% of of buffer pool size
innodb_log_file_size = 1892M
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
# In previous releases of MySQL, this was recommended to be set to 2 times the
# number of CPUs, however the default and recommended option in 5.5 is to not
# set a bound on the thread pool size.
innodb_thread_concurrency = 0

Any suggestions for improving the query performance?
Will changing the order of the PK from (fct_ts, device_key, component_key) to (device_key, component_key, fct_ts) help?
Any known issue of having timestamp as part of the PK?


Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query on partitioned tables with over 200M rows
2637
February 11, 2014 05:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.