Server Optimization Request

محروم.كوم · #1 05-02-2009, 08:50 AM

I have some problems with the forum. After it gets around 150/200 users at the same time the server load becomes too much (over 10) and it will send out "server too busy message" to the visitors. This happens everyday.

Also i receive a database error email everyday a couple of times:

Quote:
Database error in vBulletin :
mysql_connect():
/home/virtual/site1/fst/var/www/html/forum/includes/class_core.php on line 279

1. Is this on dedicated or shared virual server.
Dedicated but probably set up as a shared server.
2. your server specs. For example:
cpu speed/type single or dual cpus): Single CPU P4 3.2 Ghz
how much memory installed: 1024 Mb ram
hard drive type/configuration: 80GB SCSI non raid
linux distributor or windows version: Fedora Core
apache/IIS version: apache 2.0.51
PHP version: php 4.3.11
MySQL version: mysql 3.23.58
3. what version of vB are you running ?
vBulletin Version 3.5.4
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

n.a. using 3.23.11
5. if possible how mysql was compiled/installed
Standard Fedora install.

6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top
Quote:
top - 09:19:59 up 165 days, 6:29, 1 user, load average: 6.46, 3.19, 1.99
Tasks: 105 total, 2 running, 103 sleeping, 0 stopped, 0 zombie
Cpu0 : 61.2% us, 11.9% sy, 0.0% ni, 26.1% id, 0.8% wa, 0.1% hi, 0.0% si
Mem: 1027656k total, 988480k used, 39176k free, 109216k buffers
Swap: 2048248k total, 105108k used, 1943140k free, 427172k cached
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).

Quote:
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
set-variable = max_connections=400
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=128
set-variable = wait_timeout=14400
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
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
8. MySQL stats from ssh telnet as root user type:

Quote:
mysqladmin -u root -p var ext stat ver proc
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_log_buffer_size | 524288 |
| 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: (September 11, 2003) |
| 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 | 10 |
| 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_innodb | DISABLED |
| 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 | |
| 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 | 1044480 |
| key_buffer_size | 16773120 |
| 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 | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 400 |
| 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 | 67108864 |
| 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_app_init/mysqld.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 2093056 |
| record_rnd_buffer | 2093056 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock |
| sort_buffer | 2097144 |
| sql_mode | 0 |
| table_cache | 1024 |
| table_type | MYISAM |
| thread_cache_size | 128 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | CET |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.58 |
| wait_timeout | 14400 |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 282 |
| Aborted_connects | 37782 |
| Bytes_received | 574507196 |
| Bytes_sent | 1705968689 |
| Com_admin_commands | 0 |
| Com_alter_table | 4 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 23209696 |
| 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 | 407386 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 3202007 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 58152 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 835688 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 133392289 |
| Com_set_option | 0 |
| Com_show_binlogs | 1 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| Com_show_fields | 2 |
| Com_show_grants | 0 |
| Com_show_keys | 2 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 1 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 186 |
| Com_show_variables | 345 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 58152 |
| Com_update | 15033348 |
| Connections | 22381971 |
| Created_tmp_disk_tables | 60990 |
| Created_tmp_tables | 1219223 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 1262558 |

| Handler_read_first | 21395727 |
| Handler_read_key | 2783816635 |
| Handler_read_next | 3153985910 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 3195369846 |
| Handler_read_rnd_next | 90155193 |
| Handler_update | 2578222857 |
| Handler_write | 1469011623 |
| Key_blocks_used | 15582 |
| Key_read_requests | 3285326389 |
| Key_reads | 18467455 |
| Key_write_requests | 43486810 |
| Key_writes | 8157841 |
| Max_used_connections | 90 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1022 |
| Open_files | 1263 |
| Open_streams | 0 |
| Opened_tables | 29110 |
| Questions | 198547776 |
| Select_full_join | 222792 |
| Select_full_range_join | 473 |
| Select_range | 27815445 |
| Select_range_check | 0 |
| Select_scan | 22485709 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 3 |
| Slow_queries | 187382 |
| Sort_merge_passes | 0 |
| Sort_range | 13308525 |
| Sort_rows | 3781114356 |
| Sort_scan | 6308359 |
| Table_locks_immediate | 215184537 |
| Table_locks_waited | 262830 |
| Threads_cached | 90 |
| Threads_created | 91 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 11266656 |
+--------------------------+------------+
Uptime: 11266656 Threads: 1 Questions: 198547777 Slow queries: 187382 Opens: 29110 Flush tables: 1 Open tables: 1022 Queries per second avg: 17.623
mysqladmin Ver 8.23 Distrib 3.23.58, for redhat-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 3.23.58
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
Uptime: 130 days 9 hours 37 min 36 sec
Threads: 1 Questions: 198547777 Slow queries: 187382 Opens: 29110 Flush tables: 1 Open tables: 1022 Queries per second avg: 17.623
+----------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+------+-----------+----+---------+------+-------+------------------+
| 22381970 | root | localhost | | Query | 0 | | show processlist |
+----------+------+-----------+----+---------+------+-------+------------------+

Also if you want to better see what's going on install mysqlreport at http://www.vbulletin.com/forum/showthread.php?t=175177 and run it by typing
Quote:
./mysqlreport -r 10 > /statistics.txt
# Beginning report, 0 0:0:0
#
MySQL 3.23.58 uptime 130 9:48:50 Fri May 1 09:48:29 2009
__ Key __________________________________________________ _______________
Buffer used 15.22M of 16.00M %Used: 95.13
Write hit 81.24%
Read hit 99.44%
__ Questions __________________________________________________ _________

Total 198.56M 17.6/s
DMS 152.88M 13.6/s %Total: 76.99
Com_ 23.33M 2.1/s 11.75
COM_QUIT 22.38M 2.0/s 11.27
-Unknown 31.33k 0.0/s 0.02
Slow 10 s 187.38k 0.0/s 0.09 %DMS: 0.12 Log:
DMS 152.88M 13.6/s 76.99
SELECT 133.40M 11.8/s 67.18 87.26
UPDATE 15.03M 1.3/s 7.57 9.83
INSERT 3.20M 0.3/s 1.61 2.09
REPLACE 835.70k 0.1/s 0.42 0.55
DELETE 407.39k 0.0/s 0.21 0.27
Com_ 23.33M 2.1/s 11.75
change_db 23.21M 2.1/s 11.69
lock_tables 58.15k 0.0/s 0.03
unlock_tabl 58.15k 0.0/s 0.03
__ SELECT and Sort __________________________________________________ ___
Scan 22.49M 2.0/s %SELECT: 16.86
Range 27.82M 2.5/s 20.85
Full join 222.80k 0.0/s 0.17
Range check 0 0/s 0.00
Full rng join 473 0.0/s 0.00
Sort scan 6.31M 0.6/s
Sort range 13.31M 1.2/s
Sort mrg pass 0 0/s
__ Table Locks __________________________________________________ _______
Waited 262.83k 0.0/s %Total: 0.12
Immediate 215.19M 19.1/s
__ Tables __________________________________________________ ____________
Open 1022 of 1024 %Cache: 99.80
Opened 29.11k 0.0/s
__ Connections __________________________________________________ _______
Max used 90 of 400 %Max: 22.50
Total 22.38M 2.0/s
__ Created Temp __________________________________________________ ______
Disk table 60.99k 0.0/s
Table 1.22M 0.1/s Size: 32.0M
File 0 0/s
__ Threads __________________________________________________ ___________
Running 2 of 2
Cached 89 of 128 %Hit: 100
Created 91 0.0/s
Slow 3 0.0/s
__ Aborted __________________________________________________ ___________
Clients 282 0.0/s
Connects 37.79k 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.84G 163.4/s
Received 576.24M 51.1/s
#
# Interval report 1, +0 0:0:10
#
MySQL 3.23.58 uptime 130 9:49:0 Fri May 1 09:48:39 2009
__ Key __________________________________________________ _______________
Buffer used 15.22M of 16.00M %Used: 95.13
Write hit 71.43%
Read hit 100.00%
__ Questions __________________________________________________ _________
Total 115 11.5/s
DMS 91 9.1/s %Total: 79.13
Com_ 12 1.2/s 10.43
COM_QUIT 9 0.9/s 7.83
+Unknown 3 0.3/s 2.61
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log:
DMS 91 9.1/s 79.13
SELECT 77 7.7/s 66.96 84.62
UPDATE 12 1.2/s 10.43 13.19
REPLACE 1 0.1/s 0.87 1.10
INSERT 1 0.1/s 0.87 1.10
DELETE 0 0/s 0.00 0.00
Com_ 12 1.2/s 10.43
change_db 10 1.0/s 8.70
show_status 1 0.1/s 0.87
show_variab 1 0.1/s 0.87
__ SELECT and Sort __________________________________________________ ___
Scan 14 1.4/s %SELECT: 18.18
Range 16 1.6/s 20.78
Full join 2 0.2/s 2.60
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 6 0.6/s
Sort range 10 1.0/s
Sort mrg pass 0 0/s
__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 129 12.9/s
__ Tables __________________________________________________ ____________
Open 1022 of 1024 %Cache: 99.80
Opened 0 0/s
__ Connections __________________________________________________ _______
Max used 90 of 400 %Max: 22.50
Total 11 1.1/s
__ Created Temp __________________________________________________ ______
Disk table 0 0/s
Table 2 0.2/s Size: 32.0M
File 0 0/s
__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 90 of 128 %Hit: 100
Created 0 0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s
__ Bytes __________________________________________________ _____________
Sent 1.32M 132.2k/s
Received 27.22k 2.7k/s
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
As far I can see, VB is the only application on this server that heavily uses PHP an mySQL.

10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average concurrent users: 150
Max concurrent users: 200 (around this amount we get server too busy message which cuts off all readers)
Cookie timeout: 1800

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://www.marijuanapassion.com/info.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 = on
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 20
StartServers = 8
MaxClients = 150
Maxrequestsperchild value = 4000
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, largest files are the access logs and they are just above 1 GB (weekly rotation)
14. Post output from these 3 commands
uname -a
Quote:
Linux server7.linkgidsnetwerk.net 2.6.10-1.771_FC2 #1 Mon Mar 28 00:50:14 EST 2005 i686 i686 i386 GNU/Linux
ulimit -aH
Quote:
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16254
virtual memory (kbytes, -v) unlimited
cat /proc/cpuinfo
Quote:
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.20GHz
stepping : 3
cpu MHz : 3194.664
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 lm pni monitor ds_cpl est
cid xtpr
bogomips : 6324.22
Thanks so much. __DEFINE_LIKE_SHARE__

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Server optimization request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-20-2009 04:10 PM
Server Optimization Request. First time optimization	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-08-2009 04:40 AM
Server Optimization Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	05-07-2009 05:50 AM
Server Optimization Request - Please help! Server loads way too high!	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	05-05-2009 02:00 AM
Server Optimization Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-20-2009 03:30 PM