Challenging performance demand
I've got a Dual processor Dell poweredge 1750 with 2 SCSI drives in it and 2 gigs of RAM.
Its sole purpose is to run the following 2 queries on the following table at the uttermost possible speed (ie I need performance on the order of 5 to 10 thousand queries per second).
CREATE TABLE `stocklevel2` (
`symbol` char(12) NOT NULL default '',
`lastupdated` char(10),
`mmid` char(4) NOT NULL default '',
`price` float(10,4) NOT NULL default '0.0000',
`side` enum('B','S') NOT NULL default 'B',
`size` bigint(20) unsigned NOT NULL default '0',
`exchange` char(2) NOT NULL default '',
PRIMARY KEY (`symbol`,`mmid`,`side`,`exchange`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;
I have 2 basic SELECTS, 99% of them are like
SELECT * FROM stocklevel2 WHERE symbol=? AND mmid=? AND side=? AND exchange=?
A few are more general SELECT * FROM stocklevel2 WHERE symbol=?
These are split 50/50 with INSERT/UPDATEs
INSERT INTO stocklevel2 (<all columns>) VALUES(<every column has a value>) ON DUPLICATE KEY UPDATE set <and again we set everything>
All of this actually works PRETTY well, generally. However we're still not quite there on the speed front... Generally table size is in the 100k row range. I've got a key cache big enough (judging by the size of the index file) to cache the entire index in RAM.
Any other suggestions?