Server Optimisation ..Db problems

[djilou]

Hello,
Please help me to fix mysql server, I got a lot of Mysql error :

MySQL Error : MySQL server has gone away
Error Number : 2006

1- Dedicated server
2- dual Xeon 2.8 Ghz
memory: 3Gb ram
hard drive : 2 x 320 Gb SATA2 RAID1
CentOS 5.1
Apache 1.3.41
Php 4.4.8
MySQL 5.0.37

3- vB 3.7 gold
4- No innodb databases/tables on my server
5- I don't know
6-
top - 19:44:54 up 23 days, 5:57, 3 users, load average: 3.02, 2.03, 1.12
Tasks: 260 total, 1 running, 259 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.9%us, 1.6%sy, 0.0%ni, 69.3%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 3115224k total, 2296384k used, 818840k free, 176496k buffers
Swap: 1052152k total, 136k used, 1052016k free, 1391676k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15065 apache 15 0 35700 16m 2956 S 14 0.6 0:38.57 httpd
20382 apache 15 0 34948 15m 2592 S 11 0.5 0:27.27 httpd
20238 apache 15 0 34668 15m 2572 S 10 0.5 0:29.21 httpd
10151 apache 15 0 36012 17m 3120 S 10 0.6 2:03.69 httpd
10815 apache 15 0 35472 16m 2948 S 10 0.5 1:52.65 httpd
18975 apache 15 0 35824 16m 2928 S 10 0.6 0:12.76 httpd
20390 apache 15 0 35360 15m 2508 S 9 0.5 0:26.24 httpd
10894 apache 15 0 36700 17m 2944 S 9 0.5 0:25.96 httpd
9587 apache 16 0 36608 17m 2972 S 4 0.6 2:06.13 httpd
10894 apache 15 0 36700 17m 2944 S 3 0.6 1:49.45 httpd
9139 apache 16 0 35740 16m 3020 R 2 0.6 2:01.36 httpd
10151 apache 15 0 36012 17m 3120 S 1 0.6 2:03.39 httpd
20238 apache 15 0 34672 15m 2572 R 1 0.5 0:28.90 httpd
18975 apache 15 0 35828 16m 2928 R 1 0.6 0:12.47 httpd
20546 root 15 0 2304 1100 788 R 1 0.0 0:00.09 top
8045 mysql 15 0 136m 91m 2692 S 0 3.0 1:04.16 mysqld
19921 apache 15 0 33420 14m 2564 S 0 0.5 0:07.82 httpd


7-
[mysqld]
local-infile=0

skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout


8-
| Aborted_clients | 334 |
| Aborted_connects | 2498 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 3978577923 |
| Bytes_sent | 3523796101 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 1032 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 770504 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 6 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 905 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 81563 |
| Com_delete_multi | 75 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 847 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 15 |
| Com_grant | 10 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 447036 |
| Com_insert_select | 1910 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 329 |
| Com_optimize | 105 |
| 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 | 58641 |
| 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 | 3130670 |
| Com_set_option | 3943 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 14 |
| Com_show_charsets | 499 |
| Com_show_collations | 499 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1745 |
| Com_show_databases | 652 |
| Com_show_errors | 0 |
| Com_show_fields | 2586 |
| Com_show_grants | 378 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 351 |
| 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 | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 602 |
| Com_show_triggers | 1711 |
| Com_show_variables | 1988 |
| Com_show_warnings | 31 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 433 |
| Com_unlock_tables | 329 |
| Com_update | 637489 |
| Com_update_multi | 462 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 759368 |
| Created_tmp_disk_tables | 32793 |
| Created_tmp_files | 20840 |
| Created_tmp_tables | 108232 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 235276 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 400065 |
| Handler_read_key | 974318884 |
| Handler_read_next | 1118992206 |
| Handler_read_prev | 4579146 |
| Handler_read_rnd | 277810452 |
| Handler_read_rnd_next | 2146054679 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 16044239 |
| Handler_write | 40601939 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 2518513351 |
| Key_reads | 7698079 |
| Key_write_requests | 29928621 |
| Key_writes | 6815525 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 85 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1332 |
| Open_streams | 0 |
| Open_tables | 1021 |
| Opened_tables | 6268 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 2350 |
| Qcache_free_memory | 11480824 |
| Qcache_hits | 5474678 |
| Qcache_inserts | 3082065 |
| Qcache_lowmem_prunes | 558947 |
| Qcache_not_cached | 56610 |
| Qcache_queries_in_cache | 2915 |
| Qcache_total_blocks | 9375 |
| Questions | 11385720 |
| Rpl_status | NULL |
| Select_full_join | 9911 |
| Select_full_range_join | 0 |
| Select_range | 467392 |
| Select_range_check | 0 |
| Select_scan | 641450 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 52 |
| Sort_merge_passes | 13582 |
| Sort_range | 735252 |
| Sort_rows | 598426984 |
| Sort_scan | 152172 |
| Table_locks_immediate | 8699759 |
| Table_locks_waited | 8635 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 83 |
| Threads_connected | 2 |
| Threads_created | 85 |
| Threads_running | 1 |
| Uptime | 860463 |
| Uptime_since_flush_status | 860463 |
+-----------------------------------+------------+
Uptime: 860463 Threads: 2 Questions: 11385721 Slow queries: 52 Opens: 6268 Flush tables: 1 Open tables: 1021 Queries per second avg: 13.232
mysqladmin Ver 8.41 Distrib 5.0.37, for pc-linux-gnu on i686
Copyright (C) 2000-2006 MySQL 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 5.0.37-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 9 days 23 hours 1 min 3 sec

Threads: 2 Questions: 11385721 Slow queries: 52 Opens: 6268 Flush tables: 1 Open tables: 1021 Queries per second avg: 13.232
+--------+------------+-----------+------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------------+-----------+------------+---------+------+-------+------------------+
| 759355 | wlad_forum | localhost | wlad_forum | Sleep | 5 | | |
| 759365 | da_admin | localhost | | Query | 0 | | show processlist |
+--------+------------+-----------+------------+---------+------+-------+------------------+


9- I have other script on the serveur witch utilise php and mysql

10- max users on my vB forum : 1300, cookie timeout = 3600

11- phpinfo (http://www.wladbladi.com/phpinfoo.php)

12-
Timeout 300
KeepAlive On
MaxKeepAliveRequests 500
KeepAliveTimeout 5
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 450
MaxRequestsPerChild 1000

13-
find: /proc/21351/task/21351/fd/4: No such file or directory
find: /proc/21351/fd/4: No such file or directory
find: /proc/29781/fd/95: No such file or directory
find: /proc/29792/fd/807: No such file or directory
find: /proc/29802/fd/58: No such file or directory

14-
uname -a
Linux nt10.edelweisshosting.com 2.6.18-53.el5 #1 SMP Mon Nov 12 02:22:48 EST 2007 i686 i686 i386 GNU/Linux

ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 49140
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 49140
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited


cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2800.413
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
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 constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5602.63

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2800.413
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
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 constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5599.46

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2800.413
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
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 constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5599.51

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2800.413
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
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 constant_tsc pni monitor ds_cpl cid cx16 xtpr
bogomips : 5599.46

[Steve Machol]

Try increasing the my.cnf 'wait_timeout' setting to 200, and restart MySQL. If you still get that error after that, keep increasing it in increments of 100.

[djilou]

Thanks I'll try this.

[eva2000]

Try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade MySQL 5.0.45 or 5.0.51 if possible and then upgrade PHP to 5.2.5 / 5.2.6 or if you have problems install PHP 4.4.8 (in either case install as apache module and NOT CGI. For windows based PHP try FastCGI method or ISAPI method)
2. Edit mysql server's /etc/my.cnf or c:\my.ini for windows and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards. Make sure to restart mysql server everytime you make changes to your my.cnf for the changes to take effect.

If mysql doesn't restart properly after my.cnf changes and you're on VPS server, make sure skip-innodb entry is removed or commented out from below my.cnf


[mysqld]
#skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 256
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16

[mysqld_safe]
nice = -10
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away. Each vB forum and server will have different optimal wait_timeout values depending on your vB forum traffic patterns and server hardware specifications.

3. Remove Eaccelerator or APC Cache if they're installed (check phpinfo.php url of yours to see) and instead install Xcache v1.2.1 final release or Xcache v1.2.2 http://xcache.lighttpd.net/wiki/Release-1.2.2 which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267. Xcache site http://xcache.lighttpd.net/, documentation http://trac.lighttpd.net/trac/wiki/Docs and forums http://forum.lighttpd.net/forum/4

Remember to set in php.ini the values for xcache.size to 32M or 64M and for xcache.count to a value of equal to number of processor cores you have so single core cpu = 1 or single dual core cpu = 2 or single quad core cpu = 4 or dual cpus each with dual core = 4 or dual cpus each with quad core = 8.

4. Upgrade to vB 3.0.17 http://www.vbulletin.com/forum/showthread.php?t=209720 if you're on vB 3.0.xx or upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Or if on vB 3.6.x, upgrade to vB 3.6.10 http://www.vbulletin.com/forum/showthread.php?t=268123.

But ultimately, the latest stable vB 3.7.0 version is highly recommended http://www.vbulletin.com/forum/showthread.php?t=268833. You can use my method of upgrading outlined at http://www.vbulletin.com/forum/showthread.php?t=187770 which is essentially same in that you make a copy of your live database and import it into a new empty database and point vB 3.7.0 config.php to that new imported database name, so you essentially do an upgrade on a copy of your database, leaving original database intact in case of any problems. This method also allows you to run the original database on a different directory so to run both original forum/database along side the upgraded forum/database so you can easily revert all changed templates on upgraded forum and then using old forum/database transfer or port your custom style/images etc to the new upgrade database.

Read each versions listed thread to understand the changes that have occured etc.

5. 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

6. Check phpinfo.php url to see if you have mod_gzip (Apache 1.3.x) or mod_deflate (Apache 2.x) loaded/installed (on phpinfo.php url page in browser you can do CTRL+F to bring up find prompt and type in mod_gzip or mod_deflate to quickly see). If you have either mod_gzip or mod_deflate loaded, then ensure vB admincp gzip compression is disabled since it's the same thing as mod_gzip or mod_deflate and double compression will just increase cpu loads. If you don't have mod_gzip or mod_deflate installed, then DO NOT set vB admincp gzip compression to a level higher than 1. Higher than 1, will only increase cpu loads unncessarily.

7. Edit httpd.conf values to following and restart apache

KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 2
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 180
MaxRequestsPerChild: 1000