1

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,

Improve this question

2 Answers 2

Reset to default
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

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

GIVE IT A TRY !!!

Improve this answer
1 
> 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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.