Less Hits SLOW PHP / MYSQL connection!

Need help with opptimizing my server!

[arifagic]

Hi, there!

I have been reading for a while now, and i can`t find any info about what the problem could be.

I running a forum + few other sites that use MYSQL to get their info from.

When i run the forum it had about 60000hits per day and the server run PERFECTLY GOOD!

Now + those other sites and much less hits on the forum im getting a very slow MYSQL + the server keeps overloading!

I have read that not closing a connection wood do some damage, im not sure if i added that on those other sites.. and btw how does one close a connection!?

Thx

Any help is apprisiated!

[arifagic]

This is what im getting from

WHM

USER: %CPU %MEM Mysql Processes
mysql 38.54 12.24 0.0

Top Process: %CPU 99.9
/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/main.XXXXX.ws.pid --skip-locking

Top Process: %CPU 99.5
/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/main.XXXXX.ws.pid --skip-locking

This is what im getting..!?

ANy help please!?

[AlexK]

arifagic:

This is what im getting from WHM
Top Process: %CPU 99.9
/usr/sbin/mysqld...

I had the same problem when I first got a web-server. It was (eventually) discovered to be caused by a default setting in mysql (at least with RH8):

The following was on the `Variables' page of phpmyadmin (the emboldened line is the principal cause):

wait timeout: 28800 
thread cache size: 0 
open files limit: 0 
Max connections: 100

Putting the following in `my.cnf' fixed the problem:

# added by Alex 17 Oct 2003 
set-variable = thread_cache_size=10 
set-variable = wait_timeout=30 
(then from shell:) 
/etc/init.d/mysqld stop 
/etc/init.d/mysqld start

CPU usage under top for mysqld dropped from a typical 99.9% to 10% max & more typical 6% on the 5-sec transitory showing. `0' for the thread cache caused 99.9% cpu usage, BTW, because new threads needed to be to be spawned for every connection (30,000 in a 24 hour period before the change).

See [jeremy.zawodny.com...] for more info.

[arifagic]

Great thx for the help, like i said i dont know any thing about managing those files..

so do i edit my. << that file + all the others!?

(Sorry but i have very little knowledge about mysql etc.. )

[AlexK]

arifagic:

so do i edit my. << that file + all the others!?

Do exactly what it says... Locate `my.cnf' on your server and (with an editor) add the lines shown, then restart mysql.

You would be well advised, first, to use phpMyAdmin (log-in as the mysql control-user--usually root) and check the pages indicated for the settings indicated.

Sorry but i have very little knowledge about mysql etc..

Then get stuck in to some web-searches, and increase your knowledge.