MySQL connection timeout on connection spike

Question

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,

Answer 1

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 !!!

Answer 2

> max_connections = 61440 -- much too high; 2000 is a compromise
> table_open_cache = 32768 -- too high; use a few thousand
> max_tmp_tables=20480 -- too high (no recommendation)
> open-files-limit = 655360 -- too high; there may be a 64K limit
> innodb_open_files = 655360 -- too high; there may be a 64K limit