Hi All,
I maintain a website for someone who has a large e-commerce shop that is growing year-on-year. The problem is, this Christams I fear it has become a victim of it’s own success as we keep seeing the dreaded “SQLSTATE[08004] [1040] Too many connections” error, though if only intermittently.
I have made sure all open connections are closed properly so there are no ‘hanging’ connections, however the error still randomly appears. I have contacted the host (1&1) to get them to increase the MySQL max_connections variable (currently set at 240) who say they cannot do this because we are on a shared hosting package. So my question is, is there anything I can do other than move the site to a dedicated server (which would be triple the current hosting cost)? Would a virtual server or cloud hosting do the job? Or is there some coding tricks I can use to help alleviate the problem? Sorry for the newbie questions.
Thanks,
Mike.
You don’t have to go to dedicated immediately, you could start off with a VPS. The advantage of this is that all resources are available to your website and don’t have to be shared with other websites. Cloud hosting sounds a bit overkill to me.
As for alleviating the problem in the current situation I don’t think there’s anything you can do. Normally what people do to avoid database problems is to install caching like memcache, but those don’t usually run in shared environments and even if it does I’d highly recommend against it as everyone on your server can then see everything you store in there; not a good idea.
What e-commerce software are you running?
Thanks - VPS would be a cheaper option. How easy is it to operate a VPS?
No e-commerce software. I built the CMS myself which links to a 3rd party hosted cart.
It’s not extremely hard, but you have to have some experience with servers, i.e., OS setup, http server configuration, etc. You could also opt for managed hosting, where your hosting company manages the server for you.
Ah, so it’s just the CMS that does database requests? How often does the content of the CMS change? If it doesn’t change a lot you could see if SQLite would work as the database for the CMS instead of MySQL. Read performance of SQLite is very good, but INSERT / UPDATE is quite poor, so if you don’t INSERT and UPDATE a whole lot you should be fine. Plus you don’t have to share the connections you can make to your database with others on your server.
I have very little experience with servers. I can use Cpanel and WHM but no more. Presumably if I chose a managed VPS, I would still have root access to the MySQL variables etc?
Products are maintained regularly by the business owner in my custom built admin panel. Items are put in and out of stock all the time and new items are added or removed every month but I wouldn’t have a clue if this would be an excessive load for SQLite, as I have never used it. If there were lots of INSERT/UPDATE requests that overloaded SQLite, what would be the result? Would it just be slow in updating in the admin panel? Would the whole database driven side of the website perform slowly? Or would it stop working altogether?
Thanks so much for your advice on this.
Depends on your host, but yes, most hosts give you root access.
The SELECT queries have to wait until the INSERT / UPDATE queries are finished. What you’re describing doesn’t sound like a whole lot of INSERT/UPDATE queries, so it should be okay to at least try.
How do you do your database interaction? Via PDO or some standard class, or are you using the mysql(i) functions? If it’s latter I’d think very hard if you’re willing to make the change throughout the code or take the easy route and just get a VPS; if it takes you weeks of work you’re cheaper off with a VPS, plus it’s not guaranteed to work, at this point it’s just a theoretically viable option.
I use PDO. I would be tempted to go with the option that is the least hassle to set up. From what you are saying it sounds like VPS fits this.
I see you sign yourself as a hosting advisor - do you know of any good quality UK hosting companies who offer VPS?
Thanks again.
My thoughts exactly 
Me signing that just means I’m an advisor (moderator) on the hosting team on these forums.
And I’m sorry but seeing as how I’m based in the Netherlands I don’t know any hosts in the UK.
Are you opening database connections often or is the database connection handed around the script and any scripts that it calls?
Are you using persistent connections?
A bit of both really. There are some connections I leave open (mainly updating the database from the admin panel) and others where I am opening connections performing the queries required then immediately closing the connection again (mainly front end actions).
I haven’t set up persistent connections so I don’t think so. I use PDO ‘out of the box’ so to speak.
Try sharing the PDO object around all scripts that need database access
I’ll give it go thanks.
That won’t help anything. If he does that he will keep connections open longer, making the problem worse, not better.
I think the main problem her is that 1&1 is overselling their service; cramming more websites on a server than it can handle, making them fight for resources.