python中django连接池-django数据库优化调参

django默认连接方式为在每次请求结束时关闭数据库连接 这样会比较耗费资源
django中有内置参数,可以设置连接的过期时间,相当于优化过的连接池。
在发出请求时
如果连接池中有连接,django会默认从现有未过期的连接池中取
如果连接池中没有剩余连接,则新建连接
下面我将详细描述如何配置连接池以及如何观察连接情况

1. 连接池配置：

DATABASES = {
    'default': {
        # 'ENGINE': "django.db.backends.postgresql",
        'ENGINE': "django.contrib.gis.db.backends.postgis",
        'NAME': "ssnydjg",
        'USER': "",
        'PASSWORD': "",
        'HOST': "",
        'PORT': 5432,
        'CONN_MAX_AGE': 500,  # 默认： 0 一个数据库连接的寿命，以秒为整数。使用 0 在每次请求结束时关闭数据库连接——这是 Django 的历史行为，使用 None 则是无限的持久连接。
    }
}

2. 配置好之后，可以在数据库观察连接情况，SQL：

select * from pg_stat_activity where datname='数据库名称' and application_name != 'Navicat'

关注pid的变化情况,观察过期的连接有没有及时关闭

3. 如果没有及时关闭，可以写个中间件，在请求开始或结束时将过期连接关闭

from django import db
db.close_old_connections()

下面是django关闭连接时的源码:

1. 创建连接时，将超时时间写进去
2. 关闭不可用或过期连接时:判断当前时间大于超时时间则关闭

# BaseDatabaseWrapper中相关的方法：
def connect(self):
    """Connect to the database. Assume that the connection is closed."""
    # Check for invalid configurations.
    self.check_settings()
    # In case the previous connection was closed while in an atomic block
    self.in_atomic_block = False
    self.savepoint_ids = []
    self.needs_rollback = False
    # Reset parameters defining when to close the connection
    max_age = self.settings_dict['CONN_MAX_AGE']
    self.close_at = None if max_age is None else time.time() + max_age
    self.closed_in_transaction = False
    self.errors_occurred = False
    # Establish the connection
    conn_params = self.get_connection_params()
    self.connection = self.get_new_connection(conn_params)
    self.set_autocommit(self.settings_dict['AUTOCOMMIT'])
    self.init_connection_state()
    connection_created.send(sender=self.__class__, connection=self)

    self.run_on_commit = []

def close_if_unusable_or_obsolete(self):
    """
    Close the current connection if unrecoverable errors have occurred
    or if it outlived its maximum age.
    """
    if self.connection is not None:
        # If the application didn't restore the original autocommit setting,
        # don't take chances, drop the connection.
        if self.get_autocommit() != self.settings_dict['AUTOCOMMIT']:
            self.close()
            return

        # If an exception other than DataError or IntegrityError occurred
        # since the last commit / rollback, check if the connection works.
        if self.errors_occurred:
            if self.is_usable():
                self.errors_occurred = False
            else:
                self.close()
                return

        if self.close_at is not None and time.time() >= self.close_at:
            self.close()
            return
                
def close_old_connections(**kwargs):
    for conn in connections.all():
        conn.close_if_unusable_or_obsolete()