django+gunicorn解决数据库连接数持续增长

背景

Gunicorn是一个UNIX的Python WSGI HTTP服务器。这是一个pre-fork worker模型。Gunicorn服务器与各种web框架广泛兼容，实现简单，服务器资源少，速度相当快。

在线上高并发场景，我们会使用gunicorn来代理django项目。但是django默认的数据库连接策略是每次都新建一个连接，完成后关闭，这就导致了连接没有被正常回收。

如何启动gunicorn

<项目名称>/wsgi.py

"""
WSGI config for hyop_django project.

It exposes the WSGI callable as a module-level variable named ``application``.

For more information on this file, see
https://docs.djangoproject.com/en/3.2/howto/deployment/wsgi/
"""

import os

from django.core.wsgi import get_wsgi_application

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "hyop_django.settings")

application = get_wsgi_application()

supervisord

[program:api-server]
directory = /opt/hyop_django/
environment = PATH="/opt/hyop_django/DjangoEnv/bin"
command = gunicorn hyop_django.wsgi -b XXX:8000 -w 48 -t 300 --limit-request-line 8182
; command = python3 manage.py runserver XXX:8000
autostart = true
startsecs = 3
stdout_logfile = /data/log/hyop_django/api.log
stdout_logfile_maxbytes = 20MB
stdout_logfile_backups = 20
redirect_stderr = true

问题表现

CONN_MAX_AGE默认是0，表示每次请求完成后关闭，当并发多的时候，一定要设置关闭连接时间，MySQL默认的时间是8小时

数据库配置了

'CONN_MAX_AGE': 600        # 数据库连接的生命周期，秒

使用了gunicorn启动wsgi时候，就算什么服务都不跑，CONN_MAX_AGE会导致连接定期关闭，但是不能关闭干净，导致数据库连接数量持续增长！

根据网上说的使用连接池也不能解决问题，github.com/altairbow/d…

同样在网上找到类似解决方法：xxx/dbpool/mysql/base.py

import random
from django.core.exceptions import ImproperlyConfigured

try:
    import MySQLdb as Database
except ImportError as err:
    raise ImproperlyConfigured(
        "Error loading MySQLdb module.\n" "Did you install mysqlclient?"
    ) from err

from django.db.backends.mysql.base import *
from django.db.backends.mysql.base import DatabaseWrapper as _DatabaseWrapper

class DatabaseWrapper(_DatabaseWrapper):
    def get_new_connection(self, conn_params):
        pool_size = self.settings_dict.get("POOL_SIZE") or 10
        return ConnectPool.instance(conn_params, pool_size).get_connection()

    def _close(self):
        return None  # 覆盖掉原来的close方法，查询结束后连接不会自动关闭

class ConnectPool(object):
    def __init__(self, conn_params, pool_size=10):
        self.conn_params = conn_params
        self.pool_size = pool_size
        self.connects = []

    # 实现单例，实现连接池
    @staticmethod
    def instance(conn_params, pool_size):
        if not hasattr(ConnectPool, "_instance"):
            ConnectPool._instance = ConnectPool(conn_params, pool_size)
        return ConnectPool._instance

    def get_connection(self):
        if len(self.connects) <= self.pool_size:
            new_connect = Database.connect(**self.conn_params)
            self.connects.append(new_connect)
            return new_connect
        index = random.randint(0, self.pool_size - 1)
        try:
            # 正常就返回连接
            self.connects[index].ping()
        except Exception as e:
            # 异常重新建立连接
            self.connects[index] = Database.connect(**self.conn_params)
        return self.connects[index]

setting.py中，ENGINE使用自定义的代码

DATABASES = {
    "default": {
        "ENGINE": "xxx.dbpool.mysql",
        "POOL_SIZE": 100,
    }
}