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
- 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)
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
- 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.