Server details:
RAM: 16GB
HDD: 1000GB
OS: Linux 2.6.32-220.7.1.el6.x86_64
Processor: 6 Core
Please see the link below for my # top preview:
I can often see the error mentioned in title in my plesk panel and my /etc/my.cnf configuration are as below:
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections=20000
max_user_connections=20000
key_buffer_size=512M
join_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=512M
sort_buffer_size=8M
wait_timeout=300
interactive_timeout=300
connect_timeout=300
tmp_table_size=8M
thread_concurrency=12
concurrent_insert=2
query_cache_limit=64M
query_cache_size=128M
query_cache_type=2
transaction_alloc_block_size=8192
max_allowed_packet=512M
[mysqldump]
quick
max_allowed_packet=512M
[myisamchk]
key_buffer_size=128M
sort_buffer_size=128M
read_buffer_size=32M
write_buffer_size=32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
As my server httpd conf is set to /etc/httpd/conf.d/swtune.conf
and the configuration is as below: at prefork.c:
<IfModule prefork.c>
StartServers 8
MinSpareServers 10
MaxSpareServers 20
ServerLimit 1536
MaxClients 1536
MaxRequestsPerChild 4000
</IfModule>
If I run grep -i maxclient /var/log/httpd/error_log
then I can see everyday this error:
[root@u16170254 ~]# grep -i maxclient /var/log/httpd/error_log
[Sun Apr 15 07:26:03 2012] [error] server reached MaxClients setting, consider raising the MaxClients setting
[Mon Apr 16 06:09:22 2012] [error] server reached MaxClients setting, consider raising the MaxClients setting
I tried to explain everything that I changed to keep my server okay, but maximum time my server is down. Please help me which parameter can I change to keep my server okay and my sites can load fast. It is taking too much time to load my sites.
Best Answer
Wow.
With a
MaxClients
setting of 1536 and each Apache child using around 69MB of RAM, you need just over 100GB of RAM to avoid swapping out "hot" parts of Apache's memory. May I suggest you lower that value until it fits inside your available RAM? Swapping like that will make your server horrendously slow.Since you are running MySQL on the same server, I would go for about half of your available RAM for each service, so
MaxClients 115
would be appropriate for you. Maybe a little lower if you want to leave a little breathing room around MySQL.Now, on to MySQL, which is likely the actual cause of your server going slow. MySQL is using a lot of CPU and very little RAM. Three things to do:
long_query_time
to something high like 10 or 20 seconds. Slowly lower that setting until you start catching slow queries. Once you know which queries are causing the problem, tune them by adding appropriate indexes or modifying the query. Tuning queries is a major topic and there are several good books on it.wget mysqltuner.pl
)Going with the "half for each service" idea above,
mysqltuner.pl
will tell you the maximum amount of RAM that MySQL can use but you are unlikely to ever see this as it relies on per-connection RAM allocation and you will never see all 20,000 connections you allow. Instead, watching what MySQL actually uses and making it use about 2/3rds - 3/4ths of the remaining RAM is a sensible value.You should probably lower your
query_cache
andquery_cache_limit
and raise eitherkey_buffer_size
orinnodb_buffer_pool
depending on which engine you use.mysqltuner.pl
will have better recommendations than I do.The error message you used as the title for this question is related to the
wait_timeout
setting of MySQL. Some process has opened a connection to MySQL and then waited at least 300 seconds between commands. If this was a web request, raising this value is not the right solution. 300 seconds is far longer than people will wait around for a web request to complete. Fix whatever is causing the load average to be so high (probably the other slow queries and the swapping) and the requests will be faster.