MySQL Forums
Forum List  »  MyISAM

Re: random reads
Posted by: Rick James
Date: February 14, 2009 12:50AM

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.

Options: ReplyQuote

Written By
February 05, 2009 05:32PM
February 06, 2009 09:53PM
February 10, 2009 06:10PM
Re: random reads
February 14, 2009 12:50AM
February 23, 2009 11:53PM

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.