MySQL performance tuning related connection

All we need is an easy explanation of the problem, so here it is.

I am running openstack cloud and it use mysql database which is MariaDB 10.1.30

I am sometime seeing connection reset from multiple clients, to trying to find out what is going on in database, I am seeing following connection related status but want to make sure what expert advice here, should i need to increase max_connection in mysql?

I have 64GB memory on server but also other components running on servers so trying to avoid overcommit memory, what is the relation between threads and connection count?

MariaDB [(none)]> SHOW STATUS LIKE '%onn%';
+-----------------------------------------------+----------+
| Variable_name                                 | Value    |
+-----------------------------------------------+----------+
| Aborted_connects                              | 5951     |
| Connection_errors_accept                      | 0        |
| Connection_errors_internal                    | 0        |
| Connection_errors_max_connections             | 406      |
| Connection_errors_peer_address                | 0        |
| Connection_errors_select                      | 0        |
| Connection_errors_tcpwrap                     | 0        |
| Connections                                   | 23430967 |
| Max_used_connections                          | 1601     |
| Performance_schema_session_connect_attrs_lost | 0        |
| Slave_connections                             | 0        |
| Slaves_connected                              | 0        |
| Ssl_client_connects                           | 0        |
| Ssl_connect_renegotiates                      | 0        |
| Ssl_finished_connects                         | 0        |
| Threads_connected                             | 1284     |
| wsrep_connected                               | ON       |
+-----------------------------------------------+----------+
17 rows in set (0.00 sec)

Threads

MariaDB [(none)]> SHOW STATUS LIKE '%Threads%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Delayed_insert_threads  | 0     |
| Slow_launch_threads     | 0     |
| Threadpool_idle_threads | 0     |
| Threadpool_threads      | 0     |
| Threads_cached          | 47    |
| Threads_connected       | 1284  |
| Threads_created         | 86815 |
| Threads_running         | 1     |
+-------------------------+-------+
8 rows in set (0.01 sec)

Here is my my.cfg file.

[client]
port = 3306
socket = "/var/lib/mysql/mysql.sock"


[mysqld_safe]
socket = "/var/lib/mysql/mysql.sock"
nice = 0
log_error = /var/log/mysql_logs/galera_server_error.log


[mysql]
default-character-set = utf8


[mysqld]
user = mysql
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
datadir = /var/lib/mysql
bind-address = ::
server-id = 200

# LOGGING #
log-queries-not-using-indexes = 0
slow-query-log = 0
slow-query-log-file = /var/log/mysql_logs/mysql-slow.log
log_error = /var/log/mysql_logs/galera_server_error.log
log-bin = /var/lib/mysql/mariadb-bin
log-bin-index = /var/lib/mysql/mariadb-bin.index
expire-logs-days = 7
log_slave_updates = 1
log_bin_trust_function_creators = 1

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000

# NOTE: If galera_max_connections is not configured by user, the number of max
# connections is defined by ( host_vcpus * 100 ) with a capping value of 1600.
# This value is the lowest integer based on the ansible facts gathered from
# every galera node.
# Computing the connections value using the lowest denominator maintains
# cluster integrity by not attempting to over commit to a less capable machine.
# These are the computed max_connections based on the cluster data
# [1600, 1600, 1600]
max_connections = 1600

wait_timeout = 3600

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0M
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-file-size = 1024M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 4096M

# Depending on number of cores and disk sub
innodb-read-io-threads = 4
innodb-write-io-threads = 4
innodb-doublewrite = 1
innodb-log-buffer-size = 128M
innodb-buffer-pool-instances = 8
innodb-log-files-in-group = 2
innodb-thread-concurrency = 64

# avoid statistics update when doing e.g show tables
innodb_stats_on_metadata = 0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M


!includedir /etc/mysql/conf.d/

UPDATE – 1

After increase max_connection from 1600 to 3200 fix my connection abort issue look like

enter image description here

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Given your setting of max_connections is 1600 and max_used_connections is 1601, your 0.1% connection related issues is probably exceeding this limit.

The first initial reaction is to increase max_connections, however the underlying problem is that to get to this amount of connections, your queries are usually too slow.

Enable your slow query log and start asking questions about your slow queries.

Method 2

Suggestion for your my.cnf [mysqld] section

thread_cache_size=256  # from 50 to reduce threads_created
max_connect_errors=10  # from 1 million, no need to be liberal with Hackers/Crackers
innodb_thread_concurrency=0  # from 64 to allow system to auto calculate load

Disclaimer: I am the content author of web site mentioned in my profile, Network profile and can provide additional performance tuning suggestions.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply