Use timeouts. MySQL can be configured to kill a connection after an interval. If you use a connection/query (I wouldn't do it if I was in your case)* then you should add a parameter like "Connection Lifetime=5", even if 5 is much, it will help you since not all connections are bad and don't want to close themselves, this lifetime will save you. Also to be sure you can configurate mysql to kill any connection after no communication was done in a time span you want. (Networking options -> wait_timeout).
I use mysql's pooling system and it works wonderfully, and I wouldn't do what you do because creating a conn/query takes a few milliseconds and overall in 1 second it might be a big amount of milliseconds if there are like 100 queries.
I tried to profile my server cpu timing and I could see that to create a mysql connection it takes about 30 ms as it also pings the server before returing the connection. If the ping doesn't get back then too many connections are already opened and it will throw an error(your error.)
Better set a timeout like 2 seconds or even 1. It might not be a good idea if you also got your website to do queries on the mysql directly but try it.
Quote:
Originally Posted by .Beatz
No Impulse uses a "solid" connection
It open up when the source does and stays open and sends everything through that connection...
|
Actually no, you can use one solid connection to only execute queries that doesn't involve reading, but it's not my case. I am using 'using' statement as .NET has a nicely well done pooling system.
For stealarcher, 'using' statement doesn't dispose the connection, it keeps it into the pool until a timeout expires and it is disconnected.
By the way. Your server doesn't open thousands of connections now is because the pooling is on even if you don't want to. Add 'pooling=false' in your connectionstring if you don't want it.
I tried to make my own little mysql pool :S I failed because atm when I restart my server there are atleast 50 people who would login directly, and sincerely without mysql pooling, my plan failed. Better inform yourself about pooling. It will help you.