MySQL Forums
Forum List  »  Performance

Re: Challenging performance demand
Posted by: Tod Harter
Date: June 03, 2005 07:10AM

stocklevel2 has a composite index on symbol, mmid, side, exchange.

The queries I'm doing are really exceedingly simple and explain shows indexes are used for every query. Essentially the VAST majority of SELECTS are fetching a single unique row, a small percentage select only on the symbol field, which is the leftmost prefix of the unique key. Its cardinality is generally something in the range of 20 (so for any given symbol there are something like < 20 rows which will match). So explain shows all matches are against const values and use the index.

Currently I'm getting performance on the order of 4000 queries per second, 50/50 mixture of SELECT and INSERT ... ON DUPLICATE KEY UPDATE. There are absolutely no deletes required so the table SHOULD fall into the category of 'MyISAM table with no holes in it' and be eligible for full paralellization of INSERT and SELECT.

One thing I'm wondering is if, given the simplicity of the database operations, it might be useful to issue direct HANDLER calls to the underlying table engine, though i don't see a way with that syntax to duplicate the benefits of the INSERT ... ON DUPLICATE KEY UPDATE mechanism.

In this application dirty reads are perfectly acceptable, so realistically I could care less about ANY sort of locking. Given the reasonably modest size of the dataset and the fact that I don't really care about the data in this table once the day is over I could try going with a MEMORY (HEAP) table. The only consideration there is "would it be faster". I'm not sure given the less sophisticated form of indexing present in the HEAP engine. OTOH I could try using a BTREE indexed HEAP table... hmmm, choices choices. Anyone have any experience with this type of application and which technique is likely to pay off?

Options: ReplyQuote


Subject
Views
Written By
Posted
2385
June 02, 2005 03:16PM
Re: Challenging performance demand
1679
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.