Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Constantly getting 'Too many connections' in Django 1.4.20

Tags:

mysql

django

I currently have an app running with MySQL in webfaction. The database is a private one, and Every 12 hours or so, I get a 'Too many connections' error intermittently.

So I logged as root into mysql to check the number of active connections

mysql> show status like '%onn%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Aborted_connects               | 4     |
| Com_enable_governor_reconn     | 0     |
| Com_enable_governor_reconn_lve | 0     |
| Connections                    | 12    |
| Max_used_connections           | 1     |
| Ssl_client_connects            | 0     |
| Ssl_connect_renegotiates       | 0     |
| Ssl_finished_connects          | 0     |
| Threads_connected              | 1     |
+--------------------------------+-------+

Note that Max_used_connections = 1 (???!!!) Weird. Ok, so maybe there is a problem with processes...

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 12 | root | localhost:38884 | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Or max connections is set to an unexplainably low number...

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

So I give up. This seems like A) I ran into one of those really obscure errors or B) I need a vacation.

Anyone has an idea?

Thanks.

EDIT: found the problem

The problem was being caused by a for object in Model.object.iterator() loop in a background task running with cron. As it turns out, there was an issue with yielding and connections being properly closed. Changed that to .all() and it worked properly.

Lessons learned: 1) avoid using .iterator() with background tasks; 2) avoid using app database for background tasks, when possible.

like image 310
misterte Avatar asked May 24 '15 04:05

misterte


1 Answers

I solved this problem by increasing the maximum number of connections allowed by MySQL in my django app settings.py file. More information in MySQL docs

DATABASES = { 
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': '',
    'USER': '',
    'PASSWORD': '',
    'HOST': 'localhost',
    'PORT': '3306',
    'OPTIONS': {
           "init_command": "SET GLOBAL max_connections = 100000", #<-- The fix
    }
  }
}
like image 156
endur Avatar answered Sep 30 '22 08:09

endur