Mysql错误1040 Too many connections （Mysql库、django、peewee等场景及解决办法）_1040 too many connections 解决方法

一、应用场景

mysqld 服务器时出现错误 Too many connections, 原因是其他客户端正在使用所有可用的连接

二、解决思路

• 最终办法

1. SET GLOBAL max_connections = 800; 非持久，下次重启没啦
2. 持久化配置

   vi /etc/mysql/my.cnf
   [mysqld]
   max_connections = 500
   1
   2
   3

• 查看mysql的最大连接数

  show variables like '%max_connections%';
  1

• 查看mysql每台台主机的链接数

  select * from information_schema.processlist;
  
  select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
  1
  2
  3

  id       #ID标识，要kill一个语句的时候很有用
  use      #当前连接用户
  host     #显示这个连接从哪个ip的哪个端口上发出
  db       #数据库名
  command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）
  time     #连接持续时间，单位是秒
  state    #显示当前sql语句的状态
  info     #显示这个sql语句
  1
  2
  3
  4
  5
  6
  7
  8

  优先改链接数
  可根据主机修改链接mysql的链接数(利用链接池、单例模式、清除空闲链接等）

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
1

• 查看当前mysql线程服务信息

  show status like  'Threads%';
  1

  mysql> show status like 'Threads%';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Threads_cached    | 0     |
  | Threads_connected | 129   |
  | Threads_created   | 229   |
  | Threads_running   | 1     |
  +-------------------+-------+
  1
  2
  3
  4
  5
  6
  7
  8
  9

  --查看MySQL本次启动后的运行时间(单位：秒)
  show status like 'uptime';
  
  --查看select语句的执行数
  show  status like 'com_select';
  
  --查看insert语句的执行数
  show  status like 'com_insert';
  
  --查看update语句的执行数
  show  status like 'com_update';
  
  --查看delete语句的执行数
  show  status like 'com_delete';
  
  --查看试图连接到MySQL(不管是否连接成功)的连接数
  show status like 'connections';
  
  --查看线程缓存内的线程的数量。
  show status like 'threads_cached';
  
  --查看当前打开的连接的数量。
  show status like 'threads_connected';
  
  --查看当前打开的连接的数量。
  show status like 'threads_connected';
  
  --查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
  show status like 'threads_created';
  
  --查看激活的(非睡眠状态)线程数。
  show status like 'threads_running';
  
  --查看立即获得的表的锁的次数。
  show status like 'table_locks_immediate';
  
  --查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
  show status like 'table_locks_waited';
  
  --查看创建时间超过slow_launch_time秒的线程数。
  show status like 'slow_launch_threads';
  
  --查看查询时间超过long_query_time秒的查询的个数。
  show status like 'slow_queries';
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44

三、 django 的解决办法 设置链接过期时间、清除空闲连接

• django 设置过期时间

  DATABASES = {
      'default': {
          'ENGINE': 'django.db.backends.mysql',
          'NAME': 'book',
          'USER': 'root',
          'PASSWORD': '123456',
          'HOST': '127.0.0.1,  # 留空默认为localhost
          'PORT': 6379,  # 留空默认为3306端口
          'CONN_MAX_AGE': 180  
          # 空闲超时关闭数据库连接, 0 表示使用完马上关闭，None 表示不关闭
      }]
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  • 清除空闲链接
    关闭Django之前自动创建的与db的旧空闲的连接.

  	django.db.close_old_connections()
  1

    Django关闭先前的连接后会自动创建新的连接.
  1

四、 peewee 的解决办法

• 单例模式创建链接
• 设置链接过期时间
• 清除空闲连接

PooledDatabase(database[, max_connections=20[, stale_timeout=None[, timeout=None[, **kwargs]]]])
1

数据库( str ) – 数据库或数据库文件的名称。
max_connections ( int ) – 最大连接数。提供None无限。
stale_timeout ( int ) – 允许使用连接的秒数。
timeout ( int ) – 池已满时阻塞的秒数。默认情况下，当池已满时，peewee 不会阻塞，而只是抛出异常。要无限期地阻止，将此值设置为0。
kwargs – 传递给数据库类的任意关键字参数。
1
2
3
4
5

当连接超过其stale_timeout时，连接不会完全关闭。相反，只有在请求新连接时才会关闭陈旧连接。
如果打开的连接数超过max_connections，则会引发ValueError。 manual_close()关闭当前打开的连接而不将其返回到池中。

close_idle(timeout=3600)关闭和清理数据库连接池中的空闲连接
包括那些之前创建但后来返回到池中的连接
1
2

用于关闭和清理数据库连接池中的空闲或过期的连接
close_stale(timeout=5, stale_timeout=60)
1
2

参数： 年龄( int ) – 连接应被视为陈旧的年龄。
返回： 关闭的连接数。
关闭正在使用但超过给定年龄的连接。调用此方法时要小心！

from peewee import *
from playhouse.shortcuts import ReconnectMixin
from playhouse.pool import PooledMySQLDatabase

class ReconnectMySQLDatabase(ReconnectMixin, PooledMySQLDatabase):

    _instance = None

    @staticmethod
    def get_db_instance():
        if not ReconnectMySQLDatabase._instance:
            print('+'*100)
            ReconnectMySQLDatabase._instance = ReconnectMySQLDatabase(
                database=DATABASE_NAME,
                host=DATABASE_HOST,
                port=DATABASE_PORT,
                user=DATABASE_USER,
                passwd=DATABASE_PASS,
                charset='utf8',
                max_connections=200,
                stale_timeout=300,
                sql_mode='NO_AUTO_CREATE_USER'
            )

        return ReconnectMySQLDatabase._instance
        
database = ReconnectMySQLDatabase.get_db_instance()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27