What causes MySQL processes linger around for long periods of time?

[Zzed]

This is beginning to turn into a big problem. We were having load problems a few months back due to memory shortage and we bumped up our RAM to 1.5 gigs.

Thanks to eva2000 and the help he gave me I was able to settle things down and minimize the server load.

Lately it seems like we have MySQL processes that hang around the process table and ultimately chew up CPU and increase the load on the server.

Is this a normal behavior? I am having to shut down the MySQL server several times per day to bring the load down. I am tempted to set up a cron job to do this on hourly basis.

Is there anything else that I should/can be doing?

I thank you in advance.

Here is an output of top for MySQL:

4:00pm up 12 days, 22:02, 1 user, load average: 19.37, 15.43, 11.82
315 processes: 312 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 64.4% user, 23.2% system, 0.0% nice, 11.5% idle
CPU1 states: 77.3% user, 10.0% system, 0.0% nice, 12.2% idle
Mem: 1543556K av, 1459140K used, 84416K free, 6556K shrd, 89680K buff
Swap: 1044184K av, 2868K used, 1041316K free 1090552K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
11298 mysql 9 0 69568 67M 2240 S 4.2 4.5 1:26 mysqld
26619 mysql 9 0 69568 67M 2240 S 2.9 4.5 11:10 mysqld
26681 mysql 9 0 69568 67M 2240 S 2.4 4.5 10:54 mysqld
11302 mysql 9 0 69568 67M 2240 S 2.4 4.5 1:35 mysqld
26548 mysql 9 0 69568 67M 2240 S 2.1 4.5 11:00 mysqld
26760 mysql 9 0 69568 67M 2240 S 2.1 4.5 10:55 mysqld
11227 mysql 9 0 69572 67M 2240 S 1.9 4.5 1:30 mysqld
26755 mysql 9 0 69568 67M 2240 S 1.8 4.5 10:46 mysqld
26545 mysql 14 0 69568 67M 2240 S 1.6 4.5 11:01 mysqld
26682 mysql 9 0 69568 67M 2240 S 1.6 4.5 11:11 mysqld
26684 mysql 9 0 69568 67M 2240 S 1.6 4.5 10:55 mysqld
11220 mysql 10 0 69568 67M 2240 S 1.6 4.5 1:35 mysqld
11265 mysql 9 0 69568 67M 2240 S 1.6 4.5 1:36 mysqld
26559 mysql 9 0 69568 67M 2240 S 1.4 4.5 10:19 mysqld
26763 mysql 9 0 69568 67M 2240 S 1.4 4.5 11:00 mysqld
11328 mysql 9 0 69568 67M 2240 S 1.4 4.5 1:34 mysqld
11345 mysql 9 0 69568 67M 2240 S 1.3 4.5 1:40 mysqld
26563 mysql 9 0 69568 67M 2240 S 1.1 4.5 10:50 mysqld
26747 mysql 9 0 69568 67M 2240 S 1.1 4.5 10:47 mysqld
11229 mysql 9 0 69568 67M 2240 S 1.1 4.5 1:36 mysqld
26756 mysql 9 0 69568 67M 2240 S 0.9 4.5 10:14 mysqld
26764 mysql 9 0 69568 67M 2240 S 0.9 4.5 10:57 mysqld
11267 mysql 9 0 69568 67M 2240 S 0.9 4.5 1:39 mysqld
26546 mysql 9 0 69568 67M 2240 S 0.6 4.5 10:54 mysqld
26612 mysql 9 0 69568 67M 2240 S 0.6 4.5 10:59 mysqld
26678 mysql 9 0 69568 67M 2240 S 0.6 4.5 10:32 mysqld
11312 mysql 9 0 69568 67M 2240 S 0.6 4.5 1:26 mysqld
11318 mysql 9 0 69568 67M 2240 S 0.6 4.5 1:28 mysqld
26544 mysql 9 0 69568 67M 2240 S 0.4 4.5 10:20 mysqld
26609 mysql 9 0 69568 67M 2240 S 0.4 4.5 10:56 mysqld
28152 mysql 9 0 69568 67M 2240 S 0.4 4.5 9:55 mysqld
11252 mysql 9 0 69568 67M 2240 S 0.4 4.5 1:39 mysqld
11355 mysql 9 0 69568 67M 2240 S 0.4 4.5 1:50 mysqld
28169 mysql 9 0 69568 67M 2240 S 0.3 4.5 10:05 mysqld
26539 mysql 9 0 69568 67M 2240 S 0.1 4.5 1:56 mysqld
26564 mysql 9 0 69568 67M 2240 S 0.1 4.5 10:51 mysqld
26674 mysql 9 0 69568 67M 2240 S 0.1 4.5 10:38 mysqld
26758 mysql 10 0 69568 67M 2240 R 0.1 4.5 10:51 mysqld
26765 mysql 9 0 69568 67M 2240 S 0.1 4.5 10:20 mysqld
28157 mysql 9 0 69568 67M 2240 S 0.1 4.5 10:12 mysqld
11235 mysql 9 0 69568 67M 2240 S 0.1 4.5 1:39 mysqld
11250 mysql 9 0 69568 67M 2240 S 0.1 4.5 1:43 mysqld
32308 mysql 8 0 2768 2768 2200 S 0.0 0.1 0:01 mysqld
1494 mysql 8 0 3368 3368 2268 S 0.0 0.2 0:57 mysqld
26541 mysql 9 0 69568 67M 2240 S 0.0 4.5 0:00 mysqld
26542 mysql 9 0 69568 67M 2240 S 0.0 4.5 0:42 mysqld
26543 mysql 9 0 69568 67M 2240 S 0.0 4.5 0:00 mysqld
26547 mysql 9 0 69568 67M 2240 S 0.0 4.5 11:53 mysqld
26549 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:47 mysqld
26551 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:30 mysqld
26552 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:46 mysqld
26553 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:56 mysqld
26554 mysql 9 0 69568 67M 2240 S 0.0 4.5 11:04 mysqld
26555 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:42 mysqld
26556 mysql 9 0 69568 67M 2240 S 0.0 4.5 11:05 mysqld
26557 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:28 mysqld
26558 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:23 mysqld
26560 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:28 mysqld
26561 mysql 9 0 69568 67M 2240 S 0.0 4.5 11:08 mysqld
26562 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:57 mysqld
26567 mysql 9 0 69568 67M 2240 S 0.0 4.5 10:47 mysqld

[eva2000]

eva's a guy ;)

DON'T cron restart mysql you can end up corrupting data in your vB database!

forgive me if i forgot what your server specs and recommendations i made (just been doing sooo many of these server tweaks lately heh) - if you could refresh my memory as to what my.cnf recommendation i made for you

do you have in config.php

$usepconnect = 0;

or

$usepconnect = 1;

from what you describe you have persistent connections enabled or a high wait_timeout setting, hence the lingering mysql processes

[Zzed]

Yikes, ouch, I am so sorry to refer to you as a she. :( And to think that I was going to ask for your hand in marriage. :D

The value of $pconnect has always been 0.

To refresh your memory on what you helped me with, here is the thread that started it all back in April: http://www.vbulletin.com/forum/showthread.php?s=&threadid=44740

I have not made any changes since then.

The only thing that is now different is our member count and post counts: Members: 33,190, Threads: 122,790, Posts: 1,024,213

Does that have any bearing on the problem?

The pruning facility is painfully slow. I have even hacked functions.php and commented out the part where the user post counts are being deducted to make it slightly faster to prune.

Your help with this matter will be greatly appreciated. :)

[eva2000]

no hard feelings... everyone has had my gender confused :)

ah 3 months ago huh... guess the server needs retweaking

so ........

if you can provide the following again :)

1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

5. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is located at http://vbulletin.com/forum/showthread.php?threadid=3477

and post url to that here

6. oh and is your vB the only thing on the server? or other scripts? sites?

7. how many average and max concurrent users on your vB forum ?

8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

<?
phpinfo();
?>

i.e. yourdomain.com/phpinfo.php

9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

[Zzed]

1. your server specs, such as mysql and php version

Your MySQL connection id is 889750 to server version: 3.23.41
PHP version: 4.1.2

2. if possible how mysql was compiled/installed

This one is tough. The ISP may have installed the binaries.

3. your top stats

Of course now it is all nice and not overloaded. :D

6:00pm up 13 days, 2 min, 1 user, load average: 5.40, 6.04, 5.92
298 processes: 291 sleeping, 7 running, 0 zombie, 0 stopped
CPU0 states: 45.2% user, 54.0% system, 0.0% nice, 0.1% idle
CPU1 states: 70.2% user, 24.2% system, 0.0% nice, 4.2% idle
Mem: 1543556K av, 1500236K used, 43320K free, 6556K shrd, 90360K buff
Swap: 1044184K av, 2868K used, 1041316K free 1159476K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
28157 mysql 19 0 69952 68M 2240 R 27.8 4.5 10:31 mysqld
20049 keith 19 0 1272 1268 844 R 26.9 0.0 0:01 top
19968 apache 9 0 7872 7756 6184 S 11.7 0.5 0:02 httpd
20017 apache 15 0 7440 7324 5896 R 10.6 0.4 0:00 httpd
19988 apache 9 0 8860 8744 6320 S 10.3 0.5 0:01 httpd
19972 apache 9 0 7892 7776 6192 S 10.0 0.5 0:02 httpd
20013 apache 9 0 7680 7564 5912 S 9.4 0.4 0:00 httpd
20006 apache 9 0 8468 8352 6068 S 8.3 0.5 0:02 httpd
20048 apache 19 0 7240 7124 5764 R 6.3 0.4 0:00 httpd
11310 mysql 9 0 69952 68M 2240 S 6.0 4.5 2:08 mysqld
20022 apache 9 0 7448 7332 6036 S 4.8 0.4 0:01 httpd
11226 mysql 9 0 69952 68M 2240 S 4.5 4.5 1:57 mysqld
20011 apache 9 0 7836 7720 6088 S 4.3 0.5 0:02 httpd
20012 apache 9 0 8000 7884 6224 S 4.3 0.5 0:01 httpd
19963 apache 13 0 7940 7824 6188 S 3.4 0.5 0:00 httpd
11224 mysql 9 0 69952 68M 2240 S 3.1 4.5 1:56 mysqld
11350 mysql 9 0 70436 68M 2240 S 3.1 4.5 2:02 mysqld
26762 mysql 12 0 69952 68M 2240 S 2.8 4.5 11:02 mysqld
26553 mysql 13 0 69952 68M 2240 S 2.0 4.5 11:16 mysqld
28158 mysql 9 0 69952 68M 2240 S 1.4 4.5 10:45 mysqld
11288 mysql 13 0 69952 68M 2240 R 1.4 4.5 2:02 mysqld
11347 mysql 9 0 69952 68M 2240 S 1.4 4.5 1:53 mysqld
26552 mysql 9 0 69952 68M 2240 S 1.1 4.5 11:02 mysqld
28169 mysql 9 0 69952 68M 2240 S 1.1 4.5 10:19 mysqld
22374 root 9 0 776 776 628 S 0.8 0.0 2:32 rotatelogs
19986 apache 9 0 8020 7904 6320 S 0.8 0.5 0:01 httpd
20003 apache 12 0 7484 7368 5936 R 0.8 0.4 0:01 httpd
19995 apache 9 0 7516 7400 5884 S 0.5 0.4 0:00 httpd
20008 apache 9 0 7796 7680 6196 S 0.5 0.4 0:02 httpd
20023 apache 9 0 7576 7460 6084 S 0.5 0.4 0:00 httpd
26542 mysql 9 0 69952 68M 2240 S 0.2 4.5 0:44 mysqld
26555 mysql 9 0 69952 68M 2240 S 0.2 4.5 10:59 mysqld
19993 apache 9 0 7796 7680 6128 S 0.2 0.4 0:00 httpd
19999 apache 9 0 7920 7804 6180 S 0.2 0.5 0:01 httpd
20005 apache 9 0 7340 7224 5928 S 0.2 0.4 0:00 httpd
20015 apache 9 0 7344 7228 5920 S 0.2 0.4 0:01 httpd
20019 apache 9 0 7344 7228 5996 S 0.2 0.4 0:00 httpd
1 root 8 0 528 484 460 S 0.0 0.0 0:10 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 19 19 0 0 0 SWN 0.0 0.0 0:02 ksoftirqd_CPU0
4 root 19 19 0 0 0 RWN 0.0 0.0 0:02 ksoftirqd_CPU1
5 root 9 0 0 0 0 SW 0.0 0.0 0:21 kswapd
6 root 9 0 0 0 0 SW 0.0 0.0 0:00 kreclaimd
7 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
8 root 9 0 0 0 0 SW 0.0 0.0 0:00 kupdated
9 root -1 -20 0 0 0 SW< 0.0 0.0 0:00 mdrecoveryd
10 root 9 0 0 0 0 SW 0.0 0.0 0:00 phpd_notify
18 root 9 0 0 0 0 SW 0.0 0.0 4:07 kjournald
93 root 9 0 0 0 0 SW 0.0 0.0 0:00 khubd
186 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald
187 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald
595 root 9 0 616 596 516 S 0.0 0.0 0:29 syslogd
600 root 9 0 1192 488 440 S 0.0 0.0 0:00 klogd
714 root 9 0 1128 1052 1028 S 0.0 0.0 0:00 sshd
748 root 9 0 916 804 720 S 0.0 0.0 0:00 xinetd
775 root 9 0 2024 1576 1468 S 0.0 0.1 0:00 sendmail
794 root 9 0 460 404 392 S 0.0 0.0 0:01 gpm
836 root 8 0 668 620 576 S 0.0 0.0 0:00 crond
917 daemon 9 0 568 504 492 S 0.0 0.0 0:00 atd
937 root 8 0 620 576 548 S 0.0 0.0 0:00 rhnsd
953 root 9 0 388 332 332 S 0.0 0.0 0:00 mingetty
954 root 9 0 388 332 332 S 0.0 0.0 0:00 mingetty


4. your mysql configuration variables located at /etc/my.cnf


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=32M
set-variable = sort_buffer=32M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


5. your mysql extended-status output either still telnet as root user type

http://www.ls1.com/.zed/ext_stats.php

6. oh and is your vB the only thing on the server? or other scripts? sites?

It is a dedicated server to ls1.com alone.

7. how many average and max concurrent users on your vB forum ?

Average about 400. I have seen as much as 550-590 at peak times.

8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://www.ls1.com/phpinfo.php

9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients


KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 15
MaxSpareServers 40
StartServers 15
MaxClients 150


Many thanks in advance. :)

[Raz Meister]

Just been checking your site out and it seems the forum spends 0.8secs doing PHP which suggests you might not have something set right.

Consider upgrading to the latest version of PHPA and PHP as they both contain bugfixes and performance enhancements.

[eva2000]

well Zzed's already using PHPA 1.2p4 http://www.ls1.com/phpinfo.php

but according http://www.ls1.com/phpinfo.php you're using PHP 4.06 and not 4.1.2

I'd upgrade a few things here

Apache 1.3.26
PHP 4.1.2
MySQL 3.23.51
2.4.18-5 kernel
PHPA 1.3.2 which just came out with registration keys requirement removed!

change httpd.conf settings to

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 15
MaxSpareServers 25
StartServers 15
MaxClients 150


and my.cnf to

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=32M
set-variable = sort_buffer=32M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

restart apache and mysql

[Zzed]

I tried upgrading Apache. But at some point in time the build process just crapped out on me.

I will try and upgrade as many components as possible.

I will make the ncessary changes to my.cnf httpd.conf.

Thank you very much for qll the help and your quick response.

I will keep you posted. :)

Thanks again. :)

[eva2000]

Originally posted by Zzed
I tried upgrading Apache. But at some point in time the build process just crapped out on me.

I will try and upgrade as many components as possible.

I will make the ncessary changes to my.cnf httpd.conf.

Thank you very much for qll the help and your quick response.

I will keep you posted. :)

Thanks again. :) leave apache and kernel upgrades last.. start with the my.cnf and httpd.conf changes first and test them out over a few days

then try php and mysql upgrades test for few days etc

[Zzed]

Originally posted by eva2000
leave apache and kernel upgrades last.. start with the my.cnf and httpd.conf changes first and test them out over a few days

then try php and mysql upgrades test for few days etc
Will do. :)

Thank you. http://www.ls1.com/.zed/hail.gif

[Raz Meister]

How come you don't recommend upgrading to v4.2.2 eva?

[eva2000]

Originally posted by Razzie
How come you don't recommend upgrading to v4.2.2 eva? too early to tell... could end up like when 4.2.0 came out and break vB or something

[Raz Meister]

There haven't been any negative reports so far :)

[DBs]

4.2.2 works fine with vb 2.26