MySQL connection timeout on connection spike 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,
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 !!!