SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Too Many Connections Error on Shared Host Dilemma

    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.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by topmonkey View Post
    Thanks - VPS would be a cheaper option. How easy is it to operate a VPS?
    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.

    Quote Originally Posted by topmonkey View Post
    No e-commerce software. I built the CMS myself which links to a 3rd party hosted cart.
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    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.
    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?

    Quote Originally Posted by ScallioXTX View Post
    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.
    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.

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by topmonkey View Post
    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?
    Depends on your host, but yes, most hosts give you root access.

    Quote Originally Posted by topmonkey View Post
    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 loyad 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?
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    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.

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by topmonkey View Post
    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.
    My thoughts exactly

    Quote Originally Posted by topmonkey View Post
    I see you sign yourself as a hosting advisor - do you know of any good quality UK hosting companies who offer VPS?
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,073
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  10. #10
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Are you opening database connections often or is the database connection handed around the script and any scripts that it calls?
    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).
    Quote Originally Posted by SpacePhoenix View Post
    Are you using persistent connections?
    I haven't set up persistent connections so I don't think so. I use PDO 'out of the box' so to speak.

  11. #11
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,073
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Try sharing the PDO object around all scripts that need database access
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  12. #12
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Try sharing the PDO object around all scripts that need database access
    I'll give it go thanks.

  13. #13
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Try sharing the PDO object around all scripts that need database access
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •