mysqld load and optimization??

[atul]

Hello All,
I am bit struggling for load balancing on the Linux server.The rise in load is primarily because of mysqld daemon which occupies 99% of cpu.We have 4CPU and 2GB of RAM on server but still load on server always shows the figure of 12-20.

With this I am attaching my output of "show variables" in txt file
Can anybosy please look at this file and suggest me over it?

I want to optimize thses values to that extent so that my server load will show <5 figure.
Can anybody tell me how should i do that?We have alreay finished optimizing mysql tables by indexing.What should be the values of these varialbes?
Please help!!
Thank you.

 

[mickalo]

Optimizing MySQL is different from one appilication to the next, server setup and resources, how MySQL is being used, .... many things to take in consideration. I've found through the last couple of years working with it, alot is a simple matter of the old "Trial and Error" to some extent.

In the folder /usr/share/mysql, if you using the standard RPM install, are files:

my-huge.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

read through these to see which one would meet your requirements the best, then rename the file to my.cnf and put it in the /etc folder, replacing the old one, remove the my.cnf in the /usr/local/cpanel/whostmgr/, create a symbolic link from the new one in the /etc folder.

Now restart MySQL. The cpanel default setup doesn't optimize MySQL very well. So if you us your own configuration file, this will help alot.

Hope this helps
Mickalo