PDA

View Full Version : User already has more than 'max_user_connections' active connections


k30248
8-29-07, 04:49 PM
I have a vbulletin forum set up and when I install the shoutbox (infernal vbshout), the server performance becomes unstable and shuts down now and then. I get spammed with this email:

"mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User already has more than 'max_user_connections' active connections"

Do I need to use a less server-intensive shoutbox or is there some setting I can change to fix this problem?

k30248
8-29-07, 06:00 PM
Hmm, I'm getting this problem regardless

I'm going to switch back to the old forum version and try a new install again.

k30248
8-29-07, 06:36 PM
4) To identify a problem we usually have to identify the sites with too many connections -- once we shut the sites with 20-30(or more) connections open down, things usually return to something more normal within a few minutes
20-30 connections seems a little small for forums, is there anyway we can get this increased?

dmacminn
8-29-07, 09:59 PM
Absolutely not ... its 10 concurrent connections per dbuser.. and unless you have hundreds of users simultaneously accessing the same database records you shouldn't really need much more, most applications would use only 1 or 2 concurrent connections.

Remember these are not http connections ... they're database connections...

Tien
8-30-07, 01:21 AM
I still don't understand this concept of max_user_connections...i'm a visual leaner can someone explain it to me better?

say script1.php connect to database1...and there 100 people visiting the page script1.php and what happens from there in relationship to cause max_user_connections?

k30248
8-30-07, 03:36 AM
I honestly dont know too much about this, but from what i've been reading up on vb support, they said 25 (referring to somebody else) connections was already really small, and that most hosts offer 100, and some people had 250-500?

Edit: Well, either way, i had like 8 users online when I got these errors, so I'm guessing there was an error in my set up?

entrecon
8-30-07, 11:08 AM
If your pages load in a decent amount of time and release the connection once the page is loaded, it should be fine. The problem occurs when your script takes a long time to retrieve information from the database or maintains the connections.

So, if your site has documents or images stored in the database and not as files, you could have a problem. This would mean that someone is trying to retrieve a large amount of data out of the database and they keep that connection open during that time.

Additionally, if you fail to close connections in your scripts it is possble that someone will load a page and instead of closing the connection when teh page is loaded, it is left open. No one else can then use that connection. It would not take long for 10 connections to be used. For a visual representation of this think about the phones in your house. Say you have 4 phones in different rooms. If someone uses one of the phones and doesn't hang it up when they are done, you can't use any of the other 3 phones until the one is hung up.

HalfaBee
8-30-07, 06:21 PM
It is more like having 10 phone lines, and if all are busy you have to wait for someone to dissconnect.

tymcode
9-12-07, 04:07 PM
This is nonsense. I've been running osCommerce/osCMax for 2 years and I'd never seen this before. Since the upgrade, my customers have been seeing it every day. My traffic is low -- 400 visitors/25K hits maximum per day; I would certainly hope The Perfoect Hosting Solution could handle a rinkydink store like mine. It certainly used to.

Either something is wrong with the servers, or osCommerce has some fundamental problem with MySQL5. The interesting thing is that PowWeb support refuses to respond to my emails about this.

--Mike Jennings
--http://www.hannahgrey.com/shop/ (MySQL11)

YvetteKuhns
9-12-07, 04:28 PM
Mike Jennings, I visited your shopping cart website. I looked at products and thought the heart shaped shark tooth was cool. I added an item to the cart, but when I clicked checkout, I could not continue. Eventually, I got the error:

Warning: mysql_pconnect() [function.mysql-pconnect]: User hgoscmax already has more than 'max_user_connections' active connections in /hermes/web02/bxxx/pow.username/htdocs/shop/includes/functions/database.php on line 17
Unable to connect to database server!

I wasn't in there long. How do you store your sessions? I had a discussion in another thread about shopping carts, because I have a strange issue with Zen Cart on another web host. We discussed how storing in a text file is better than the database. I had to optimize the sessions table in the database daily when I tried that method.

Check your web server for sessions and delete old ones. Check your CGI error logs for errors.

tymcode
9-12-07, 07:43 PM
Thanks. We sell a lot of odd cool stuff like that. Or at least we try to. Obviously, that error is why I'm posting here...

I still don't think that it's my shopping cart since even phpmyadmin is a dog right now. But I'll try anything.

I don't know much about the session management except that osCMax has a garbage collection routine. I'm set to use MySQL in the database right now, and the sessions database has 245 records. I don't know what that should be, or how to optimize the database.

In the configure.php I've now set STORE_SESSIONS to blank (default handler) for both admin and catalog. In the admin I set the session directory and then set up corresponding folders in the admin and catalog with permissions of 700.

The sessions directories both got items in them as soon as I re-logged-in. It didn't help. It was sprightly for a minute, but now I'm back to greater-than-2-minute load times, on both catalog and admin (which are using different user accounts).

Thanks for the idea, and for taking the time. At least I've eliminated a variable, and reduced the number of MySQL hits per user....

--Mike Jennings
--http://www.hannahgrey.com/shop/ (MySQL11)

YvetteKuhns
9-12-07, 08:00 PM
how to optimize the database.

I know I posted this elsewhere, but:

Login into OPS, click Services tab, click MySQL in menu under tabs, click Administer under the database table name for your username. You will be logged into phpmyadmin.

Click the name of your database and wait for it to load. (It is usually fast, but not for you.) You will see a list of tables. At the bottom, you will see Check All and other links. Click Check All if you want to check all tables at once.

There is a drop down box where you can select Check Table, Analyze Table, Optimize Table, Repair Table and other things. You can do any and all of these. You can also choose to check one table at a time. This is recommended if you want to test this first or view each separately.

Be sure to make backups BEFORE doing any of this. The Repair option can fix minor repairs, but it won't remove spam and magically fix any problem.

tymcode
9-12-07, 08:57 PM
Thank you very much, Yvette. That seemed to fix it, it was faster than ever for about five minutes, and then it hit the wall again.

These CGI Error Logs: Are these osCommerce logs? (I can't find them in my home directory, my htdocs directory or my catalog directory...)

--Mike Jennings

YvetteKuhns
9-12-07, 09:15 PM
CGI Error Logs:

Log into OPS, click Services tab, click Site Tools, click CGI Error Logs. This shows errors for PHP, Perl and CGI errors.

tymcode
9-12-07, 10:32 PM
Aha. Never needed that before, never noticed it. OK, it has a bunch of stuff going way back. So I reset it and began navigating around my site. I was experiencing poky performance when I got one of these, out of nowhere:

--------------------------------------
Network Error (tcp_error)

A communication error occurred: ""
The Web Server may be down, too busy, or experiencing other problems preventing it from responding to requests. You may wish to try again at a later time.

For assistance, contact your network support team.
-------------------------------------------

I'm at my day job at Adobe so I'm pretty sure that wasn't on our end. Refreshing made it go away, followed by the Max_connections error. Looking now I have 5 users on my site, and four Max Connection errors in my CGI log. This just ain't happening.

--Mike Jennings

HalfaBee
9-12-07, 11:14 PM
You can show current processes using this SQL

SHOW PROCESSLIST

Mojave
9-14-07, 04:44 PM
Hi--

It appears that this problem arises when page views cause multiple mysql connections to be created per user, and then persist even after the page has been viewed, such that even a small number of users can exceed the maximum number of connections quite easily.

I found a couple of methods that site admins have used to fix this issue, however not all of them may be available to you, depending on how much access your host gives you to your server settings. If you are on a dedicated box, you will have more solutions available than if you are sharing a box.

Here are some steps you can use to mitigate this issue:

- Edit the "db/mysql.php" and "db/mysql4.php" scripts to replace the optional use of "mysql_pconnect()" with just "mysql_connect()".

- In "/db/mysql4.php" find the following line, and change the $persistency argument to "false":

function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)

- If you have access to "config.php", turn off "pconnects".

- Take a look at MySQL's "wait_timeout" or "connect_timeout" directives. By default it may be set to 8 hours, but almost any decent production server will have been lowered to the 60 second range.

For the curious, here is a page with a detailed explanation about persistent connections from the perspective of the PHP language:

http://www.php.net/manual/en/features.persistent-connections.php

The take away is that persistent connections do not enable any new functionality - they are only an efficiency measure, and so disabling "pconnects" will only reduce the performance of the site.

If your site keeps going down due to this issue, it appears that sacrificing the performance of "pconnects" would be worthwhile, even if it results in a slower site.

I hope this helps.

tymcode
9-14-07, 06:07 PM
Here is the response I got from Powweb tech support:

We apologize for any inconvenience this may have caused you. The maximum number of concurrent MySQL connections per user is 10. This is the reason why you are getting the error message. The scripts what you are using to connect to the database is using lot of connections to the database and therefore, you will need to upgrade your scripts.

And my response:

The scripts I am using are:

The same ones I've been using for 2 years on Powweb, with no problem until the MySQL 5 upgrade
The scripts available in Powweb's Installer Central (osCommerce)


What good is 100GB a day of transfers if I can only have a couple of users?

I'm afraid I'm now forced to seek an alternative IHP, a time-consuming and annoying task for which I will always resent Powweb.

--Mike Jennings

YvetteKuhns
9-14-07, 06:22 PM
The maximum number of concurrent MySQL connections per user is 10. This is the reason why you are getting the error message. The scripts what you are using to connect to the database is using lot of connections to the database and therefore, you will need to upgrade your scripts.

This doesn't mean you can only have a few users. It means you can't have 10 simultaneous users. Imagine more than ten people trying to walk through a door at the same time. Ouch! It reminds me of the end of the school day when everyone rushes to leave the school! :D

The ten users could be you, a few search engine robots and a few visitors to your website. You can control the robots or spiders using robots.txt or .htaccess files. Watch for bad bots such as FunWebProducts. They were hammering Zen Cart (on another web host).

You are using osCommerce? Are you storing your sessions in the database (default setting during installation) or as text files? When storing in the database, there is a lot of overhead in the sessions and whos_online tables. You can check and optimize those tables daily! What a pain.

Most shared servers have similar limitations. You should still be able to use your shopping cart here, if it doesn't get too many visits in an extremely short period of time.

tymcode
9-14-07, 06:22 PM
Thanks, Mojave, that's intriguing information. I'll experiment with what I can to hold me over until the switch. Bur frankly, even 10 concurrent users isn't enough. I'm trying to run a store and Powweb just doesn't scale.

tymcode
9-14-07, 07:59 PM
Thanks, Yvette. You've been very helpful. I've tried all this with pconnect on and off, and per your suggestion I moved to storing sessions in text files, and changed usernames for my admin user, etc. and the bottom line is that it's too easy to hit the concurrency limit with just a few users. Powweb simply cannot host osCommerce sites reliably, and I'm actually not having much trouble finding hosting providers with "bigger doorways", to the tune of 300 concurrent users. So I'm going to have to throw in the towel.

Up until August, it had been fun, but it's time to move on. I'm not going to wait around for Powweb to figure out that osCommerce won't really run under these conditions.

--Mike Jennings
--www.hannahgrey.com

YvetteKuhns
9-14-07, 08:09 PM
I can't believe they haven't upgraded this yet, either. I have also seen the other hosting solutions and their connection limits. Good luck!