Re: random reads

[Rick James]

Don't let key_buffer_size get to a full 4GB -- there is a hard limit. Values of 4GB or more are wrapped. (You can create multiple key_buffers, but that is another story.)

You say "20 threads connected". Well I often see hundreds connected. BUT... I rarely see more than a few doing anything other than "Sleep" in SHOW PROCESSLIST. What do you see? The number of active connections is important. SHOW STATUS LIKE 'Threads_running' is another way of judging the active user count.

Do you have multiple cores (CPUs)? More active threads -> more contention for the key_buffer (and Query Cache, if you using that). MySQL has inefficient code for SMP. I have often seen performance degrade after about 4 cores. So...
* Turn off all core after 4.
* Turn off HyperThreading.
* Avoid having too many clients.
* Optimize whatever you can -- speeding up queries will reduce contention.
* Benchmark InnoDB -- it has totally different code, and has other multi-core problems, but it might perform better.
* Turn OFF the Query Cache. (Even DEMAND has SMP bottlenecks.)

Note in your benchmark... 300ms/70ms = 4; 80/20 = 4. They are stumbling over each other, and taking longer. Lose-lose.