What happens If an SQL query takes more time and the left age of CONN_MAX_AGE is less?

user_5464518 0
Viewed 365

Using:

  • Django 1.10 and MySQL

I would like to timeout a query when a particular query takes longer time. One closest thing I found is this. https://docs.djangoproject.com/en/1.10/ref/settings/#conn-max-age

CONN_MAX_AGE

The lifetime of a database connection, in seconds. Use 0 to close database connections at the end of each request — Django’s historical behavior — and None for unlimited persistent connections.

Let's assume the following scenario:

  • CONN_MAX_AGE is set to 5 seconds
  • On an average, each DB query takes 3 seconds.
  1. Request #1 comes in and since there is no active conn, It creates a connection and set the connection age to 5 seconds.
  2. Request #1 finishes after 3 seconds and age left for conn to expire is 2 seconds.
  3. Now, Request #2 comes in, Since the conn is available it uses the old open conn.

Now, What happens to Request #2? Will it finish successfully or Will it terminate because the conn this query is using has less time left to expire?

mysql django-models django python
1 Answers

No, CONN_MAX_AGE only starts after a request has completed. It is used to pool existing connections instead of constantly closing and opening new ones.

What you are looking for is MySQL's max_execution_time setting:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        ...
        'OPTIONS': {
            'init_command': 'SET max_execution_time=5000'
        }
    }
}

This will limit each statement to a maximum of 5 seconds.

user_20111470
Related