Wednesday, December 17, 2014

ubuntu mysql too many connections



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.


Answer



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


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...