0

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.

Jiechao Li
  • 253
  • 1
  • 4
  • 13

3 Answers3

2

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: https://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
Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 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 05:18
  • Zero means infinite waiting. Setting those values to nonzero forces the timeout with a finite value. – RolandoMySQLDBA Jan 30 '13 at 06:37
1

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
Jiechao Li
  • 253
  • 1
  • 4
  • 13
  • 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 00: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 05:13
0

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
  • 954
  • 2
  • 12
  • 24