MySQL Forums
Forum List  »  Performance

Re: Sudden drop in read performance
Posted by: Rick James
Date: May 23, 2012 11:18PM

Run EXPLAIN PARTITIONS SELECT ...; to verify that it is hitting only the one partition.

SHOW VARIABLES LIKE 'key_buffer_size';

> START_TS > '2011-05-01' and START_TS < '2012-05-01'
You are accidentally skipping both midnights?

My mantra "Count the disk hits".

* 30K rows.
* Index will be compactly scanned
* Data is probably scattered
* Worst case (scattered rows): 30K disk reads to get the data rows. That could be 300 seconds.
* Best case (adjacent rows): 30K * 47B = 1.4MB.
* 1.4MB / 1KB = 1400 blocks to read (or find cached). That could be 14 seconds.

From another approach...
* 1.76GB in 10 partitions; assume evenly spread
* 4M rows per partition
* 176MB of data per partition
* Perhaps 5GB of file cache in OS (based on 8GB of RAM).
* That's more than enough to have the entire table cached.

What happens if you run the same query a second time?
* First run: slow; but it is caching the data
* Second run: 10 times as fast (data is cached)
* Or, second time takes essentially 0 seconds -- this would imply that the Query cache kicked in.

What other queries do you have?
If this is the only query, then non-PARTITIONed InnoDB would run considerably faster. This is because the data is "clustered" on the PRIMARY KEY, and the PK is perfect for this query.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Sudden drop in read performance
1373
May 23, 2012 11:18PM
1235
June 12, 2012 11:03PM


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.