我正在執行 openstack cloud，它使用 mysql 數據庫，它是MariaDB 10.1.30

我有時會看到來自多個客戶端的連接重置，試圖找出數據庫中發生了什麼，我看到以下連接相關狀態，但想確定這裡有什麼專家建議，我應該增加max_connectionmysql 嗎？

我在伺服器上有 64GB 記憶體，但也有其他組件在伺服器上執行，因此試圖避免過度使用記憶體，執行緒和連接數之間的關係是什麼？

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)

執行緒

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)

這是我的 my.cfg 文件。

[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/

更新 - 1

從 1600增加到max_connection3200 後修復我的連接中止問題看起來像

鑑於您的 max_connections 設置為 1600 且 max_used_connections 為 1601，您的 0.1% 連接相關問題可能超過了此限制。

第一個初始反應是增加 max_connections，但是潛在的問題是要達到這個數量的連接，您的查詢通常太慢。

啟用您的慢查詢日誌並開始詢問有關您的慢查詢的問題。

對您的 my.cnf 的建議

$$ mysqld $$部分

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

免責聲明：我是我的個人資料，網路個人資料中提到的網站的內容作者，可以提供額外的性能調整建議。

引用自：https://dba.stackexchange.com/questions/233986