SQLSTATE[HY000] [1040] Too many connections 解決方法

當網站遇到SQLSTATE[HY000] [1040] Too many connections 怎麼辦？

這個錯誤表示 MySQL 伺服器的目前連線數超過了配置的最大允許連線數（預設151），導致新連線被拒絕。通常發生在高並發網站（如WordPress、Laravel）中，原因包括應用程式未及時釋放連線、突發流量或配置不足。別擔心，它通常可快速修復，但需結合監控避免復發。

在MySQL 世界中遇到的最常見錯誤之一是臭名昭著的錯誤1040：

實際上，這意味著MySQL 實例已達到其允許的最大客戶端連線數限制。在連接關閉之前，伺服器將不會接受任何新連線。

我想討論一些防止這種情況的實用建議，或者如果你發現自己處於這種情況，如何恢復。

精確調整max_connections 參數

此設定定義了MySQL 實例可接受的最大連線數。設定最大連線數的「原因」取決於伺服器的可用資源和應用程式的使用模式。允許不受控制的連接可能會導致伺服器崩潰，這可能比阻止進一步的連接「更糟」。 Max_connections 是一個用來保護伺服器的值，而不是修復與劫持連線相關的問題。

與伺服器的每個連接都會消耗固定數量的開銷，例如管理連接的“線程”和用於管理連接的內存，以及可變資源（例如用於創建內存表的內存）。測量應用程式的資源模式並找到超過該連接數的危險點非常重要。

Percona 監控和管理(PMM) 可以幫助您找到這些值。查看記憶體使用模式、正在運行的線程，並將它們與連接數關聯起來。 PMM 還可以顯示連接活動的峰值，讓您了解距離閾值的距離。請根據伺服器的資源限制進行相應的調整。

下面看到的是一個具有非常穩定的連接模式的伺服器，並且最大使用量和最大連接數之間有很大空間。

MySQL：如何修復Too many connections 錯誤：（HY000/1040）：連線數太多

首先調整mysql設定文件，然後重新啟動MySQL

# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # this is read by the standalone daemon and emservered for serverssql daemon [mysqld] # # * Basic Settings # #user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr #gdatadir = /var/lib/my DNS #mpin DNS 53smp considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolve # Instead of skip-networking the default is now to listen on left only n 。 1024M open_files_limit = 0 #key_buffer_size = 128M #max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 # This replaces # the first time they are touched #myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 myisam_recover_options = BACKUP max_connections = 64 myisam_recover_options = BACKUP max_connections = 64 myisam_recover_options = BACKUP max_connections = 199019000 * Logging and Replication # # Note: The configured log file or its directory need to be created # and be writable by the mysql user, eg: # $ sudo mkdir -m 2750 5/sql /var/log/mysql # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # Recommend only changing this at runtime for short testing pergeneds ifif /var/log/mysql/mysql.log #general_log = 1 # When running under systemd, error logging goes via stdout/stderr to journald # and when running legacy init error logging goror 1 /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf # Enable this if you want to have error logging into a separate file #log_error = /var/log/mysql/error.able 1 月 6 月 3 月 5 月 30 月 mysql15 月 15% long duration #log_slow_query_file = /var/log/mysql/mariadb-slow.log #log_slow_query_time = 10 #log_slow_verbosity = query_plan,explain #log-queries-not15Tlog-quees-not1us_plans_ron_wvh 1000 long_query_time =5 # 建議1-5 秒，根據負載測試slow_query_log = 1 # 啟用慢查詢日誌（如果未開式） slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbossql/mariadb-slow.log log_slow_verbossql/mariadb-slow.log log_slow_verbossql/mariadb-slow。記錄執行計劃log_queries_not_using_indexes = 1 # 記錄未使用索引查詢log_slow_admin_statements = 1 # 記錄管理語句# The following can be used as easy to replay backup logs or for repliting be used as easy to replay backup logs or for repliting: 15 月, youf 15 月 3up README.Debian about other # settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_bin.log expire_logs_days = 10 #max_binlog_size = 15151515155 STP # For documentation, please read # https://mariadb.com/kb/en/securing-connections-for-client-and-server/ #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql-cert.pem. /etc/mysql/server-key.pem #require-secure-transport = on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full default is Latin1, but in Debian we rather default to the full 1. client.cnf character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in / options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innfin_bize; innodb_buffer_pool_size = 48G innodb_buffer_pool_instances = 16 innodb_log_file_size = 12G innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_trx_commit = 2 innodb_flush_甲基innodb_write_io_threads = 8 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you read by MariaDB servers, not by MySQL. # If you read the same for Mariafm. options here [mariadb] # This group is only read by MariaDB-10.11 servers. # If you use the same .cnf file for MariaDB of different serverions, # use this dbst options DB of different versions, # use this