Re: Sudden drop in read performance

[Douglas McGowan]

Hello Rick,

Explain partitions returned just p3

Here is the output for the show variables like ‘key_buffer_size’

Variable_name - key_buffer_size
Value - 268435456 (256 MB)

There is one other more complicated query but it’s also dependant on the primary key. This one does potentially select across partitions. It selects from the metric table and then joins back onto the metric table (these queries are used to provide data for a regression graph)

select * from METRIC a inner join METRIC b on a.START_TS = b.START_TS where a.MET_DESC_ID = 23 and a.APP_ID = 3 and a.SOURCE = 'server1' and b.MET_DESC_ID = 23 and b.APP_ID = 3 and b.SOURCE = 'server2' and a.START_TS > '2011-05-01 00:00:00' and a.START_TS < '2012-05-01 23:59:59';

Second Sample Query 2 explain
Type – range; eq
Possible keys – primary; primary
Key – primary; primary
Key_len – 63; 63
Ref – NULL; const,const,const,CAPMANDB.b.START_TS
Rows – 24829; 1
Extra – Using where; Using where

The query takes almost 0 time to run the second time its triggered so I’d say the cache is working

I was always told MyISAM engine was always faster than InnoDB but what you mentioned makes sense. Plus I probably didn’t need to partition the table. I’ll try creating a new InnoDB table without partitioning and see how it runs.

Thanks again.