MySQL Forums
Forum List  »  Performance

Challenging performance demand
Posted by: Tod Harter
Date: June 02, 2005 03:16PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Challenging performance demand
2385
June 02, 2005 03:16PM
1680
June 03, 2005 07:10AM
1612
July 13, 2005 10:10AM
1646
July 14, 2005 10:02AM
1729
July 14, 2005 12:03PM
1648
July 17, 2005 03:07AM


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.