server optimization request. Urgent!

[bplein]

I'm actually having severe problems, but haven't had any feedback to my initial problem report. I've also filed a support request, but frankly we can't continue to operate like this!

If anyone can take a look, see my original thread here as well for the SQL that is blocking.

http://www.vbulletin.com/forum/showthread.php?t=177277

Info follows:

============================================
1. VBulletin on a dedicated web server plus a dedicated database server
2. Web server: Dual 2.8 Xeon
4GB RAM
RAID-1+0 (caching) 15krpm SCSI drives
FreeBSD
apache 1.3.34
php 4.4.2

DB Server: Dual 2.8 Xeon
2GB RAM
RAID-1 (caching) 10Krpm SCSI drives
MySQL ver. 4.1.18-log

Private GiGE network connection for database.

3. Innodb used for the following tables:
pm, user, deletionlog

4. MySQL compiled from source from mysql.com, with no special flags
5.

top for web server:

last pid: 73511; load averages: 3.21, 4.79, 6.89 up 2+21:17:50 19:06:07
208 processes: 2 running, 206 sleeping
CPU states: 44.2% user, 0.0% nice, 11.5% system, 2.1% interrupt, 42.2% idle
Mem: 1078M Active, 1769M Inact, 349M Wired, 224M Cache, 199M Buf, 477M Free
Swap: 4096M Total, 24K Used, 4096M Free

top for db server:

top - 18:51:44 up 81 days, 11:53, 1 user, load average: 0.28, 0.32, 0.45
Tasks: 572 total, 1 running, 571 sleeping, 0 stopped, 0 zombie
Cpu(s): 4.5% us, 2.1% sy, 0.0% ni, 89.6% id, 3.1% wa, 0.2% hi, 0.5% si
Mem: 2074688k total, 2014568k used, 60120k free, 0k buffers
Swap: 4000760k total, 0k used, 4000760k free, 1248752k cached
6. my.cnf

[mysqld]
ft_min_word_len = 2
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp/mysql
language = /usr/local/mysql/share/mysql/english
skip-locking
skip-name-resolve
back_log = 50
bind-address=192.168.0.100
max_connections = 650
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 = 512
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
bulk_insert_buffer_size = 8M
thread_concurrency = 4
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
log_slow_queries
long_query_time = 30
key_buffer = 512M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 6G
myisam_max_extra_sort_file_size = 6G
myisam_repair_threads = 2
skip-bdb
innodb_additional_mem_pool_size = 18M
innodb_buffer_pool_size = 128M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 100M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 50

mysqladmin -u root -p var ext stat ver
Enter password:
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 33554432 |
| 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 | 2 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| 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 | 18874368 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 134217728 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| 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 | 8388608 |
| innodb_log_file_size | 104857600 |
| 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 | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | /var/log/mysql/mysqld.err |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 30 |
| 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 | 650 |
| 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_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 | 6442450944 |
| myisam_max_sort_file_size | 6442450944 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 2 |
| myisam_sort_buffer_size | 33554432 |
| 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 | 4260 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 16384 |
| 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 | 16384 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 16773120 |
| 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/run/mysqld/mysqld.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 | PST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 512 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 67108864 |
| tmpdir | /tmp/mysql |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.18-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 180 |
+---------------------------------+---------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 1537 |
| Aborted_connects | 5471 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1155689340 |
| Bytes_sent | 1639945892 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 39 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 477273 |
| 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 | 12 |
| Com_dealloc_sql | 0 |
| Com_delete | 14207 |
| 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 | 186167 |
| 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 | 9078 |
| 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 | 1256042 |
| Com_set_option | 7 |
| 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 | 35 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 18 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 18 |
| 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 | 526849 |
| Com_update_multi | 0 |
| Connections | 482758 |
| Created_tmp_disk_tables | 1892 |
| Created_tmp_files | 1575 |
| Created_tmp_tables | 39010 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 42452 |
| Handler_discover | 0 |
| Handler_read_first | 176542 |
| Handler_read_key | 67797293 |
| Handler_read_next | 274181194 |
| Handler_read_prev | 2499037 |
| Handler_read_rnd | 25439984 |
| Handler_read_rnd_next | 241067637 |
| Handler_rollback | 443438 |
| Handler_update | 482657 |
| Handler_write | 23342540 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 109223 |
| Key_blocks_used | 354697 |
| Key_read_requests | 228568625 |
| Key_reads | 355990 |
| Key_write_requests | 311212 |
| Key_writes | 268634 |
| Max_used_connections | 651 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1570 |
| Open_streams | 0 |
| Open_tables | 1578 |
| Opened_tables | 1669 |
| Qcache_free_blocks | 7082 |
| Qcache_free_memory | 41974880 |
| Qcache_hits | 1423760 |
| Qcache_inserts | 1242530 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 13452 |
| Qcache_queries_in_cache | 14150 |
| Qcache_total_blocks | 35729 |
| Questions | 4369934 |
| Rpl_status | NULL |
| Select_full_join | 6462 |
| Select_full_range_join | 0 |
| Select_range | 305778 |
| Select_range_check | 0 |
| Select_scan | 174130 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 33 |
| Sort_merge_passes | 803 |
| Sort_range | 261693 |
| Sort_rows | 36430551 |
| Sort_scan | 85449 |
| Table_locks_immediate | 3716108 |
| Table_locks_waited | 14262 |
| Threads_cached | 447 |
| Threads_connected | 65 |
| Threads_created | 789 |
| Threads_running | 64 |
| Uptime | 42482 |
+----------------------------+------------+
Uptime: 42482 Threads: 65 Questions: 4369935 Slow queries: 33 Opens: 1669 Flush tables: 1 Open tables: 1578 Queries per second avg: 102.866
mysqladmin Ver 8.40 Distrib 4.0.24, for pc-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 4.1.18-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 11 hours 48 min 2 sec

Threads: 65 Questions: 4369935 Slow queries: 33 Opens: 1669 Flush tables: 1 Open tables: 1578 Queries per second avg: 102.866
8. Other scripts on the site: gallery, phpadsnew
9. Typical connected users online: 1200, high, 1600, max ever was 3500
10: phpinfo at http://www.pirate4x4.com/vbinfo.php (fixing it now, imagemagick library errors)
11.
KeepAlive On
MaxKeepAliveRequests 5000
KeepAliveTimeout 2
MinSpareServers 5
MaxSpareServers 30
StartServers 50
MaxClients 512
MaxRequestsPerChild 5000
12. vb 3.5.4
13. No file exceeded messages, etc.
14. web server:
-su-2.05b$ uname -a
FreeBSD www.pirate4x4.net 4.11-STABLE FreeBSD 4.11-STABLE #0: Sun Apr 3 17:09:40 PDT 2005 root@www.pirate4x4.net:/usr/obj/usr/src/sys/ATLAS i386
-su-2.05b$
-su-2.05b$ ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) 1048576
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 11095
pipe size (512 bytes, -p) 1
stack size (kbytes, -s) 1048576
cpu time (seconds, -t) unlimited
max user processes (-u) 5547
virtual memory (kbytes, -v) unlimited
-su-2.05b$
-su-2.05b$ cat /proc/cpuinfo
cat: /proc/cpuinfo: No such file or directory

Database server:

atlas2 root # uname -a
Linux atlas2 2.6.11-gentoo-r4 #2 SMP Tue Apr 5 18:26:54 PDT 2005 i686 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux
atlas2 root #
atlas2 root # ulimit -aH
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) 16382
virtual memory (kbytes, -v) unlimited
atlas2 root #
atlas2 root # cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.678
cache size : 1024 KB
physical id : 0
siblings : 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 sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 5537.79

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 1
cpu MHz : 2793.678
cache size : 1024 KB
physical id : 0
siblings : 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 sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl cid cx16 xtpr
bogomips : 5586.94

[bplein]

By the way, on a different IP, same web server, we use thttpd to serve up many of the site images, so most of what hits this server is PHP

[eva2000]

Having 4GB on db server might help when allocating more memory to mysql buffers... basically the configuration of your web server would make a more ideal database server with it's 4GB ram and raid 10 scsi disks.

You are hitting your preset max_connection limit at 650 mysql connections, so might need to raise max_connections limit

try this and see if it helps - you might use more memory for mysql as well with this my.cnf

[mysqld]
ft_min_word_len = 2
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp/mysql
language = /usr/local/mysql/share/mysql/english
skip-locking
skip-name-resolve
back_log = 100
bind-address=192.168.0.100
skip-bdb
safe-show-database
old_passwords
back_log = 50
skip-innodb
max_connections = 900
key_buffer = 128M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768

innodb_additional_mem_pool_size = 18M
innodb_buffer_pool_size = 256M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 100M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 50

[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

[mysqlhotcopy]
interactive-timeout



and edit httpd.conf change from

KeepAlive On
MaxKeepAliveRequests 5000
KeepAliveTimeout 2
MinSpareServers 5
MaxSpareServers 30
StartServers 50
MaxClients 512
MaxRequestsPerChild 5000

to

KeepAlive On
MaxKeepAliveRequests 150
KeepAliveTimeout 5
MinSpareServers 20
MaxSpareServers 25
StartServers 20
MaxClients 200
MaxRequestsPerChild 10000

restart apache


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

[bplein]

Thanks. On an unrelated screwup, we lost SSH to the server.

I know that the web server is a better DB server physically, our plan is to swap them around eventually. But for reasons that made it easier on us when we went from 1 to 2 servers originally, that's what we have today.

As soon as I get back into the box, I'll try your suggestions.

That being said, none of the suggestions seem to be any silver bullet for speeding up the one slow query we have. Most queries run very fast, it's just those darned full text searches that sometimes seem to hang, or at least take an inordinate amount of time to finish.

Please note that we were running with our search problem but otherwise fine until I ugpraded to MySQL 4.1.18, that or something else in the mix completely shut down our site... it will run OK for a little while, then apache goes crazy, maxes out the system with runaway children.

For that reason, I am a little skeptical of suggestions that don't actually address the real problem we saw (one type of slow query). I've already followed the general advice I find on the board by upgrading PHP and MySQL and Apache, and it made things horribly worse.

[eva2000]

would help to repost current output for

mysqladmin -u root -p var ext stat ver

and top stats for your servers

to see how the server faired with the changes.... the process is of trial and error for most of the times :)

did you disable the 4 options i listed above in vbadmincp ?

[bplein]

I'll have to make the changes before I can post them! I am going to the colocation tonight, I should have numbers by tomorrow.

Again, disabling the "who's online" isn't going to stop a lock on the POST or THREAD table. It will just reduce load, keeping us just out of the danger zone. Based on our growth rate, that's just delaying the inevitable, which is a deadlock caused by the search joins.

[bplein]

We're back in, but I'll leave it up overnight to gather good stats and get back here.

Still seeing very slow searches.

[bplein]

Another admin restarted the services, so my data isn't old.

We're still getting killed with fulltext searches. Some searches generate long running queries that lock the threads and posts tables. This is typical in the slow queries log:

# Time: 060307 21:33:01
# User@Host: lance[lance] @ [192.168.0.50]
# Query_time: 53 Lock_time: 21 Rows_sent: 200 Rows_examined: 656
SELECT
DISTINCT thread.threadid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('high steer arm CAD') AND thread.forumid NOT IN (0,54,52,64,46,27,47,41,66,65)
LIMIT 200;
# User@Host: lance[lance] @ [192.168.0.50]
# Query_time: 207 Lock_time: 175 Rows_sent: 200 Rows_examined: 652
SELECT
DISTINCT thread.threadid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('dana 60 high steer arms') AND thread.forumid NOT IN (0,54,52,64,46,27,47,41,66,65)
LIMIT 200;


Here is the other info you requested:

-su-2.05b# mysqladmin -u root -hatlas2 -p var ext stat ver
Enter password:
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 16777216 |
| 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 | 2 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| 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 | 18874368 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| 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 | 8388608 |
| innodb_log_file_size | 104857600 |
| 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 | 2093056 |
| key_buffer_size | 134217728 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | /var/log/mysql/mysqld.err |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 30 |
| 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 | 900 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 536869888 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| 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 | 134217728 |
| 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 | 8192 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| 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/run/mysqld/mysqld.sock |
| sort_buffer_size | 4194296 |
| 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 | PST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 512 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 536870912 |
| tmpdir | /tmp/mysql |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.18-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 60 |
+---------------------------------+---------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 12 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 700445398 |
| Bytes_sent | 3675202424 |
| 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 | 312613 |
| 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 | 6758 |
| 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 | 132898 |
| 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 | 2758 |
| 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 | 788448 |
| Com_set_option | 2 |
| 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 | 1586 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1587 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 11 |
| 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 | 320436 |
| Com_update_multi | 0 |
| Connections | 312617 |
| Created_tmp_disk_tables | 869 |
| Created_tmp_files | 754 |
| Created_tmp_tables | 20533 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 22635 |
| Handler_discover | 0 |
| Handler_read_first | 105549 |
| Handler_read_key | 37573672 |
| Handler_read_next | 131184795 |
| Handler_read_prev | 1589585 |
| Handler_read_rnd | 15105973 |
| Handler_read_rnd_next | 155961436 |
| Handler_rollback | 281906 |
| Handler_update | 278288 |
| Handler_write | 14143338 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 115980 |
| Key_read_requests | 87438979 |
| Key_reads | 224292 |
| Key_write_requests | 191059 |
| Key_writes | 171557 |
| Max_used_connections | 579 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1517 |
| Open_streams | 0 |
| Open_tables | 1516 |
| Opened_tables | 1522 |
| Qcache_free_blocks | 4753 |
| Qcache_free_memory | 116121944 |
| Qcache_hits | 831773 |
| Qcache_inserts | 782508 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 5940 |
| Qcache_queries_in_cache | 8853 |
| Qcache_total_blocks | 22788 |
| Questions | 2711477 |
| Rpl_status | NULL |
| Select_full_join | 2562 |
| Select_full_range_join | 0 |
| Select_range | 164886 |
| Select_range_check | 0 |
| Select_scan | 108164 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 1 |
| Slow_queries | 9 |
| Sort_merge_passes | 375 |
| Sort_range | 163617 |
| Sort_rows | 22662536 |
| Sort_scan | 47537 |
| Table_locks_immediate | 2379752 |
| Table_locks_waited | 4875 |
| Threads_cached | 508 |
| Threads_connected | 4 |
| Threads_created | 579 |
| Threads_running | 1 |
| Uptime | 16789 |
+----------------------------+------------+
Uptime: 16789 Threads: 4 Questions: 2711483 Slow queries: 9 Opens: 1522 Flush tables: 1 Open tables: 1516 Queries per second avg: 161.504
mysqladmin Ver 8.23 Distrib 3.23.59-nightly-20050301, for portbld-freebsd4.11 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.18-log
Protocol version 10
Connection atlas2 via TCP/IP
TCP port 3306
Uptime: 4 hours 39 min 49 sec

Threads: 3 Questions: 2711492 Slow queries: 9 Opens: 1522 Flush tables: 1 Open tables: 1516 Queries per second avg: 161.504
-su-2.05b#


top on Linux DB server:

top - 21:42:36 up 87 days, 14:44, 2 users, load average: 0.20, 1.09, 1.59
Tasks: 578 total, 1 running, 577 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.0% us, 2.3% sy, 0.0% ni, 93.6% id, 0.8% wa, 0.0% hi, 0.3% si
Mem: 2074688k total, 2020664k used, 54024k free, 0k buffers
Swap: 4000760k total, 0k used, 4000760k free, 1560328k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29728 mysql 6 -10 833m 374m 3308 S 6.3 18.5 0:03.20 mysqld
30261 bill 16 0 2220 1328 796 R 1.0 0.1 0:00.26 top
29625 mysql 5 -10 833m 374m 3308 S 0.7 18.5 0:02.19 mysqld
29631 mysql 5 -10 833m 374m 3308 S 0.7 18.5 0:02.65 mysqld
30007 mysql 5 -10 833m 374m 3308 S 0.7 18.5 0:00.11 mysqld
30022 mysql 6 -10 833m 374m 3308 S 0.7 18.5 0:00.13 mysqld
29960 mysql 6 -10 833m 374m 3308 S 0.3 18.5 0:00.14 mysqld
30104 mysql 5 -10 833m 374m 3308 S 0.3 18.5 0:00.15 mysqld
30162 mysql 6 -10 833m 374m 3308 S 0.3 18.5 0:00.11 mysqld


Top on FreeBSD web server:

last pid: 47791; load averages: 0.32, 0.32, 0.32 up 276+05:39:27 21:59:37
234 processes: 1 running, 232 sleeping, 1 zombie
CPU states: 4.3% user, 0.5% nice, 1.3% system, 0.0% interrupt, 94.0% idle
Mem: 797M Active, 2467M Inact, 444M Wired, 177M Cache, 199M Buf, 12M Free
Swap: 4096M Total, 3228K Used, 4093M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
47786 root 28 0 2216K 1156K CPU1 2 0:01 7.90% 2.05% top
46247 www 2 0 61732K 26472K poll 2 0:04 0.98% 0.98% httpd
45973 www 2 0 61688K 26072K poll 2 0:05 0.88% 0.88% httpd
45894 www 2 0 61952K 25768K accept 0 0:07 0.83% 0.83% httpd
47659 www 2 0 61468K 20204K poll 1 0:00 0.84% 0.83% httpd
47674 www 2 0 61768K 18920K accept 0 0:01 0.79% 0.78% httpd
47675 www 2 0 61640K 18076K poll 1 0:01 0.79% 0.78% httpd



Note: search is turned off at the moment, and the time of day and load is different than the last ones, so I'm not exactly certain what you are going to gain from the "top" listings.

[bplein]

OK, it's 3 days later, and I suppose it's time to give up. I can't get a response here.

We've taken FULLTEXT offline. The word amongst the "Large Forums" users is that you can't use FULLTEXT unless you have search slaves (mysql for master, fulltext for search slave).

Jelsoft should just post this as part of the product. It's a shame that we pay for the software, then get hints that are NOT designed to solve the problem (I have a problem with locking on ONE query), and then listen to the other users for their hints.

If this was open source, I'd understand that the user community was the primary source of support.

VB has a great feature set, but it doesn't scale past 4-5MM posts and 1500 or so "online" users without hacking. And we, the larger sites, put ourselves at risk when hacking the sites as we can't take security patches in as quickly or easily as the unhacked sites.

I really think that Jelsoft should consider an Oracle or Postgresql port. If this takes a commited user group to help fund it, I believe that you could find 10-20 or more licensed sites that would be willing to beta and even pay for the port.

Comments are appreciated.

[eva2000]

Hi how are the loads though even with table locking ? better than they were before the recommended tweaks above ??? as it seems

Table_locks_waited | 4875

is minimal there

I've asked a vB developer to look into this thread as i can't comment on the Oracle/Postgresql plans etc.

As you are aware table locking is a limitation for mysql and hence vBulletin - although we do have vB forums with well over 5million posts who are functioning with 1,500+ users online with much beefier hardware but they have involved some form of hacking/tweaking of the code.

[ALanJay]

As you are aware table locking is a limitation for mysql and hence vBulletin - although we do have vB forums with well over 5million posts who are functioning with 1,500+ users online with much beefier hardware but they have involved some form of hacking/tweaking of the code.

Indeed - but apart from the upgrade process being a real pain after hacking the code vB can cope with much more. Our site had 7.5 million posts and runs most days with 6-7,000 users on line (at the moment we have 1716 members and 7905 guests and the most users ever online was 9621).

I admit we have quite a large amount of hardware thrown at this - 7 load front end HTML servers and a big mySQL server. But with enough hardware and enough tweaking it can be made to work :).

[bplein]

With table locking, people can't get work done.

I'm not talking about a general slow-down. If the posts table locks, people can't post. They eventually get a white screen.

table_locks_waited is a meaningless number by itself. The fact is that the uptime is very low in that statistics dump, that is a table lock waited every 3 seconds.

How many of those were acceptably short? How many were unnacceptably long?

60 seconds of locking up the posts and threads tables is unacceptable.

We've moved to Innodb and regular VB search, and that is too slow to search although we've reduced locking.

We're rearranging the hardware, starting next week. We have a better disk subsystem on what is our web server, it will eventually become the disk subsystem for the database server, and the current DB server will become the web server, and we are adding one more web server. This should speed up searches as there will be more spindles in the raid group.