Persistent Connections

Since Django 1.6

CONN_MAX_AGE

CONN_MAX_AGE

Default: 0

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.

Thread Safety

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

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

    from threading import local
    
    class ConnectionHandler(object):
        def __init__(self, databases=None):
            """
            databases is an optional dictionary of database definitions (structured
            like settings.DATABASES).
            """
            self._databases = databases
            self._connections = local()
    
        def __getitem__(self, alias):
            if hasattr(self._connections, alias):
                return getattr(self._connections, alias)
    
            self.ensure_defaults(alias)
            db = self.databases[alias]
            backend = load_backend(db['ENGINE'])
            conn = backend.DatabaseWrapper(db, alias)
            setattr(self._connections, alias, conn)
            return conn
    

Settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'chipdb',
        'USER': 'chip',
        'PASSWORD': 'mypassword',
        'HOST': 'chip.mysql.rds.aliyuncs.com',
        'PORT': '3306',
        'CONN_MAX_AGE': 60,  # The lifetime of a database connection, in seconds. Default 0.
      }
 }

Problems

  • MySQL server has gone away

    • References

    • Reasons

      • uWSGI start

        • The value of CONN_MAX_AGE is more than the value of DB's IDLE/TIMEOUT
      • python manage.py shell

        • Old django would close every connection right away, django 1.6 checks with CONN_MAX_AGE

          1. It gets CONN_MAX_AGE from DATABASES, sets close_at

            max_age = self.settings_dict['CONN_MAX_AGE']
            self.close_at = None if max_age is None else time.time() + max_age
            
          2. Actually the code above affects close_if_unusable_or_obsolete, which closes the connection if 'self.close_at is not None and time.time() >= self.close_at'

          3. close_if_unusable_or_obsolete itself is being called by close_old_connections, which in turn is a request handler for signals.request_started and signals.request_finished

        • We have a worker, which is effectively a django app but it doesn't process any HTTP requests. In fact that makes all connections persistent because close_old_connections never gets called

    • Solutions

  • Too many connections

    • Reasons

      • uWSGI start

        • One uWSGI Thread One Connection, by default uWSGI starts with a single process and a single thread

          # number of worker processes
          processes       = 10
          # number of threads for each worker processes
          threads         = 5
          
        • connections = processes threads = 10 5 = 50

      • python manage.py runserver

        • The development server creates a new thread for each request it handles

References

[1] [email protected], Databases

[2] [email protected], Persistent connections, take 2

results matching ""

    No results matching ""