Not the answer you need?
Register and ask your own question!

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

botnoebotnoe EntrantLegacy User Role Beginner
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.

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.