☰

為什麼 MySQL 允許的連線數超過 max_connections 限制？

tag-icon • tag-icon mysql
為什麼 MySQL 允許的連線數超過 max_connections 限制？

這是我的連接統計數據。

mysql> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 274   |
+-----------------------------------+-------+


mysql> SHOW VARIABLES LIKE 'max_%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_allowed_packet         | 16777216             |
| max_binlog_cache_size      | 18446744073709547520 |
| max_binlog_size            | 104857600            |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors         | 100                  |
| max_connections            | 151                  |
| max_delayed_threads        | 20                   |
| max_digest_length          | 1024                 |
| max_error_count            | 64                   |
| max_execution_time         | 0                    |
| max_heap_table_size        | 16777216             |
| max_insert_delayed_threads | 20                   |
| max_join_size              | 18446744073709551615 |
| max_length_for_sort_data   | 1024                 |
| max_points_in_geometry     | 65536                |
| max_prepared_stmt_count    | 16382                |
| max_relay_log_size         | 0                    |
| max_seeks_for_key          | 18446744073709551615 |
| max_sort_length            | 1024                 |
| max_sp_recursion_depth     | 0                    |
| max_tmp_tables             | 32                   |
| max_user_connections       | 0                    |
| max_write_lock_count       | 18446744073709551615 |
+----------------------------+----------------------+

我想知道為什麼允許的連線數超過限制max_connections？ mysql 不應該拒絕更多的連線或慢慢修剪舊的連線嗎？從日誌看來後者是真的？

tail -f /var/log/mysql/error.log
2020-12-15T07:21:59.253969Z 60 [Note] Aborted connection 60 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.253979Z 57 [Note] Aborted connection 57 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.253994Z 56 [Note] Aborted connection 56 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254119Z 58 [Note] Aborted connection 58 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254136Z 54 [Note] Aborted connection 54 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254143Z 59 [Note] Aborted connection 59 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254154Z 61 [Note] Aborted connection 61 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254565Z 55 [Note] Aborted connection 55 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254581Z 53 [Note] Aborted connection 53 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)
2020-12-15T07:21:59.254593Z 62 [Note] Aborted connection 62 to db: 'my_db' user: 'my_user' host: 'my_ip' (Got an error reading communication packets)

這是否意味著如果不再需要數據，關閉舊連接並不重要，因為 mysql 無論如何都會慢慢修剪這些數據？

答案1

我認為您應該查看同時處理多少個連接的參數是最大已用連線數。

連接欄位應充當自上次重新啟動以來的連接計數器。

連接

嘗試連接 MySQL 伺服器的次數（成功或失敗）。

最大已用連線數

自伺服器啟動以來同時使用的最大連線數。

您可以參考這個SE答案和文件進一步深入。

答案2

這不是活動連線數。Connections是連線嘗試成功或失敗的次數。你需要看Threads_connected數字。您也可以使用netstatmysql 連接埠檢查連接並進行過濾。

這是stackoverlow 上的類似帖子。

相關內容