Optimization Request

[bplinson]

1. Dedicated Server

2. Intel 2.4 ghz
1 gb ram
80 gb eide non raid
redhat el3
Apache v2.0.46
PHP 4.3.2
MySQL Version 3.23.58

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.40GHz
stepping : 7
cpu MHz : 2400.059
cache size : 512 KB
physical id : 0
siblings : 1
runqueue : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
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
bogomips : 4784.12

3. ? - How do I find out?

4. Installed by hosting company (ev1servers.net)

5. [mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
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

6. 109 processes: 108 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 8.2% 0.0% 1.2% 0.4% 0.0% 1.4% 88.8%
Mem: 1020412k av, 1000692k used, 19720k free, 0k shrd, 40396k buff
753524k actv, 82172k in_d, 17900k in_c
Swap: 2048276k av, 129808k used, 1918468k free 594028k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
7926 apache 15 0 18768 16M 3644 S 5.7 1.6 0:10 0 httpd
3587 mysql 15 0 31620 14M 1788 S 0.7 1.4 6:26 0 mysqld
7923 apache 15 0 18752 16M 3624 S 0.3 1.6 0:14 0 httpd
6749 apache 15 0 18076 15M 2972 S 0.1 1.5 0:27 0 httpd
6752 apache 15 0 18284 15M 2952 S 0.1 1.5 0:27 0 httpd
6804 apache 15 0 18668 16M 3156 S 0.1 1.6 0:28 0 httpd
6806 apache 15 0 18860 16M 2892 S 0.1 1.6 0:28 0 httpd
7011 apache 15 0 16884 14M 2868 S 0.1 1.4 0:23 0 httpd
7907 apache 15 0 17348 15M 3680 S 0.1 1.5 0:11 0 httpd
7918 apache 15 0 18364 16M 3916 S 0.1 1.6 0:14 0 httpd
7919 apache 15 0 19004 16M 3756 S 0.1 1.6 0:13 0 httpd
7925 apache 15 0 18740 16M 3640 S 0.1 1.6 0:12 0 httpd
7941 apache 15 0 18100 15M 3656 S 0.1 1.5 0:10 0 httpd
7946 apache 15 0 18548 13M 3560 S 0.1 1.3 0:09 0 httpd
8951 root 16 0 1180 1180 900 R 0.1 0.1 0:00 0 top
1 root 15 0 96 68 48 S 0.0 0.0 0:03 0 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
4 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kapmd
5 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
6 root 15 0 0 0 0 SW 0.0 0.0 0:03 0 kswapd
7 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kscand
9 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kupdated
10 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
14 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kjournald
70 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
2937 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
3302 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 eth0
3355 root 15 0 260 236 184 S 0.0 0.0 0:00 0 syslogd
3359 root 24 0 60 4 0 S 0.0 0.0 0:00 0 klogd
3464 root 15 0 736 332 248 S 0.0 0.0 0:00 0 cupsd
3505 named 25 0 920 544 324 S 0.0 0.0 0:00 0 named
3518 root 15 0 628 524 380 S 0.0 0.0 0:00 0 sshd
3532 root 15 0 456 412 336 S 0.0 0.0 0:00 0 xinetd
3551 root 25 0 144 4 0 S 0.0 0.0 0:00 0 safe_mysqld
3589 root 25 0 188 4 0 S 0.0 0.0 0:00 0 couriertcpd
3591 root 25 0 176 4 0 S 0.0 0.0 0:00 0 courierlogger
3601 root 25 0 192 4 0 S 0.0 0.0 0:00 0 couriertcpd
3603 root 25 0 176 4 0 S 0.0 0.0 0:00 0 courierlogger
3611 root 15 0 364 224 176 S 0.0 0.0 0:00 0 couriertcpd
3617 root 15 0 516 380 332 S 0.0 0.0 0:00 0 courierlogger
3622 root 25 0 192 4 0 S 0.0 0.0 0:00 0 couriertcpd
3625 root 25 0 176 4 0 S 0.0 0.0 0:00 0 courierlogger
3633 qmails 15 0 180 164 96 S 0.0 0.0 0:00 0 qmail-send
3635 qmaill 15 0 236 228 172 S 0.0 0.0 0:00 0 splogger
3636 root 15 0 156 76 68 S 0.0 0.0 0:00 0 qmail-lspawn
3637 qmailr 15 0 164 140 76 S 0.0 0.0 0:00 0 qmail-rspawn
3638 qmailq 15 0 100 88 48 S 0.0 0.0 0:00 0 qmail-clean
3756 root 19 0 18712 4 0 S 0.0 0.0 0:00 0 spamd
3828 popuser 15 0 19320 2308 684 S 0.0 0.2 0:00 0 spamd
3841 popuser 15 0 19224 2244 592 S 0.0 0.2 0:00 0 spamd
3958 root 15 0 5496 2776 308 S 0.0 0.2 0:01 0 httpd


7. http://xlforum.net/mysqlinfo.php

back_log 50
basedir /usr/
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 NO
have_gemini NO
have_innodb YES
have_isam YES
have_raid NO
have_openssl NO
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_mirrored_log_groups 1
interactive_timeout 28800
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
log_long_queries OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
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_max_extra_sort_file_size 256
myisam_max_sort_file_size 2047
myisam_recover_options 0
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
record_rnd_buffer 131072
query_buffer_size 0
safe_show_database ON
server_id 0
slave_net_timeout 3600
skip_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 65536
transaction_isolation READ-COMMITTED
timezone GMT
tmp_table_size 33554432
tmpdir /tmp/
version 3.23.58
wait_timeout 28800
Aborted_clients 2
Aborted_connects 1
Bytes_received 4284892
Bytes_sent 387691402
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 826
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 0
Com_delete 108
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_insert 475
Com_insert_select 2
Com_kill 0
Com_load 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 125
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_select 11801
Com_set_option 14
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 0
Com_show_fields 0
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_status 0
Com_show_status 1
Com_show_innodb_status 0
Com_show_tables 2
Com_show_variables 3
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 2101
Connections 828
Created_tmp_disk_tables 36
Created_tmp_tables 164
Created_tmp_files 0
Delayed_insert_threads 1
Delayed_writes 90
Delayed_errors 0
Flush_commands 1
Handler_delete 1042
Handler_read_first 1855
Handler_read_key 361226
Handler_read_next 1534861
Handler_read_prev 0
Handler_read_rnd 314363
Handler_read_rnd_next 18853437
Handler_update 387742
Handler_write 307133
Key_blocks_used 7336
Key_read_requests 1296505
Key_reads 6870
Key_write_requests 54599
Key_writes 1122
Max_used_connections 22
Not_flushed_key_blocks 2
Not_flushed_delayed_rows 0
Open_tables 64
Open_files 112
Open_streams 0
Opened_tables 1588
Questions 16277
Select_full_join 10
Select_full_range_join 0
Select_range 4179
Select_range_check 0
Select_scan 2635
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 7
Sort_merge_passes 0
Sort_range 1437
Sort_rows 1240053
Sort_scan 1252
Table_locks_immediate 20187
Table_locks_waited 139
Threads_cached 0
Threads_created 826
Threads_connected 5
Threads_running 4
Uptime 953
8. One other extremly low traffic website on server is running WordPress. Also running vbPortal and Photopost.

9. Average user on vB forum is about 90 and max is about 150-170. Cookie timeout is 900

10. http://xlforum.net/phpinfo.php

11. KeepAlive - Off
MaxKeepAliveRequests - 100
KeepAliveTimeout - 15
MinSpareServers - 5
MaxSpareServers - 20
StartServers - 8
MaxClients - 150
AND Maxrequestsperchild value = 100

12. 3.5.0 patched to 3.5.3

13. None

Please let me kow if I missed anything.

[bplinson]

Server has been crashing and locking up everynight for the last four or five days.

The only changes I made the server was change the my.cnf and put set-variable=max_connections=500 and in the php.ini I changed the mysql.allow_persistent to Off.

The server crashed on the evening of 15 Jan so I removed the set-variable=max_connections=500 from my.cnf putting it back to its original configuration.

Server crashed again last night so I have set the mysql.allow_persistent back to on.

When the server locks up I can hardly even ssh in so I have to open a trouble ticket with ev1servers.net to have them rebbot it onsite.

Still getting this though:

Database error in vBulletin :

mysql_connect(): Too many connections
/home/httpd/vhosts/xlforum.net/httpdocs/vbportal/forums/includes/class_core.php on line 273

MySQL Error :
Error Number :
Date : Tuesday, January 17th 2006 @ 04:18:54 AM
Script : http://xlforum.net/vbportal/forums/search.php?searchid=210664 (http://webmail.xlforum.net/horde/services/go.php?url=http%3A%2F%2Fxlforum.net%2Fvbportal%2Ff orums%2Fsearch.php%3Fsearchid%3D210664)
Referrer :
IP Address : 72.24.6.51
Username :
Classname : vb_database

I am at my wits end here. Is my server too weak to handle the traffic?

[eva2000]

vB 3.5.x needs at least MySQL 4 you're running a really out dated version of mysql

1. Upgrade MySQL server to 4.0.25/26
2. Upgrade PHP to 4.4.1 if you use phpadsnew make sure to update to phpadsnew 2.0.7 to work with PHP 4.4.1
3. 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
skip-innodb
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 = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[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


4. Install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367

5. Upgrade to vB 3.5.3 http://www.vbulletin.com/forum/showthread.php?t=166391

6. If you just upgraded to vB 3.5.x and above changes still have load issues, try to disable these 4 options:

Admin CP -> vBulletin Options -> Server Settings and Optimization 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

[bplinson]

Thanks I will give it a try.

My server is running Plesk and I am looking into how to upgrade the MySQL without breaking Plesk since I have been reading that this is a problem.

Is it okay for me to go ahead and do the my.cnf configurations settings before doing the MySQL upgrade? If not, then cna you provide me with the setting that I should use?

Thanks again.

[eva2000]

ah plesk is a pain to upgrade you have to be careful and read ev1server and plesk.com support forums for threads where sucessful upgrade of php and mysql have been done... otherwise best to look for a new server with the upgrades already on it

[bplinson]

Yup, I have been reading the ev1servers forum all morning. The Plesk forums are a joke.

Is it okay for me to make the my.cnf file changes before I upgrade MySQL?

[eva2000]

well my.cnf i recommended only works with 4.x series not 3.23.58

for mysql 3.23.58 try this


[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=1800
set-variable = thread_cache_size=128
set-variable = wait_timeout=900
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]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[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


although mysql 4.x is at least 4x times faster than mysql 3.23.x so it's worth changing to !

[bplinson]

Any idea what this is saying? I noticed that you have removed all references to InnoDB in the new my.cnf file. My mysqld.log is full of this.


060115 05:23:17 mysqld started
060115 5:23:19 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
060115 5:23:23 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 56035366
InnoDB: Doing recovery: scanned up to log sequence number 0 56035366
060115 5:23:24 InnoDB: Flushing modified pages from the buffer pool...
060115 5:23:24 InnoDB: Started
/usr/libexec/mysqld: ready for connections
060115 8:39:53 /usr/libexec/mysqld: Normal shutdown

[eva2000]

Are you using plesk control panel ? what version it may need innodb to run for plesk

if it does readd this line under [mysqld] in my.cnf

innodb_data_file_path=ibdata1:10M:autoextend

and restart mysql

[bplinson]

Yes I am using plesk. And yes I put that line back in when I realized I could not get into plesk.

See I am not a complete imbecile!! LOL