How to configure MySQL to handle 3K to 6K incoming connections?

Our DB is slowing down due to hundreds to thousands of concurrent connections. We are running php-fpm server with max of 800 request for 12 nodes. Thats equivalent to maximum of 9600 conccurent request per seconds. The nodes are reaching only 60 to 75% concurrent requests.

Here’s the DB settings:

Here’s the DB Server config

Current top:
top - 12:24:09 up 1 day, 9 min, 3 users, load average: 11.79, 13.25, 14.82
Tasks: 533 total, 1 running, 532 sleeping, 0 stopped, 0 zombie
%Cpu(s): 27.1 us, 9.7 sy, 0.0 ni, 61.9 id, 0.8 wa, 0.0 hi, 0.4 si, 0.0 st
KiB Mem : 13597926+total, 14679136 free, 11821054+used, 3089576 buff/cache
KiB Swap: 8388604 total, 8388604 free, 0 used. 17335328 avail Mem

mysql> show variables like ‘%timeout%’;
±----------------------------±---------+
| Variable_name | Value |
±----------------------------±---------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 500 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 500 |
±----------------------------±---------+

parts of my.cnf

skip-name-resolve

CACHES AND LIMITS

tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_type = 0 # 1 # cache all select except with SELECT SQL_NO_CACHE
query_cache_size = 0 # 20M # cache query result to memory
read_buffer_size = 16M
read_rnd_buffer_size = 8M
sort_buffer_size = 32M # 16M

binlog_cache_size = 8M

tmp_table_size = 64M

max_connections = 11800
thread_cache_size = 250
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 30048 # affects OS file descriptors = conccurent connection * table_open_cache
bulk_insert_buffer_size = 64M

INNODB

autocommit = 0
innodb_flush_method = O_DIRECT # check innodb I/O for adjustment.
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_log_buffer_size = 8M # per session, size of transaction before commit
innodb_buffer_pool_size = 120G

innodb_buffer_pool_instances = 20
innodb_change_buffering = ALL
innodb_support_xa = 0

innodb_thread_concurrency

0 if using innodb+XtraDB, but still needs monitoring

used in pair with innodb_commit_concurrency and innodb_concurrency_tickets

innodb_thread_concurrency = 0 # 0 if using innodb+XtraDB, but still needs monitoring
innodb_data_file_path = ibdata1:2G:autoextend

LOGGING

general_log = 0

log_queries_not_using_indexes = 1
slow_query_log = 0
long_query_time = 3

Attached is the processlist with 3k of login state.

Hi botnoe;

Have you ran a slow query log to analyze your slow queries and optimize them? The number of connections means little, as it is all up to the work the connections are doing. Your setup looks fine generally speaking. More than likely your issue is with the queries, not the config.