SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Question

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.

Answer 1

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:

1. Turn on the mysql slow query log and set the 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.
2. Run mysqltuner.pl and follow its recommendations. ( wget mysqltuner.pl )
3. Install a server performance monitoring tool such as Cacti, Munin or Zabbix (there are other options) and add the "Better Cacti Templates" or "Appaloosa Templates".

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 and query_cache_limit and raise either key_buffer_size or innodb_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.