mysql variables -wait, etc

Posted: Fri Mar 09, 2007 11:11 am

mysql is dragging my vps down, and if I'm not constantly restarting the service it could take the entire node down ... there were hours of downtime yesterday. Then, I had to repair the db tables on only the busiest domain, unable to open files errors, was a clue something had to be done.

(Of course, I'm not 100% sure why the node went down, because of the vps situ and the lack of details coming from the host, but I was sitting here at the time, one of many who were pushing pixels into that huge database.)

Anyways...

From top: mysqld is constantly on the creep in TIME and %CPU spikes at 80%.

I have several domains, they are mostly sleepy, with little data. All the action is on one or two domains (avg logged in 15 users/hr, except during mid-night) One site has lots of phpbb data going back almost 5 years, and a spectrum of scripts and forms that generate many, many queries.

I'm not a db engineer, and frankly am terrified of anything but default mysql. Smile

But google is good and I found the following quote. (My settings are waittimeout 28800 and thread cache is 0, too!)

Is the following good advice? Can you tell me why it should work? Or possible pitfalls in changing things?

Quote:

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).

Happy Nuking!!

Posted: Fri Mar 09, 2007 5:36 pm

So, after all that stress, I edited the file to add mysql variables.

Seems to make a difference in %CPU, from spiking at 80% down to the 25% range.
However, TIME+ continues to creep ahead. Is that a problem or just service uptime?

Unfortunately, page loads didn't improve, maybe a tad slower, ouch. Silly celeron.

Posted: Sat Mar 10, 2007 2:59 am

Hmm that is interesting. I'll have to try it sometime.. perhaps when I need to do other maintainance to my server

I do notice that my server is very high on memory usage. I will have to check the CPU usage stats too.