Reply
Wed 7 Mar, 2007 09:36 am
I'm getting the following error, usually during the early mornings on my website.
Warning: mysql_connect(): Too many connections
My hosting provider just sends me a cookie cutter response and doesn't help much.
I'm not so sure that the problem is my website because I'm using 100% PHP scripts which are supposed to automatically close unused connections at the end of the script. Even so, I'm still calling mysql_close($conn) in my footer so it's definitely closing connections at the end of each page load. Having that close code there worries me, but it hasn't caused any problems, so I keep it there.
Before this I was getting an error, and I can't recall the specific wording of the error but it was telling me that I was exceeding the number of Max_Queries allowed. I resolved that problem by changing my SQL User every 60 seconds. I was kind of thinking that my fix for that problem created this problem. But I'm only changing the SQL User, I'm not actually opening a new connection. I only use mysql_connect() once in my config script.
So, I guess my question is, could changing the SQL User every 60 seconds be causing this problem, even if I'm not calling a new connection when I change the user.
And, is it really possible that a website written in 100% PHP could be causing this error.
I kind of think it's some other website that's on the same shared server as me that's causing the problem. But iPowerWeb is no help. They just keep sending me the following pre-written replies:
"The error that you occoured happens when an account on the server is opening to many connections to mysql on the server This happens sometimes with poorly written code that leaves connections open and keeps opening more connections. When one account does this it effects the entire mysql server resulting in the error you recieved and is only fixed when we find the issue and stop those lingering connections.
If you run into this issue further please feel free to let us know."
Any advice would be appreciated.
OK, I had to resolve this my self. The problem when you write your own code is that you have nobody but your self to turn to when you need support.
I had too much activity going to my sessions table and this was causing the entire server to crash. I had to cut way back on the data that I write to sessions and it appears to have done the trick. Also reduced my home page to not pull much data from SQL.
It was me killing the server, but not anymore
You still might want to check the online MySQL docs or Google groups such as comp.databases.mysql for further insights into related issues...
Searching references won't help much to optimize your own sql statements. But you may find good references on how to tweak server configuration settings to improve performance.
Given that your problems are sql statements you wrote a good tool to use to find the troublemakers is mytop (but you may need to get the server admin to install it for you).
Additionally enabling the slow queries log will log the problematic queries for you (again, this may not be available to you on a shared host).
Using those tools, find the problematic queries, many times they can be rewritten to greatly improve performance. Sometimes the database design itself will need to be changed (sometimes being less normalized will result in performance gain).
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace