kknicker
Sun 1st Jul '07, 10:41pm
We have started seeing "Lost connection to MySQL server during query" errors on the thread table during peak load times. Please advise about our current configuration.
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Dedicated
2. your server specs. For example:
Intel P4 2.8GHz Dual Core 920
2GB RAM
2 x 160GB Hard Drive
CentOS 4.4
Apache 2.0.59
PHP 4.4.4
MySQL 4.1.22
3. what version of vB are you running ?
3.6.7
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
No innodb databases or tables
5. if possible how mysql was compiled/installed
RPMs downloaded from mysql.com
6. your top stats
top - 20:19:09 up 95 days, 20:23, 1 user, load average: 14.14, 10.55, 9.73
Tasks: 109 total, 12 running, 97 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.9% us, 6.5% sy, 0.0% ni, 63.6% id, 1.0% wa, 0.0% hi, 0.0% si
Mem: 2066648k total, 1916292k used, 150356k free, 157152k buffers
Swap: 2040244k total, 8168k used, 2032076k free, 954344k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12325 mysql 15 0 235m 204m 2668 S 75 10.2 2261:57 mysqld
7119 nobody 16 0 52308 18m 8168 R 20 0.9 0:07.42 httpd
7113 nobody 16 0 51720 19m 9.9m S 16 1.0 0:06.03 httpd
7069 nobody 17 0 51524 22m 12m R 8 1.1 0:10.57 httpd
7088 nobody 17 0 51564 20m 11m R 8 1.0 0:05.68 httpd
7137 nobody 17 0 53144 20m 9612 R 8 1.0 0:01.83 httpd
7138 nobody 16 0 52164 19m 10m S 8 1.0 0:02.10 httpd
7046 nobody 16 0 51528 23m 14m S 6 1.2 0:11.32 httpd
7142 nobody 15 0 51444 16m 7876 S 6 0.8 0:02.24 httpd
7020 nobody 15 0 52240 22m 12m S 4 1.1 0:10.40 httpd
7092 nobody 16 0 53588 23m 11m S 4 1.1 0:06.23 httpd
7126 nobody 16 0 51492 19m 10m S 4 1.0 0:04.35 httpd
7135 nobody 17 0 51380 17m 8880 R 4 0.9 0:02.90 httpd
7136 nobody 17 0 51248 14m 5900 R 4 0.7 0:00.92 httpd
7146 nobody 17 0 53088 19m 8700 R 4 1.0 0:01.76 httpd
7207 nobody 17 0 51100 15m 6528 R 4 0.8 0:00.29 httpd
7210 nobody 16 0 48856 10m 3788 S 4 0.5 0:00.02 httpd
7059 nobody 16 0 52084 25m 16m S 2 1.3 0:10.04 httpd
7141 nobody 15 0 53356 21m 10m S 2 1.1 0:01.65 httpd
7212 nobody 16 0 47756 9188 3472 R 2 0.4 0:00.01 httpd
1 root 16 0 1900 104 72 S 0 0.0 0:20.65 init
2 root RT 0 0 0 0 S 0 0.0 0:00.67 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:02.62 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.53 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:02.24 ksoftirqd/1
6 root 5 -10 0 0 0 S 0 0.0 0:00.04 events/0
7 root 5 -10 0 0 0 S 0 0.0 0:00.02 events/1
8 root 12 -10 0 0 0 S 0 0.0 0:00.00 khelper
9 root 15 -10 0 0 0 S 0 0.0 0:00.00 kacpid
29 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/0
30 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/1
31 root 15 0 0 0 0 S 0 0.0 0:00.00 khubd
51 root 6 -10 0 0 0 S 0 0.0 0:00.00 aio/0
52 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/1
50 root 15 0 0 0 0 S 0 0.0 5:45.52 kswapd0
198 root 15 0 0 0 0 S 0 0.0 0:00.00 kseriod
305 root 5 -10 0 0 0 S 0 0.0 0:00.22 ata/0
306 root 5 -10 0 0 0 S 0 0.0 0:00.22 ata/1
310 root 22 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
311 root 22 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_1
331 root 15 0 0 0 0 S 0 0.0 48:44.70 kjournald
1510 root 6 -10 3400 20 16 S 0 0.0 0:00.02 udevd
1934 root 6 -10 0 0 0 S 0 0.0 0:00.00 kauditd
1999 root 7 -10 0 0 0 S 0 0.0 0:00.00 kmirrord
2018 root 22 0 0 0 0 S 0 0.0 0:00.00 kjournald
2019 root 16 0 0 0 0 S 0 0.0 7:12.44 kjournald
2771 root 16 0 3296 404 312 S 0 0.0 0:21.79 syslogd
2775 root 16 0 2316 200 144 S 0 0.0 0:00.00 klogd
2785 root 16 0 3228 272 208 S 0 0.0 0:34.70 irqbalance
2864 root 16 0 4032 84 48 S 0 0.0 0:05.27 rpc.idmapd
2913 root 15 0 2940 256 104 S 0 0.0 0:01.45 smartd
2922 root 20 0 3128 20 16 S 0 0.0 0:00.00 acpid
2931 root 16 0 5804 380 264 S 0 0.0 0:14.89 sshd
2944 root 16 0 3316 16 12 S 0 0.0 0:00.00 xinetd
2961 ntp 16 0 5128 5128 3436 S 0 0.2 0:39.38 ntpd
2997 root 16 0 6332 524 472 S 0 0.0 0:04.11 crond
3014 root 16 0 3300 324 284 S 0 0.0 0:00.16 atd
3023 dbus 16 0 3256 292 288 S 0 0.0 0:00.01 dbus-daemon-1
3032 root 16 0 7556 1480 556 S 0 0.1 1:08.26 hald
3185 root 18 0 1664 352 348 S 0 0.0 0:00.00 mingetty
3192 root 18 0 3236 352 348 S 0 0.0 0:00.00 mingetty
3193 root 18 0 1844 352 348 S 0 0.0 0:00.00 mingetty
3194 root 18 0 3236 352 348 S 0 0.0 0:00.00 mingetty
3195 root 18 0 2628 352 348 S 0 0.0 0:00.00 mingetty
3197 root 18 0 1724 352 348 S 0 0.0 0:00.00 mingetty
4341 root 16 0 8152 1016 744 S 0 0.0 0:27.07 sendmail
4349 smmsp 16 0 6764 712 600 S 0 0.0 0:00.12 sendmail
6239 named 19 0 49500 3980 1968 S 0 0.2 0:26.87 named
12293 root 25 0 4964 1104 944 S 0 0.1 0:00.01 mysqld_safe
12366 root 16 0 44136 3828 1780 S 0 0.2 0:21.38 httpd
5778 root 15 0 0 0 0 S 0 0.0 0:00.15 pdflush
5929 root 15 0 0 0 0 S 0 0.0 0:00.08 pdflush
6912 root 16 0 8308 2224 1788 S 0 0.1 0:00.01 sshd
6914 kknicker 15 0 8468 1676 1216 S 0 0.1 0:00.04 sshd
6915 kknicker 15 0 4736 1468 1196 S 0 0.1 0:00.03 bash
7009 nobody 15 0 54132 26m 14m S 0 1.3 0:09.00 httpd
7016 root 16 0 6172 1168 924 S 0 0.1 0:00.00 su
7022 nobody 15 0 53916 28m 16m S 0 1.4 0:10.81 httpd
7023 root 15 0 4560 1420 1200 S 0 0.1 0:00.03 bash
7054 nobody 15 0 51976 23m 13m S 0 1.1 0:08.79 httpd
7064 nobody 15 0 52192 23m 13m S 0 1.2 0:09.15 httpd
7072 nobody 15 0 53324 23m 12m S 0 1.2 0:08.17 httpd
7094 nobody 15 0 54172 25m 13m S 0 1.3 0:07.11 httpd
7095 nobody 15 0 51688 20m 10m S 0 1.0 0:07.54 httpd
7096 nobody 16 0 54000 24m 12m S 0 1.2 0:07.66 httpd
7099 nobody 15 0 54112 22m 10m S 0 1.1 0:06.51 httpd
7108 nobody 17 0 52016 18m 9116 R 0 0.9 0:06.28 httpd
7109 nobody 15 0 52800 21m 10m S 0 1.0 0:06.54 httpd
7129 nobody 15 0 51432 16m 7044 S 0 0.8 0:05.04 httpd
7132 nobody 15 0 51504 18m 8904 S 0 0.9 0:02.73 httpd
7133 nobody 16 0 51504 16m 7404 S 0 0.8 0:02.60 httpd
7139 nobody 16 0 51616 18m 9172 S 0 0.9 0:02.77 httpd
7140 nobody 15 0 52360 17m 7524 S 0 0.9 0:02.00 httpd
7157 nobody 15 0 51344 16m 7256 S 0 0.8 0:01.42 httpd
7168 nobody 15 0 51360 15m 6460 S 0 0.8 0:01.21 httpd
7169 nobody 15 0 51204 15m 6980 S 0 0.8 0:01.04 httpd
7171 nobody 16 0 51588 16m 7232 S 0 0.8 0:01.27 httpd
7175 nobody 15 0 51392 15m 6140 S 0 0.8 0:00.57 httpd
7179 nobody 15 0 50748 14m 6232 S 0 0.7 0:00.22 httpd
7180 nobody 16 0 51456 15m 6444 S 0 0.8 0:01.10 httpd
7181 nobody 15 0 53880 20m 9400 S 0 1.0 0:00.66 httpd
7182 nobody 15 0 51368 14m 5840 S 0 0.7 0:00.46 httpd
7183 nobody 15 0 50632 13m 5108 S 0 0.7 0:00.19 httpd
7184 nobody 17 0 51260 13m 4780 R 0 0.7 0:00.87 httpd
7206 nobody 15 0 51828 15m 5756 S 0 0.8 0:00.14 httpd
7211 nobody 15 0 44136 3472 1388 S 0 0.2 0:00.00 httpd
7213 nobody 15 0 44136 3468 1388 S 0 0.2 0:00.00 httpd
7214 nobody 15 0 44136 3468 1388 S 0 0.2 0:00.00 httpd
7215 root 15 0 3160 872 684 R 0 0.0 0:00.00 top
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
skip-name-resolve
datadir=/disk2/mysql
socket=/var/lib/mysql/mysql.sock
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 80M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 22
connect_timeout = 10
tmp_table_size = 80M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
8. MySQL stats from ssh telnet as root user type:
MySQL 4.1.22-standard uptime 4 11:19:46 Sun Jul 1 20:21:53 2007
__ Key __________________________________________________ _______________
Buffer used 70.91M of 80.00M %Used: 88.63
Current 79.20M %Usage: 99.00
Write ratio 0.468
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 19.86M 51.4/s
DMS 9.51M 24.6/s %Total: 47.91
QC Hits 7.17M 18.6/s 36.13
Com_ 1.59M 4.1/s 7.98
COM_QUIT 1.58M 4.1/s 7.98
-Unknown 26 0.0/s 0.00
Slow 2.09k 0.0/s 0.01 %DMS: 0.02
DMS 9.51M 24.6/s 47.91
SELECT 6.70M 17.3/s 33.75 70.44
UPDATE 2.17M 5.6/s 10.94 22.84
INSERT 411.61k 1.1/s 2.07 4.33
REPLACE 123.55k 0.3/s 0.62 1.30
DELETE 104.14k 0.3/s 0.52 1.09
Com_ 1.59M 4.1/s 7.98
change_db 1.58M 4.1/s 7.98
truncate 223 0.0/s 0.00
drop_table 219 0.0/s 0.00
__ SELECT and Sort __________________________________________________ ___
Scan 1.15M 3.0/s %SELECT: 17.17
Range 1.09M 2.8/s 16.19
Full join 33.96k 0.1/s 0.51
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 805.86k 2.1/s
Sort range 737.30k 1.9/s
Sort mrg pass 101.32k 0.3/s
__ Query Cache __________________________________________________ _______
Memory usage 37.85M of 80.00M %Used: 47.32
Block Fragmnt 23.03%
Hits 7.17M 18.6/s
Inserts 6.61M 17.1/s
Insrt:Prune 137.65:1 17.0/s
Hit:Insert 1.09:1
__ Table Locks __________________________________________________ _______
Waited 276.48k 0.7/s %Total: 1.45
Immediate 18.78M 48.6/s
__ Tables __________________________________________________ ____________
Open 774 of 1800 %Cache: 43.00
Opened 4.39k 0.0/s
__ Connections __________________________________________________ _______
Max used 53 of 500 %Max: 10.60
Total 1.59M 4.1/s
__ Created Temp __________________________________________________ ______
Disk table 190.58k 0.5/s
Table 662.58k 1.7/s
File 202.64k 0.5/s
__ Threads __________________________________________________ ___________
Running 16 of 20
Cached 33 of 384 %Hit: 100
Created 53 0.0/s
Slow 2 0.0/s
__ Aborted __________________________________________________ ___________
Clients 3.02k 0.0/s
Connects 13 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.49G 3.9k/s
Received 2.08G 5.4k/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
vBulletin and vbAdvanced
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average users: 320
Maximum users: 810
Cookie timeout: 1800 seconds
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://vmkmagic.com/phpinfo.php
12. 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 Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 15
MaxSpareServers 20
StartServers 15
MaxClients 50
MaxRequestsPerChild 500
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
No files greater than 2GB
14. Post output from these 3 commands
uname -a
Linux www.vmkmagic.com 2.6.9-42.0.3.ELsmp #1 SMP Fri Oct 6 06:21:39 CDT 2006 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32636
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) D CPU 2.80GHz
stepping : 4
cpu MHz : 2792.624
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor
ds_cpl cid xtpr
bogomips : 5586.95
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) D CPU 2.80GHz
stepping : 4
cpu MHz : 2792.624
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor
ds_cpl cid xtpr
bogomips : 5581.39
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Dedicated
2. your server specs. For example:
Intel P4 2.8GHz Dual Core 920
2GB RAM
2 x 160GB Hard Drive
CentOS 4.4
Apache 2.0.59
PHP 4.4.4
MySQL 4.1.22
3. what version of vB are you running ?
3.6.7
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
No innodb databases or tables
5. if possible how mysql was compiled/installed
RPMs downloaded from mysql.com
6. your top stats
top - 20:19:09 up 95 days, 20:23, 1 user, load average: 14.14, 10.55, 9.73
Tasks: 109 total, 12 running, 97 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.9% us, 6.5% sy, 0.0% ni, 63.6% id, 1.0% wa, 0.0% hi, 0.0% si
Mem: 2066648k total, 1916292k used, 150356k free, 157152k buffers
Swap: 2040244k total, 8168k used, 2032076k free, 954344k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12325 mysql 15 0 235m 204m 2668 S 75 10.2 2261:57 mysqld
7119 nobody 16 0 52308 18m 8168 R 20 0.9 0:07.42 httpd
7113 nobody 16 0 51720 19m 9.9m S 16 1.0 0:06.03 httpd
7069 nobody 17 0 51524 22m 12m R 8 1.1 0:10.57 httpd
7088 nobody 17 0 51564 20m 11m R 8 1.0 0:05.68 httpd
7137 nobody 17 0 53144 20m 9612 R 8 1.0 0:01.83 httpd
7138 nobody 16 0 52164 19m 10m S 8 1.0 0:02.10 httpd
7046 nobody 16 0 51528 23m 14m S 6 1.2 0:11.32 httpd
7142 nobody 15 0 51444 16m 7876 S 6 0.8 0:02.24 httpd
7020 nobody 15 0 52240 22m 12m S 4 1.1 0:10.40 httpd
7092 nobody 16 0 53588 23m 11m S 4 1.1 0:06.23 httpd
7126 nobody 16 0 51492 19m 10m S 4 1.0 0:04.35 httpd
7135 nobody 17 0 51380 17m 8880 R 4 0.9 0:02.90 httpd
7136 nobody 17 0 51248 14m 5900 R 4 0.7 0:00.92 httpd
7146 nobody 17 0 53088 19m 8700 R 4 1.0 0:01.76 httpd
7207 nobody 17 0 51100 15m 6528 R 4 0.8 0:00.29 httpd
7210 nobody 16 0 48856 10m 3788 S 4 0.5 0:00.02 httpd
7059 nobody 16 0 52084 25m 16m S 2 1.3 0:10.04 httpd
7141 nobody 15 0 53356 21m 10m S 2 1.1 0:01.65 httpd
7212 nobody 16 0 47756 9188 3472 R 2 0.4 0:00.01 httpd
1 root 16 0 1900 104 72 S 0 0.0 0:20.65 init
2 root RT 0 0 0 0 S 0 0.0 0:00.67 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:02.62 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.53 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:02.24 ksoftirqd/1
6 root 5 -10 0 0 0 S 0 0.0 0:00.04 events/0
7 root 5 -10 0 0 0 S 0 0.0 0:00.02 events/1
8 root 12 -10 0 0 0 S 0 0.0 0:00.00 khelper
9 root 15 -10 0 0 0 S 0 0.0 0:00.00 kacpid
29 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/0
30 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/1
31 root 15 0 0 0 0 S 0 0.0 0:00.00 khubd
51 root 6 -10 0 0 0 S 0 0.0 0:00.00 aio/0
52 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/1
50 root 15 0 0 0 0 S 0 0.0 5:45.52 kswapd0
198 root 15 0 0 0 0 S 0 0.0 0:00.00 kseriod
305 root 5 -10 0 0 0 S 0 0.0 0:00.22 ata/0
306 root 5 -10 0 0 0 S 0 0.0 0:00.22 ata/1
310 root 22 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
311 root 22 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_1
331 root 15 0 0 0 0 S 0 0.0 48:44.70 kjournald
1510 root 6 -10 3400 20 16 S 0 0.0 0:00.02 udevd
1934 root 6 -10 0 0 0 S 0 0.0 0:00.00 kauditd
1999 root 7 -10 0 0 0 S 0 0.0 0:00.00 kmirrord
2018 root 22 0 0 0 0 S 0 0.0 0:00.00 kjournald
2019 root 16 0 0 0 0 S 0 0.0 7:12.44 kjournald
2771 root 16 0 3296 404 312 S 0 0.0 0:21.79 syslogd
2775 root 16 0 2316 200 144 S 0 0.0 0:00.00 klogd
2785 root 16 0 3228 272 208 S 0 0.0 0:34.70 irqbalance
2864 root 16 0 4032 84 48 S 0 0.0 0:05.27 rpc.idmapd
2913 root 15 0 2940 256 104 S 0 0.0 0:01.45 smartd
2922 root 20 0 3128 20 16 S 0 0.0 0:00.00 acpid
2931 root 16 0 5804 380 264 S 0 0.0 0:14.89 sshd
2944 root 16 0 3316 16 12 S 0 0.0 0:00.00 xinetd
2961 ntp 16 0 5128 5128 3436 S 0 0.2 0:39.38 ntpd
2997 root 16 0 6332 524 472 S 0 0.0 0:04.11 crond
3014 root 16 0 3300 324 284 S 0 0.0 0:00.16 atd
3023 dbus 16 0 3256 292 288 S 0 0.0 0:00.01 dbus-daemon-1
3032 root 16 0 7556 1480 556 S 0 0.1 1:08.26 hald
3185 root 18 0 1664 352 348 S 0 0.0 0:00.00 mingetty
3192 root 18 0 3236 352 348 S 0 0.0 0:00.00 mingetty
3193 root 18 0 1844 352 348 S 0 0.0 0:00.00 mingetty
3194 root 18 0 3236 352 348 S 0 0.0 0:00.00 mingetty
3195 root 18 0 2628 352 348 S 0 0.0 0:00.00 mingetty
3197 root 18 0 1724 352 348 S 0 0.0 0:00.00 mingetty
4341 root 16 0 8152 1016 744 S 0 0.0 0:27.07 sendmail
4349 smmsp 16 0 6764 712 600 S 0 0.0 0:00.12 sendmail
6239 named 19 0 49500 3980 1968 S 0 0.2 0:26.87 named
12293 root 25 0 4964 1104 944 S 0 0.1 0:00.01 mysqld_safe
12366 root 16 0 44136 3828 1780 S 0 0.2 0:21.38 httpd
5778 root 15 0 0 0 0 S 0 0.0 0:00.15 pdflush
5929 root 15 0 0 0 0 S 0 0.0 0:00.08 pdflush
6912 root 16 0 8308 2224 1788 S 0 0.1 0:00.01 sshd
6914 kknicker 15 0 8468 1676 1216 S 0 0.1 0:00.04 sshd
6915 kknicker 15 0 4736 1468 1196 S 0 0.1 0:00.03 bash
7009 nobody 15 0 54132 26m 14m S 0 1.3 0:09.00 httpd
7016 root 16 0 6172 1168 924 S 0 0.1 0:00.00 su
7022 nobody 15 0 53916 28m 16m S 0 1.4 0:10.81 httpd
7023 root 15 0 4560 1420 1200 S 0 0.1 0:00.03 bash
7054 nobody 15 0 51976 23m 13m S 0 1.1 0:08.79 httpd
7064 nobody 15 0 52192 23m 13m S 0 1.2 0:09.15 httpd
7072 nobody 15 0 53324 23m 12m S 0 1.2 0:08.17 httpd
7094 nobody 15 0 54172 25m 13m S 0 1.3 0:07.11 httpd
7095 nobody 15 0 51688 20m 10m S 0 1.0 0:07.54 httpd
7096 nobody 16 0 54000 24m 12m S 0 1.2 0:07.66 httpd
7099 nobody 15 0 54112 22m 10m S 0 1.1 0:06.51 httpd
7108 nobody 17 0 52016 18m 9116 R 0 0.9 0:06.28 httpd
7109 nobody 15 0 52800 21m 10m S 0 1.0 0:06.54 httpd
7129 nobody 15 0 51432 16m 7044 S 0 0.8 0:05.04 httpd
7132 nobody 15 0 51504 18m 8904 S 0 0.9 0:02.73 httpd
7133 nobody 16 0 51504 16m 7404 S 0 0.8 0:02.60 httpd
7139 nobody 16 0 51616 18m 9172 S 0 0.9 0:02.77 httpd
7140 nobody 15 0 52360 17m 7524 S 0 0.9 0:02.00 httpd
7157 nobody 15 0 51344 16m 7256 S 0 0.8 0:01.42 httpd
7168 nobody 15 0 51360 15m 6460 S 0 0.8 0:01.21 httpd
7169 nobody 15 0 51204 15m 6980 S 0 0.8 0:01.04 httpd
7171 nobody 16 0 51588 16m 7232 S 0 0.8 0:01.27 httpd
7175 nobody 15 0 51392 15m 6140 S 0 0.8 0:00.57 httpd
7179 nobody 15 0 50748 14m 6232 S 0 0.7 0:00.22 httpd
7180 nobody 16 0 51456 15m 6444 S 0 0.8 0:01.10 httpd
7181 nobody 15 0 53880 20m 9400 S 0 1.0 0:00.66 httpd
7182 nobody 15 0 51368 14m 5840 S 0 0.7 0:00.46 httpd
7183 nobody 15 0 50632 13m 5108 S 0 0.7 0:00.19 httpd
7184 nobody 17 0 51260 13m 4780 R 0 0.7 0:00.87 httpd
7206 nobody 15 0 51828 15m 5756 S 0 0.8 0:00.14 httpd
7211 nobody 15 0 44136 3472 1388 S 0 0.2 0:00.00 httpd
7213 nobody 15 0 44136 3468 1388 S 0 0.2 0:00.00 httpd
7214 nobody 15 0 44136 3468 1388 S 0 0.2 0:00.00 httpd
7215 root 15 0 3160 872 684 R 0 0.0 0:00.00 top
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
skip-name-resolve
datadir=/disk2/mysql
socket=/var/lib/mysql/mysql.sock
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 80M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 22
connect_timeout = 10
tmp_table_size = 80M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
8. MySQL stats from ssh telnet as root user type:
MySQL 4.1.22-standard uptime 4 11:19:46 Sun Jul 1 20:21:53 2007
__ Key __________________________________________________ _______________
Buffer used 70.91M of 80.00M %Used: 88.63
Current 79.20M %Usage: 99.00
Write ratio 0.468
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 19.86M 51.4/s
DMS 9.51M 24.6/s %Total: 47.91
QC Hits 7.17M 18.6/s 36.13
Com_ 1.59M 4.1/s 7.98
COM_QUIT 1.58M 4.1/s 7.98
-Unknown 26 0.0/s 0.00
Slow 2.09k 0.0/s 0.01 %DMS: 0.02
DMS 9.51M 24.6/s 47.91
SELECT 6.70M 17.3/s 33.75 70.44
UPDATE 2.17M 5.6/s 10.94 22.84
INSERT 411.61k 1.1/s 2.07 4.33
REPLACE 123.55k 0.3/s 0.62 1.30
DELETE 104.14k 0.3/s 0.52 1.09
Com_ 1.59M 4.1/s 7.98
change_db 1.58M 4.1/s 7.98
truncate 223 0.0/s 0.00
drop_table 219 0.0/s 0.00
__ SELECT and Sort __________________________________________________ ___
Scan 1.15M 3.0/s %SELECT: 17.17
Range 1.09M 2.8/s 16.19
Full join 33.96k 0.1/s 0.51
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 805.86k 2.1/s
Sort range 737.30k 1.9/s
Sort mrg pass 101.32k 0.3/s
__ Query Cache __________________________________________________ _______
Memory usage 37.85M of 80.00M %Used: 47.32
Block Fragmnt 23.03%
Hits 7.17M 18.6/s
Inserts 6.61M 17.1/s
Insrt:Prune 137.65:1 17.0/s
Hit:Insert 1.09:1
__ Table Locks __________________________________________________ _______
Waited 276.48k 0.7/s %Total: 1.45
Immediate 18.78M 48.6/s
__ Tables __________________________________________________ ____________
Open 774 of 1800 %Cache: 43.00
Opened 4.39k 0.0/s
__ Connections __________________________________________________ _______
Max used 53 of 500 %Max: 10.60
Total 1.59M 4.1/s
__ Created Temp __________________________________________________ ______
Disk table 190.58k 0.5/s
Table 662.58k 1.7/s
File 202.64k 0.5/s
__ Threads __________________________________________________ ___________
Running 16 of 20
Cached 33 of 384 %Hit: 100
Created 53 0.0/s
Slow 2 0.0/s
__ Aborted __________________________________________________ ___________
Clients 3.02k 0.0/s
Connects 13 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.49G 3.9k/s
Received 2.08G 5.4k/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
vBulletin and vbAdvanced
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average users: 320
Maximum users: 810
Cookie timeout: 1800 seconds
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://vmkmagic.com/phpinfo.php
12. 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 Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 15
MaxSpareServers 20
StartServers 15
MaxClients 50
MaxRequestsPerChild 500
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
No files greater than 2GB
14. Post output from these 3 commands
uname -a
Linux www.vmkmagic.com 2.6.9-42.0.3.ELsmp #1 SMP Fri Oct 6 06:21:39 CDT 2006 i686 i686 i386 GNU/Linux
ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32636
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) D CPU 2.80GHz
stepping : 4
cpu MHz : 2792.624
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor
ds_cpl cid xtpr
bogomips : 5586.95
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) D CPU 2.80GHz
stepping : 4
cpu MHz : 2792.624
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor
ds_cpl cid xtpr
bogomips : 5581.39