Server wigging this week, opt request?

[CreationNation]

Hey there! My server has been a dreammmm lately, until this week. Really spiking a lot on server load and SOMEthing happened a bit ago to drive processes up over 1,000!! Memory use has been climbing to around 40-50% too whereas 20ish is normal. So, here's my info if there's any room for improvement! :) Also, average process has usually been around 250 with average user base on the forum at around 250, saw 300 once this week.


1. is this on dedicated or shared virual server
DEDICATED

2. your server specs:
cpu speed/type single or dual cpus: Dual Xeon 2.4
how much memory installed: 2 GB ram
hard drive type/configuration: 74GB SCSI (x 1 more but not raid/used yet)
linux distributor or windows version: Redhat Linue Enterprise 3
apache/IIS version: apache 1.3.31
PHP version: php 4.3.8
MySQL version: mysql 4.0.20

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? NO

4. if possible how mysql was compiled/installed: Easyapache install/cpanel

5. your top stats

13:59:17 up 1:55, 1 user, load average: 5.55, 3.33, 3.07
294 processes: 292 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 13.8% 0.0% 3.9% 0.1% 0.4% 65.8% 15.7%
cpu00 9.7% 0.0% 4.1% 0.3% 1.7% 83.8% 0.0%
cpu01 15.1% 0.0% 3.1% 0.0% 0.0% 49.9% 31.8%
cpu02 15.5% 0.0% 4.7% 0.0% 0.1% 79.5% 0.0%
cpu03 14.9% 0.0% 3.5% 0.1% 0.0% 50.0% 31.2%
Mem: 2061576k av, 2034932k used, 26644k free, 0k shrd, 27536k buff
1532964k actv, 189356k in_d, 29976k in_c
Swap: 2048276k av, 19940k used, 2028336k free 949552k cached


6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
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


Extended Status: http://www.metalagesrecords.com/stat.php
NOTE: This is after a reboot today unfortunately, when I had that over 1000 processes thing. Mine currently reads:
Fri Jul 30 14:25:42 CDT 2004


14:25:43 up 2:22, 2 users, load average: 1.85, 2.83, 2.84
300 processes: 299 sleeping, 1 running, 0 zombie, 0 stopped
total 51.2% 0.0% 6.7% 0.4% 1.4% 21.2% 18.8%
cpu00 58.6% 0.0% 4.8% 0.9% 4.8% 7.6% 23.0%
cpu01 60.5% 0.0% 5.7% 0.0% 0.0% 24.0% 9.6%
cpu02 56.3% 0.0% 10.6% 0.0% 0.9% 6.7% 25.2%
cpu03 29.1% 0.0% 5.8% 0.9% 0.0% 46.6% 17.4%
Mem: 2061576k av, 2034936k used, 26640k free, 0k shrd, 18744k buff
1531012k actv, 191076k in_d, 30392k in_c
Swap: 2048276k av, 19932k used, 2028344k free 1042172k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
30316 nobody 20 0 1420 1420 888 R 0.7 0.0 0:00 2 top


Http processes currently running = 134
Mysql processes currently running = 73

Netstat information summary
3 CLOSE_WAIT
5 SYN_RECV
32 LISTEN
33 FIN_WAIT2
110 TIME_WAIT
143 ESTABLISHED

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16 |
| Aborted_connects | 54 |
| Bytes_received | 94753608 |
| Bytes_sent | 3146755811 |
| Com_admin_commands | 71 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 53638 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 386 |
| Com_delete | 3209 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 10384 |
| Com_insert_select | 81 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 985 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1260 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 103210 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 30 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 7 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 220 |
| Com_show_variables | 8 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 985 |
| Com_update | 41867 |
| Connections | 52575 |
| Created_tmp_disk_tables | 1098 |
| Created_tmp_tables | 7158 |
| Created_tmp_files | 2 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 42 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 6984 |
| Handler_read_first | 10593 |
| Handler_read_key | 18537399 |
| Handler_read_next | 19143086 |
| Handler_read_prev | 150708 |
| Handler_read_rnd | 3129383 |
| Handler_read_rnd_next | 40330982 |
| Handler_rollback | 0 |
| Handler_update | 600417 |
| Handler_write | 1307520 |
| Key_blocks_used | 15586 |
| Key_read_requests | 56204984 |
| Key_reads | 114276 |
| Key_write_requests | 341449 |
| Key_writes | 27998 |
| Max_used_connections | 130 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Open_files | 1412 |
| Open_streams | 0 |
| Opened_tables | 1091 |
| Questions | 507062 |
| Qcache_queries_in_cache | 6256 |
| Qcache_inserts | 101712 |
| Qcache_hits | 238295 |
| Qcache_lowmem_prunes | 1043 |
| Qcache_not_cached | 1496 |
| Qcache_free_memory | 5136552 |
| Qcache_free_blocks | 1912 |
| Qcache_total_blocks | 15205 |
| Rpl_status | NULL |
| Select_full_join | 148 |
| Select_full_range_join | 339 |
| Select_range | 22043 |
| Select_range_check | 0 |
| Select_scan | 19835 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 124 | (execution time > 10 secs)
| Sort_merge_passes | 1 |
| Sort_range | 20139 |
| Sort_rows | 4039567 |
| Sort_scan | 7602 |
| Table_locks_immediate | 279517 |
| Table_locks_waited | 4167 |
| Threads_cached | 63 |
| Threads_created | 139 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 8483 | 2 hrs 21 mins 23 secs
+--------------------------+------------+


Key Reads/Key Read Requests = 0.002033 (Cache hit = 99.997967%)
Key Writes/Key Write Requests = 0.081998
Connections/second = 6.198 (/hour = 22311.682)
KB received/second = 10.908 (/hour = 39268.561)
KB sent/second = 247.218 (/hour = 889985.100)
Temporary Tables Created/second = 0.844 (/hour = 3037.699)
Opened Tables/second = 0.129 (/hour = 462.997)
Slow Queries/second = 0.015 (/hour = 52.623)
% of slow queries = 0.024%
Queries/second = 59.774 (/hour = 215186.043

Is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?: Other sites as well, but the VB site is the biggest/most trafficed. Others are either low traffic or not enough I would be concerned about. Some sites have decent traffic but are mostly PHP only.

how many average and max concurrent users on your vB forum ? and what your cookie timeout is ? Usually around 220-250 since I have run it (2.5 months) I did see it get to 310 yesterday. Cookie timeout = 600

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

HTTPD.CONF settings:

KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 10
MinSpareServers 32
MaxSpareServers 64
StartServers 5
MaxClients 425


what version of vB are you running ? 3.0.0


Any help appreciated! I will addthat my memorywithin WHM is normally around 20% use but this week has been like 40-50%, with server load spiking to 4-8, one point to 15% this week. I installed spamassasin at server level and that brought down the load some.

[CreationNation]

Also, after that snafu this morning I am getting a lot of these emails now:

Database error in vBulletin :

Link-ID == false, connect failed

mysql error:

mysql error number: 0


I am kinda new to the VB scene, is there something I should run to evaluate and possibly repair for these kind of notices?

[Zachery]

I would suggest upgrading to 3.0.3 as it has a few optmzations.

Your Max Clients looks atad too high, try somthing lower like 256.
Max Connections might be as well..

But as I am still learning you should wait for Eva :) But do upgrade to 3.0.3 :D

[Zachery]

That means vBulletin could not connect to MySQL, it could have happend during your reboot while MySQLD was down and apache was still online.

[CreationNation]

Ok cool. Yeah since I am newer VB user I have been real nervous to upgrade haha. I'm nervous I will goof something up somewhere! Maybe I will do that late tonight when the user base is down.

[Zachery]

As long as you don't have any(or many) hacks install upgrading is a breeze, upload the new files and run upgrade.php. As a percaution you should always backup your database before somthing like this.

You can also setup a test instaltion to do run code changes or preform test upgrades as long as it is not publicly accessable.

[CreationNation]

Yeah the backup takes forever so ends up being a lot of down time. I am going to try the dump method mentioned on these forums though (I usually do it through VB Admin interface). It's a 2 GB tar file.

Threads: 133,513, Posts: 2,673,054, Members: 33,303

I only have a Now Playing hack installed. So I guess that wont be too bad, I downloaded Beyond Compare to try that out.

If I go in and prune old junk out I assume that will help as well? Does post count get altered after pruning? People on this site are obsessed with post count altering sheesh! haha.

[Zachery]

Only if you update post counters.

Since your running vB3, how much of that database is attachments?

[CreationNation]

I don't know? How do I check that?

Also to note, for the first time ever I got this message:


Apache has reached the MaxClients

limit. You should edit httpd.conf and change the MaxClients to something

higher. cPanel normally does this for you, however it will not set this

to a number above 255 since this may cause the server to crash/fallover. The MaxClients setting is currently set at 425

You may wish to suspend the user with the largest access log as they are generally the person using up all of the avalible connections. However, your should have your system admin verify this first.



and also:

[maxfilescheck] Apache on has exceeded the maximum amount of open files
I am bumping httpd.conf up to 450.

[Zachery]

AdminCP > vBulletin Options > AdminCP Options > Enable Quick Stats.

Click on contorl panel home link and you will see some stats about your server and vBulletin :)

[CreationNation]

PHP Maximum Upload Size2M
PHP Max Post Size55M
PHP Memory LimitNone
MySQL Maximum Packet Size16.00 MB
Database Data Disk Usage1,291.00 MB
Database Index Disk Usage479.22 MB
Attachment Disk Usage32.47 MB
Custom Profile Picture Disk Usage8.00 MB

[Zachery]

Ah, i was thinking that your attachments might have been bogging down your site, i guess not :)

[CreationNation]

By the way thanks for the tip. I am still learning where everything is in this program, it's insane!

[Zachery]

It took me the better part of a year to know as much as i do ;)

I've been using vBulletin 3 since beta 3 :)

[eva2000]

with such a size large forum and max_used_connections or mysql concurrent connections approaching 417+ from mysql extended-status it's time you moved to a 2 server setup for best performance, 1 web server dual cpu and 1 database server dual cpu.

but for now you're seriously lacking memory as you're swapping to disk by 160-200+ MB

upgrading to vB 3.0.3 will help some but with your size it's time to split to 2 servers to handle the forum db size and activity

[CreationNation]

Man, you're not supposed to tell me what I don't want to hear don't you know that!? This is where you are supposed to pull out the magic server wand and make everything b-e-t-t-e-r! LOL! Ugh... I'm strapped on cash so I don't know what I will do right now.

Would moving the MySQL DB onto the other SCSI drive help in this situation or no? Between doing that and the 3.0.3 upgrade, and pruning some old stuff out, would that give me a little leeway?

[eva2000]

separate disk for mysql will help some but you'd eventually be choking on the 1 server for your forum activity

you could try upgrading cpus on existing server to dual P4 3.06ghz xeons and up total ram to 3GB and fast scsi 15k rpm disks but eventually moving to 2 server setup is the way