Jump to content
MakeWebGames

Max User Connections Error


Smokey

Recommended Posts

I'm wondering if a shared server is just too small for the amount of activity, which is anywhere from 3 to 14 in the last minute, usually 15 - 30 on within 15 min and around 130 - 150 daily, it seems when it hits closer to 30 online within the last 15 min it starts throwing a max_user_connections error. Even worse it seems when I have either Arrowchat or Dave Macaulay's Ajax Chat running. I bought the Arrowchat because my last host said Dave's chat was not closing connections properly, as did the host I'm using now - Bluehost, when I asked them to raise my max user connections limit - which they didn't. But to my understanding the mysql connections should not be persistent therefore close automatically anyway.

From the database: max. concurrent connections - 757, I don't see a max user connections setting.

So, I'm a bit stumped. Is this really a code issue or do I need a VPS server?

Link to comment
Share on other sites

This is party a code issue, 700+ connections per 15/m is insane for the activity you've specified. Make sure you are closing the connections after usage, persistent connections are a bad idea with MySQL and PHP.

Persistent connections are only advantageous when it's resource intensive to create one to the DMBS(MySQL here). But you're probably on a localhost DB so drop that.

You see, when you create a persistent connection to the database server, you're basically telling *PHP* to "keep this connection open until I close it/or times out". In theory you're creating more connections than you actually need to query the database.

Link to comment
Share on other sites

But its suppose to NOT be persistent connections so I shouldn't have to close, they should be closing automatically or so I thought. And to be honest I'm not exactly sure how to. On someones advice I put mysql_close(); at the bottom of the header, no change.

Link to comment
Share on other sites

Run the following query and post output:

SHOW PROCESSLIST[code]

It should look something like this:
[code]
+----------+----------+-----------+-----------------+---------+-------+-------+------------------+
| Id       | User     | Host      | db              | Command | Time  | State | Info             |
+----------+----------+-----------+-----------------+---------+-------+-------+------------------+
| 14633899 | x| localhost | x| Sleep   |     1 |       | NULL             |
| 16440330 | x| localhost | x| Sleep   |  9868 |       | NULL             |
*snip*
Link to comment
Share on other sites

I'm wondering if a shared server is just too small for the amount of activity, which is anywhere from 3 to 14 in the last minute, usually 15 - 30 on within 15 min and around 130 - 150 daily, it seems when it hits closer to 30 online within the last 15 min it starts throwing a max_user_connections error. Even worse it seems when I have either Arrowchat or Dave Macaulay's Ajax Chat running. I bought the Arrowchat because my last host said Dave's chat was not closing connections properly, as did the host I'm using now - Bluehost, when I asked them to raise my max user connections limit - which they didn't. But to my understanding the mysql connections should not be persistent therefore close automatically anyway.

From the database: max. concurrent connections - 757, I don't see a max user connections setting.

So, I'm a bit stumped. Is this really a code issue or do I need a VPS server?

The system is in bad need of an upgrade, was written before I had any idea of what optimization meant. It will run fine on smaller games with less traffic but otherwise it'll cause problems like this.

Please contact me and I'll refund you, I don't have the time to upgrade this at the moment sadly.

Link to comment
Share on other sites

The system is in bad need of an upgrade, was written before I had any idea of what optimization meant. It will run fine on smaller games with less traffic but otherwise it'll cause problems like this.

Please contact me and I'll refund you, I don't have the time to upgrade this at the moment sadly.

I've tried to contact you through Oxyden but its closed down for remodeling :P

Link to comment
Share on other sites

  • 2 months later...

I still have not figured out what is causing this issue, the errors are always timestamped 1 second after the minute (:01) or occasionally 2 seconds after. So I figured it was in the minute cron file. but I cannot for the life of me see anything in my minute cron that would cause this unless it has to do with the db connection itself.

include_once "config.php";
global $_CONFIG;
define("MONO_ON", 1);
require "class/class_db_{$_CONFIG['driver']}.php";
$db=new database;
$db->configure($_CONFIG['hostname'],
$_CONFIG['username'],
$_CONFIG['password'],
$_CONFIG['database'],
$_CONFIG['persistent']);
$db->connect();
$c=$db->connection_id;

 

Looks fine to me. There are some exceptionally good PHP coders here so surely someone would have some sort of clue as to what is causing this? My max_user_connections is set at 15. Also my max_connections is set at 1500. When I run the command to see connected threads (SHOW STATUS WHERE `variable_name` = 'Threads_connected';) it can vary greatly, from 4 threads up to 60 or so. And it can go up that far within a couple seconds as I can run the command twice within a few seconds and get a result from both ends of that range.

I really need to get this issue sorted as the game is trying to grow but cant considering once so many people (30 or so) are online at the same time it starts throwing these errors.

Link to comment
Share on other sites

I still have not figured out what is causing this issue, the errors are always timestamped 1 second after the minute (:01) or occasionally 2 seconds after. So I figured it was in the minute cron file. but I cannot for the life of me see anything in my minute cron that would cause this unless it has to do with the db connection itself.
include_once "config.php";
global $_CONFIG;
define("MONO_ON", 1);
require "class/class_db_{$_CONFIG['driver']}.php";
$db=new database;
$db->configure($_CONFIG['hostname'],
$_CONFIG['username'],
$_CONFIG['password'],
$_CONFIG['database'],
$_CONFIG['persistent']);
$db->connect();
$c=$db->connection_id;

Looks fine to me. There are some exceptionally good PHP coders here so surely someone would have some sort of clue as to what is causing this? My max_user_connections is set at 15. Also my max_connections is set at 1500. When I run the command to see connected threads (SHOW STATUS WHERE `variable_name` = 'Threads_connected';) it can vary greatly, from 4 threads up to 60 or so. And it can go up that far within a couple seconds as I can run the command twice within a few seconds and get a result from both ends of that range.

I really need to get this issue sorted as the game is trying to grow but cant considering once so many people (30 or so) are online at the same time it starts throwing these errors.

The issues is caused by the way I foolishly originally created the system. Each new chat window for each user is a new connection to the database. Meaning if each user has 3 chats (global, trade, gang for instance) then every time the script checks for new messages it's doing 3 connections at the same time.

This coupled up with the amount of queries which already happen on the site amounts to a hell of a lot.

Very inefficient creation of mine, I have a new version in planning which will resolve these issues. I can't give any idea of a release date due to other commitments.

Link to comment
Share on other sites

When you release an 'updated' version Dave will it be free to those who have already purchased this one?

With the update I'm going to address the issues but then add in a couple more features to give the system extra value. There will be a small upgrade cost though. I plan to 'bake' the chats in with the current mail system allowing private user to user chats along with other general improvements to the system.

Link to comment
Share on other sites

No Dave, this happens when I am not running your chat as well. I have not had your chat running for at least 48 hours now, and just put in my header code for arrow chat. Same thing happens, it lags out and I start getting these connection errors. And to be honest it happens extremely quicker with arrow chat as it does with yours. And it only happens 1 second after the minute mark which I find to be very odd as I really don't think its my cron file.

Link to comment
Share on other sites

With the update I'm going to address the issues but then add in a couple more features to give the system extra value. There will be a small upgrade cost though. I plan to 'bake' the chats in with the current mail system allowing private user to user chats along with other general improvements to the system.

Really look forward to it.

Do you have any rough time scale idea?

Link to comment
Share on other sites

http://www.vbulletin.org/forum/showthread.php?t=137238

//------------------------------------------------------------------------------------

Check the MAX USER_CONNECTIONS setting on your MySQL server for the user. In PHPMyAdmin go to the server page (Click on the Server:<>) and in the sub-menu click on priviledges. Edit the user and the MAX USER_CONNECTIONS will be on the right side. By default I believe it is set to 0 (unlimited), yours maybe restricted depending on who setup the server.

//-----------------------------------------------------------------------------------

5 down vote

 

First of all, try to know from your hosting server administrator about the max consecutive active connections available to the MySQL database. This is the most basic & primary information to have knowledge about.

If your page(s) 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 some long time to retrieve information from the database or maintains the connections.

Since you are executing INSERT and / or UPDATE operations of millions of rows, so you may have some problem.

Additionally, if you fail to close connections in your script(s), it is possible that someone will load a page and instead of closing the connection when the page is loaded, it is left open. No one else can then use that connection. So please make sure that at the end of execution of all the MySQL / SQL queries, the database connection is closed. Also please make sure that your server provides more than 250 connections, since 100 connections is available in almost all the servers generally.

Also make sure that you are not using the persistent connections (which is available when using the built-in function "mysql_pconnect()"), since this will lock up the user until the connection is manually closed.

Hope it helps.

Edited by rockwood
Link to comment
Share on other sites

I have seen that thread and a hundred like it. None of them give me any real answers. It was to my understanding that connections were by default non-persistent so they should close automatically.

 

So please make sure that at the end of execution of all the MySQL / SQL queries, the database connection is closed

Exactly how do you do this? Like I said I thought they closed automatically.

As for the phpmyadmin, i have no privileges tab. But as stated my max_user_connections is set at 15 and from what I hear that is plenty. Also max_connections is set at 1500.

Link to comment
Share on other sites

I have seen that thread and a hundred like it. None of them give me any real answers. It was to my understanding that connections were by default non-persistent so they should close automatically.

 

Exactly how do you do this? Like I said I thought they closed automatically.

As for the phpmyadmin, i have no privileges tab. But as stated my max_user_connections is set at 15 and from what I hear that is plenty. Also max_connections is set at 1500.

what kind of service you have shared or VPS ?

you are using which version of mccode ??

in v2

 

 function disconnect()
 {
   if($this->connection_id) { mysql_close($this->connection_id); $this->connection_id=0; return 1; }
   else { return 0; }
 }

hint for version 2:- (header.php) just in endpage() at very end line of code call disconnect()

hint for mysql :- MAX USER_CONNECTIONS make sure it is By default 0 (unlimited) (in setting of pphpmyadmin)

hope it may help you

other wise your queries are not optimized

Edited by rockwood
Link to comment
Share on other sites

No Dave, this happens when I am not running your chat as well. I have not had your chat running for at least 48 hours now, and just put in my header code for arrow chat. Same thing happens, it lags out and I start getting these connection errors. And to be honest it happens extremely quicker with arrow chat as it does with yours. And it only happens 1 second after the minute mark which I find to be very odd as I really don't think its my cron file.

Ah okay, any form of live chat is going to be doing a lot of requests. Mine could be a lot more efficient.

It sounds like you need to move host and either upscale on your shared package and request to have the limit increased or to move over to some form of managed VPS.

Link to comment
Share on other sites

what kind of service you have shared or VPS ?

you are using which version of mccode ??

in v2

 

 function disconnect()
 {
   if($this->connection_id) { mysql_close($this->connection_id); $this->connection_id=0; return 1; }
   else { return 0; }
 }

hint for version 2:- (header.php) just in endpage() at very end line of code call disconnect()

hint for mysql :- MAX USER_CONNECTIONS make sure it is By default 0 (unlimited) (in setting of pphpmyadmin)

hope it may help you

other wise your queries are not optimized

 

I have bluehost shared and using v2. I contacted them some time ago and this was their response:

This problem is caused by an issue with the coding. Your mysql connections are not closing where and when they should close. You should contact a website builder to help you fix that code.

Pretty vague. I'm hoping its not some rogue query that is maybe causing an infinite loop or something, it would be near impossible for me to go through every file and every query looking for it.

Link to comment
Share on other sites

1. one more point need notice, are you facing this problem from starting or after add any modification (like after add any new mod in your current game ).

2. please check properly mysql database (i am talking about your cpanel phpmyadmin).

it will be miner problem so please check by cool mind.

if you need my more help then you can PM me or contact me on Skype .

Link to comment
Share on other sites

This has been an issue for well over a year now, so if it was because of a modification I put in I wouldnt have a clue as to what it was. It doesn't happen until there are about 30 people online. As far as it being my shared host, I had the same problem, even worse, with past hosts. When it first started happening I figured it was due to more and more users, so I moved to a VPS. It not only caused the errors still but it also caused that VPS to keep restarting. Bluehost I have had the least amount of problems with and have been with them for almost a year.

2. please check properly mysql database (i am talking about your cpanel phpmyadmin).

I'm not sure what you mean here. Check what about the database?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...