Server Optimisation Request please

[hpwilhelm]

Hi, we need some server config optimisation at http://forum.mammanett.no. Average serverload is between 1.5-3 with ~100 users (30-40 registered). The load peaks every evening, and somedays during lunchhours.

When the load peaks (sometimes as high as 20) we see tablelocking on the session and user tables.

The top stats show that there is relatively much unused ram even during load peaks. Please advise as to what config settings will make vbulletin happy.

Thanks.

1. Dedicated server (Servermatrix Super Server 2.8)

2. Specs:
- HW: P4 2.8Ghz, 1GB RAM, 80GB IDE-HDD
- OS: Red Hat Enterprise Linux ES release 4 (Nahant Update 3)
- PHP: php-5.1.2-5.js.el4
- MySQL: mysql-server-4.1.20-1.RHEL4.1
- Apache: httpd-2.0.52-22.ent

3. No innoDB tables

4. MySql installed as default OS rpm

5. Top Stats.
Load peaks at 16-22 during the highest activity in the evenings.

Top stats, users online: 135 (47 members og 88 guests):

top - 22:16:01 up 96 days, 8:24, 1 user, load average: 3.53, 5.01, 5.90
Tasks: 144 total, 5 running, 138 sleeping, 0 stopped, 1 zombie
Cpu(s): 48.8% us, 2.6% sy, 0.0% ni, 8.8% id, 39.7% wa, 0.2% hi, 0.0% si
Mem: 1018372k total, 377304k used, 641068k free, 1444k buffers
Swap: 2040244k total, 79596k used, 1960648k free, 24136k cached


6. My.cnf:

[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
default-character-set=latin1

#Einartest
max_connections=200
key_buffer=16M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=512
thread_cache_size=256
wait_timeout=240
connect_timeout=10
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
skip-innodb
skip-locking
log_slow_queries=/tmp/slowmysql.log

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

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

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


7. MySQL stats from ssh telnet as root:

+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 262144 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_ndbcluster | NO |
| have_openssl | YES |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1234 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| 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 | CEST |
| table_cache | 512 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| 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.20-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 240 |
+---------------------------------+--------------------------------------------------------+
+--------------------------------+-----------+
| Variable_name | Value |
+--------------------------------+-----------+
| Aborted_clients | 2 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 404311514 |
| Bytes_sent | 124809453 |
| 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 | 74321 |
| 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 | 9514 |
| 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 | 49277 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| 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 | 20354 |
| 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 | 526406 |
| Com_set_option | 1653 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 688 |
| Com_show_variables | 15 |
| 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 | 0 |
| Com_update | 161027 |
| Com_update_multi | 0 |
| Connections | 74324 |
| Created_tmp_disk_tables | 293 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 46193 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 31271 |
| Handler_discover | 0 |
| Handler_read_first | 5692 |
| Handler_read_key | 24359112 |
| Handler_read_next | 10742967 |
| Handler_read_prev | 1027885 |
| Handler_read_rnd | 757933 |
| Handler_read_rnd_next | 45562402 |
| Handler_rollback | 0 |
| Handler_update | 807177 |
| Handler_write | 1300957 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 108731767 |
| Key_reads | 183572 |
| Key_write_requests | 363500 |
| Key_writes | 228128 |
| Max_used_connections | 83 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 523 |
| Open_streams | 0 |
| Open_tables | 512 |
| Opened_tables | 618 |
| Qcache_free_blocks | 1868 |
| Qcache_free_memory | 52520064 |
| Qcache_hits | 355787 |
| Qcache_inserts | 410943 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 115497 |
| Qcache_queries_in_cache | 5053 |
| Qcache_total_blocks | 12280 |
| Questions | 1273404 |
| Rpl_status | NULL |
| Select_full_join | 267 |
| Select_full_range_join | 0 |
| Select_range | 94034 |
| Select_range_check | 0 |
| Select_scan | 128318 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2334 |
| Sort_merge_passes | 0 |
| Sort_range | 79107 |
| Sort_rows | 5626664 |
| Sort_scan | 64725 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 1435219 |
| Table_locks_waited | 25860 |
| Threads_cached | 67 |
| Threads_connected | 16 |
| Threads_created | 83 |
| Threads_running | 16 |
| Uptime | 139142 |
+--------------------------------+-----------+
Uptime: 139143 Threads: 16 Questions: 1273406 Slow queries: 2334 Opens: 618 Flush tables: 1 Open tables: 512 Queries per second avg: 9.152
mysqladmin Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on i386
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.20-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 day 14 hours 39 min 3 sec

Threads: 16 Questions: 1273406 Slow queries: 2334 Opens: 618 Flush tables: 1 Open tables: 512 Queries per second avg: 9.152


8. The servers most trafficed area is the vbulletin forums.
Other scripts: (photopost)vba_gallery, php_list(monthly newsletter), phpadsnew(using custom 1min cached banner invocation) - all latest stable release.

phpadsnew is running on a seperate subdomain (same server). Presistent connections are turned off.

9. Average users online: 100-140 (30-50 registered users). Max concurrent users: ~220. Cookie timeout: 15min

10. http://forum.mammanett.no/phpinfo.php

11. Apache.conf

KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 6
MinSpareServers 15
MaxSpareServers 20
StartServers 10
MaxClients 180
AND Maxrequestsperchild value = 1500


12. Powered by vBulletin Version 3.5.4
Some settings:

- Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Enabled for all users

- Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums.
Enabled only for registered users (plugin from vb.org)

- Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread.
Enabeled only for registered users (plugin)

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

- From config.php: $config['MasterServer']['usepconnect'] = 0;

13. no large log files

14. Command outputs

uname -a

Linux mammanett 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:32:14 EDT 2005 i686 i686 i386 GNU/Linux


ulimit -aH

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


cat /proc/cpuinfo

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 5
cpu MHz : 2814.275
cache size : 512 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 : 2
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 cid xtpr
bogomips : 5554.17

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 5
cpu MHz : 2814.275
cache size : 512 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 : 2
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 cid xtpr
bogomips : 5619.71

[eva2000]

Well slow IDE disks won't help with table locking issues. Have you tried with photopost disabled for a few days ?

Try the following in this order

1. Upgrade MySQL server to 4.1.20 http://www.vbulletin.com/forum/showthread.php?t=186856
2. Downgrade PHP to 4.4.2
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
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 = 3M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 30
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 = 4M
query_cache_size = 64M
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
nice = -5
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


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 - please remove Zend Optimizer from php.ini before installing APC Cache

5. Edit httpd.conf values from

KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 6
MinSpareServers 15
MaxSpareServers 20
StartServers 10
MaxClients 180
AND Maxrequestsperchild value = 1500

to

KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 5
MinSpareServers 15
MaxSpareServers 20
StartServers 15
MaxClients 180
AND Maxrequestsperchild value = 500

restart apache

6. If you just upgraded to vB 3.5.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

7. Then when you get high cpu loads again post output for these 2 commands

top

mysqladmin -u root -p var ext ver proc > stats.txt

open the stats.txt file to copy and paste it's contents.

[hpwilhelm]

7. Then when you get high cpu loads again post output for these 2 commands

top

mysqladmin -u root -p var ext ver proc > stats.txt

open the stats.txt file to copy and paste it's contents.
Thank you George!

We are aware of the I/O issues with the IDE drive, we are considering to upgrade to a faster drive, but we first need to do as good as we can with the hardware we have.

We implemented everything you suggested yesterday except downgrading to PHP4. PHP4 is not an option at the moment because we currently have some code that require PHP5 running on our box.

We have got som db-errors thorugh the day. Timeout issues. This one is representative:

Invalid SQL:

UPDATE session
SET lastactivity = 1150742473, location = '/showthread.php?p=837371', incalendar = 0, badlocation = 0
WHERE sessionhash = 'd45d55af77939cb4f538d0c67dbf5de5';

MySQL Error : MySQL server has gone away
Error Number : 2006
Date : Monday, June 19th 2006 @ 08:43:22 PM


The average load is much improved, with average load 1-2. Even seeing numbers below 1 sometimes.

This evening we have high load again.

top

top - 20:38:25 up 100 days, 6:47, 1 user, load average: 15.74, 10.56, 6.97
Tasks: 144 total, 1 running, 143 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.7% us, 0.9% sy, 0.0% ni, 76.7% id, 13.7% wa, 0.1% hi, 0.0% si
Mem: 1018372k total, 347656k used, 670716k free, 1284k buffers
Swap: 2040244k total, 99820k used, 1940424k free, 33268k cached


mysqladmin -u root -p var ext ver proc > stats.txt

+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| back_log | 75 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 921600 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_ndbcluster | NO |
| have_openssl | YES |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 33554432 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67107840 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 4110 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 4194304 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| 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 | 3145720 |
| 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 | CEST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 67108864 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.20 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 30 |
+---------------------------------+--------------------------------------------------------+
+--------------------------------+-----------+
| Variable_name | Value |
+--------------------------------+-----------+
| Aborted_clients | 60 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 238955827 |
| Bytes_sent | 746488941 |
| 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 | 42624 |
| 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 | 5249 |
| 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 | 27427 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| 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 | 11629 |
| 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 | 310205 |
| Com_set_option | 886 |
| 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 | 83 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 380 |
| Com_show_variables | 12 |
| 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 | 0 |
| Com_update | 93408 |
| Com_update_multi | 0 |
| Connections | 42629 |
| Created_tmp_disk_tables | 244 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 29716 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 17690 |
| Handler_discover | 0 |
| Handler_read_first | 3645 |
| Handler_read_key | 13297661 |
| Handler_read_next | 6746612 |
| Handler_read_prev | 637128 |
| Handler_read_rnd | 415720 |
| Handler_read_rnd_next | 28869699 |
| Handler_rollback | 0 |
| Handler_update | 153116 |
| Handler_write | 1364499 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 28995 |
| Key_read_requests | 60324220 |
| Key_reads | 51320 |
| Key_write_requests | 206415 |
| Key_writes | 126921 |
| Max_used_connections | 52 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 536 |
| Open_streams | 0 |
| Open_tables | 454 |
| Opened_tables | 460 |
| Qcache_free_blocks | 1518 |
| Qcache_free_memory | 53732816 |
| Qcache_hits | 205760 |
| Qcache_inserts | 235144 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 75087 |
| Qcache_queries_in_cache | 4206 |
| Qcache_total_blocks | 10261 |
| Questions | 740273 |
| Rpl_status | NULL |
| Select_full_join | 128 |
| Select_full_range_join | 0 |
| Select_range | 54564 |
| Select_range_check | 0 |
| Select_scan | 75912 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1877 |
| Sort_merge_passes | 0 |
| Sort_range | 44221 |
| Sort_rows | 3190966 |
| Sort_scan | 39829 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 830496 |
| Table_locks_waited | 16628 |
| Threads_cached | 25 |
| Threads_connected | 27 |
| Threads_created | 52 |
| Threads_running | 22 |
| Uptime | 85368 |
+--------------------------------+-----------+
mysqladmin Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on i386
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.20
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 23 hours 42 min 48 sec

Threads: 27 Questions: 740274 Slow queries: 1877 Opens: 460 Flush tables: 1 Open tables: 454 Queries per second avg: 8.672
+-------+-------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 42550 | forum | localhost | forum | Query | 24 | Sending data | SELECT COUNT(*) AS count
FROM post AS post
WHERE threadid = 31659
AND visible = 1
AND dateline |
| 42567 | forum | localhost | forum | Query | 12 | Sending data | SELECT
SUM(IF(visible = 1, attach, 0)) AS attachsum,
SUM(IF(visible = 1, 1, 0)) AS visible,
|
| 42573 | forum | localhost | forum | Query | 5 | Sending data | SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM post AS post
WHERE post.use |
| 42575 | forum | localhost | forum | Query | 3 | Sending data | SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM post AS post
WHERE post.use |
| 42581 | forum | localhost | forum | Query | 2 | Sending data | SELECT COUNT(*) AS count
FROM thread AS thread
LEFT JOIN threadread AS threadread ON (thread |
| 42583 | forum | localhost | forum | Query | 0 | statistics | SELECT dateline, pagetext
FROM post
WHERE postid = 837371 |
| 42585 | forum | localhost | forum | Query | 24 | Sorting result | SELECT postid
FROM post AS post

WHERE threadid = 31053
AND visible = 1

ORDER B |
| 42588 | forum | localhost | forum | Query | 12 | Sorting result | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 42590 | forum | localhost | forum | Query | 8 | statistics | SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM post AS post
WHERE post.use |
| 42595 | forum | localhost | forum | Query | 12 | Sorting result | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 42603 | forum | localhost | forum | Query | 3 | Sending data | SELECT thread.threadid, thread.forumid,
IF(threadread.readtime IS NULL, 1150137490, IF(threadread |
| 42611 | forum | localhost | forum | Query | 11 | Sending data | SELECT IF(visible = 2, 1, 0) AS isdeleted,


threadrate.vote,threadread.readtime AS threadread |
| 42612 | forum | localhost | forum | Sleep | 0 | | |
| 42613 | forum | localhost | forum | Query | 0 | Sending data | SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, reply |
| 42614 | forum | localhost | forum | Query | 3 | Locked | UPDATE session
SET lastactivity = 1150742313, location = '/showthread.php?t=30899', inforum = 2 |
| 42615 | forum | localhost | forum | Sleep | 0 | | |
| 42616 | forum | localhost | forum | Query | 3 | Sending data | SELECT thread.threadid
FROM thread AS thread

LEFT JOIN threadread AS threadread ON (thread |
| 42619 | forum | localhost | forum | Sleep | 0 | | |
| 42620 | forum | localhost | forum | Query | 3 | Sending data | SELECT user.username, (user.options & 512) AS invisible, user.usergroupid, session.userid, session.i |
| 42621 | forum | localhost | forum | Sleep | 0 | | |
| 42622 | forum | localhost | forum | Query | 0 | Sorting result | SELECT postid
FROM post AS post

WHERE threadid = 1757
AND visible = 1

ORDER BY |
| 42623 | forum | localhost | forum | Query | 0 | Sorting result | SELECT postid
FROM post AS post

WHERE threadid = 2355
AND visible = 1

ORDER BY |
| 42624 | forum | localhost | forum | Query | 0 | Sending data | SELECT
IF(votenum >= 1, votenum, 0) AS votenum,
IF(votenum >= 1 AND votenum > 0, votetotal / |
| 42625 | forum | localhost | forum | Query | 3 | Sending data | SELECT threadid AS itemid, forumid, visible AS thread_visible,
IF(postuserid = 3772, 'self', 'oth |
| 42626 | forum | localhost | forum | Query | 3 | Sending data | SELECT threadid AS itemid, forumid, visible AS thread_visible,
IF(postuserid = 1193, 'self', 'oth |
| 42627 | forum | localhost | forum | Sleep | 0 | | |
| 42628 | root | localhost | | Query | 0 | | show processlist |
+-------+-------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+

[hpwilhelm]

In addition to the high load issue from the previous post, users are also experiencing server error messages: "page not found", or a blank page (newreply.php) when posting. The post goes through, and is in the appropiate thread when the user returns via the forumlisting or new posts search. Quickreply only loads, no post apears until manual browser refresh.

Some have even reported a vbulletin message saying that the Forums are closed for new posts. Any tips?

:)

[eva2000]

for 'MySQL server has gone away' message try increase in my.cnf the wait_timeout value in 30 to 60 second increments and restarting mysql server and waiting 12-24hrs each time to see if the messages go away... it's just wait_time was set too low for your mysql traffic patterns and mysql 'MySQL server has gone away' occurs

as to load issues doesn't seem to be mysql related.. tried disabling photopost and other plugin/hacks for a few days yet ?

[hpwilhelm]

Thanks George, I agree that MySql is not the main culprit here. On to others...

Photopost and plugins are now disabled. I'm going to move phpads to a different server, and in that process I will leave the ads off during prime-time this evening.

Any comment on the 'page not found' / blank page when posting? I'm guessing that APC got something to do with it.