Sudden drop in read performance
Hi Everyone,
I have had a sudden drop in read performance on one of my databases and not sure if it’s a design problem with my database, or if some settings for the server itself need to be tweaked. I have provided the details of the table in question and a sample query. Please let me know if you have any recommendations or require any further info.
I have a table which has over 40 million rows in it. Up until a few days ago, queries were typically coming back in under 2 seconds. Now the queries are taking 10 to 13 seconds to return.
The queries only use the primary key in the where clause and can return up to a maximum of 30,000 rows.
Thanking you all in advanced.
Table:
CREATE TABLE `METRIC` (
`MET_DESC_ID` int(11) NOT NULL,
`APP_ID` int(11) NOT NULL,
`START_TS` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`SOURCE` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`M_VALUE` decimal(15,2) DEFAULT NULL,
`END_TS` datetime DEFAULT NULL,
`ETL_LOAD_ID` int(11) NOT NULL,
PRIMARY KEY (`APP_ID`,`MET_DESC_ID`,`SOURCE`,`START_TS`),
KEY `ETL_LOAD_ID` (`ETL_LOAD_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY KEY (MET_DESC_ID,APP_ID,`SOURCE`)
PARTITIONS 10 */
Table Statistics
TABLE_NAME - METRIC
ENGINE - MyISAM
TABLE_ROWS - 40606826
AVG_ROW_LENGTH - 47 Bytes
DATA_LENGTH - 1.76 GB
INDEX_LENGTH - 1.05 DB
Sample Query (NOTE : Query shouldn't try and get data across multiple partitions)
select * from METRIC where MET_DESC_ID = 23 and APP_ID = 3 and SOURCE = 'servername' and START_TS > '2011-05-01' and START_TS < '2012-05-01';
Sample Query Explain
type - range
possible_keys - PRIMARY
key - PRIMARY
key_len - 63
ref - NULL
rows - 23869
Extra - Using Where
Server Specs :
VMware - 4 cores - 8GB RAM - Suse 11.3