Large Site Server Optmization [Archive] - vBulletin Community Forum

TechGuy

Sat 30th Jun '07, 3:57pm

Server Optimization

We are using vb3.5.4, with 2 web servers running Fedora Core 4 with Apache 2.0.52, 2 web servers running CentOS 5 running Apache 2.2.3, 1 server dedicated for attachments, and one server dedicated to MySQL.

We have recently upgraded our database server running CentOS 4.5 with the following specs:
dual Xeon Clovertown E5345, 8x 2.33Ghz cores, 4x 4M L2
12GB memory
RAID 1 configuration

The only table using InnoDB is post.

The top statistics from the DB server are:

top - 14:39:57 up 9 days, 21:35, 2 users, load average: 0.75, 1.06, 1.05
Tasks: 114 total, 1 running, 113 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.5% us, 0.2% sy, 0.0% ni, 99.0% id, 0.2% wa, 0.0% hi, 0.1% si
Mem: 12302496k total, 10587032k used, 1715464k free, 71808k buffers
Swap: 2048276k total, 208k used, 2048068k free, 8735008k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23365 mysql 15 0 2238m 1.0g 4696 S 5 8.6 375:36.46 mysqld
1 root 16 0 4756 556 460 S 0 0.0 0:01.11 init
2 root RT 0 0 0 0 S 0 0.0 0:00.05 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.98 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.02 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/1
6 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/2
7 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/2
8 root RT 0 0 0 0 S 0 0.0 0:00.03 migration/3
9 root 34 19 0 0 0 S 0 0.0 0:00.03 ksoftirqd/3
10 root RT 0 0 0 0 S 0 0.0 0:00.02 migration/4
11 root 34 19 0 0 0 S 0 0.0 0:00.06 ksoftirqd/4
12 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/5
13 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/5
14 root RT 0 0 0 0 S 0 0.0 0:00.01 migration/6
15 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/6
16 root RT 0 0 0 0 S 0 0.0 0:00.01 migration/7
17 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/7
18 root 5 -10 0 0 0 S 0 0.0 0:00.07 events/0
19 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/1
20 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/2
21 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/3
22 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/4
23 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/5
24 root 5 -10 0 0 0 S 0 0.0 0:00.01 events/6
25 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/7
26 root 7 -10 0 0 0 S 0 0.0 0:00.00 khelper
27 root 15 -10 0 0 0 S 0 0.0 0:00.00 kacpid
70 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/0
71 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/1
72 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/2
73 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/3
74 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/4
75 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/5
76 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/6
77 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/7
78 root 15 0 0 0 0 S 0 0.0 0:00.00 khubd
115 root 15 0 0 0 0 S 0 0.0 5:42.90 kswapd0
116 root 12 -10 0 0 0 S 0 0.0 0:00.00 aio/0
117 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/1
118 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/2
119 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/3
120 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/4
121 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/5
122 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/6
123 root 5 -10 0 0 0 S 0 0.0 0:00.00 aio/7
267 root 23 0 0 0 0 S 0 0.0 0:00.00 kseriod
400 daemon 20 0 27264 3012 940 S 0 0.0 0:00.00 pound
469 daemon 20 0 27264 3012 940 S 0 0.0 0:00.00 pound
503 root 16 0 6152 1024 760 R 0 0.0 0:00.00 top
514 root 20 0 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
515 root 15 0 0 0 0 S 0 0.0 0:00.00 aacraid
536 root 7 -10 0 0 0 S 0 0.0 0:00.00 ata/0
537 root 5 -10 0 0 0 S 0 0.0 0:00.00 ata/1
538 root 5 -10 0 0 0 S 0 0.0 0:00.00 ata/2

MySQL was installed using standard RPM's for CentOS 4.5

/etc/my.cnf:
# skip-innodb
skip-locking
skip-name-resolve
max_connections = 1800

key_buffer = 400M
#key was 128

myisam_sort_buffer_size = 128M
#sort was 64

join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 500
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 15
tmp_table_size = 64M
max_allowed_packet = 16M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 32M
thread_concurrency = 4

query_cache_limit = 4M
query_cache_size = 384M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

long_query_time = 30
log-slow-queries = /var/log/slowlog
#log-error

#mjc Added by dan for innodb
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
#
innodb_buffer_pool_size=1024M
#transaction-isolation=READ-UNCOMMITTED
innodb_additional_mem_pool_size=100M
#
# Set the log file size to about 25% of the buffer pool size
# innodb_log_file_size=25M
# innodb_log_buffer_size=8M
#
# innodb_flush_log_at_trx_commit=1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
#key was 64

sort_buffer = 384M
#sort was 64

read_buffer = 256M
write_buffer = 256M

We use sphinx for search and have phpAdsNew installed.

At average there are about 3500 total users online (guests and users).

phpinfo():

System Linux www2.techguy.org 2.6.9-22.0.1.ELsmp #1 SMP Tue Oct 18 18:39:27 EDT 2005 i686
Build Date Feb 16 2007 09:40:30
Configure Command './configure' '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--enable-force-cgi-redirect' '--disable-debug' '--enable-pic' '--disable-rpath' '--enable-inline-optimization' '--with-bz2' '--with-db4=/usr' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--with-gd=shared' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-ncurses=shared' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-xml' '--with-expat-dir=/usr' '--with-dom=shared,/usr' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--with-xmlrpc=shared' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-bcmath' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-pear=/usr/share/pear' '--with-imap=shared' '--with-imap-ssl' '--with-kerberos' '--with-ldap=shared' '--with-mysql=shared,/usr' '--with-pgsql=shared' '--with-snmp=shared,/usr' '--with-snmp=shared' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--enable-mbstring=shared' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-mime-magic=/usr/share/file/magic.mime' '--enable-force-cgi-redirect' '--enable-pcntl'
Server API CGI
Virtual Directory Support disabled
Configuration File (php.ini) Path /etc/php.ini
Scan this dir for additional .ini files /etc/php.d
additional .ini files parsed /etc/php.d/gd.ini, /etc/php.d/imap.ini, /etc/php.d/ldap.ini, /etc/php.d/mysql.ini
PHP API 20020918
PHP Extension 20020429
Zend Extension 20021010
Debug Build no
Thread Safety disabled
Registered PHP Streams php, http, ftp, https, ftps, compress.bzip2, compress.zlib

Zend logo This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies with eAccelerator v0.9.3, Copyright (c) 2004-2005 eAccelerator, by eAccelerator

PHP Credits
Configuration
PHP Core
Directive Local Value Master Value
allow_call_time_pass_reference Off Off
allow_url_fopen On On
always_populate_raw_post_data Off Off
arg_separator.input & &
arg_separator.output & &
asp_tags Off Off
auto_append_file no value no value
auto_prepend_file no value no value
browscap no value no value
default_charset no value no value
default_mimetype text/html text/html
define_syslog_variables Off Off
disable_classes no value no value
disable_functions no value no value
display_errors Off Off
display_startup_errors Off Off
doc_root no value no value
docref_ext no value no value
docref_root no value no value
enable_dl On On
error_append_string no value no value
error_log no value no value
error_prepend_string no value no value
error_reporting 2047 2047
expose_php On On
extension_dir /usr/lib/php4 /usr/lib/php4
file_uploads On On
gpc_order GPC GPC
highlight.bg #FFFFFF #FFFFFF
highlight.comment #FF8000 #FF8000
highlight.default #0000BB #0000BB
highlight.html #000000 #000000
highlight.keyword #007700 #007700
highlight.string #DD0000 #DD0000
html_errors On On
ignore_repeated_errors Off Off
ignore_repeated_source Off Off
ignore_user_abort Off Off
implicit_flush Off Off
include_path .:/usr/share/pear .:/usr/share/pear
log_errors On On
log_errors_max_len 1024 1024
magic_quotes_gpc Off Off
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off
max_execution_time 40 40
max_input_time 30 30
memory_limit 128M 128M
open_basedir no value no value
output_buffering no value no value
output_handler no value no value
post_max_size 8M 8M
precision 14 14
register_argc_argv On On
register_globals Off Off
report_memleaks On On
safe_mode Off Off
safe_mode_exec_dir no value no value
safe_mode_gid Off Off
safe_mode_include_dir no value no value
sendmail_from no value no value
sendmail_path /usr/sbin/sendmail -t -i /usr/sbin/sendmail -t -i
serialize_precision 100 100
short_open_tag On On
SMTP localhost localhost
smtp_port 25 25
sql.safe_mode Off Off
track_errors Off Off
unserialize_callback_func no value no value
upload_max_filesize 2M 2M
upload_tmp_dir no value no value
user_dir no value no value
variables_order EGPCS EGPCS
xmlrpc_error_number 0 0
xmlrpc_errors Off Off
y2k_compliance On On

bcmath
BCMath support enabled

bz2
BZip2 Support Enabled
BZip2 Version 1.0.2, 30-Dec-2001

calendar
Calendar support enabled

ctype
ctype functions enabled

curl
CURL support enabled
CURL Information libcurl/7.12.1 OpenSSL/0.9.7a zlib/1.2.1.2 libidn/0.5.6

dba
DBA support enabled
Supported handlers cdb cdb_make db4 inifile flatfile

dbx
dbx support enabled
dbx version 1.0.0
supported databases MySQL ODBC PostgreSQL Microsoft SQL Server FrontBase Oracle 8 (oci8) Sybase-CT

Directive Local Value Master Value
dbx.colnames_case lowercase lowercase

dio
dio support enabled

eAccelerator
eAccelerator support enabled
Version 0.9.3
Caching Enabled false
Optimizer Enabled false

Directive Local Value Master Value
eaccelerator.cache_dir /tmp/eaccelerator /tmp/eaccelerator
eaccelerator.check_mtime 1 1
eaccelerator.compress 1 1
eaccelerator.compress_level 9 9
eaccelerator.content shm_and_disk shm_and_disk
eaccelerator.debug 0 0
eaccelerator.enable 1 1
eaccelerator.filter no value no value
eaccelerator.keys shm_and_disk shm_and_disk
eaccelerator.name_space no value no value
eaccelerator.optimizer 1 1
eaccelerator.sessions shm_and_disk shm_and_disk
eaccelerator.shm_max 0 0
eaccelerator.shm_only 0 0
eaccelerator.shm_prune_period 0 0
eaccelerator.shm_size 16 16
eaccelerator.shm_ttl 0 0

exif
EXIF Support enabled
EXIF Version 1.4 $Id: exif.c,v 1.118.2.35 2005/03/05 18:30:47 rasmus Exp $
Supported EXIF Version 0220
Supported filetypes JPEG,TIFF

ftp
FTP support enabled

gd
GD Support enabled
GD Version bundled (2.0.28 compatible)
FreeType Support enabled
FreeType Linkage with freetype
GIF Read Support enabled
GIF Create Support enabled
JPG Support enabled
PNG Support enabled
WBMP Support enabled
XBM Support enabled

gettext
GetText Support enabled

gmp
gmp support enabled

iconv
iconv support enabled
iconv implementation glibc
iconv library version 2.3.4

Directive Local Value Master Value
iconv.input_encoding ISO-8859-1 ISO-8859-1
iconv.internal_encoding ISO-8859-1 ISO-8859-1
iconv.output_encoding ISO-8859-1 ISO-8859-1

imap
IMAP c-Client Version 2001
SSL Support enabled
Kerberos Support enabled

ldap
LDAP Support enabled
RCS Version $Id: ldap.c,v 1.130.2.10 2004/06/01 21:05:33 iliaa Exp $
Total Links 0/unlimited
API Version 3001
Vendor Name OpenLDAP
Vendor Version 20213

memcache
memcache support enabled
Active persistent connections 0
Revision $Revision: 1.62 $

Directive Local Value Master Value
memcache.allow_failover 0 0
memcache.chunk_size 8192 8192
memcache.default_port 11211 11211
memcache.max_failover_attempts 20 20

mime_magic
mime_magic support enabled

Directive Local Value Master Value
mime_magic.magicfile /usr/share/file/magic.mime /usr/share/file/magic.mime

mysql
MySQL Support enabled
Active Persistent Links 0
Active Links 0
Client API version 4.1.20
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

Directive Local Value Master Value
mysql.allow_persistent On On
mysql.connect_timeout 60 60
mysql.default_host no value no value
mysql.default_password no value no value
mysql.default_port no value no value
mysql.default_socket no value no value
mysql.default_user no value no value
mysql.max_links Unlimited Unlimited
mysql.max_persistent Unlimited Unlimited
mysql.trace_mode Off Off

openssl
OpenSSL support enabled
OpenSSL Version OpenSSL 0.9.7a Feb 19 2003

overload
User-Space Object Overloading Support enabled

pcntl
pcntl support enabled

pcre
PCRE (Perl Compatible Regular Expressions) Support enabled
PCRE Library Version 4.5 01-December-2003

posix
Revision $Revision: 1.51.2.2 $

pspell
PSpell Support enabled

session
Session Support enabled
Registered save handlers files user eaccelerator

Directive Local Value Master Value
session.auto_start Off Off
session.bug_compat_42 Off Off
session.bug_compat_warn On On
session.cache_expire 180 180
session.cache_limiter nocache nocache
session.cookie_domain no value no value
session.cookie_lifetime 0 0
session.cookie_path / /
session.cookie_secure Off Off
session.entropy_file no value no value
session.entropy_length 0 0
session.gc_divisor 1000 1000
session.gc_maxlifetime 1440 1440
session.gc_probability 1 1
session.name PHPSESSID PHPSESSID
session.referer_check no value no value
session.save_handler files files
session.save_path /var/lib/php/session /var/lib/php/session
session.serialize_handler php php
session.use_cookies On On
session.use_only_cookies Off Off
session.use_trans_sid Off Off

shmop
shmop support enabled

sockets
Sockets Support enabled

standard
Regex Library Bundled library enabled
Dynamic Library Support enabled
Path to sendmail /usr/sbin/sendmail -t -i

Directive Local Value Master Value
assert.active 1 1
assert.bail 0 0
assert.callback no value no value
assert.quiet_eval 0 0
assert.warning 1 1
auto_detect_line_endings 0 0
default_socket_timeout 60 60
safe_mode_allowed_env_vars PHP_ PHP_
safe_mode_protected_env_vars LD_LIBRARY_PATH LD_LIBRARY_PATH
url_rewriter.tags a=href,area=href,frame=src,input=src,form=fakeentr y a=href,area=href,frame=src,input=src,form=fakeentr y
user_agent no value no value

tokenizer
Tokenizer Support enabled

wddx
WDDX Support enabled
WDDX Session Serializer enabled

xml
XML Support active
XML Namespace Support active
EXPAT Version expat_1.95.7

yp
YP Support enabled

zlib
ZLib Support enabled
Compiled Version 1.2.1.2
Linked Version 1.2.1.2

Directive Local Value Master Value
zlib.output_compression Off Off
zlib.output_compression_level -1 -1
zlib.output_handler no value no value

Additional Modules
Module Name
sysvsem
sysvshm

Environment
Variable Value
HOSTNAME www2.techguy.org
TERM xterm
SHELL /bin/bash
HISTSIZE 1000
SSH_CLIENT ::ffff:192.168.0.1 41231 22
SSH_TTY /dev/pts/0
USER root
LS_COLORS no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd =40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;4 1:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.b tm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar= 00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00 ;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.b z2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio= 00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00 ;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL /var/spool/mail/root
PATH /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
INPUTRC /etc/inputrc
PWD /root
LANG en_US.UTF-8
SHLVL 1
HOME /root
LOGNAME root
SSH_CONNECTION ::ffff:192.168.0.1 41231 ::ffff:192.168.0.2 22
LESSOPEN |/usr/bin/lesspipe.sh %s
G_BROKEN_FILENAMES 1
_ /usr/bin/php

PHP Variables
Variable Value
_SERVER["HOSTNAME"] www2.techguy.org
_SERVER["TERM"] xterm
_SERVER["SHELL"] /bin/bash
_SERVER["HISTSIZE"] 1000
_SERVER["SSH_CLIENT"] ::ffff:192.168.0.1 41231 22
_SERVER["SSH_TTY"] /dev/pts/0
_SERVER["USER"] root
_SERVER["LS_COLORS"] no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd =40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;4 1:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.b tm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar= 00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00 ;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.b z2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio= 00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00 ;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
_SERVER["MAIL"] /var/spool/mail/root
_SERVER["PATH"] /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
_SERVER["INPUTRC"] /etc/inputrc
_SERVER["PWD"] /root
_SERVER["LANG"] en_US.UTF-8
_SERVER["SHLVL"] 1
_SERVER["HOME"] /root
_SERVER["LOGNAME"] root
_SERVER["SSH_CONNECTION"] ::ffff:192.168.0.1 41231 ::ffff:192.168.0.2 22
_SERVER["LESSOPEN"] |/usr/bin/lesspipe.sh %s
_SERVER["G_BROKEN_FILENAMES"] 1
_SERVER["_"] /usr/bin/php
_SERVER["PHP_SELF"] no value
_SERVER["argv"]

Array

_SERVER["argc"] 1
_ENV["HOSTNAME"] www2.techguy.org
_ENV["TERM"] xterm
_ENV["SHELL"] /bin/bash
_ENV["HISTSIZE"] 1000
_ENV["SSH_CLIENT"] ::ffff:192.168.0.1 41231 22
_ENV["SSH_TTY"] /dev/pts/0
_ENV["USER"] root
_ENV["LS_COLORS"] no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd =40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;4 1:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.b tm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar= 00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00 ;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.b z2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio= 00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00 ;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
_ENV["MAIL"] /var/spool/mail/root
_ENV["PATH"] /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
_ENV["INPUTRC"] /etc/inputrc
_ENV["PWD"] /root
_ENV["LANG"] en_US.UTF-8
_ENV["SHLVL"] 1
_ENV["HOME"] /root
_ENV["LOGNAME"] root
_ENV["SSH_CONNECTION"] ::ffff:192.168.0.1 41231 ::ffff:192.168.0.2 22
_ENV["LESSOPEN"] |/usr/bin/lesspipe.sh %s
_ENV["G_BROKEN_FILENAMES"] 1
_ENV["_"] /usr/bin/php

eva2000

Thu 5th Jul '07, 6:04am

Hi you seem to be missing the mysql extended-status output for new db server..

can you post output for this command for new db server and if possible for the old db server as well

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

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

TechGuy

Tue 10th Jul '07, 11:24pm

Sorry about the delayed response, here is what you've requested:

+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 256000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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 | 15 |
| 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 | YES |
| 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 | 104857600 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 1073741824 |
| innodb_data_file_path | ibdata1:100M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| 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 | 419430400 |
| 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 | 30 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 1800 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 9223372036854775807 |
| 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 | OFF |
| open_files_limit | 9010 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 16384 |
| query_cache_limit | 4194304 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 402653184 |
| 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 | 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 | 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 | EDT |
| table_cache | 500 |
| 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-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 180 |
+---------------------------------+--------------------------------------------------------+
+--------------------------------+--------------+
| Variable_name | Value |
+--------------------------------+--------------+
| Aborted_clients | 23851 |
| Aborted_connects | 5 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2321036721 |
| Bytes_sent | 213401802167 |
| Com_admin_commands | 626577 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1685264 |
| 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 | 12584 |
| 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 | 398812 |
| 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 | 21431 |
| Com_replace_select | 38 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2778641 |
| Com_set_option | 918528 |
| 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 | 0 |
| 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 | 728828 |
| Com_update_multi | 0 |
| Connections | 930700 |
| Created_tmp_disk_tables | 1154 |
| Created_tmp_files | 22132 |
| Created_tmp_tables | 45992 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 18596 |
| Handler_discover | 0 |
| Handler_read_first | 646080 |
| Handler_read_key | 1189504402 |
| Handler_read_next | 984564239 |
| Handler_read_prev | 1072329 |
| Handler_read_rnd | 69155048 |
| Handler_read_rnd_next | 3169918644 |
| Handler_rollback | 7307 |
| Handler_update | 689251 |
| Handler_write | 70924943 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 42475 |
| Key_blocks_used | 294511 |
| Key_read_requests | 486398570 |
| Key_reads | 318966 |
| Key_write_requests | 406775 |
| Key_writes | 307969 |
| Max_used_connections | 210 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 358 |
| Open_streams | 0 |
| Open_tables | 500 |
| Opened_tables | 1348 |
| Qcache_free_blocks | 2188 |
| Qcache_free_memory | 385275616 |
| Qcache_hits | 3991492 |
| Qcache_inserts | 2720116 |
| Qcache_lowmem_prunes | 327711 |
| Qcache_not_cached | 58541 |
| Qcache_queries_in_cache | 5294 |
| Qcache_total_blocks | 12972 |
| Questions | 11454391 |
| Rpl_status | NULL |
| Select_full_join | 42 |
| Select_full_range_join | 0 |
| Select_range | 548287 |
| Select_range_check | 0 |
| Select_scan | 878583 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 5 |
| Sort_merge_passes | 11358 |
| Sort_range | 610134 |
| Sort_rows | 1407647249 |
| Sort_scan | 473900 |
| 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 | 7247192 |
| Table_locks_waited | 3108 |
| Threads_cached | 76 |
| Threads_connected | 135 |
| Threads_created | 210 |
| Threads_running | 4 |
| Uptime | 67391 |
+--------------------------------+--------------+
mysqladmin Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on x86_64
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: 18 hours 43 min 12 sec

Threads: 135 Questions: 11454406 Slow queries: 5 Opens: 1348 Flush tables: 1 Open tables: 500 Queries per second avg: 169.967

eva2000

Thu 12th Jul '07, 10:43am

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

1. Upgrade MySQL server to 4.1.22
2. On web server, upgrade PHP to latest stable 4.4.x or 5.2.x release after upgrading MySQL (ensure you install as ISAPI and NOT CGI)
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards

[mysqld]
safe-show-database
back_log = 100
max_connections = 1024
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 8M
table_cache = 2000
thread_cache_size = 512
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 16
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 16M
query_cache_limit = 8M
query_cache_size = 144M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size=1024M
innodb_additional_mem_pool_size=100M

[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 1024M
sort_buffer = 1024M
read_buffer = 512M
write_buffer = 512M

[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. OR try the alternative PHP caching software, Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267

5. Upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Upgrade to vB 3.6.7 http://www.vbulletin.com/forum/showthread.php?t=229950 if you're on vB 3.6.x

6. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

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

7. You may need to install same mysql version on web server, and recompile PHP with the --with-mysql=/path/to/mysql option to tell PHP to use the latest mysql 4.1.x client libraries i.e. if mysqldump/mysqlcheck/mysqladmin are located at /usr/bin/ or /usr/local/mysql/bin then you need to compile PHP with --with-mysql=/usr or --with-mysql=/usr/local/mysql - just had a few issues with one vB client have issues with PHP connecting to external mysql server until i did this.

8. If you have split web + db servers, ensure web server has dual network cards as outlined at http://www.vbulletin.org/forum/showthread.php?t=111191