CODEWITHSUNDEEP
mysqldjango
LittleBread
LittleBread

Reputation: 366

Django mysql too many connections

I use Django with mysql, and having this problem of too many connections.

I run python script from command line, but integrated with Django model to check the data from database. The script runs every 30 seconds, and I use thread to control that. My_function is the function that will check db status.

while True: 
    now = time.time() 
    if now < next: 
        time.sleep(next - now) 
        t = Thread(target=my_function,)
        t.start()# start a thread

    next += interval

The problem is when I monitor mysql server. There are about 10 connections all the time, and all of them are sleeping. I just don't understand why. There are 2 active python threads running constantly, and all the other threads are terminated when they finish. How come the mysql connection are like 10? Anyone can help me? Much appreciate!

Update 1: Now put the screenshot of mysql processlist. The connections are all in sleep mode and does nothing, and the thread that creates the connection already terminates. That's really strange.

+------+------+-----------------+----------+---------+-------+-------+------------------+
| Id   | User | Host            | db       | Command | Time  | State | Info             |
+------+------+-----------------+----------+---------+-------+-------+------------------+
|  411 | root | localhost:47347 | NULL     | Sleep   |     2 |       | NULL             |
|  412 | root | localhost:47350 | NULL     | Sleep   |     3 |       | NULL             |
|  479 | root | localhost       | NULL     | Sleep   | 27164 |       | NULL             |
|  918 | root | localhost       | EZ_Green | Sleep   | 14006 |       | NULL             |
|  953 | root | localhost       | EZ_Green | Sleep   | 12956 |       | NULL             |
|  989 | root | localhost       | EZ_Green | Sleep   | 11874 |       | NULL             |
| 1025 | root | localhost       | EZ_Green | Sleep   | 10796 |       | NULL             |
| 1061 | root | localhost       | EZ_Green | Sleep   |  9716 |       | NULL             |
| 1097 | root | localhost       | EZ_Green | Sleep   |  8636 |       | NULL             |
| 1132 | root | localhost       | EZ_Green | Sleep   |  7586 |       | NULL             |
| 1168 | root | localhost       | EZ_Green | Sleep   |  6506 |       | NULL             |
| 1204 | root | localhost       | EZ_Green | Sleep   |  5426 |       | NULL             |
| 1240 | root | localhost       | EZ_Green | Sleep   |  4346 |       | NULL             |
| 1276 | root | localhost       | EZ_Green | Sleep   |  3266 |       | NULL             |
| 1312 | root | localhost       | EZ_Green | Sleep   |  2186 |       | NULL             |
| 1348 | root | localhost       | EZ_Green | Sleep   |  1106 |       | NULL             |
| 1384 | root | localhost       | EZ_Green | Sleep   |    26 |       | NULL             |
| 1385 | root | localhost       | NULL     | Query   |     0 | NULL  | show processlist |
+------+------+-----------------+----------+---------+-------+-------+------------------+

Upvotes: 5

Views: 15053

Answers (5)

Yusuf &#199;akmak
Yusuf &#199;akmak

Reputation: 55

Django in debug mode, creates a new thread for each request it handles, negating the effect of persistent connections. From docs:

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

Upvotes: 1

Scene
Scene

Reputation: 315

I had the same problem but it was because all the views were using the @login_required.

Sometimes this decorator is unnecessary and it creates new connections for each query. Use it wisely, and also you can use connection.close() to close the connection before moving to the next task.

Upvotes: 0

elad silver
elad silver

Reputation: 9695

Although this is not specific to your question but rather to the title and might help someone else, In my company we got this message and couldn't figure out where it was originating. After backtracking we found out that using django built-in Site model with debug mode caused a lot of connections to stay open.

We ended up setting a timeout on DB connections that are open more then 20 minutes.

Upvotes: 1

lethe3000
lethe3000

Reputation: 153

As dusty said,

Every thread that uses Django ORM creates a new database connection. And Django will not manage the connection automatically that created by your own thread. So you should manage it.

You can simple do this before each thread exit:

from django.db import connection

# your work thread method 

def my_function():
    # do something...
    # close the db connection explicitly

    connection.close()

Upvotes: 7

dusty
dusty

Reputation: 573

Please, see this question

Every thread that uses Django ORM creates a new database connection.

Upvotes: 4

Related Questions