 Too Many Connections Created In Mysql By My Site |
|
|
|
|
  |
  Mar 31 2006, 01:42 PM
Post
#1
|
|
|
Super Member  Group: Members Posts: 204 Joined: 6-October 04 From: London, uk Member No.: 1,444  |
Hi
i am hosted at computinghost (which is great btw) but my site is causing mysql to hang because it is flooded and it causes my account to be suspended alot.. What is confusing is that not many users (7) are only when it happens and it is not a deliberate flooding. The mysql proccesslist when it gets too many connections: QUOTE | 7971 | andre_krap | localhost | andre_wap | Query | 439 | Sending data | SELECT unread,sentby,id FROM private WHERE sentto='Candi.Boi' ORDER BY id DESC LIMIT 0,15 | | 7973 | andre_krap | localhost | andre_wap | Query | 2 | Sending data | SELECT COUNT(*) FROM posts WHERE forum='43' AND thread='1' | | 7975 | andre_krap | localhost | andre_wap | Sleep | 1 | | | | 7987 | andre_krap | localhost | andre_wap | Query | 10 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='fatal.' | | 7992 | andre_krap | localhost | andre_wap | Query | 21 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='butta' | | 7996 | andre_krap | localhost | andre_wap | Query | 22 | closing tables | SELECT COUNT(*) FROM private WHERE sentto='rap.pro' | | 8001 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='PROPHETZ' | | 8006 | andre_krap | localhost | andre_wap | Query | 21 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='at0z' | | 8009 | andre_krap | localhost | andre_wap | Sleep | 1 | | | | 8012 | andre_krap | localhost | andre_wap | Sleep | 1 | | | | 8015 | andre_krap | localhost | andre_wap | Sleep | 3 | | | | 8016 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lacoste' | | 8029 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='rap.pro' | | 8030 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='angelfly' | | 8042 | andre_krap | localhost | andre_wap | Query | 24 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='at0z' | | 8052 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8073 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='angelfly' | | 8093 | andre_krap | localhost | andre_wap | Query | 20 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='angelfly' | | 8105 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='candi.boi' | | 8106 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8107 | andre_krap | localhost | andre_wap | Query | 21 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='at0z' | | 8121 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8123 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='angelfly' | | 8125 | andre_krap | localhost | andre_wap | Query | 24 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8138 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8139 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='candi.boi' | | 8143 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='angelfly' | | 8145 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8153 | andre_krap | localhost | andre_wap | Query | 21 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lacoste' | | 8166 | andre_krap | localhost | andre_wap | Query | 22 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8167 | andre_krap | localhost | andre_wap | Query | 22 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8175 | andre_krap | localhost | andre_wap | Query | 24 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='candi.boi' | | 8181 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8186 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lacoste' | | 8187 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8191 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lamyboy' | | 8199 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='thug_h.b.k' | | 8203 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lamyboy' | | 8204 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8211 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8222 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8226 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lacoste' | | 8231 | andre_krap | localhost | andre_wap | Query | 23 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='fatal.' | | 8244 | andre_krap | localhost | andre_wap | Query | 15 | Sending data | SELECT unread,sentby,id FROM private WHERE sentto='M2' ORDER BY id DESC LIMIT 0,15 | | 8248 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lamyboy' | | 8267 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8268 | andre_krap | localhost | andre_wap | Query | 15 | Sending data | SELECT unread,sentby,id FROM private WHERE sentto='M2' ORDER BY id DESC LIMIT 0,15 | | 8270 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='candi.boi' | | 8272 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='at0z' | | 8273 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='lacoste' | | 8295 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8315 | andre_krap | localhost | andre_wap | Query | 23 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='at0z' | | 8319 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8322 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='gattwo' | | 8333 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8337 | andre_krap | localhost | andre_wap | Query | 7 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='fatal.' | | 8339 | andre_krap | localhost | andre_wap | Query | 17 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='LAM' | | 8344 | andre_krap | localhost | andre_wap | Query | 7 | Sending data | SELECT COUNT(*) FROM private WHERE sentto='tuli2' | | 8346 | andre_krap | localhost | andre_wap | Query | 10 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='lacoste' | | 8353 | andre_krap | localhost | andre_wap | Query | 3 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND sentto='At0z' | | 8354 | andre_krap | localhost | andre_wap | Query | 2 | Sending data | SELECT COUNT(*) FROM private WHERE unread='y' AND I have tried to make the timeout less which helped a bit but it still happened And my .htaccess QUOTE php_value mysql.connect_timeout 20 php_flag register_globals on AddType application/vnd.nok-s40theme nth ErrorDocument 404 http://krapwap.com/404.php ErrorDocument 503 http://krapwap.com/503.php ErrorDocument 403 http://krapwap.com/403.php i really dont know what to do  please help,, thanks Andre |
 |
 
|
|
Mar 31 2006, 02:28 PM
Post
#2
|
|
|
Administrator  Group: Admin Posts: 1,479 Joined: 11-June 04 From: Somewhere in Time & Space. Member No.: 1 |
Hey Andre, Thanks for not making another topic in ComputingHost section and ranting there. I really appreciate your trying to seek a solution to this problem.
I'd recommend you try using PERSISTENT CONNECTIONS. And You close the connection properly at the end of your script. |
|
|
|
|
Apr 18 2006, 02:41 PM
Post
#3
|
|
|
Super Member Group: Members Posts: 204 Joined: 6-October 04 From: London, uk Member No.: 1,444 |
QUOTE(OpaQue @ Mar 31 2006, 03:28 PM)  I'd recommend you try using PERSISTENT CONNECTIONS. ok ive found how to do that with msql_pconnect() QUOTE(OpaQue @ Mar 31 2006, 03:28 PM) And You close the connection properly at the end of your script. But i found this in php.net.. QUOTE msql_pconnect() acts very much like msql_connect() with two major differences. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host. If one is found, an identifier for it will be returned instead of opening a new connection. Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (msql_close() will not close links established by this function). Now im confused   Andre |
|
|
|
|
Apr 18 2006, 04:25 PM
Post
#4
|
|
|
Privileged Member Group: [HOSTED] Posts: 504 Joined: 14-November 05 From: Britannia! Member No.: 14,287 |
If you use persistent connections, it will not stop MySql even if you tell it to. You have to stop it being persistent. (Anyone know how?)
|
|
|
|
|
Apr 20 2006, 06:03 PM
Post
#5
|
|
|
Super Member Group: Members Posts: 204 Joined: 6-October 04 From: London, uk Member No.: 1,444 |
I was editing my script to use pconnect but i discovered i was using it already
hmmm |
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 12th October 2008 - 09:51 PM |