Maximum SQL connection is reached daily [Archive]

khalid

Sun 18th Jun '06, 4:32pm

I face the problem of reaching the maximum allowed connections in my server. The maximum set to 650 and my server keeps reaching the max every day. This is the output of mysqlreport script:

http://www.lakii.net/images/Jun06/admin1_sql.jpg

I know that the maximum connections should not exceed ~ 100 but mine reaches 650. This is porbably becuase of a bad php script that I am using, but how to find which script is causing this max connections.

Any idea !!

Thanks for the help.

Tomek

Sun 18th Jun '06, 5:02pm

What does this command say:
mysqladmin processlist
Disable permanent mysql connections in your php.ini, if you have not done already:
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off

khalid

Mon 19th Jun '06, 7:52am

Thanks Tomek for the reply.

When the connections is maxed out this is the "mysqladmin proccesslist" (This is 1/4 of the whole list.):

+--------+----------------------+------------------------------------+-------------------+---------+-------+------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------------------+------------------------------------+-------------------+---------+-------+------------------+------------------------------------------------------------------------------------------------------+
| 13425 | dbase_user_01 | 10.0.0.1:49328 | dbase___database1 | Sleep | 19180 | | |
| 22034 | dbase_user_01 | 10.0.0.1:58073 | dbase___database1 | Sleep | 18800 | | |
| 95030 | dbase__user__04 | 10.0.0.1:48826 | db_databas2 | Sleep | 15549 | | |
| 132252 | dbase_user_01 | 10.0.0.1:58838 | dbase___database1 | Sleep | 13988 | | |
| 199062 | dbase_user_01 | 10.0.0.1:42459 | dbase___database1 | Sleep | 10931 | | |
| 256536 | dbase_user_01 | 10.0.0.1:44697 | db___database3 | Sleep | 8042 | | |
| 256539 | dbase_user_2 | 10.0.0.1:44700 | db__database4 | Sleep | 8047 | | |
| 256544 | dbase__user__03 | 10.0.0.1:44706 | db__database5 | Sleep | 8041 | | |
| 256554 | dbase_user_2 | 10.0.0.1:44716 | db__database4 | Sleep | 8046 | | |
| 256606 | unauthenticated user | 10.0.0.1:44771 | | Connect | | Reading from net | |
| 256623 | dbase_user_01 | 10.0.0.1:44788 | db___database3 | Query | 8045 | Writing to net | select * from cook_det |
| 256650 | unauthenticated user | 10.0.0.1:44815 | | Connect | | Reading from net | |
| 256658 | unauthenticated user | 10.0.0.1:44823 | | Connect | | Reading from net | |
| 256662 | dbase_user_2 | 10.0.0.1:44827 | db__database4 | Sleep | 8040 | | |
| 256668 | dbase__user__03 | 10.0.0.1:44833 | dbase__database6 | Sleep | 8045 | | |
| 256674 | dbase_user_01 | 10.0.0.1:44839 | dbase___database1 | Sleep | 8046 | | |
| 256687 | unauthenticated user | 10.0.0.1:44852 | | Connect | | Reading from net | |
| 256699 | unauthenticated user | 10.0.0.1:44864 | | Connect | | Reading from net | |
| 256722 | dbase_user_2 | 10.0.0.1:44887 | db__database4 | Sleep | 8048 | | |
| 256724 | dbase_user_2 | 10.0.0.1:44889 | db__database4 | Sleep | 8047 | | |
| 256752 | dbase_user_2 | 10.0.0.1:44917 | db__database4 | Sleep | 8047 | | |
| 256775 | dbase_user_2 | 10.0.0.1:44942 | db__database4 | Sleep | 8022 | | |
| 256939 | dbase_user_01 | 10.0.0.1:45115 | db___database3 | Query | 8037 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1743' |
| 256977 | dbase_user_01 | 10.0.0.1:45156 | db___database3 | Query | 8033 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1617' |
| 257043 | dbase_user_01 | 10.0.0.1:45226 | db___database3 | Query | 8029 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1617' |
| 257044 | dbase_user_01 | 10.0.0.1:45227 | db___database3 | Query | 8029 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1617' |
| 257067 | dbase_user_01 | 10.0.0.1:45250 | db___database3 | Query | 8025 | Locked | select cook_id, cook_name FROM cook_det order by cook_id DESC limit 10 |
| 257077 | dbase_user_01 | 10.0.0.1:45260 | db___database3 | Query | 8026 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1537' |
| 257210 | dbase_user_01 | 10.0.0.1:45397 | db___database3 | Query | 8022 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1284' |
| 257224 | dbase_user_01 | 10.0.0.1:45412 | db___database3 | Query | 8021 | Locked | select cat_id, cat1_id, cook_name, cook_recipe, cook_desc, cook_img, cook_owner from cook_det where |
| 257271 | dbase_user_01 | 10.0.0.1:45461 | db___database3 | Query | 8019 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='144' |
| 257295 | dbase_user_01 | 10.0.0.1:45485 | db___database3 | Query | 8019 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='59' |
| 257494 | dbase_user_01 | 10.0.0.1:45694 | db___database3 | Query | 8012 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='59' |
| 257721 | dbase_user_01 | 10.0.0.1:45928 | db___database3 | Query | 8002 | Locked | update cook_det set cook_score=cook_score+3, cook_ratings=cook_ratings+1 where cook_id='833' |
| 257814 | dbase_user_01 | 10.0.0.1:46025 | db___database3 | Query | 7996 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='31' order |
| 257823 | dbase_user_01 | 10.0.0.1:46035 | db___database3 | Query | 7995 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='9' order |
| 257869 | dbase_user_01 | 10.0.0.1:46083 | db___database3 | Query | 7993 | Locked | update cook_det set cook_score=cook_score+3, cook_ratings=cook_ratings+1 where cook_id='833' |
| 257977 | dbase_user_01 | 10.0.0.1:46193 | db___database3 | Query | 7987 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='1537' |
| 258002 | dbase_user_01 | 10.0.0.1:46220 | db___database3 | Query | 7982 | Locked | update cook_det set cook_counter=cook_counter+1 where cook_id='40' |
| 258017 | dbase_user_01 | 10.0.0.1:46236 | db___database3 | Query | 7981 | Locked | select cook_id, cook_name FROM cook_det order by cook_id DESC limit 10 |
| 258035 | dbase_user_01 | 10.0.0.1:46254 | db___database3 | Query | 7981 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='9' order |
| 258050 | dbase_user_01 | 10.0.0.1:46269 | db___database3 | Query | 7981 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='9' order |
| 258073 | dbase_user_01 | 10.0.0.1:46292 | db___database3 | Query | 7980 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='9' order |
| 258116 | dbase_user_01 | 10.0.0.1:46337 | db___database3 | Query | 7979 | Locked | select cook_id, cook_name, cook_special, cook_tried, cook_img from cook_det where cat1_id='9' order |

I changed the usernames and database names for security reasons.

As for your other suggestion, I just turned mysql.allow_persistent = Off and restarted apache.

I still see the max. connections happens.

eva2000

Tue 20th Jun '06, 4:26am

post all info asked at http://www.vbulletin.com/forum/showthread.php?t=70117

khalid

Tue 20th Jun '06, 8:21am

eva2000

Tue 20th Jun '06, 10:39pm

it's best to post info i asked first to get a better picture

the unauthenticaed user means http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html

unauthenticated user

The state of a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.

just means about to connect to mysql

could be due to your wait_timeout set too high and mysql is waiting and waiting and waiting..

khalid

Wed 21st Jun '06, 8:02am

Thank you eva2000 for this info. As for the server optimization, I did ask you before on this thread:
http://www.vbulletin.com/forum/showthread.php?t=186385

I applied some of the changes you requested on that thread for my.cnf file and installed APC cache as well. That didn't help much at that time as my servers keeps failing everytime. So I asked the hosting company to do something about it and they did modify apache settings on server #1 (apache server). They said, if you still have any problems, then it must be from mysql server (server #2). After that, all went well, and these days I was faced with this problems of many connections and slowness.
What I did is that I added 1 GB to Server #2 (MySql server) to become 3 GB RAM.

Yesterday, after posting this thread, I applied all the changes you asked me to the my.cnf file and upgraded php from 4.4.1 to 4.4.2. Till now, I didn't have this problem, but not sure if it has gone away or not.

Thanks a lot for your support.

eva2000

Wed 21st Jun '06, 11:42am

oh so you didn't do ALL the suggested changes until now ? probably part of the reason heh

khalid

Thu 22nd Jun '06, 11:16am

No, unfortunatly I didn't :rolleyes: . Now everything works fine so far. I hope it remains this way.

eva2000

Fri 23rd Jun '06, 8:03am

Heh, well wish you all the best them :)

khalid

Sat 24th Jun '06, 7:23am

I forgot to ask you if there is anything to change in the my.cnf file after I have increased the RAM to 3GB instead of 2GB for mysql server?

eva2000

Sat 24th Jun '06, 10:24am

depends on info provided from questions at http://www.vbulletin.com/forum/showthread.php?t=70117 (most up to date info)