Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have no expirience with MySQL and database performance tuning in general.

Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within 30ms per request in average.

Some details:

  • PK is 16 bytes (it's UUID stored as binary(16)).
  • Each record is approximately 100 bytes and can be shrinked to 24 bytes if normalized (though cardinality is approximately 10 to 1 and we need joins in this case).
  • Table should be writeable. There may be 1 update per 1 select, though updates can be deferred and their performance is less critical.
  • I could allocate up to 8GB for mysql needs.
  • Disk is a regular HDD (this concerns me the most, because seek time is about 9ms)

Please, suggest, what storage engine is preferable, what mysql parameters should be tuned. Also suggest, please, some technics (perhaps selecting groups of records with where in() against record by record, etc.) to improve performance.

Thanks in advance.

share|improve this question
    
Can you post an show create table [table_name] here? and the query(s) you intended to run on your data without this basic information because the answers will be based on guessing and will be most likely be useless answers... –  Raymond Nijland Dec 7 '13 at 17:21

1 Answer 1

The problem here is "large number of queries" which you likely have other ideas of than me (who says sure - you need a lot of hardware for 30.000 queries per second).

Generally otherwise - yes. This is possible.

Lots of memory needed ;) keep it all in memory.

O rget a proper disc subsystem. Seriously, your "regular hard disc" is dead slow. Like terribly slow. SSD area good and low cost alternative (roughly 550 times as fast as your hard disc in random access). Unless you RAM up to keep it all in memory your disc IO is the typical bottleneck... and SSD are the cheapest solution. Unless you REALLY up your RAM you simply have no other alternative than gettig some decent IO capabilities, the (end user level) hard disc (i.e. a non SAS 15k high performance disc) just wont get you far.

share|improve this answer
    
I've edited the question. Actually 10M of records work for me, data fits into RAM in this case. Problems arise when there are more than this. Sorry for misleading. –  edio Oct 8 '13 at 9:40
    
I know, that hardware is the key. But would like to squeeze maximum with what I have right now. Would appreciate suggestions on db engine choice, mysql configuration, etc.. –  edio Oct 8 '13 at 9:49
    
Problem is there is not a lot you CAN do - the momentyou need more memory than you have you hit the IOPS limit. And that is where "tuning" is over because "physics" takes over, and physics says moving a head costs time. –  TomTom Oct 8 '13 at 10:00
    
MyISAM engine would still trigger random disk I/O lookups (expensive) on .MYD files based on ROWID pointer because the data isnt cached but only indexes... so the Lots of memory needed isnt always an good statement.. –  Raymond Nijland Dec 7 '13 at 17:24
    
See. Random disc IO is not expensive with a SSD. And a SSD For some small tiny amount of data (such as here) is CHEAP. So, where is the problem? The IO Problem is where "physics takes over" comes in, but with something that tiny that means "use SSD". –  TomTom Dec 8 '13 at 8:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.