I just noticed that one of my sites is getting the following mysql error: I am on shared reseller webhosting. I did a "show status" command and the Threads_Connected value was at 4. The strange this is that this site maybe gets like 9000 hits a day and on average 900-1000 uniques a day. On the same hosting I have a site that does 40000 hits and 3000 uniques a day and it works fine. (and does a lot more db queries per pageload than this site) This site is a recipe site with the recipes in the db. So an average pageload does 2 db queries so it is not really cpu intensive. I already contacted my hosts support and they increased the max_connections setting from 50 to 200 but the problem still persists. I have disabled my search functionality which are the heaviest queries. (lot of joins and searching in text with "like" ...) Maybe these queries bog down the server and cause the connections to stack up. I have gone over my stats and nothing looks out of the ordinary. The site's traffic is increasing slowly as it ages and I add more recipes. Anyone have any ideas what could be causing this or how to debug further ?
are you using persistant connections within your code? If you are not, then there is your problem, everytime a hit happens it creates another connection instead of just keeping it open. You can also use tools such as mytop, or do a `show full processlist` to show who and where connections are being made.
Thanks for the info guys. After I disabled the search stuff all was ok again. I will also look into using persistant connections.
If you have access to root in the server you can also edit your my.conf file and increase your max connections as well.
if you are on a shared hosting then make sure that your script closes the mysql connection after running the queryes. (might help).