hi all ,
i need your help, just look at my configuration and what you may advice
2 web servers
RHEL v5.2 x64 - apache - PHP eaccelerator
HP blade server - Intel(R) Xeon(R) X5450 3.00GHz - 8GB RAM
1 DB server
RHEL v5.2 x64
HP blade server - Intel(R) Xeon(R) X5450 3.00GHz - 16GB RAM
-------------------------------------------------
Version
Server version: Apache/2.2.3
Server built: Jan 11 2008 08:20:08
Server's Module Magic Number: 20051115:3
Server loaded: APR 1.2.7, APR-Util 1.2.7
Compiled using: APR 1.2.7, APR-Util 1.2.7
Architecture: 64-bit
Server MPM: Prefork
threaded: no
forked: yes (variable process count)
Server compiled with....
-D APACHE_MPM_DIR="server/mpm/prefork"
-D APR_HAS_SENDFILE
-D APR_HAS_MMAP
-D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
-D APR_USE_SYSVSEM_SERIALIZE
-D APR_USE_PTHREAD_SERIALIZE
-D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
-D APR_HAS_OTHER_CHILD
-D AP_HAVE_RELIABLE_PIPED_LOGS
-D DYNAMIC_MODULE_LIMIT=128
-D HTTPD_ROOT="/etc/httpd"
-D SUEXEC_BIN="/usr/sbin/suexec"
-D DEFAULT_PIDLOG="logs/httpd.pid"
-D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
-D DEFAULT_LOCKFILE="logs/accept.lock"
-D DEFAULT_ERRORLOG="logs/error_log"
-D AP_TYPES_CONFIG_FILE="conf/mime.types"
-D SERVER_CONFIG_FILE="conf/httpd.conf"
-------------------------------------------------
httpd.conf
-------------------------------------------------
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 500
MaxClients 500
MaxRequestsPerChild 25
</IfModule>
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 120
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive Off
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 60
-------------------------------------------------
my.cnf
-------------------------------------------------
[mysqld]
set-variable=max_connections=1500
set-variable=max_connect_errors=1500
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
bind-address=172.18.2.105
max_allowed_packet=32M
skip-bdb
skip-locking
skip-innodb
#skip-networking
query_cache_type=1
query_cache_limit=36M
query_cache_size=36M
query_cache_min_res_unit=2K
join_buffer_size=1M
#interactive_timeout=100
interactive_timeout=10
#wait_timeout=57600
wait_timeout=18000
connect_timeout=10
table_cache=2048
tmp_table_size=256M
max_heap_table_size=256M
thread_cache_size=8
#key_burrer_size=16M
key_buffer_size=8M
read_buffer_size=2M
thread_concurrency=16
#read_rnd_buffer_size=16M
#bulk_insert_buffer_size=64M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
# log slow queries is a must. Many queries that take more than 2 seconds.
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
open-files-limit = 8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#pid-file=/var/lib/mysql/squmoodle.squ.edu.om.pid
[client]
socket=/var/lib/mysql/mysql.sock
-------------------------------------------------
other settings:
F5 bigip load balancer
SSL is used for Login page.
scheduled cron job to optimize moodle db daily.
i run mysql tuner and here is some its log.
-------------------------------------------------
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 5G (Tables: 909)
[!!] Total fragmented tables: 188
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 31m 50s (5M q [445.183 qps], 230K conn, TX: 31B, RX: 2B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 310.0M global + 7.2M per thread (1500 max threads)
[OK] Maximum possible memory usage: 10.9G (69% of installed RAM)
[OK] Slow queries: 0% (904/5M)
[OK] Highest usage of available connections: 84% (1263/1500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.7G
[OK] Key buffer hit rate: 99.5% (4B cached / 21M reads)
[OK] Query cache efficiency: 78.3% (3M cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (14 temp sorts / 142K sorts)
[!!] Joins performed without indexes: 12730
[!!] Temporary tables created on disk: 32% (102K on disk / 315K total)
[OK] Thread cache hit rate: 91% (19K created / 230K connections)
[OK] Table cache hit rate: 29% (2K open / 7K opened)
[OK] Open file limit used: 36% (2K/8K)
[!!] Table locks acquired immediately: 87%
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
join_buffer_size (> 1020.0K, or always use indexes with joins)
-------------------------------------------------
Issues:
it becomes slow when number of connections in LB reach 200. since 1 connection can create more than 1 thread in mysql.and when it reachs beyond 1500 connection in mysql it goes off.
its slow to open the ssl login page and sometimes it appears incomplete page.
i guess this specs can handle more than 1000 concurrent connections
What are the parameters in my.cnf that need to be modified so mysql can handle as large as possible connections with no slowness and errors?
for those who are dealing with enterprise setup like universities please post what you suggest.
Thanks,
Khalil
