MySQL select by primary key against 100M of records within 30ms

Question

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.

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.