rockwood Posted August 13, 2013 Share Posted August 13, 2013 what is url link ? Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 13, 2013 Share Posted August 13, 2013 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). Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 14, 2013 Author Share Posted August 14, 2013 (edited) 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 August 14, 2013 by Smokey Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 14, 2013 Share Posted August 14, 2013 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. Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 14, 2013 Author Share Posted August 14, 2013 (edited) 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 August 14, 2013 by Smokey Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 15, 2013 Share Posted August 15, 2013 (edited) 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 August 15, 2013 by rockwood Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 15, 2013 Share Posted August 15, 2013 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. Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 15, 2013 Share Posted August 15, 2013 (edited) * is doing what ? its not for db problem ?? if you can do this vi /etc/my.cnf "mysql -e "set global max_connections = 700;"" and you should optimize your queries yes. But you can also run the command i gave u Edited August 15, 2013 by rockwood Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 15, 2013 Author Share Posted August 15, 2013 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). Quote Link to comment Share on other sites More sharing options...
Seker Posted August 15, 2013 Share Posted August 15, 2013 It's 264 columns But.... Why? /nonconstructivecomment Quote Link to comment Share on other sites More sharing options...
sniko Posted August 15, 2013 Share Posted August 15, 2013 But.... Why? /nonconstructivecomment Easier to leech off the 'predefined' $ir, than select and store in memory yourself :? Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 15, 2013 Author Share Posted August 15, 2013 (edited) 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 August 16, 2013 by Smokey Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 16, 2013 Share Posted August 16, 2013 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 :). Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 16, 2013 Author Share Posted August 16, 2013 Yea I will definitely do it on my dev site and move it over. We'll see how that goes and I'll post up how well it worked out when I'm done, and thanks for your help Haunted Dawg. Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 16, 2013 Share Posted August 16, 2013 promise from my side you can't remove this error without change Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 16, 2013 Author Share Posted August 16, 2013 Who said I wasn't going to change anything? I'm going to lower the amount of columns in the users table...that is change. Quote Link to comment Share on other sites More sharing options...
Dragon Blade Posted August 16, 2013 Share Posted August 16, 2013 I told you Smokey. BlueHost is rubbish. Change to StableHost! Ask Dave about them! Quote Link to comment Share on other sites More sharing options...
jedigunz Posted August 17, 2013 Share Posted August 17, 2013 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 Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 17, 2013 Share Posted August 17, 2013 my suggestion :-buy a nice VPS Quote Link to comment Share on other sites More sharing options...
jedigunz Posted August 17, 2013 Share Posted August 17, 2013 VPS is expensive though !!,. alot more than a shared host ,. Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 17, 2013 Share Posted August 17, 2013 But few Cheap too 1 Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 19, 2013 Share Posted August 19, 2013 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. 1 Quote Link to comment Share on other sites More sharing options...
Dave Posted August 19, 2013 Share Posted August 19, 2013 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.