django的mysql设置和mysql服务器闲置时间设置

服务器启动后，每个进程都会主动连接到mysql，要是长时间没有数据交互，mysql会自动断开连接。

show variables like  '%timeout%';

闲置连接的超时时间由wait_timeout控制，默认8小时。

django的database设置：通过设置CONN_MAX_AGE<8小时，让客户端主动断开闲置的连接，避免客户端因闲置超时发生连接错误

DATABASES = {
    'default': {
        # 'ENGINE': 'django.db.backends.sqlite3',
        # 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'ENGINE': 'django.db.backends.mysql',
        'NAME': conf.get('mysql', 'database'),
        'USER': conf.get('mysql', 'username'),
        'PASSWORD': conf.get('mysql', 'password'),  # 密码,
        'HOST': conf.get('mysql', 'host'),
        'PORT': conf.get('mysql', 'port'),
        # mysql服务器的设置是：闲置时间超过8个小时则服务端主动断开连接，因此这里设置客户端最多显示6个小时，就主动断开连接，
        # 下次连接时，重新建立新的连接,参考：https://docs.djangoproject.com/en/2.2/ref/databases/ Persistent connections
        'CONN_MAX_AGE': 60*60*6,  # 数据库每个连接断开的时间设置
        'OPTIONS': {
            # "init_command": "SET sql_mode='STRICT_TRANS_TABLES'",
            # 'init_command': "SET innodb_strict_mode=1",
            # https://django-mysql.readthedocs.io/en/latest/checks.html
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1",
            'charset': 'utf8mb4',
        },
        # Tell Django to build the test database with the 'utf8mb4' character set
        'TEST': {
            'CHARSET': 'utf8mb4',
            'COLLATION': 'utf8mb4_general_ci',
        },
        # 使每一个http请求都是事务性的
        # 'ATOMIC_REQUESTS': True
    }
}

原文细读：

Persistent connections¶

Persistent connections avoid the overhead of re-establishing a connection to the database in each request. They’re controlled by the CONN_MAX_AGE parameter which defines the maximum lifetime of a connection. It can be set independently for each database.

The default value is 0, preserving the historical behavior of closing the database connection at the end of each request. To enable persistent connections, set CONN_MAX_AGE to a positive number of seconds. For unlimited persistent connections, set it to None.

Connection management¶

Django opens a connection to the database when it first makes a database query. It keeps this connection open and reuses it in subsequent requests. Django closes the connection once it exceeds the maximum age defined by CONN_MAX_AGE or when it isn’t usable any longer.

In detail, Django automatically opens a connection to the database whenever it needs one and doesn’t have one already — either because this is the first connection, or because the previous connection was closed.

At the beginning of each request, Django closes the connection if it has reached its maximum age. If your database terminates idle connections after some time, you should set CONN_MAX_AGE to a lower value, so that Django doesn’t attempt to use a connection that has been terminated by the database server. (This problem may only affect very low traffic sites.)

At the end of each request, Django closes the connection if it has reached its maximum age or if it is in an unrecoverable error state. If any database errors have occurred while processing the requests, Django checks whether the connection still works, and closes it if it doesn’t. Thus, database errors affect at most one request; if the connection becomes unusable, the next request gets a fresh connection.


Caveats¶

Since each thread maintains its own connection, your database must support at least as many simultaneous connections as you have worker threads.

Sometimes a database won’t be accessed by the majority of your views, for example because it’s the database of an external system, or thanks to caching. In such cases, you should set CONN_MAX_AGE to a low value or even 0, because it doesn’t make sense to maintain a connection that’s unlikely to be reused. This will help keep the number of simultaneous connections to this database small.

The development server creates a new thread for each request it handles, negating the effect of persistent connections. Don’t enable them during development.

When Django establishes a connection to the database, it sets up appropriate parameters, depending on the backend being used. If you enable persistent connections, this setup is no longer repeated every request. If you modify parameters such as the connection’s isolation level or time zone, you should either restore Django’s defaults at the end of each request, force an appropriate value at the beginning of each request, or disable persistent connections.

参考：

https://www.cnblogs.com/li1234yun/p/7729800.html

https://docs.djangoproject.com/en/2.2/ref/databases/