| Bug #42061 | Different intervals for purging cause the shorter interval to full scan dc table | ||
|---|---|---|---|
| Submitted: | 12 Jan 2009 19:42 | Modified: | 21 Jul 2009 14:53 |
| Reporter: | Mark Leith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Enterprise Monitor: Server | Severity: | S3 (Non-critical) |
| Version: | 2.0.1.7125 | OS: | Any |
| Assigned to: | MC Brown | CPU Architecture: | Any |
[17 Mar 2009 13:16]
Eric Herman
in the trunk delete is now DELETE FROM dc_foo WHERE end_time <= ? LIMIT ? mysql> -- DELETE FROM dc_ng_string_now WHERE end_time <= 1236685908715 LIMIT 10000 mysql> EXPLAIN SELECT * FROM dc_ng_string_now WHERE end_time <= 1236685908715 LIMIT 10000; +----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | dc_ng_string_now | range | end_time | end_time | 8 | NULL | 1 | Using where | +----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql>
[30 Apr 2009 18:41]
Diego Medina
Verified fixed on 2.1.0.1032 mysql> EXPLAIN SELECT * FROM dc_ng_string_now WHERE end_time <= 1241114637951 LIMIT 10000; +----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+ | 1 | SIMPLE | dc_ng_string_now | range | end_time | end_time | 8 | NULL | 239727583 | Using where | +----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+ 1 row in set (0.00 sec) (Tested with 8 weeks of data )
[21 Jul 2009 14:53]
Tony Bedford
An entry was added to the 2.1.0 changelog:
Using a long interval for the long data collection purging (such as 6 weeks), and a short interval for the query analysis purging (such as 1 week) caused the Query Analyzer purge EXPLAIN for the INSERT ... SELECT into the temp_dc_ng_*_now table to perform a full scan on the dc_ng_*_now.end_time index. For example:
explain SELECT instance_attribute_id, end_time, end_time FROM dc_ng_long_now JOIN
inventory_instance_attributes USING (instance_attribute_id) JOIN inventory_instances
USING (instance_id) WHERE dc_ng_long_now.end_time <= 1230814870074 AND
dc_ng_long_now.instance_attribute_id AND type_id in (8, 9, 7, 6) ORDER BY
dc_ng_long_now.end_time ASC LIMIT 10000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dc_ng_long_now
type: range
possible_keys: PRIMARY,end_time
key: end_time
key_len: 8
ref: NULL
rows: 8205369
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: inventory_instance_attributes
type: eq_ref
possible_keys: PRIMARY,instance_id
key: PRIMARY
key_len: 4
ref: mem.dc_ng_long_now.instance_attribute_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: inventory_instances
type: eq_ref
possible_keys: PRIMARY,FKD4320F5BBDD9C29B
key: PRIMARY
key_len: 4
ref: mem.inventory_instance_attributes.instance_id
rows: 1
Extra: Using where
3 rows in set (0.55 sec)
Description: Using a long interval for the long data collection purging (such as 6 weeks), and a short interval for the query analysis purging (such as 1 week) causes the QUAN purge EXPLAIN for the INSERT ... SELECT in to the temp_dc_ng_*_now table to full scan the dc_ng_*_now.end_time index, such as the following SELECT: explain SELECT instance_attribute_id, end_time, end_time FROM dc_ng_long_now JOIN inventory_instance_attributes USING (instance_attribute_id) JOIN inventory_instances USING (instance_id) WHERE dc_ng_long_now.end_time <= 1230814870074 AND dc_ng_long_now.instance_attribute_id AND type_id in (8, 9, 7, 6) ORDER BY dc_ng_long_now.end_time ASC LIMIT 10000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dc_ng_long_now type: range possible_keys: PRIMARY,end_time key: end_time key_len: 8 ref: NULL rows: 8205369 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: inventory_instance_attributes type: eq_ref possible_keys: PRIMARY,instance_id key: PRIMARY key_len: 4 ref: mem.dc_ng_long_now.instance_attribute_id rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: inventory_instances type: eq_ref possible_keys: PRIMARY,FKD4320F5BBDD9C29B key: PRIMARY key_len: 4 ref: mem.inventory_instance_attributes.instance_id rows: 1 Extra: Using where 3 rows in set (0.55 sec) How to repeat: On an instance with 7 weeks of data: o Set the data collection purge to 6 weeks o Set the query analysis purge to 1 week o Check the run times of the queries with SHOW PROCESSLIST/SHOW INNODB STATUS for the MEM repository Suggested fix: Researching now.