High server load - optimize request [Archive]

dethfire

Tue 22nd Aug '06, 1:00pm

dedicated
Intel(R) Pentium(R) 4 CPU 3.00GHz
2gig ram
160gb hd

CentOS 4.3 2.6.9-34.0.2.EL
mysql 4.1.21
php 4.4.4
apache 1.3.37
apc 3.0.11

top - 10:11:13 up 4 days, 7:36, 2 users, load average: 3.21, 2.34, 4.23
Tasks: 124 total, 2 running, 121 sleeping, 0 stopped, 1 zombie
Cpu(s): 42.5% us, 13.3% sy, 0.0% ni, 0.0% id, 32.2% wa, 12.0% hi, 0.0% si
Mem: 2067956k total, 1416768k used, 651188k free, 21956k buffers
Swap: 4192944k total, 160k used, 4192784k free, 851364k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28564 nobody 15 0 18072 12m 3672 S 21.6 0.6 3:34.81 httpd
18969 mysql 15 0 145m 40m 3288 S 13.6 2.0 49:26.99 mysqld
21422 nobody 16 0 18216 12m 3600 R 7.6 0.6 0:21.58 httpd
13371 nobody 16 0 0 0 0 Z 4.3 0.0 0:00.31 httpd <defunct>
11143 nobody 15 0 15968 10m 3696 S 3.7 0.5 0:05.04 httpd
25040 nobody 15 0 18212 12m 3600 S 2.3 0.6 0:36.77 httpd
17508 nobody 15 0 18056 12m 3716 S 2.0 0.6 10:14.75 httpd
12519 nobody 15 0 14284 8864 3504 S 2.0 0.4 0:01.56 httpd
13089 nobody 16 0 15424 9.9m 3612 S 0.7 0.5 0:00.51 httpd
33 root 15 0 0 0 0 S 0.3 0.0 1:08.60 kswapd0
1972 root 16 0 2228 476 416 S 0.3 0.0 0:46.45 cpuspeed
17511 nobody 16 0 18064 12m 3772 S 0.3 0.6 7:42.28 httpd
1 root 16 0 2544 512 436 S 0.0 0.0 0:15.85 init
2 root 34 19 0 0 0 S 0.0 0.0 0:03.44 ksoftirqd/0
3 root 5 -10 0 0 0 S 0.0 0.0 0:03.55 events/0
4 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 khelper
5 root 15 -10 0 0 0 S 0.0 0.0 0:00.00 kacpid

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | EDT |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 4 |
| Aborted_connects | 7939 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 732478592 |
| Bytes_sent | 2564448344 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 225644 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 5794 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 106524 |
| Com_insert_select | 61 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 659 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 6832 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2294480 |
| Com_set_option | 221 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 229 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 96 |
| Com_show_variables | 101 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 659 |
| Com_update | 140679 |
| Com_update_multi | 17 |
| Connections | 220184 |
| Created_tmp_disk_tables | 2628 |
| Created_tmp_files | 172 |
| Created_tmp_tables | 19251 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 26506 |
| Handler_discover | 0 |
| Handler_read_first | 393155 |
| Handler_read_key | 22922069 |
| Handler_read_next | 24683229 |
| Handler_read_prev | 34438484 |
| Handler_read_rnd | 2547588 |
| Handler_read_rnd_next | 245110940 |
| Handler_rollback | 0 |
| Handler_update | 218103 |
| Handler_write | 913214 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1644 |
| Key_blocks_used | 7248 |
| Key_read_requests | 66947126 |
| Key_reads | 1935685 |
| Key_write_requests | 446864 |
| Key_writes | 331526 |
| Max_used_connections | 101 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 152 |
| Open_streams | 0 |
| Open_tables | 134 |
| Opened_tables | 259494 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 2994010 |
| Rpl_status | NULL |
| Select_full_join | 1183 |
| Select_full_range_join | 0 |
| Select_range | 509129 |
| Select_range_check | 0 |
| Select_scan | 407547 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 23 |
| Slow_queries | 342 |
| Sort_merge_passes | 84 |
| Sort_range | 197601 |
| Sort_rows | 10607788 |
| Sort_scan | 205983 |
| Table_locks_immediate | 3609777 |
| Table_locks_waited | 6163 |
| Threads_cached | 0 |
| Threads_connected | 58 |
| Threads_created | 220183 |
| Threads_running | 56 |
| Uptime | 72952 |
+----------------------------+------------+
Uptime: 72953 Threads: 57 Questions: 2994014 Slow queries: 342 Opens: 259496 Flush tables: 1 Open tables: 134 Queries per second avg: 41.040
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 20 hours 15 min 53 sec
Threads: 57 Questions: 2994014 Slow queries: 342 Opens: 259496 Flush tables: 1 Open tables: 134 Queries per second avg: 41.040

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

vB 3.5.4
server contains two vb communities and some small other sites
about 200+ users online average
no files over 2gb

KeepAlive = On
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 10
StartServers = 5
MaxClients = 150
Maxrequestsperchild value = 0

Linux nyc1.physicsforums.com 2.6.9-34.0.2.EL #1 Fri Jul 7 19:24:57 CDT 2006 i686 i686 i386 GNU/Linux

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) 32639
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping : 3
cpu MHz : 3001.041
cache size : 2048 KB
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 mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est cid xtpr
bogomips : 6005.42

Steve Machol

Tue 22nd Aug '06, 1:28pm

Please see this thread for help with optimizing your server:

http://www.vbulletin.com/forum/showthread.php?threadid=70117

Then post the requested info.

dethfire

Tue 22nd Aug '06, 1:45pm

hmmm what info am I missing? sorry!?

BruceWest

Tue 22nd Aug '06, 7:48pm

hmmm what info am I missing? sorry!?
I believe Steve is used to number-based replies (response to question 1 would start with a 1 and so on). Try numbering your responses - it makes it easier to support your request.

eva2000

Wed 23rd Aug '06, 12:20pm

1. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 32M
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 = 45
connect_timeout = 10
tmp_table_size = 64M
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 = 3M
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

2. Upgrade to vB 3.5.5 http://www.vbulletin.com/forum/showthread.php?t=194061 if you're on vB 3.5.x

3. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

4. Edit httpd.conf values from

KeepAlive = On
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 10
StartServers = 5
MaxClients = 150
Maxrequestsperchild value = 0

to

KeepAlive = On
MaxKeepAliveRequests = 100
KeepAliveTimeout = 5
MinSpareServers = 5
MaxSpareServers = 10
StartServers = 5
MaxClients = 150
Maxrequestsperchild value = 500

restart apache

5. If you're using CentOS 4.2/4.3 linxu operating system, then upgrade your smp linux kernel to 2.6.9-39 or higher version at least to fix the high cpu load issue with CentOS linux http://www.vbulletin.com/forum/showthread.php?t=171071

dethfire

Thu 24th Aug '06, 4:18pm

anyone see anything weird? i checked the queue via whm and there were only a few messages in there. why are some messages being sent by root and some mailnull? also why 22 zombies?

top - 14:57:22 up 6 days, 9:39, 2 users, load average: 183.37, 191.28, 108.85
Tasks: 460 total, 34 running, 403 sleeping, 1 stopped, 22 zombie
Cpu(s): 23.2% us, 35.3% sy, 0.0% ni, 0.0% id, 0.0% wa, 41.5% hi, 0.0% si
Mem: 2067956k total, 1440440k used, 627516k free, 11080k buffers
Swap: 4192944k total, 856k used, 4192088k free, 514060k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
25395 mysql 15 0 203m 160m 2832 S 24.1 8.0 54:45.11 mysqld
29056 named 22 0 36700 3444 2252 S 6.2 0.2 1:42.64 named
17691 root 15 0 5524 1592 1320 S 3.4 0.1 0:00.84 sshd
5238 nobody 18 0 52280 19m 10m R 3.0 1.0 0:00.59 httpd
25089 nobody 16 0 50556 42m 35m S 2.5 2.1 0:25.47 httpd
191 root 15 0 0 0 0 S 2.2 0.0 43:17.89 kjournald
25171 nobody 15 0 51360 38m 30m S 1.5 1.9 0:23.35 httpd
9515 nobody 18 0 48648 10m 5404 S 1.2 0.5 0:00.14 httpd
9523 root 18 0 9876 3716 2668 S 1.0 0.2 0:00.12 exim
7371 mailnull 16 0 9060 3580 2556 D 0.9 0.2 0:00.12 exim
9513 root 15 0 10104 3700 2672 S 0.9 0.2 0:00.11 exim
9579 mailnull 17 0 8924 3316 2412 R 0.9 0.2 0:00.11 exim
31196 nobody 15 0 50576 19m 12m S 0.9 1.0 0:01.62 httpd
9522 root 17 0 8796 3772 2712 S 0.9 0.2 0:00.10 exim
9581 root 18 0 9380 3756 2712 S 0.9 0.2 0:00.10 exim
26121 nobody 15 0 52240 34m 25m S 0.8 1.7 0:05.97 httpd
9514 nobody 16 0 48640 10m 5992 S 0.8 0.5 0:00.09 httpd
9519 root 18 0 8668 2928 2396 R 0.8 0.1 0:00.09 exim
9520 root 15 0 9692 3756 2712 S 0.8 0.2 0:00.09 exim
9524 root 17 0 9956 3760 2712 S 0.8 0.2 0:00.09 exim
9580 mailnull 16 0 9060 3568 2544 D 0.8 0.2 0:00.09 exim
6289 root 17 0 2284 1296 740 R 0.7 0.1 0:00.80 top
9516 nobody 16 0 49232 11m 5784 S 0.7 0.6 0:00.09 httpd
9507 mailnull 17 0 8928 3288 2412 R 0.7 0.2 0:00.08 exim
9604 mailnull 16 0 9060 3560 2536 D 0.7 0.2 0:00.08 exim
9477 mailnull 16 0 9060 3572 2548 D 0.5 0.2 0:00.12 exim
9486 mailnull 15 0 0 0 0 Z 0.5 0.0 0:00.15 exim <defunct>
9504 root 16 0 8260 3776 2712 S 0.5 0.2 0:00.07 exim
26503 nobody 15 0 52324 31m 23m S 0.4 1.6 0:02.30 httpd
9478 mailnull 16 0 9060 3580 2556 D 0.4 0.2 0:00.12 exim
9485 mailnull 15 0 0 0 0 Z 0.4 0.0 0:00.16 exim <defunct>
9501 mailnull 16 0 9060 3568 2548 D 0.4 0.2 0:00.12 exim
9510 root 18 0 8988 3692 2668 S 0.4 0.2 0:00.05 exim
9577 mailnull 16 0 9060 3568 2544 D 0.4 0.2 0:00.05 exim
9605 mailnull 17 0 8796 3076 2280 D 0.4 0.1 0:00.05 exim
10381 mailnull 25 0 8264 1724 1424 R 0.3 0.1 0:05.61 exim
4429 crnc 16 0 5332 1908 1432 S 0.3 0.1 0:00.99 psybnc
7288 mailnull 16 0 9060 3580 2556 D 0.3 0.2 0:00.11 exim

eva2000

Fri 25th Aug '06, 10:52am

probably zombied exim processes as your mail server is being hammered.. you send out mass mailings alot ? have users with bounced email a/cs ? what scripts on the server besides vB use mail/email function ?

dethfire

Wed 11th Oct '06, 5:32pm

my db is crashing alot lately. I get the "there seems to be a problem with you db" error all the time, it will only last a minute of two and then go away. is that a maxconnection problem or timeout problem?

Steve Machol

Wed 11th Oct '06, 5:54pm

We need to see the real error message. Either post the contents of the email you received, or right-click on the error page and go to View/Source. Then copy-n-paste the error message here.

dethfire

Wed 11th Oct '06, 6:11pm

is there a setting i need to edit?

Steve Machol

Wed 11th Oct '06, 6:41pm

The server has maxed out the number of MySQL connections it allows. This is a server-wide limit, not just your forums. You can try turning persistent connections off in your config.php:

$config['MasterServer']['usepconnect'] = 0;

But if you still have problems after that, all you can do is ask your host to raise the maximum number of connections they allow.

Here is more info on that error:

http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

eva2000

Thu 12th Oct '06, 12:41pm

my db is crashing alot lately. I get the "there seems to be a problem with you db" error all the time, it will only last a minute of two and then go away. is that a maxconnection problem or timeout problem?
repost info asked at http://www.vbulletin.com/go/tweak again in this thread or better yet start a new thread with info asked at http://www.vbulletin.com/go/tweak :)