Hey guys my load is always going hight when I have 200 users browsing my phpBB forum. but its fine when I have 170 users online.
I have done many kind of mysql optimization and yet the load still wont go any down. when I have 170 users online the load is 1-2 but as soon as it hits 200 it becomes huge
Server Spec
p4 3.2 ghz
2Gb ram
300gb HDD
here is TOP
And heres whats inside my.cnf
[mysqld]
datadir=/var/lib/mysql
safe-show-database
old_passwords
back_log = 15
skip-innodb
max_connections = 200
key_buffer = 256M
myisam_sort_buffer_size = 128M
join_buffer_size = 1M
read_buffer_size = 16M
sort_buffer_size = 32M
table_cache = 2048
thread_cache_size = 784
wait_timeout = 15
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 8M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 712M
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/lib/mysql/mysql.pid
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
ServerType standalone
ServerRoot "/etc/httpd"
LockFile /var/run/httpd.lock
PidFile /var/run/httpd.pid
ScoreBoardFile logs/apache_runtime_status
ResourceConfig /dev/null
AccessConfig /dev/null
Timeout 120
KeepAlive On
MaxKeepAliveRequests 300
KeepAliveTimeout 5
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 300
MaxRequestsPerChild 10
#LoadModule mmap_static_module modules/mod_mmap_static.so
LoadModule vhost_alias_module modules/mod_vhost_alias.so
<IfDefine HAVE_BANDWIDTH>
LoadModule bandwidth_module modules/mod_bandwidth.so
</IfDefine>
<IfDefine HAVE_THROTTLE>
LoadModule throttle_module modules/mod_throttle.so
</IfDefine>
LoadModule env_module modules/mod_env.so
LoadModule config_log_module modules/mod_log_config.so
LoadModule agent_log_module modules/mod_log_agent.so
LoadModule referer_log_module modules/mod_log_referer.so
#LoadModule mime_magic_module modules/mod_mime_magic.so
LoadModule mime_module modules/mod_mime.so
LoadModule negotiation_module modules/mod_negotiation.so
LoadModule status_module modules/mod_status.so
LoadModule info_module modules/mod_info.so
LoadModule includes_module modules/mod_include.so
LoadModule autoindex_module modules/mod_autoindex.so
LoadModule dir_module modules/mod_dir.so
LoadModule cgi_module modules/mod_cgi.so
LoadModule asis_module modules/mod_asis.so
LoadModule imap_module modules/mod_imap.so
LoadModule action_module modules/mod_actions.so
#LoadModule speling_module modules/mod_speling.so
LoadModule userdir_module modules/mod_userdir.so
LoadModule alias_module modules/mod_alias.so
LoadModule rewrite_module modules/mod_rewrite.so
LoadModule access_module modules/mod_access.so
LoadModule auth_module modules/mod_auth.so
LoadModule anon_auth_module modules/mod_auth_anon.so
#LoadModule db_auth_module modules/mod_auth_db.so
#LoadModule auth_any_module modules/mod_auth_any.so
#LoadModule dbm_auth_module modules/mod_auth_dbm.so
#LoadModule auth_ldap_module modules/mod_auth_ldap.so
#LoadModule mysql_auth_module modules/mod_auth_mysql.so
#LoadModule auth_pgsql_module modules/mod_auth_pgsql.so
#LoadModule digest_module modules/mod_digest.so
#LoadModule proxy_module modules/libproxy.so
#LoadModule cern_meta_module modules/mod_cern_meta.so
LoadModule expires_module modules/mod_expires.so
LoadModule headers_module modules/mod_headers.so
#LoadModule usertrack_module modules/mod_usertrack.so
#LoadModule example_module modules/mod_example.so
#LoadModule unique_id_module modules/mod_unique_id.so
LoadModule setenvif_module modules/mod_setenvif.so
<IfDefine HAVE_PERL>
LoadModule perl_module modules/libperl.so
</IfDefine>
<IfDefine HAVE_PHP>
LoadModule php_module modules/mod_php.so
</IfDefine>
<IfDefine HAVE_PHP3>
LoadModule php3_module modules/libphp3.so
</IfDefine>
#<IfDefine HAVE_PHP4>
#LoadModule php4_module modules/libphp4.so
#</IfDefine>
LoadModule php4_module /usr/lib/apache/libphp4.so
<IfDefine HAVE_DAV>
LoadModule dav_module modules/libdav.so
</IfDefine>
<IfDefine HAVE_ROAMING>
LoadModule roaming_module modules/mod_roaming.so
</IfDefine>
###<IfDefine HAVE_SSL>
LoadModule ssl_module modules/libssl.so
###</IfDefine>
<IfDefine HAVE_PUT>
LoadModule put_module modules/mod_put.so
</IfDefine>
<IfDefine HAVE_PYTHON>
LoadModule python_module modules/mod_python.so
</IfDefine>
ClearModuleList
#AddModule mod_mmap_static.c
AddModule mod_vhost_alias.c
<IfDefine HAVE_BANDWIDTH>
AddModule mod_bandwidth.c
</IfDefine>
<IfDefine HAVE_THROTTLE>
AddModule mod_throttle.c
</IfDefine>
AddModule mod_env.c
AddModule mod_log_config.c
AddModule mod_log_agent.c
AddModule mod_log_referer.c
#AddModule mod_mime_magic.c
AddModule mod_mime.c
AddModule mod_negotiation.c
AddModule mod_status.c
AddModule mod_info.c
AddModule mod_include.c
AddModule mod_autoindex.c
AddModule mod_dir.c
AddModule mod_cgi.c
AddModule mod_asis.c
AddModule mod_imap.c
AddModule mod_actions.c
#AddModule mod_speling.c
AddModule mod_userdir.c
AddModule mod_alias.c
AddModule mod_rewrite.c
AddModule mod_access.c
AddModule mod_auth.c
AddModule mod_auth_anon.c
#AddModule mod_auth_db.c
#AddModule mod_auth_any.c
#AddModule mod_auth_dbm.c
#AddModule auth_ldap.c
#AddModule mod_auth_mysql.c
#AddModule mod_auth_pgsql.c
#AddModule mod_digest.c
#AddModule mod_proxy.c
#AddModule mod_cern_meta.c
AddModule mod_expires.c
AddModule mod_headers.c
#AddModule mod_usertrack.c
#AddModule mod_example.c
#AddModule mod_unique_id.c
AddModule mod_so.c
AddModule mod_setenvif.c
AddModule mod_frontpage.c
<IfDefine HAVE_PERL>
AddModule mod_perl.c
</IfDefine>
<IfDefine HAVE_PHP>
AddModule mod_php.c
</IfDefine>
<IfDefine HAVE_PHP3>
AddModule mod_php3.c
</IfDefine>
###<IfDefine HAVE_PHP4>
AddModule mod_php4.c
###</IfDefine>
<IfDefine HAVE_DAV>
AddModule mod_dav.c
</IfDefine>
<IfDefine HAVE_ROAMING>
AddModule mod_roaming.c
</IfDefine>
###<IfDefine HAVE_SSL>
AddModule mod_ssl.c
###</IfDefine>
<IfDefine HAVE_PUT>
AddModule mod_put.c
</IfDefine>
<IfDefine HAVE_PYTHON>
AddModule mod_python.c
</IfDefine>
Port 80
Listen 80
Listen 443
User apache
Group apache
ServerAdmin root@localhost
DocumentRoot "/var/www/html"
<Directory />
Options FollowSymLinks
AllowOverride All
</Directory>
<Directory "/var/www/html">
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
Allow from all
</Directory>
AddHandler cgi-script .cgi .pl
<IfModule mod_userdir.c>
#UserDir public_html
UserDir disabled
</IfModule>
<Directory /home/*>
AllowOverride All
Options -MultiViews -Indexes FollowSymlinks IncludesNoExec +Includes
<Limit GET POST OPTIONS PROPFIND>
Order allow,deny
Allow from all
</Limit>
<LimitExcept GET POST OPTIONS PROPFIND>
Order deny,allow
Deny from all
</LimitExcept>
</Directory>
<IfModule mod_dir.c>
DirectoryIndex index.html index.htm index.shtml index.php index.php4 index.php3 index.phtml index.cgi
</IfModule>
AccessFileName .htaccess
<Files ~ "^\.ht">
Order allow,deny
Deny from all
Satisfy All
</Files>
UseCanonicalName Off
<IfModule mod_mime.c>
TypesConfig /etc/mime.types
</IfModule>
DefaultType text/plain
<IfModule mod_mime_magic.c>
# MIMEMagicFile /usr/share/magic.mime
MIMEMagicFile conf/magic
</IfModule>
HostnameLookups Off
ErrorLog logs/error_log
LogLevel warn
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent
LogFormat "%b" bytes
CustomLog logs/access_log combined
ServerSignature On
######
# Uncomment this next bit if you need to
# see the apache status of all incoming
# connections
#
#ExtendedStatus On
#<Location /httpd-status>
#SetHandler server-status
#</Location>
#
#######
<IfModule mod_alias.c>
Alias /icons/ "/var/www/icons/"
<Directory "/var/www/icons">
Options Indexes MultiViews
AllowOverride None
Order allow,deny
Allow from all
</Directory>
Alias /server-images/ "/var/www/html/images/"
<Directory "/var/www/html/images">
Options Indexes MultiViews
AllowOverride None
Order allow,deny
Allow from all
</Directory>
#ScriptAlias /cgi-sbin/ "/var/www/cgi-bin/"
<Directory "/var/www/cgi-sbin">
AllowOverride None
Options None
Order allow,deny
Allow from all
</Directory>
</IfModule>
<IfModule mod_autoindex.c>
IndexOptions FancyIndexing NameWidth=*
AddIconByEncoding (CMP,/icons/compressed.gif) x-compress x-gzip
AddIconByType (TXT,/icons/text.gif) text/*
AddIconByType (IMG,/icons/image2.gif) image/*
AddIconByType (SND,/icons/sound2.gif) audio/*
AddIconByType (VID,/icons/movie.gif) video/*
AddIcon /icons/binary.gif .bin .exe
AddIcon /icons/binhex.gif .hqx
AddIcon /icons/tar.gif .tar
AddIcon /icons/world2.gif .wrl .wrl.gz .vrml .vrm .iv
AddIcon /icons/compressed.gif .Z .z .tgz .gz .zip
AddIcon /icons/a.gif .ps .ai .eps
AddIcon /icons/layout.gif .html .shtml .htm .pdf
AddIcon /icons/text.gif .txt
AddIcon /icons/c.gif .c
AddIcon /icons/p.gif .pl .py
AddIcon /icons/f.gif .for
AddIcon /icons/dvi.gif .dvi
AddIcon /icons/uuencoded.gif .uu
AddIcon /icons/script.gif .conf .sh .shar .csh .ksh .tcl
AddIcon /icons/tex.gif .tex
AddIcon /icons/bomb.gif core
AddIcon /icons/back.gif ..
AddIcon /icons/hand.right.gif README
AddIcon /icons/folder.gif ^^DIRECTORY^^
AddIcon /icons/blank.gif ^^BLANKICON^^
DefaultIcon /icons/unknown.gif
#AddDescription "GZIP compressed document" .gz
#AddDescription "tar archive" .tar
#AddDescription "GZIP compressed tar archive" .tgz
ReadmeName README
HeaderName HEADER
IndexIgnore .??* *~ *# HEADER* README* RCS CVS *,v *,t
</IfModule>
<IfModule mod_mime.c>
AddEncoding x-compress Z
AddEncoding x-gzip gz tgz
AddLanguage da .dk
AddLanguage nl .nl
AddLanguage en .en
AddLanguage et .ee
AddLanguage fr .fr
AddLanguage de .de
AddLanguage el .el
AddLanguage he .he
AddCharset ISO-8859-8 .iso8859-8
AddLanguage it .it
AddLanguage ja .ja
AddCharset ISO-2022-JP .jis
AddLanguage kr .kr
AddCharset ISO-2022-KR .iso-kr
AddLanguage nn .nn
AddLanguage no .no
AddLanguage pl .po
AddCharset ISO-8859-2 .iso-pl
AddLanguage pt .pt
AddLanguage pt-br .pt-br
AddLanguage ltz .lu
AddLanguage ca .ca
AddLanguage es .es
AddLanguage sv .se
AddLanguage cz .cz
AddLanguage ru .ru
AddLanguage zh-tw .tw
AddLanguage tw .tw
AddCharset Big5 .Big5 .big5
AddCharset WINDOWS-1251 .cp-1251
AddCharset CP866 .cp866
AddCharset ISO-8859-5 .iso-ru
AddCharset KOI8-R .koi8-r
AddCharset UCS-2 .ucs2
AddCharset UCS-4 .ucs4
AddCharset UTF-8 .utf8
<IfModule mod_negotiation.c>
LanguagePriority en da nl et fr de el it ja kr no pl pt pt-br ru ltz ca es sv tw
</IfModule>
AddType application/x-tar .tgz
<IfModule mod_php4.c>
AddHandler application/x-httpd-php .inc .php .php4 .php3 .phtml
AddHandler application/x-httpd-php-source .phps
</IfModule>
<IfModule mod_php3.c>
AddHandler application/x-httpd-php3 .php3
AddHandler application/x-httpd-php3-source .phps
</IfModule>
<IfModule mod_php.c>
AddHandler application/x-httpd-php .phtml
</IfModule>
AddHandler cgi-script .cgi .pl
AddType text/html .shtml
AddHandler server-parsed .shtml
#AddHandler send-as-is asis
AddHandler imap-file map
#AddHandler type-map var
</IfModule>
<IfModule mod_setenvif.c>
BrowserMatch "Mozilla/2" nokeepalive
BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0
BrowserMatch "RealPlayer 4\.0" force-response-1.0
BrowserMatch "Java/1\.0" force-response-1.0
BrowserMatch "JDK/1\.0" force-response-1.0
</IfModule>
Alias /doc/ /usr/share/doc/
<Directory /usr/share/doc>
order deny,allow
deny from all
allow from localhost .localdomain
Options Indexes FollowSymLinks
</Directory>
###<IfDefine HAVE_SSL>
AddType application/x-x509-ca-cert .crt
AddType application/x-pkcs7-crl .crl
###</IfDefine>
<IfModule mod_ssl.c>
# Pass Phrase Dialog:
# Configure the pass phrase gathering process.
# The filtering dialog program (`builtin' is a internal
# terminal dialog) has to provide the pass phrase on stdout.
SSLPassPhraseDialog builtin
# Inter-Process Session Cache:
# Configure the SSL Session Cache: First the mechanism
# to use and second the expiring timeout (in seconds).
SSLSessionCache none
#SSLSessionCache shmht:logs/ssl_scache(512000)
#SSLSessionCache shmcb:logs/ssl_scache(512000)
#SSLSessionCache dbm:logs/ssl_scache
SSLSessionCacheTimeout 300
# Semaphore:
# Configure the path to the mutual exclusion semaphore the
# SSL engine uses internally for inter-process synchronization.
SSLMutex file:logs/ssl_mutex
# Pseudo Random Number Generator (PRNG):
# Configure one or more sources to seed the PRNG of the
# SSL library. The seed data should be of good random quality.
# WARNING! On some platforms /dev/random blocks if not enough entropy
# is available. This means you then cannot use the /dev/random device
# because it would lead to very long connection times (as long as
# it requires to make more entropy available). But usually those
# platforms additionally provide a /dev/urandom device which doesn't
# block. So, if available, use this one instead. Read the mod_ssl User
# Manual for more details.
SSLRandomSeed startup builtin
SSLRandomSeed connect builtin
#SSLRandomSeed startup file:/dev/random 512
#SSLRandomSeed startup file:/dev/urandom 512
#SSLRandomSeed connect file:/dev/random 512
#SSLRandomSeed connect file:/dev/urandom 512
# Logging:
# The home of the dedicated SSL protocol logfile. Errors are
# additionally duplicated in the general error log file. Put
# this somewhere where it cannot be used for symlink attacks on
# a real server (i.e. somewhere where only root can write).
# Log levels are (ascending order: higher ones include lower ones):
# none, error, warn, info, trace, debug.
SSLLog logs/ssl_engine_log
SSLLogLevel error
</IfModule>
###<IfDefine HAVE_SSL>
##
## SSL Virtual Host Context
##
<VirtualHost _default_:443>
ErrorLog logs/error_log
TransferLog logs/access_log
# SSL Engine Switch:
# Enable/Disable SSL for this virtual host.
SSLEngine on
# SSL Cipher Suite:
# List the ciphers that the client is permitted to negotiate.
# See the mod_ssl documentation for a complete list.
#SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP:+eNULL
# Server Certificate:
# Point SSLCertificateFile at a PEM encoded certificate. If
# the certificate is encrypted, then you will be prompted for a
# pass phrase. Note that a kill -HUP will prompt again. A test
# certificate can be generated with `make certificate' under
# built time. Keep in mind that if you've both a RSA and a DSA
# certificate you can configure both in parallel (to also allow
# the use of DSA ciphers, etc.)
SSLCertificateFile /etc/httpd/conf/ssl.crt/server.crt
#SSLCertificateFile /etc/httpd/conf/ssl.crt/server-dsa.crt
# Server Private Key:
# If the key is not combined with the certificate, use this
# directive to point at the key file. Keep in mind that if
# you've both a RSA and a DSA private key you can configure
# both in parallel (to also allow the use of DSA ciphers, etc.)
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/server.key
#SSLCertificateKeyFile /etc/httpd/conf/ssl.key/server-dsa.key
# Server Certificate Chain:
# Point SSLCertificateChainFile at a file containing the
# concatenation of PEM encoded CA certificates which form the
# certificate chain for the server certificate. Alternatively
# the referenced file can be the same as SSLCertificateFile
# when the CA certificates are directly appended to the server
# certificate for convinience.
#SSLCertificateChainFile /etc/httpd/conf/ssl.crt/ca.crt
# Certificate Authority (CA):
# Set the CA certificate verification path where to find CA
# certificates for client authentication or alternatively one
# huge file containing all of them (file must be PEM encoded)
# Note: Inside SSLCACertificatePath you need hash symlinks
# to point to the certificate files. Use the provided
# Makefile to update the hash symlinks after changes.
#SSLCACertificatePath /etc/httpd/conf/ssl.crt
#SSLCACertificateFile /etc/httpd/conf/ssl.crt/ca-bundle.crt
# Certificate Revocation Lists (CRL):
# Set the CA revocation path where to find CA CRLs for client
# authentication or alternatively one huge file containing all
# of them (file must be PEM encoded)
# Note: Inside SSLCARevocationPath you need hash symlinks
# to point to the certificate files. Use the provided
# Makefile to update the hash symlinks after changes.
#SSLCARevocationPath /etc/httpd/conf/ssl.crl
#SSLCARevocationFile /etc/httpd/conf/ssl.crl/ca-bundle.crl
# Client Authentication (Type):
# Client certificate verification type and depth. Types are
# none, optional, require and optional_no_ca. Depth is a
# number which specifies how deeply to verify the certificate
# issuer chain before deciding the certificate is not valid.
#SSLVerifyClient require
#SSLVerifyDepth 10
# Access Control:
# With SSLRequire you can do per-directory access control based
# on arbitrary complex boolean expressions containing server
# variable checks and other lookup directives. The syntax is a
# mixture between C and Perl. See the mod_ssl documentation
# for more details.
#<Location />
#SSLRequire ( %{SSL_CIPHER} !~ m/^(EXP|NULL)/ \
# and %{SSL_CLIENT_S_DN_O} eq "Snake Oil, Ltd." \
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"} \
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5 \
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 ) \
# or %{REMOTE_ADDR} =~ m/^192\.76\.162\.[0-9]+$/
#</Location>
# SSL Engine Options:
# Set various options for the SSL engine.
# o FakeBasicAuth:
# Translate the client X.509 into a Basic Authorisation. This means that
# the standard Auth/DBMAuth methods can be used for access control. The
# user name is the `one line' version of the client's X.509 certificate.
# Note that no password is obtained from the user. Every entry in the user
# file needs this password: `xxj31ZMTZzkVA'.
# o ExportCertData:
# This exports two additional environment variables: SSL_CLIENT_CERT and
# SSL_SERVER_CERT. These contain the PEM-encoded certificates of the
# server (always existing) and the client (only existing when client
# authentication is used). This can be used to import the certificates
# into CGI scripts.
# o StdEnvVars:
# This exports the standard SSL/TLS related `SSL_*' environment variables.
# Per default this exportation is switched off for performance reasons,
# because the extraction step is an expensive operation and is usually
# useless for serving static content. So one usually enables the
# exportation for CGI and SSI requests only.
# o CompatEnvVars:
# This exports obsolete environment variables for backward compatibility
# to Apache-SSL 1.x, mod_ssl 2.0.x, Sioux 1.0 and Stronghold 2.x. Use this
# to provide compatibility to existing CGI scripts.
# o StrictRequire:
# This denies access when "SSLRequireSSL" or "SSLRequire" applied even
# under a "Satisfy any" situation, i.e. when it applies access is denied
# and no other module can change it.
# o OptRenegotiate:
# This enables optimized SSL connection renegotiation handling when SSL
# directives are used in per-directory context.
#SSLOptions +FakeBasicAuth +ExportCertData +CompatEnvVars +StrictRequire
<Files ~ "\.(cgi|shtml|phtml|php3?)$">
SSLOptions +StdEnvVars
</Files>
<Directory "/var/www/cgi-bin">
SSLOptions +StdEnvVars
</Directory>
SetEnvIf User-Agent ".*MSIE.*" \
nokeepalive ssl-unclean-shutdown \
downgrade-1.0 force-response-1.0
# Per-Server Logging:
# The home of a custom SSL log file. Use this when you want a
# compact non-error SSL logfile on a virtual host basis.
CustomLog logs/ssl_request_log \
"%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"
