VBulletin - Really slow :-(

[developpez.com]

Hi,

I'm a french administrator, we just migrate our old board PhpBB 2.1.8 to Vbulletin 3.5.4.

But we have serious issues. The new board is really slower than the older one.

We have :
- 92923 members
- 131021 threads
- 844409 posts
- 450 members online

Hosted on a Bi-Xeon with 4Go RAM (d?dicated server)

The load is (in admin index)
6,16 5,80 5,55

You can see our board on http://www.developpez.net/forums/

Somebody konw if it's normal ? if our server is under-rated?

[Zachery]

We'd need alot more info, server spec, etc,

http://www.vbulletin.com/forum/forumdisplay.php?f=14 This will get you the best help over all :) Read the sticky and provide the info.

Just because you've got two xeons and 4gb of ram, doesn't mean that the server is optmized for what youu are doing.

[developpez.com]

Ok, sorry :)

This is a dedicated server.
Dual Xeon 3.2 GHz
RAM: 4 GB
Hard drive: SCSI 70 GB (no RAID)
Linux distribution: RedHat 7.2
Apache: 1.3.33
PHP: 4.3.11
MySQL: 4.0.20-standard

No InnoDB tables.
MySQL 4.0.20 official RPM form MySQL.


top
3:59pm up 1 day, 22:15, 2 users, load average: 6,33, 5,72, 5,68
218 processes: 215 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 43,3% user, 8,3% system, 0,0% nice, 47,2% idle
CPU1 states: 40,4% user, 4,4% system, 0,0% nice, 54,1% idle
CPU2 states: 30,3% user, 3,0% system, 0,0% nice, 66,0% idle
CPU3 states: 34,3% user, 4,1% system, 0,0% nice, 60,4% idle
Mem: 4134396K av, 3481088K used, 653308K free, 0K shrd, 82988K buff
Swap: 522104K av, 0K used, 522104K free 1972768K cached

my.cnf
# The following options will be passed to all MySQL clients
[client]
#password=your_password
port=3306
socket=/var/lib/mysql/mysql.sock


# The MySQL server
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable=key_buffer=16M
set-variable=max_allowed_packet=1M
set-variable=table_cache=256
set-variable=max_tmp_tables=64
set-variable=sort_buffer=2M
set-variable=net_buffer_length=8K
set-variable=myisam_sort_buffer_size=2M
#log-bin
#server-id=1
set-variable=connect_timeout=8
set-variable=max_connections=250
set-variable=wait_timeout=4
set-variable=query_cache_type=1
set-variable=query_cache_size=1048576
max_allowed_packet=16M

[mysqldump]
quick
set-variable=max_allowed_packet=4M

[mysql]
no-auto-rehash

[isamchk]
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[myisamchk]
[mysqlhotcopy]
interactive-timeout
#skip-networking
skip-bdb
skip-innodb
memlock
set-variable = slave_net_timeout=8

MySQL stats
| back_log | 50
|
| basedir | /
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set | latin1
|
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_r
u latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
|
| connect_timeout | 8
|
| convert_character_set |
|
| datadir | /var/lib/mysql/
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_min_word_len | 4
| ft_max_word_len | 254
|
| ft_max_word_len_for_sort | 20
|
| ft_stopword_file | (built-in)
|
| have_bdb | NO
|
| have_crypt | YES
|
| have_innodb | YES
|
| have_isam | YES
|
| have_raid | NO
|
| have_symlink | YES
|
| have_openssl | NO
|
| have_query_cache | YES
|
| init_file |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_file_io_threads | 4
|
| innodb_force_recovery | 0
|
| innodb_thread_concurrency | 8
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_fast_shutdown | ON
|
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
|
| innodb_log_arch_dir | ./
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_mirrored_log_groups | 1
|
| innodb_max_dirty_pages_pct | 90
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 16777216
|
| language | /usr/share/mysql/english/
|
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_update | OFF
|
| log_bin | OFF
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
| log_warnings | ON
|
| 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_connections | 250
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|
| max_insert_delayed_threads | 20
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 64
|
| max_write_lock_count | 4294967295
|
| myisam_max_extra_sort_file_size | 268435456
|
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
|
| myisam_recover_options | OFF
|
| myisam_sort_buffer_size | 2097152
|
| net_buffer_length | 8192
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| open_files_limit | 1260
|
| pid_file | /var/lib/mysql/ns6075.ovh.net.pid
|
| log_error |
|
| port | 3306
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_size | 1048576
|
| query_cache_type | ON
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
|
| server_id | 0
|
| slave_net_timeout | 3600
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 2097144
|
| sql_mode | 0
|
| table_cache | 256
|
| table_type | MYISAM
|
| thread_cache_size | 0
|
| thread_stack | 126976
|
| tx_isolation | REPEATABLE-READ
|
| timezone | CEST
|
| tmp_table_size | 33554432
|
| tmpdir | /tmp/
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| version | 4.0.20-standard
|
| version_comment | Official MySQL RPM
| version_compile_os | pc-linux
|
| wait_timeout | 4
|
+---------------------------------+-------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------+
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 12 |
| Aborted_connects | 0 |
| Bytes_received | 185412183 |
| Bytes_sent | 1793936693 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 51480 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 4717 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 22521 |
| Com_insert_select | 961 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 7497 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 414838 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 22 |
| Com_show_fields | 2 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 128 |
| Com_show_variables | 28 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 52137 |
| Connections | 51456 |
| Created_tmp_disk_tables | 311 |
| Created_tmp_tables | 34706 |
| Created_tmp_files | 4 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 19371 |
| Handler_read_first | 91139 |
| Handler_read_key | 29668965 |
| Handler_read_next | 25389255 |
| Handler_read_prev | 538726 |
| Handler_read_rnd | 14018049 |
| Handler_read_rnd_next | 472949661 |
| Handler_rollback | 0 |
| Handler_update | 68792 |
| Handler_write | 13729008 |
| Key_blocks_used | 15586 |
| Key_read_requests | 91874827 |
| Key_reads | 108686 |
| Key_write_requests | 115444 |
| Key_writes | 97074 |
| Max_used_connections | 32 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 343 |
| Open_streams | 0 |
| Opened_tables | 338 |
| Questions | 623720 |
| Qcache_queries_in_cache | 4 |
| Qcache_inserts | 262477 |
| Qcache_hits | 17913 |
| Qcache_lowmem_prunes | 233961 |
| Qcache_not_cached | 152361 |
| Qcache_free_memory | 9512 |
| Qcache_free_blocks | 2 |
| Qcache_total_blocks | 93 |
| Rpl_status | NULL |
| Select_full_join | 6980 |
| Select_full_range_join | 0 |
| Select_range | 118523 |
| Select_range_check | 0 |
| Select_scan | 84268 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 2 |
| Sort_range | 35393 |
| Sort_rows | 14027508 |
| Sort_scan | 39039 |
| Table_locks_immediate | 776387 |
| Table_locks_waited | 5627 |
| Threads_cached | 0 |
| Threads_created | 51455 |
| Threads_connected | 2 |
| Threads_running | 2 |
| Uptime | 6975 |
+--------------------------+------------+
Uptime: 6975 Threads: 2 Questions: 623721 Slow queries: 0 Opens: 338 Flush tables: 1 Open tables: 256 Queries per secon
d avg: 89.422
mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux 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.0.20-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 56 min 15 sec

Threads: 2 Questions: 623721 Slow queries: 0 Opens: 338 Flush tables: 1 Open tables: 256 Queries per second avg: 89.422

On this server there is only vB and a script to replicate vb user table for our blog and chat system (this script is run every ten minutes). Blogs and chat are located on another server.

With 5 minutes cookies timeout we have 400 connections. With 15 minutes cookies timeout we have 900 connections.

http://private.developpez.net/phpinfo.php

Apache config
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 10
MaxSpareServers 20
StartServers 15
MaxClients 150

vBulletin 3.5.4

No big files other than our database backups.

# uname -a
Linux ns6075.ovh.net 2.4.31grs-bipiv-ipv4 #1 SMP Wed Oct 5 19:31:57 CEST 2005 i686 unknown

# ulimit -aH
core file size (blocks) unlimited
data seg size (kbytes) unlimited
file size (blocks) unlimited
max locked memory (kbytes) unlimited
max memory size (kbytes) unlimited
open files 1024
pipe size (512 bytes) 8
stack size (kbytes) unlimited
cpu time (seconds) unlimited
max user processes 7168
virtual memory (kbytes) unlimited

# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3200.203
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm pni monitor ds_cpl cid
bogomips : 6383.20

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3200.203
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm pni monitor ds_cpl cid
bogomips : 6396.31

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3200.203
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm pni monitor ds_cpl cid
bogomips : 6396.31

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 1
cpu MHz : 3200.203
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm pni monitor ds_cpl cid
bogomips : 6396.31

[Erwin]

Besides optimizing apache and mysql, you might want to use 2 boxes - split web and database.

[Allan]

Il est impossible de s'inscrire sur votre forum, l'image de vérification n'apparait pas.

Verification image for register to your forum dont' show !

[Zachery]

I suggest making the post in the forum where I pointed you to, Eva2000 will look over your request and provide some insight.

However, your wait timeout is insanely low, it should be at least 180, and your apache and php versions are out of date as well.

[developpez.com]

Thanks,

We just upgrade to apache 2.2 and keep php 4.3.11, newer require glibc 2.3 and we juste have 2.2.

Due tu upgrade we forgot to add GD. I have de-activate image verification temporarly.

Ok somebody on Vbulletin.com tell us to set set-variable=query_cache_size=192M into my.cnf.

This modification appears to improve performance !

We keep trying to do better :-)

[Zachery]

php 4.3.11 has security and massive preofrmance issues.

[kmike]

php 4.3.11 has security and massive preofrmance issues.

Not sure about security, but I think you mix 4.1.11 with 4.3.10 which had a performance bug in unserialize(). 4.3.11 doesn't have any performance issues AFAIK.

[Zachery]

If it didn't have any issues, the latest stable for 4.x would still be 4.3.11


http://www.php.net/release_4_4_0.php


Bugfix release

Memory corruptions with references.
Small security problem with bundled shtool.For a full list of changes in PHP 4.4.0, see the ChangeLog (http://www.php.net/ChangeLog-4.php#4.4.0).


http://www.php.net/release_4_4_1.php


This is a bug fix release, which addresses some security problems too. The security issues that this release fixes are:

Fixed a Cross Site Scripting (XSS (http://www.cgisecurity.com/articles/xss-faq.shtml)) vulnerability in phpinfo (http://php.net/phpinfo)() that could lead f.e. to cookie exposure, when a phpinfo() script is accidently left on a production server.
Fixed multiple safe_mode/open_basedir bypass vulnerabilities in ext/curl and ext/gd that could lead to exposure of files normally not accessible due to safe_mode or open_basedir restrictions.
Fixed a possible $GLOBALS overwrite problem in file upload handling, extract() and import_request_variables() that could lead to unexpected security holes in scripts assumed secure. (For more information, see here (http://www.hardened-php.net/globals-problem)).
Fixed a problem when a request was terminated due to memory_limit constraints during certain parse_str (http://php.net/parse_str)() calls. In some cases this can result in register_globals being turned on.
Fixed an issue with trailing slashes in allowed basedirs. They were ignored by open_basedir checks, so that specified basedirs were handled as prefixes and not as full directory names.
Fixed an issue with calling virtual (http://php.net/virtual)() on Apache 2. This allowed bypassing of certain configuration directives like safe_mode or open_basedir.
Updated to the latest pcrelib to fix a possible integer overflow vulnerability announced in CAN-2005-2491 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-2491).This release also fixes 35 other defects, where the most important is the the fix that removes a notice when passing a by-reference result of a function as a by-reference value to another function. (Bug #33558 (http://bugs.php.net/33558)).
For a full list of changes in PHP 4.4.1, see the ChangeLog (http://www.php.net/ChangeLog-4.php#4.4.1).


http://www.php.net/release_4_4_2.php

The PHP Development Team would like to announce the immediate release of PHP 4.4.2 (http://www.php.net/downloads.php).

This is a bug fix release, which addresses some security problems too. The major points that this release corrects are:

Prevent header injection by limiting each header to a single line.
Possible XSS inside error reporting functionality.
Missing safe_mode/open_basedir checks into cURL extension.
Apache 2 regression with sub-request handling on non-Linux systems.
key() and current() regression related to references.This release also fixes about 30 other defects.
For a full list of changes in PHP 4.4.2, see the ChangeLog (http://www.php.net/ChangeLog-4.php#4.4.2)

[developpez.com]

We upgrade in 4.4.2 and we have seen better performance !

Now the have :
- Apache 2.2.0
- Php 4.4.2
- MySQL 4.0.20 (we think upgrade at least to 4.0.26)

With 756 users online, the load is 2,57 1,89 1,72

I think it's good now :-)

Thanks all !

[kmike]

Not to hijack this thread, but I don't see any performance fixes in your post, Zachery. And I don't see anything which seriosly affects the dedicated server owners running nothing more than vBulletin in that impressive list of security fixes. Some of them fix issues which appeared first in 4.4.0! And the $GLOBALS issue is fixed in 4.3.11, according to hardened-php project.

Also, apparently php 4.3.x is still considered stable enough by RedHat in their RHEL4 product: it's at 4.3.9 with relevant fixes ported from upstream.