Mysql "sleep" Processes And Unusual Httpd Process Behaviour

[cybernck]

hello there,

i've moved my forum (IPB 2.3.1, ~3000 members, ~300 Mb DB, 50 users online
on average) away from shared hosting to a VPS and it seems to be running
a lot faster, however, i've now come across some errors.


it seems that many of my forum's MySQL processes eventually and in "sleep"
status and stay that way until i re-start mysqld service.

3456	username	localhost	database	Sleep	21502		NULL

3557	username	localhost	database	Sleep	21035		NULL

3691	username	localhost	database	Sleep	19915		NULL

...

9969	username	localhost	database	Sleep	486		NULL

10023	username	localhost	database	Sleep	486		NULL

10096	username	localhost	database	Sleep	28		NULL

also, in Apache Status process lists, the top five or six processes have CPU time continually rising:

Srv	PID	Acc			M	CPU	SS	Req	Conn	Child	Slot	Host	VHost	Request

0-0	15577	0/9077/9077	W 	242.81	17	0	0.0	35.75	35.75 	***	***	GET ***

1-0	15578	0/8380/8380	W 	236.46	49	0	0.0	33.59	33.59 	***	***	GET ***

2-0	15593	0/9116/9116	_ 	231.10	6	0	0.0	33.53	33.53 	***	***	GET ***

3-0	15604	0/8646/8646	W 	235.78	8	0	0.0	32.48	32.48 	***	***	GET ***

4-0	15651	0/8140/8140	W 	240.25	55	0	0.0	35.52	35.52 	***	***	GET ***

5-0	17488	0/20/5944	W 	0.41	17	0	0.0	0.06	22.94 	***	***	GET ***

6-0	18211	0/15/4638	W 	0.02	71	0	0.0	0.04	16.65 	***	***	POST ***

7-0	18260	0/15/3371	W 	0.01	52	0	0.0	0.04	12.90 	***	***	POST ***

8-0	18261	1/11/2332	W 	0.00	61	0	0.0	0.01	9.23 	***	***	GET ***

9-0	27997	0/2/2089	W 	0.33	29	0	0.0	0.01	6.59 	***	***	GET ***

10-0	28181	0/2/1206	W 	0.00	32	0	0.0	0.08	4.62 	***	***	GET ***

11-0	28186	0/3/992			W 	0.00	30	0	0.0	0.11	3.78 	***	***	GET ***

12-0	31960	0/21/989	W 	0.32	18	0	0.0	0.00	3.20 	***	***	GET ***

13-0	9715	0/1/881	  _ 	0.00	8	8	0.0	0.04	2.45 	***	***	GET ***

14-0	-	0/0/610	  . 	0.45	77	0	0.0	0.00	2.59 	***	***	***

15-0	-	0/0/572	  . 	0.31	77	0	0.0	0.00	2.50 	***	***	GET ***

...

is this normal behaviour? with number of process requests aproaching 20, my forum pages start loading slower and slower while the server load gets higher and higher, until the pages start timing-out. after a refresh, a page either loads up quickly, or slowly, or repeatedly times out - depending on the number of process requests. both the old and the new host have MySQL 5, but not the same PHP version (PHP 4 on the old and PHP 5 on the new host). here's what my my.cnf file looks like:

[mysqld]

skip-networking

skip-innodb

log-slow-queries


set-variable = key_buffer_size=16M

set-variable = max_connections=200

set-variable = table_cache=512

set-variable = max_delayed_threads=20

set-variable = max_tmp_tables=32

set-variable = query_cache_type=1

set-variable = query_cache_size=4M

set-variable = thread_cache_size=4

pretty much what came standard standard except for the first three entries.

httpd.conf is completely standard (for this VPS).


have you got any suggestions as to what should i check, modify or try out?
do you need any futher info for diagnosing the root of the problem?


thanks and please reply as soon as possible.

[bfarber]

While MySQL doesn't seem to be the problem, the key_buffer is pretty low. It should be about 25% of your system memory.

Add to your my.cnf

set-variable = wait_timeout=60
set-variable = interactive_timeout=60

That should kill the bulk of the sleeping processes (by default it's 28800 I think)

As for Apache, search this forum for rct2dotcom's posts - he puts a lot of good info about configuring Apache in here in various posts.