Too many MySql connections

I had a few problems with a test site Im creating ive contacted the host and they have said
We have a limit of 10 concurrent MySQL connections and for database, it appears like you have exceeded the above limit and so you are receiving the error message. To over come by exceeding the maximum concurrent connections, please close all open connections in your script when there is no scope for fetching data from database?

The maximum number of concurrent MySQL connections per user is 10. The limits are based per database login, not Control Panel log in or MySQL CNAME. It’s solely based on database login i.e. if you have one username connecting to 50 databases, there will be 50 connections, per database. You should use mysql_close () to close a normal connection at the end of the script or else the session with the database will be kept open. If you are using mysql_pconnect(), then such persistent connections are closed when the script finishes.



Does anyone have any idea how or where im supposed to change those connections?
Top
 
User avatar
AMurray
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 3853
Joined: Sat Feb 13, 2010 7:35 am
Location: Australia

Re: Too many MySql connections

Post by AMurray » Fri Sep 26, 2014 1:52 am

It sounds like a third party extension is causing this (otherwise more people would report it if it were the Joomla core installation only.

Can you please provide additional information, such as:

1) Is this a clean install of Joomla (core only) or have you added third party extensions?

2) When did this error start occuring - has Joomla worked correctly at any point? Does this occur when accessing administrator or front-end of the site?


Suggested solutions:

1) Perhaps try disabling extensions until the error does not occur?

2) If you managed to find the trouble extension, ask the extension developer for assistance in providing a fix, or pointing you to the file required to change. Ask on the developer's website/forum etc if applicable.

3) Duplicate the installation of Joomla in a separate folder and database; if you can use Akeeba Backup then us it to backup and restore with Kickstart, otherwise use the stand-alone edition of Akeeba.

4) Install WAMPServer or equivalent on your PC and restore the site with Kickstart to your own computer. Your PC running Apache, MYsql, PHP won't be an exact match for your live host but it will determine if Joomla can run correctly without the error which could just mean the number of connections is set higher than your host's restrictions - doesn't exactly resolve the issue but points you in the right direction.
Regards,

8 Ways to speed up Joomla! – Learn how to improve Joomla! performance with just a few config settings

All Joomla! performance changes and optimizations can be made through the administrator back-end of Joomla!, or by downloading and editing the configuration.php file. Don’t know how to download and edit the configuration.php file? Have a look at my Dutch article on how to edit files through FTP (Google Translate the article, or just look at the screenshots).

Let’s start tuning Joomla!

Joomla! MySQL database functions

Current versions of Joomla! can operate on MySQL, SQL Server and PostgreSQL database back-ends. For MySQL there are two interface choices: “MySQL” and “MySQLi“. Nowadays every Joomla! version supports the MySQLi database functions, so we want to use that.

The i in MySQLi stands for improved and is the recommended MySQL interface to use, and offers better performance.

 

We all know the old ext/mysql functions will be removed from PHP in the near future.

Update: ext/mysql is already removed since PHP 7.

The specified MySQL interface is defined in configuration.php:

public $dbtype = 'mysql';

Change this to mysqli:

public $dbtype = 'mysqli';

Joomla! Session handler

Optimize Joomla!’s session handler. By default, Joomla! stores all session information in its MySQL database. This can be ideal if both the MySQL database and Joomla! website are on the same physical server. When it’s not, this generates a lot of unnecessary traffic between the database and website, just to verify a session. For every visit. See the chart:

Joomla ACL decision flow-chart

Joomla ACL decision flow-chart

This, of course, is a performance drain.

Therefore, it’s recommended to store Joomla session information on the web server’s file system. This provides faster access to its session files and information.

How and where sessions are stored is defined by the Session Handler: $session_handler. When the session handler is set to 'database', change it to 'none':

public $session_handler = 'database';
public $session_handler = 'none';

Remove unused Components, Plugins, Modules, and Templates

Over time a lot of components, plugins and modules are installed, tested, and disabled on your website. This probably, goes for templates as well. When a request to your website is made and the PHP process starts up, it indexes all files in your website. Even those files of disabled components, plugins, modules and templates, simply because they are on the file system.

This may interest you:   Feedly Saotn.org RSS feed on Android

Going through the file system and indexing those unnecessary files takes up a lot of additional time (relatively), it’s best to simply remove (delete by FTP) all unneeded files and directories.

Convert your Joomla! MyISAM database tables to InnoDB

When you run Joomla! on your website for a longer period of time now, chances are your database tables are still using the older and less performing MyISAM storage engine. It is wise to update and convert these tables to InnoDB. The reason is that InnoDB is actively developed and MyISAM is not, therefore InnoDB tables provide more performance than older MyISAM.

Joomla cache handler – Set up a Joomla! caching handler with WinCache, Cache_Lite or File

Use caching in Joomla!

If your web hosting provider has WinCache available on the web server, then this will give a great performance boost for your website. If WinCache is not available, at least use Cache_Lite as cache handler: in your configuration.php, set $caching to 1 (enabled, conservative), or 2 for progressive caching.

Joomla Cache Settings where you can choose Cache_Lite

Joomla Cache Settings

Set Joomla!’s $cache_handler to File or cachelite:

public $caching = '1';
public $cache_handler = 'File';
 // public $cache_handler = 'cachelite';
 // or 'wincache' if available

Don’t forget to make sure your cache\ folder is writable.

WinCache for Joomla! 1.5?

WinCache as cache backend is not supported on Joomla! 1.5. You shouldn’t be using an ancient version like Joomla! 1.5 anymore! See Joomla!’s CMS versions for more information.

Anyway, if you’d like to add WinCache support for Joomla! 1.5, grab Don Raman’s wincache.php cache storage file from the blog post performance improvement in Joomla using WINCACHE user cache, and place the file in libraries\joomla\cache\storage. Now you can configure $cache_handler to utilize WinCache.

Joomla Gzip compression

Enable gzip in Joomla, you don’t want to have gzip disabled!

Gzip compression compresses the response body sent from the web server to the browser. A smaller file is downloaded faster, making visitors happy, and a happy visitor is a returning visitor. Gzip compression in Joomla! is also known as buffered output.

In your configuration.php file, locate

public $gzip = '0';

and set $gzip to 1, to enable gzip compression in Joomla!

public $gzip = '1';

Let me repeat: Enable Joomla! Gzip compression is one of the things you must do to make your website load faster. To Enable Gzip go to System > Global Configuration > Server – where you’ll find an option Gzip Page Compression. Simply click Yes and Save.

how to enable gzip compression in Joomla through the Server Settings screen

enable Gzip compression in Joomla!

To Disable Gzip go to System > Global Configuration > Server, set to No, and then Save.

This may interest you:   Decoupling Umbraco from your front-end website

Joomla! optimization extensions

There are many extensions that try to improve the performance of your Joomla! website. Like the earlier mentioned WinCache. Here are a few that I can recommend:

  • JCH Optimize – Combines JavaScript & CSS, combines images into sprites, minifies and compresses JavaScript, JCH Optimize helpts to minimize expensive http requests.
  • Jbetolo – All the functionality that JCH Optimize has plus CDN support, jbetolo primarily helps you with your front-end optimization,
  • JotCache – Improves the built-in Joomla caching system, JotCache is advanced solution for page caching in Joomla!
  • Cache Cleaner – With Cache Cleaner you can clean your cache fast and easily via a link in your Joomla! Administrator

Bonus Tips: run an as high as possible Joomla version

Every new Joomla! version comes with new optimized PHP code, functions and settings. Use them! Always run an as high as possible Joomla! version to make use of these improvements.

Optimze and lossless compress images, minify JavaScript & CSS
Make sure your images are optimized for the web properly. Optimize and lossless compress images and minify JavaScript and CSS files is very easy to do. It drastically downsizes your website size (footprint), and making it load faster.

And further, curious how I optimized my web hosting? Learn how to do the same

Let’s be honest, PHP is PHP, so whether you use Joomla! as your CMS or Drupal or WordPress, most performance is gained through your PHP hosting environment. And MySQL, do not forget about your MySQL database server! So read how I optimized my web hosting environment, and start doing the same.

Pro tip: Learn how to optimize PHP on your website or IIS web server? Or just to improve OPcache settings.

Conclusion

Out-of-the-box, Joomla! is not very optimized for performance. With just a few basic tweaks and settings, we’ve optimized Joomla! performance extensively. Page loads are much faster. All changes can be made through the administrator back-end, or by downloading and editing the configuration.php file.

Set Joomla Session Handler to PHP instead of Database

Here is the relevant transcript for this video:

A tutorial showing how to set Session Handler to PHP.Hey there, Joomla fans. Tim Davis here. I'm a Joomla fan too. Thanks for tuning in to this week's Maintenance Monday Live Stream.

I missed last week. Things got super busy. But I'm doing one today so remember, subscribe to the channel and ring the bell to receive updates and notifications of new live streams and tutorials.

Today we're going to look at what to set the Session Handler to on our Joomla sites. So let's head on over to the screen and we will take a look at what we're talking about here.

I can't really zoom in like I do when I make a tutorial so we're just down here at the bottom of the screen in the Configuration File. Now to get there we go to System, Global Configuration, and then click on the System tab. You might see Site first, but click on System.

So basically, the Session is information that is set when you log in saying, "Hey Tim is logged in to the site, and so we will talk to Tim's computer." And in layman's terms there's a permission set and connections. Then once that Session is set you have in this case, 15 minutes to be doing something on the site: saving it or closing a window or calls to the server, and each time you do something the Session is reset.

If you leave - in this case the way this site is set - after 15 minutes and do nothing, then the Session expires, so that if someone came walking along to go on your computer they won’t be able to get on; unfortunately, that also includes you.

There are two options right now in Joomla. This is a Joomla 3.7.5 site and there are two options of where to store the Session information.

Probably by default when you set up your Joomla site it is set to Database. So, the information that records that your visit is active is stored right in your Joomla database, and that works great. But if your server or your web host is set up for it - and on Cybersalt Hosting it is set up for this - if you switch from Database to PHP then your Session information, instead of being sent to the database and a hard drive spinning, or maybe you have an SSD drive on your servers or your host has SSD drives - instead of going to a drive, the Session information is stored in a file right on the server. That's always faster. When you can write to a file that's running in the memory on the server instead of saving it to a hard drive then that is faster. And especially some people will run Joomla but the database is on an entirely different server. So, this can speed up things.

Now the reason I haven't saved this yet is because if your web host is not set up to handle Sessions through the PHP method or through a file method then if you save this you might get locked out of your Joomla site. So don't try this unless you're sure that you can switch up the PHP - maybe you have sites on the same server and you're working on different ones, or until you are sure that you can go into your control panel for your web hosting to go and edit this right in the file. Now, before I save this, we'll just look how to do that.

In this tab, here's the file manager for this web hosting account that I have this Joomla site on and basically what I've already done in the file manager in this Control Panel, is clicked on file manager and then in the root folder that Joomla is installed you want to find the configuration.PHP file. Then when you find that you want to edit it.

Now I'm not going to open this right now because I can't blur out password information on a Live Stream, but what I will show you is this. If you save your configuration file after switching to PHP for your Session Handler and your site doesn't work, go into the file space, go into configuration.PHP, find the line in your configuration.PHP that says "public session_handler" and what you will see is this: once you switch to PHP the handler equals "none." You would think they would say PHP but it says "none" because I think that's a carry-over from some other versions, because the option used to be between database or none. So, when you see public session _ handler = "none" if you want to switch back, switch that to "Database" and then save your file in your file manager and then your Joomla site will come back online. So let's just go back here because I want to show you what happens when you do change this.

One another thing. Remember back up your site. I've got a tutorial on how to do that which you can access here. Backup your site regularly because as you're tinkering around with this stuff something can happen at any time; you never know.

Alright, so now we're logged in to the backend of the site here still, Session Handler was at Database, we switched it to PHP, now I'm going to save it. When I save it, I'm going to get kicked out of the back end of the site and that's why we wanted to make sure that you have access to the file manager on your web hosting because if the screen had just gone black then I would have been in a bad way. But basically, when you switch the method to where your sessions are stored, you have to create a new session - you're logged out.

So I can log into here and that will take us back into the site and we can go System, Global Configuration, and just for maintaining or perhaps even it's more just setting up our site here we now have changed Session Handler from Database to PHP which is the "none" method, and there it is and it stuck.

So that's just something that you can do to give yourself a little bit of boost of performance in your Joomla site and a good way to set it up if your web hosting is set up for that.

So as always, enjoy your Joomla sites and God bless you.

Don't forget to subscribe.

Top