I’ve set up a new website and actually I’m preparing some “load” testing regarding a couple of search queires.
I’ve seen a behaviour that I cannot explain, so maybe you guys have some answers?
There is Mysql 5.0.30 running on a 64bit Debian Etch distribution. Server has two XEON CPUs and 8 GB of Ram. We mostly use MyIsam tables so I’ve set the key_buffer to the maximum of 4 GB.
Next there are 31 tables in a seperate database with overall 111 million rows. Each table has an average index size of 100 MB (datasize 170 MB). The “data” is split among 31 tables due to partioning ( so we don’t have to deal with MyIsam locking issues too much).
The table structures are identical:
and only one primary key (index) on the fields (userID1, userID2).
Now our application typically tries to find records where userID1 is given and sometimes userVote1 or userVote2 is queried, e.g.
SELECT userID2 FROM table_1 WHERE userID1 = 100 AND userVote1=2
So far so good. The first time a query is started it typically lasts about 5-10 seconds (sometimes 10-20 seconds) for the query above. Each subsequent query for the specified userID (in the example ‘100’) then only lasts about 0.2 - 0.4 seconds.
This is not due to query cache( because it’s still the same when we use SQL_NO_CACHE). I suggest the performance increase is a result of loading the key cache for that table.
When I send random queries for userIDs between 1 and 10000 (which are stored in table table_1) after a couple of queries every following query only needs 2 - 5 seconds. So thats fine (and again I guess a result of the Key Cache).
But: When I start to spread queries to all of the tables (actually 31, userID1 between 1 and 300000) , there’s no such a performance increase, altough there should be enough memory for mysql to hold all keys (overall size about 3.1 GB) in RAM.
Do you have any idea, why the “key cache” thing only works, when I spread the queries to about 5 tables. Also using a seperate key cache (SET GLOBAL keycache bla bla) doesn’t help.
I’ve also tried to use LOAD INDEX INTO CACHE table1, table2… but that also doesn’t help.
Does anyone have an idea? Our variables regarding key_cache are:
Key_blocks_unused 1.646 k
Key_blocks_used 418 k
Key_read_requests 2.429 M
Key_reads 88 M
Key_write_requests 56 M
Key_writes 36 M
Key_buffer_fraction_% 19,64 %
Would the “Midpoint Insertion Strategy” help?
If so, what do you guess to set the key_cache_division_limit value?
I appreciate any help.