Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using django with mysql on ubuntu 12.04.

It's strange that when I tried to do very simple command in mysql, it will suggest error "ERROR 1040 (HY000): Too many connections"

All I want to do is create about 20 tables using a django mysql framework, south. I am the only user connecting mysql, how come there are too many connections?

I did not change the configuration file in mysql, so the max connections should be default 150, I believe.

Could anyone help me with that? Thanks a million

update 1: After I restart mysql server and run show processlist, I found 26 connections even though I am not doing anything. Sometimes the number would drop to 10, and come up again at 25 or something. Is that normal?

update 2:

Okay, I found what's the problem. Thanks for your help.

share|improve this question
    
what is the output for showprocess list in mysql? That would give a good insight about the connections. –  APZ Jan 29 '13 at 7:56
    
Thanks for your comments, I have attached the result. Could you help me again, please? Thanks –  Jiechao Li Jan 29 '13 at 18:02
    
What was the problem, can you please share it with us. I can't see any attached results. Thanks –  APZ Jan 29 '13 at 19:18
    
Maybe the mysql library you are using isn't thread safe? –  Mike Jan 30 '13 at 7:05

3 Answers 3

MySQL Connections are sometimes held in abeyance by the OS.

Many times a week, someone's application has lingering MySQL Connections that were supposedly closed by Apache or Python or PHP. Sure, it is closed in the eyes of the Application, but TIME_WAITs sometimes take precedence. There is a hack I recent discovered in ServerFault. I now this hack:

Here is a nice question about TIME_WAIT : How to forcibly close a socket in TIME_WAIT?. I use this answer: http://serverfault.com/a/329848/69271

In essence, I would run this on the Ubuntu machine:

SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse
share|improve this answer
    
Hi, I checked on my ubuntu machine, both the value of 'tcp_tw_recycle' and 'tcp_tw_reuse' are 0. And I don't know the way Django connects mysql is using socket or tcp. –  Jiechao Li Jan 30 '13 at 5:18
    
Zero means infinite waiting. Setting those values to nonzero forces the timeout with a finite value. –  RolandoMySQLDBA Jan 30 '13 at 6:37
up vote 1 down vote accepted

Okay, let me answer my problem.

Later I found that I run another python script which I forgot to terminate. The script run every 1 second to query mysql database.

+-----+------+-----------------+----------+---------+------+-------+------------------+
| Id  | User | Host            | db       | Command | Time | State | Info             |
+-----+------+-----------------+----------+---------+------+-------+------------------+
| 384 | root | localhost       | EZ_Green | Sleep   | 2682 |       | NULL             |
| 407 | root | localhost       | EZ_Green | Sleep   | 2052 |       | NULL             |
| 411 | root | localhost:47347 | NULL     | Sleep   |    3 |       | NULL             |
| 412 | root | localhost:47350 | NULL     | Sleep   |    1 |       | NULL             |
| 445 | root | localhost       | EZ_Green | Sleep   |  972 |       | NULL             |
| 448 | root | localhost       | EZ_Green | Sleep   |  882 |       | NULL             |
| 451 | root | localhost       | EZ_Green | Sleep   |  792 |       | NULL             |
| 454 | root | localhost       | EZ_Green | Sleep   |  702 |       | NULL             |
| 457 | root | localhost       | EZ_Green | Sleep   |  612 |       | NULL             |
| 460 | root | localhost       | EZ_Green | Sleep   |  522 |       | NULL             |
| 463 | root | localhost       | EZ_Green | Sleep   |  432 |       | NULL             |
| 466 | root | localhost       | EZ_Green | Sleep   |  342 |       | NULL             |
| 469 | root | localhost       | EZ_Green | Sleep   |  252 |       | NULL             |
| 472 | root | localhost       | EZ_Green | Sleep   |  162 |       | NULL             |
| 475 | root | localhost       | EZ_Green | Sleep   |   72 |       | NULL             |
| 476 | root | localhost       | EZ_Green | Sleep   |   42 |       | NULL             |
| 478 | root | localhost       | EZ_Green | Sleep   |   12 |       | NULL             |
| 479 | root | localhost       | NULL     | Query   |    0 | NULL  | show processlist |
+-----+------+-----------------+----------+---------+------+-------+------------------+

But I still don't understand why it takes so many connections and they are all sleeping? I use python thread to execute another function that query database every 1 second. There are 2 active python threads running constantly, and all the other threads are terminated when they finish. The script is here.

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

    next += interval
share|improve this answer
    
I am not a python guy so cant tell with surety, it might be possible that the connection pool you are using is not closing connections properly. Its difficult to say anything without knowing what are you doing in my_function. For sqlalchemy connection objects are not thread safe (I am quoting the docs here), you might want to try and close the connections in the script itself and see if that helps. –  APZ Jan 30 '13 at 0:30
    
Thanks. I guess it's the problem of django orm that I am using. Everytime a new thread is created, it will create a new database connection. And it seems the connection is not closed even after the thread is terminated. –  Jiechao Li Jan 30 '13 at 5:13
    
Cool, can you please mark this as answer. Thanks –  APZ Jan 30 '13 at 5:46

I am not a python guy so cant tell with surety, it might be possible that the connection pool you are using is not closing connections properly. Its difficult to say anything without knowing what are you doing in my_function. For sqlalchemy connection objects are not thread safe (I am quoting the docs here), you might want to try and close the connections in the script itself and see if that helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.