pymysql.err.OperationalError: (1040, 'Too many connections') 超出連接數(shù)據(jù)庫最大連接數(shù)所致，修改最大連接數(shù)

一、連接遠程數(shù)據(jù)庫：

YoungdeMacBook-Pro:ids Young$ mysql -h 172.117.43.14 -u root -p
Enter password: 

二、查看 mysql 狀態(tài)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name  | Value  |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

2.2 查看服務(wù)器響應(yīng)的最大連接數(shù)

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value     |
+----------------------+-------+
| Max_used_connections | 5   |
+----------------------+-------+
1 row in set (0.00 sec)

同時查看2條數(shù)據(jù)：

   '''
  mysql>  show variables like '%max_connections%';
  +------------------------+-------+
  | Variable_name          | Value |
  +------------------------+-------+
  | max_connections        | 151   |
  | mysqlx_max_connections | 100   |
  +------------------------+-------+
  2 rows in set (0.00 sec)
  '''

修改最大連接數(shù)：

  set global max_connections=1000 

查看數(shù)據(jù)庫當前運行狀態(tài)：

  mysql> show status like 'Threads%';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Threads_cached    | 58    |
  | Threads_connected | 57    |   ###這個數(shù)值指的是打開的連接數(shù)
  | Threads_created   | 3676  |
  | Threads_running   | 4     |   ###這個數(shù)值指的是激活的連接數(shù)，這個數(shù)值一般遠低于connected數(shù)值
  +-------------------+-------+

Threads_connected 跟show processlist結(jié)果相同，表示當前連接數(shù)。準確的來說，Threads_running是代表當前并發(fā)數(shù)

對于 mysql 服務(wù)器最大連接數(shù)值的設(shè)置范圍比較理想的是：
服務(wù)器響應(yīng)的最大連接數(shù)值占服務(wù)器上限連接數(shù)值的比例值在 10% 以上，如果在 10% 以下，說明 mysql 服務(wù)器最大連接上限值設(shè)置過高。

Max_used_connections / max_connections * 100%

三、問題分析

3.1 max_connections

MySQL無論如何都會保留一個用于管理員（SUPER）登陸的連接，用于管理員連接數(shù)據(jù)庫進行維護操作，即使當前連接數(shù)已經(jīng)達到了max_connections。因此MySQL的實際最大可連接數(shù)為 max_connections+1；

這個參數(shù)實際起作用的最大值（實際最大可連接數(shù)）為16384，即該參數(shù)最大值不能超過16384，即使超過也以16384為準； 增加max_connections參數(shù)的值，不會占用太多系統(tǒng)資源。系統(tǒng)資源（CPU、內(nèi)存）的占用主要取決于查詢的密度、效率等； 該參數(shù)設(shè)置過小的最明顯特征是出現(xiàn)”Too many connections”錯誤；

3.2 mysql 最大連接數(shù) 151 問題

如果我設(shè)置連接小于151時，比如 150，那么實際連接數(shù)就是 150，也就是說，我的配置文件是沒有問題的。

查 MySQL 官方文檔，里面說了

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform.

Increasing open-files-limit may be necessary. Also see Section 2.5, “Installing MySQL on Linux”, for how to raise the operating system limit on how many handles can be used by MySQL.

大概意思是 MySQL 能夠支持的最大連接數(shù)量受限于操作系統(tǒng),必要時可以增大 open-files-limit。換言之，連接數(shù)與文件打開數(shù)有關(guān)

4 問題解決

更改 MySQL 在 Linux 的最大文件描述符限制，編輯/usr/lib/systemd/system/mariadb.service文件，在文件[Service]下添加:

LimitNOFILE=65535
LimitNPROC=65535

保存后，執(zhí)行下面命令，使配置生效

# systemctl daemon-reload
# systemctl restart  mariadb.service

問題解決

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 4096  |
+-----------------+-------+