MySQL connection timeout on connection spike

MySQLmysql-5.6

I have a little bit difficult problem, whenever a spike of connection occurs our MySQL server, connecting from all sources to the MySQL is practically impossible, the max_connections setting is not reached, load is ok 2-3 on a total 48 core.

My suspicion is the back_log and OS back_log, and my question is should i decrease or increase the back_log / os back_log ? or what am i doing wrong

Server info:

OS Debian GNU Linux 7.7

MySQL 5.6.16-enterprise-commercial-advanced-log

CPU 2 x Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz

256 GB DDR3 @ 1866Mhz ECC

RAID 10 (6 x 1.2 TB SAS 10k)

MySQL settings

back_log = 2000 max_connections = 61440 max_connect_errors = 256
key_buffer_size = 2048M table_open_cache = 32768 sort_buffer_size = 8M
read_buffer_size = 8M read_rnd_buffer_size = 32M ft_min_word_len = 4
thread_stack = 256K max_allowed_packet = 1024M bulk_insert_buffer_size
= 64M thread_cache_size = 512 query_cache_size = 128M thread_concurrency = 48 innodb_read_io_threads=64
innodb_write_io_threads=64 innodb_checksum_algorithm=INNODB
binlog_checksum=NONE tmp_table_size = 128M max_tmp_tables=20480
max_heap_table_size = 64M join_buffer_size = 128M
innodb_buffer_pool_size = 160G innodb_thread_concurrency = 0
open-files-limit = 655360 innodb_open_files = 655360
plugin-load=thread_pool.so thread_pool_size = 16

OS settings:

net.core.netdev_max_backlog = 20480

net.ipv4.tcp_max_syn_backlog = 20480

P.S Please do not ask or leave links regarding to tutorials or mysql documentation, for i have done many tests and this is a question suited for Rolando or persons with strong mysql background.

Thanks,

Best Answer

What caught my eye immediately was the following

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
join_buffer_size = 128M

The sum of these buffer sizes is 176M.

This would be the maximum amount of RAM used per connection.

If you multiply that by your max_connections (61440) you get

11331331686400 Bytes
11065753600 KB
10806400 MB
10553.125 GB
10.3057861328125 TB

This means that you would need to have 10.5 TB of RAM to accommodate 61440 open connections.

I have mentioned this formula before

May 09, 2013 : Should I increase max_connections in AWS RDS t1-micro for MySQL?
Mar 28, 2014 : MySQL not releasing memory

Here is a query you can run to see these metrics

SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) BBB) AA) A,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) B;

You can run this one to test out changing max_connections (Example: What if you wanted to lower max_connections to 76):

SET @conn = 76;
SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT @conn maxconn) BBB) AA) A,
(SELECT @conn maxconn) B;

YOUR CURRENT SETTINGS

Let's compare how much RAM can potentially be consumed given your current settings

Look at your InnoDB Buffer Pool (innodb_buffer_pool_size). It's 160G.
Look at your MyISAM Key Cache (key_buffer_size). It's 2048M or 2G.
256G - 160G - 2G = 94G available to the OS
94G / 176M (per connection) = 546 Connections
You will never reach 546 connections because mysqld is competing with the OS for RAM

SUGGESTIONS

You need to lower your max_connections so that it does not eat the 94G of leftover RAM.
You may need to consider lowering join_buffer_size if your joins are fast.
You could probably lower read_buffer_size and read_rnd_buffer_size if you are not reading from MyISAM tables.

GIVE IT A TRY !!!

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.

Thesql connection timeout on small traffic server

It is bad practice to sit there connected for arbitrarily long. It is better to have wait_timeout be something more like 30 (seconds) and write code to deal with disconnects. Reconnecting to MySQL is quite quick, so it is not a performance issue. If the app is idle for (say) 30 seconds, it should expect to get an error and have to reconnect.

Keep in mind that network glitches, etc, can cause disconnects. Hence, you need to code for disconnects anyway.

Edit

Do not use AUTORECONNECT with InnoDB, it can cause nasty glitches. Instead, catch 'not connected' error after each query and restart the transaction. Another approach is to execute a 'ping' whenever you might be coming back from a long idle period.