Jump to content
MakeWebGames

Max User Connections Error


Smokey

Recommended Posts

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.

When there is around 25 people online, do you notice a lot of waiting time?

If your hitting max connections on 30 people, there must be some sort of loop?

If your using mccodes v2, or a database class, it's quite easy to add tracking onto it.

You should also ask your host to extract all the "slow_query" logs for your account.

To better understand your issue, you going to need to track it, so until you've found the culprit, changing host is not going to work, as the next might be worse (as you said you've experienced).

Link to comment
Share on other sites

Yea that's what I think too there must be sort of query loop. I have looked through the header and index files and made sure no user table querys selected everything but only what it needed (and most other queries as well). I'm going to have to look into how to track it as I have no clue. The slow_queries I'm sure is too high, right now when I checked it it was at 145.2k so there are for sure some slow queries, I just don't know how to track them down yet.

Ok so I found the slow queries logs, there is one particular query that is repeated more than any other, and its a default query that comes with mccodes and is in the header.php:

 

$db->query("UPDATE `users` SET `laston` = ".$_SERVER['REQUEST_TIME'].",`lastip` = '$IP' WHERE `userid` = $userid",$c);

 

Wouldn't it just be easier to SET laston = unix_timestamp() ?

Edited by Smokey
Link to comment
Share on other sites

Ok so I found the slow queries logs, there is one particular query that is repeated more than any other, and its a default query that comes with mccodes and is in the header.php:

 

$db->query("UPDATE `users` SET `laston` = ".$_SERVER['REQUEST_TIME'].",`lastip` = '$IP' WHERE `userid` = $userid",$c);

 

Wouldn't it just be easier to SET laston = unix_timestamp() ?

Yes, that could be set like that. But, best sure to re-write the query string:

Optimally, here we would look for the best and fastest solution for a unix time stamp to be stored.

You can change the laston field to an INT, as it is faster than the date_* columns in mysql. We can let php do the dirty work with time():

$db->query('UPDATE `users` SET `laston` = '.time().', `lastip` = "'.$IP.'" WHERE `user_id` = '.$userid, $c);

 

Or we can use your method:

$db->query('UPDATE `users` SET `laston` = UNIX_TIMESTAMP(), `lastip` = "'.$IP.'" WHERE `user_id` = '.$userid, $c);

 

Post up your slow queries, and I will be glad to assist in making them more efficient.

Link to comment
Share on other sites

laston is already INT(11). I did change it to use time() so hopefully that will help. Honestly I'm thinknig its something else that is causing the queries to run slow. One of the queries in the log:

	$db->query("UPDATE `events` SET `evREAD` = 1 WHERE `evUSER` = $userid");

Which just to try and optimize a bit I changed to:

	$db->query("UPDATE `events` SET `evREAD` = 1 WHERE `evREAD` != 1 AND `evUSER` = $userid");

This is a pretty simple query though and I don't see how it would cause a problem like this, I'm wondering if maybe my users table is just too big so it causes other queries to lag out as well, it has 264 columns.

Edited by Smokey
Link to comment
Share on other sites

laston is already INT(11). I did change it to use time() so hopefully that will help. Honestly I'm thinknig its something else that is causing the queries to run slow. One of the queries in the log:
	$db->query("UPDATE `events` SET `evREAD` = 1 WHERE `evUSER` = $userid");

Which just to try and optimize a bit I changed to:

	$db->query("UPDATE `events` SET `evREAD` = 1 WHERE `evREAD` != 1 AND `evUSER` = $userid");

This is a pretty simple query though and I don't see how it would cause a problem like this, I'm wondering if maybe my users table is just too big so it causes other queries to lag out as well, it has 264 columns.

still having problem ?

thats a mysql limitation, but you may need to just add db indexes(http://dev.mysql.com/doc/refman/5.0/en/create-index.html) if your queries are taking so long that 30 of them are processing at the same time (unless you have thousands of users on at once)

help link :- https://dev.mysql.com/doc/refman/5.0/en/user-resources.html

you could release your connection as soon as you're finished with it, you could perform faster queries, you could raise your limit on the mysql side, there's no easy debugging that in a jiffy

Edited by rockwood
Link to comment
Share on other sites

still having problem ?

thats a mysql limitation, but you may need to just add db indexes(http://dev.mysql.com/doc/refman/5.0/en/create-index.html) if your queries are taking so long that 30 of them are processing at the same time (unless you have thousands of users on at once)

help link :- https://dev.mysql.com/doc/refman/5.0/en/user-resources.html

you could release your connection as soon as you're finished with it, you could perform faster queries, you could raise your limit on the mysql side, there's no easy debugging that in a jiffy

I am not sure if you even understand what the issue is here.

You keep referring him to his database. The database alone is fast and should have no impact on the amount of users he has in there. Indexing can be beneficial, but to what extent? If you don't know how to do it properly, leave it, or you could make it result in being slower.

The issue here seem's to be the way php is handling the db queries.

 

@Smokey, 264 columns or 264 users in your users table? 264 columns is quite a lot if you ask me. So each time, it has to pull 264 columns, and knowing how MCCode uses the * for every mysql query, that could be one of your issues.

Your best bet right now, would be to put a slow query logging system onto your mysql class.

I could assist you further, but that would require further access. Until then, we are going to be throwing guesses back and forth.

Link to comment
Share on other sites

It's 264 columns, only 1577 rows(users) and about 10 or so of those are deleted. I'm thinking what I may do, it can't hurt anyways, is take the last 50 columns or so (1 at a time) and move them to another table then change the necessary files to reflect the table change (luckily those columns will only point to a couple files each if that), creating a 2nd users table and using this table for all future users table columns. It will take me some time to do I suppose.

I'm not sure how to put a slow query log on the mysql class I'll have to research that. I'm a very quick study, almost anything I try to learn, I learn. :P

It seems its the same 2 or 3 queries that keep repeating in my tmp slow query log, updating the events (events table) and laston, lastip (users table).

Link to comment
Share on other sites

The reason the users table has so many columns is because when I first started coding I thought everything pertaining to a users account was suppose to be held in the users table. So a lot of columns were added when adding mods and different things. Now when I build a mod I do my best to keep it out of the users table completely.

Edited by Smokey
Link to comment
Share on other sites

The reason the users table has so many columns is because when I first started coding I thought everything pertaining to a users account was suppose to be held in the users table. So a lot of columns were added when adding mods and different things. Now when I build a mod I do my best to keep it out of the users table completely.

Unfortunately, MCCode's has taught some bad practice coding around here. I too was prune to it, I too added all columns into the users table. I see it still happening today, and that's some website's which don't even know about gaming at all!

Anyways, your idea of moving the columns to a new table sounds right. It should speed it up a bit, it will be a tedious task, and you should do it in a development environment, than on live site.

Glad to have been of assistance, you can PM me any script, and i'll do my best to make it more efficient for you :).

Link to comment
Share on other sites

dragon blade,. stablehost have a set ammount off entry processes ,.

only 10 pages can be loaded from the website at any one time,..

every host seems to have its con with shared plans,.

hostgator great but set limit on cron jobs,.

cant have anything more frequent than 15 minutes on crons

back in the day we never used to have these problems,.. all hosts were simple and not restricted in these ways

muggins c***'s in my opinion,.. but if i ran a host id probably do the same to get more money so,. lool

Link to comment
Share on other sites

But few Cheap too

Would you ****ing quit posting **** that does not concern the OP's issue? Telling them to move host? *** is wrong with you? Are you going to tell him to move host again when the next host is worse? Rather fix the ****! FFS!

Stating that you "promise" the issue won't be resolved by the changes as said. Clearly show's your incompetence.

  • Like 1
Link to comment
Share on other sites

I told you Smokey. BlueHost is rubbish. Change to StableHost! Ask Dave about them!

StableHost is great if you're running 'normal' sites such as Wordpress etc.

Running something server intensive like a game, or a large scale e-commerce site would require dedicated(/virtual) hardware.

In this case you could most likely re-write parts of the engine to resolve the issues as the core of McCodes contains lot of poorly built queries which are just eating away at the connections to 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...