Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Magento 1.4.1.1 SQLSTATE[42000] [1203] User already has more than ‘max_user_connections’ active connections
 
kgowda
Jr. Member
 
Total Posts:  7
Joined:  2011-05-16
 

We continue to get Max_User_Connections on our proudction server.

Magento 1.4.1.1 SQLSTATE[42000] [1203] User already has more than ‘max_user_connections’ active connections

Where should we be looking for monitoring user connections and performance?

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1848
Joined:  2009-06-04
 

Your web server is being overloaded to the point that MySQL cannot keep up with database query requests and has exceeded the number of connections allowed.

Use all available caching methods to reduce the number of queries being sent to MySQL.
1) Magento’s caching for Configuration, Layout, and HTML Blocks
2) APC cache or similar for op-code caching.
3) Purchase LightSpeed full page caching so that Magento doesn’t have to make database queries every time a page is viewed, including caching CMS pages.

Use a hosting provider that optimizes their systems to run Magento (MySQL configuration, Apache configuration( or better yet use Nginx or LiteSpeed), hardware (dedicated with multiple core and fast disc subsystems) and memory)

Get your site off of shared hosting, you will be vastly restricted in your use of the MySQL database engine to be fair to all the other people on the server with you. Magento doesn’t like fair treatment of others and is very selfish at taking over all resources.

If this is on a recently opened web site, ban Yandex and Baidu from scanning your website as initial Google, Yahoo and Bing indexing traffic can bring your website to its knees, leaving very little traffic capacity to serve your customers. (monitor how many connections are attacking your website through Customers->Online Customers, most listed will be duplicates and search engines when the site is under heavy traffic)

 
Magento Community Magento Community
Magento Community
Magento Community
 
kgowda
Jr. Member
 
Total Posts:  7
Joined:  2011-05-16
 

all great points and will start running thru them with the hosting provider. 

usually hosting providers provide some memory utilization and administrative tools to monitor app/system performance.  is there any other 3rd party tools you can recommend so i can guage where the bottle necks are after i’ve exhausted all of my caching options?

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1848
Joined:  2009-06-04
 

If you have a hosting provider that will work with you, MySQL has a bit of tuning that if done to tailor it to your installation and their system, can help a bit.

How-To: http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

The Script: http://mysqltuner.pl/mysqltuner.pl

Brent Peterson works with some well loaded Magento sites, and so has some my.cnf recommendations here for a server running 32GB, a bit out of our league unless you scale some of the settings down:

http://brentwpeterson.blogspot.com/2011/09/magento-mysql-mycnf-perfect-setup.html

Some recommendations for mysql parameter tuning using mysqlreport recommendations:

http://www.magentocommerce.com/boards/viewthread/4168/

Anything that makes MySQL respond faster and clear out its requests faster helps, the caching mentioned in the previous post help Magento serve out more static type content which reduces the requests to MySQL in the first place. This is where LightSpeed page caching helps immensely. You serve cached pages to the search engines and guest customers which gets you down to 300-500ms html response and requires MySQL involvement only for dynamic content that you have allowed, all the rest emulated static content. Without it, Black Friday would have been Black along the line of lights out instead of more sales.

Another big help in taking the strain off the web server is to use Amazon CloudFront to serve out just the images (media), though this just frees up resources on the server for other activities, not necessarily directly involving db server performance.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kgowda
Jr. Member
 
Total Posts:  7
Joined:  2011-05-16
 

Thanks again. This is all good helpful information as i know we’re losing sales on our shopping cart due to something around caching/sql calls taking a long time after each successive add to the cart.  this shouldn’t be happening.

If you go to www.goorin.com and add products to the cart you’ll notice this.

we have large page sizes that the AmazonCloud can help with but i need to performance tune the Checkout and where do you look for this?  this has to be DB calls, no?

I will report back on some items checked off but i’m looking at moving this site to a different hosting provider (Peer1) in the coming year and will work to configure/architect a better performing site.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sheba2
Sr. Member
 
Avatar
Total Posts:  111
Joined:  2011-01-05
United States
 

HELP!  I am using 1.6.0.  Got message after adding one product.  How did you fix your issue?  Which hosting company are you using now?  Thanks for your prompt attention.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fangwei
Jr. Member
 
Total Posts:  1
Joined:  2012-08-16
 

i got the same problem! every morning when the customers is more than 200 there will be an error message, my website is mosolacewigs.com, what can i do? just enable cache should be ok?
any other suggestions?

 
Magento Community Magento Community
Magento Community
Magento Community
 
sheba2
Sr. Member
 
Avatar
Total Posts:  111
Joined:  2011-01-05
United States
 

DOUBLE the number in your my.cnf which is located at /etc/my.cnf.

For example, if max_user_connections in MySQL’s /etc/my.cnf is 25, change it to 50.

Hope that helps!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top