Optimising vb Server, very high load :(

[1stTrade]

first, my specs

PIII 1000
512 MB Ram

about 150-200 users sim. online during peaks. 100 - 120 is average.

2.00, 1.82, 1.97 49 users online (17 members & 32 guests) This is the best case, sometimes he runs up to my old limit of 15 or further to 29

mysql max_connections 300

Thu Sep 26 00:15:49 GMT 2002


12:15am up 4:11, 0 users, load average: 2.65, 2.13, 2.06
220 processes: 211 sleeping, 9 running, 0 zombie, 0 stopped
Mem: 512904K av, 504460K used, 8444K free, 512K shrd, 3484K buff
Swap: 1024088K av, 174412K used, 849676K free 37612K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
15266 mysql 9 0 26796 11M 9104 D 1.8 2.2 0:00 mysqld
15267 mysql 9 0 26796 11M 9104 D 0.9 2.2 0:00 mysqld


Http processes currently running = 78
Mysql processes currently running = 52

Netstat information summary
1 CLOSE_WAIT
1 FIN_WAIT1
1 SYN_SENT
3 FIN_WAIT2
11 LAST_ACK
14 LISTEN
29 SYN_RECV
139 TIME_WAIT
6053 ESTABLISHED


+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1770 |
| Aborted_connects | 2 |
| Bytes_received | 93082214 |
| Bytes_sent | 1885484295 |
| Connections | 5835 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 13342 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 5024 |
| Handler_read_first | 26010 |
| Handler_read_key | 3833152 |
| Handler_read_next | 16969717 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9986844 |
| Handler_read_rnd_next | 76290716 |
| Handler_update | 90081 |
| Handler_write | 2653288 |
| Key_blocks_used | 7793 |
| Key_read_requests | 18844273 |
| Key_reads | 21834 |
| Key_write_requests | 47985 |
| Key_writes | 35109 |
| Max_used_connections | 155 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 102 |
| Open_streams | 0 |
| Opened_tables | 4113 |
| Questions | 505124 |
| Select_full_join | 2 |
| Select_full_range_join | 0 |
| Select_range | 115040 |
| Select_range_check | 0 |
| Select_scan | 75198 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 39 |
| Slow_queries | 133 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 104284 |
| Sort_rows | 10005441 |
| Sort_scan | 54069 |
| Table_locks_immediate | 567306 |
| Table_locks_waited | 3108 |
| Threads_cached | 0 |
| Threads_created | 5834 |
| Threads_connected | 45 |
| Threads_running | 1 |
| Uptime | 15034 | 4 hrs 10 mins 34 secs
+---------------------------+-----------------+


Key Reads/Key Read Requests = 0.001159 (Cache hit = 99.998841%)
Key Writes/Key Write Requests = 0.731666
Connections/second = 0.388 (/hour = 1397.233)
KB received/second = 6.046 (/hour = 21766.662)
KB sent/second = 122.475 (/hour = 440910.922)
Temporary Tables Created/second = 0.887 (/hour = 3194.838)
Opened Tables/second = 0.274 (/hour = 984.888)
Slow Queries/second = 0.009 (/hour = 31.848)
% of slow queries = 0.026%
Queries/second = 33.599 (/hour = 120955.594)

[eva2000]

need more detail info :)

please provide the following

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 either located

- in your vB 2.2.6 or higher vB version's zip file extra's folder, upload mysqlinfo.php script to your site or if you're on an pre vB 2.2.6 install go to
- http://vbulletin.com/forum/showthread.php?threadid=3477 and install that scrip making sure to edit $mysqllogin line with your own mysqlusername and password

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

10. what version of vB are you running ?

[1stTrade]

ok, back again with everything you want from me ;)

took a bit longer, but now its urgent again. At about 250 users online ( cookie 300 sec.) the serverload explodes. (example: 16.34, 11.82, 8.00 259 users online (67 members & 192 guests).
.)
16.34, 11.82, 8.00 259 users online (67 members & 192 guests).

About 180-200 users online AVERAGE, peak times 230-250

current:
12.85, 9.29, 6.74 232 users online (69 members & 163 guests).

1. Server specs
Php Version Php 4.2.3
Mysql Version: mysql Ver 11.13 Distrib 3.23.36, for redhat-linux-gnu (i386)
Headerinfos:
Apache/1.3.19 (Unix) (Red-Hat/Linux) mod_perl/1.24_01 PHP/4.2.3 FrontPage/5.0.2 mod_ssl/2.8.1 OpenSSL/0.9.6


2. Normaly on Redhat 7.2

3. This topstat: 9min before i must restart the load was by 19

7:23pm up 9 min, 1 user, load average: 3.83, 3.79, 2.26
322 processes: 313 sleeping, 9 running, 0 zombie, 0 stopped
CPU states: 65.8% user, 12.4% system, 4.4% nice, 17.3% idle
Mem: 512904K av, 415192K used, 97712K free, 5356K shrd, 14540K buff
Swap: 1024088K av, 0K used, 1024088K free 114444K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1094 mysql 10 0 22112 21M 2236 S 9.3 4.3 0:01 mysqld
1130 apache 9 0 10564 10M 8320 S 8.4 2.0 0:01 httpd
1395 mysql 9 0 22112 21M 2236 S 7.8 4.3 0:00 mysqld
1124 apache 9 0 9496 9496 8212 S 5.2 1.8 0:02 httpd
2242 apache 9 0 8956 8956 7808 S 4.7 1.7 0:00 httpd
1159 apache 9 0 9812 9812 8424 R 3.7 1.9 0:01 httpd
2266 root 14 0 1272 1272 840 R 3.7 0.2 0:00 top
1031 apache 9 0 9920 9920 8152 S 3.2 1.9 0:02 httpd
1067 apache 11 0 9524 9524 8128 S 3.2 1.8 0:01 httpd
1133 apache 9 0 9996 9996 8628 S 3.2 1.9 0:02 httpd
1030 apache 9 0 10392 10M 8208 S 2.7 2.0 0:02 httpd
1079 apache 9 0 10836 10M 8532 S 2.7 2.1 0:02 httpd
1123 apache 9 0 9452 9452 8096 S 2.7 1.8 0:02 httpd
1058 apache 9 0 9476 9476 8064 S 2.6 1.8 0:01 httpd
1152 apache 9 0 9528 9528 8136 S 2.6 1.8 0:01 httpd
2194 apache 9 0 9208 9208 7940 S 2.4 1.7 0:00 httpd
1142 apache 9 0 9504 9504 8140 S 1.7 1.8 0:01 httpd
1137 apache 9 0 9088 9088 8100 S 1.4 1.7 0:01 httpd
1145 apache 14 0 9816 9816 8252 R 1.4 1.9 0:02 httpd
1156 apache 9 0 9568 9568 8164 S 1.4 1.8 0:01 httpd
2243 apache 9 0 8984 8984 7736 S 1.4 1.7 0:00 httpd
1085 apache 9 0 9728 9728 8184 S 1.3 1.8 0:02 httpd
944 apache 9 0 10296 10M 8000 S 0.6 2.0 0:01 httpd
2215 mysql 9 0 22112 21M 2236 S 0.6 4.3 0:00 mysqld
2271 mysql 9 0 22112 21M 2236 S 0.6 4.3 0:00 mysqld
1011 apache 9 0 9356 9356 8180 S 0.4 1.8 0:01 httpd
1110 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:01 mysqld
1136 apache 9 0 9424 9424 8120 R 0.4 1.8 0:01 httpd
1163 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:00 mysqld
1224 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:01 mysqld
1727 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:00 mysqld
1871 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:00 mysqld
1922 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:00 mysqld
1923 mysql 9 0 22112 21M 2236 S 0.4 4.3 0:00 mysqld
1138 apache 9 0 9268 9268 8124 S 0.3 1.8 0:01 httpd
1167 apache 9 0 9508 9508 8088 S 0.3 1.8 0:01 httpd
1230 mysql 9 0 22112 21M 2236 S 0.3 4.3 0:01 mysqld
1715 mysql 9 0 22112 21M 2236 S 0.3 4.3 0:00 mysqld
2270 mysql 9 0 22112 21M 2236 S 0.3 4.3 0:00 mysqld
951 mysql 9 0 22112 21M 2236 S 0.1 4.3 0:01 mysqld
1010 apache 9 0 10124 9.9M 8136 S 0.1 1.9 0:01 httpd
1012 apache 9 0 10528 10M 8140 S 0.1 2.0 0:02 httpd
1019 mysql 9 0 22112 21M 2236 S 0.1 4.3 0:00 mysqld
1045 apache 9 0 9552 9552 8180 S 0.1 1.8 0:03 httpd
1084 apache 9 0 9956 9956 8468 S 0.1 1.9 0:01 httpd
1128 apache 9 0 9584 9584 8228 S 0.1 1.8 0:02 httpd
1146 apache 9 0 9320 9320 8080 S 0.1 1.8 0:01 httpd
1458 mysql 9 0 22112 21M 2236 S 0.1 4.3 0:00 mysqld
1597 apache 9 0 9344 9344 8004 S 0.1 1.8 0:00 httpd
2143 mysql 9 0 22112 21M 2236 S 0.1 4.3 0:00 mysqld
2181 apache 9 0 7660 7660 7232 S 0.1 1.4 0:00 httpd
2268 apache 9 0 7324 7324 7084 S 0.1 1.4 0:00 httpd
2272 apache 9 0 7652 7652 7228 S 0.1 1.4 0:00 httpd
1 root 8 0 532 532 464 S 0.0 0.1 0:05 init
2 root 8 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:00 ksoftirqd_CPU0
4 root 9 0 0 0 0 SW 0.0 0.0 0:00 kswapd
5 root 9 0 0 0 0 SW 0.0 0.0 0:00 kreclaimd
6 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
7 root 9 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root -1 -20 0 0 0 SW< 0.0 0.0 0:00 mdrecoveryd
12 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald
82 root 9 0 0 0 0 SW 0.0 0.0 0:00 khubd
152 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald

4.

4.1 /etc/my.cnf
[root@ensim /root]# more /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

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

[mysqld]
set-variable = max_connections=300
set-variable = interactive_timeout=120
set-variable = wait_timeout=120
[root@ensim /root]#

[1stTrade]

4.2 Variables


| Variable_name | Value
| ansi_mode | OFF
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /var/lib/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (March 27, 2001)
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 |
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innobase | NO
| have_isam | YES
| have_raid | NO
| have_ssl | NO
| init_file |
| interactive_timeout | 120
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295

[1stTrade]

| max_binlog_size | 1073741824
| max_connections | 300
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer | 2097144
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | GMT | tmp_table_size | 1048576 | tmpdir | /tmp/ | version | 3.23.36
| wait_timeout | 120



5. Extended Status of mysql
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 140 |
| Aborted_connects | 1 |
| Bytes_received | 10361016 |
| Bytes_sent | 226843634 |
| Connections | 477 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1067 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 1425 |
| Handler_read_first | 5287 |
| Handler_read_key | 382663 |
| Handler_read_next | 4430145 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1304601 |
| Handler_read_rnd_next | 24677706 |
| Handler_update | 3354 |
| Handler_write | 175333 |
| Key_blocks_used | 5235 |
| Key_read_requests | 2150103 |
| Key_reads | 5231 |
| Key_write_requests | 6072 |
| Key_writes | 4624 |
| Max_used_connections | 144 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 94 |
| Open_streams | 0 |
| Opened_tables | 371 |
| Questions | 64810 |
| Select_full_join | 3 |
| Select_full_range_join | 0 |
| Select_range | 9579 |
| Select_range_check | 0 |
| Select_scan | 9164 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 3 |
| Slow_queries | 14 |
| Sort_merge_passes | 0 |
| Sort_range | 12734 |
| Sort_rows | 1312586 |
| Sort_scan | 5262 |
| Table_locks_immediate | 68362 |
| Table_locks_waited | 298 |
| Threads_cached | 0 |
| Threads_created | 476 |
| Threads_connected | 123 |
| Threads_running | 3 |
| Uptime | 1046 |
+--------------------------+-----------+

6. Nothing :) no other sites are running on this machine
Only HotSaNic for protocoll

7. ava. 120 max 250 by 250 the serverload goes UP TO GOD :)

8. next post :) too long

9. Apache
With this options i test configs but it was more creasy.
Tested MinS on 350 MaxS 350 StartSer 250 and so on.....
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 15
StartServers 5
MaxClients 150
#MaxRequestsPerChild 30 <----not in use

10 vb 2.2.8

[1stTrade]

just phpinfo.php url please ;)

[1stTrade]

only need the url/link to phpinfo.php page ;)

[eva2000]

change or replace current my.cnf with below one and restart mysql


[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=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

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

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

[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