Smokey Posted May 20, 2013 Share Posted May 20, 2013 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? Quote Link to comment Share on other sites More sharing options...
Spudinski Posted May 20, 2013 Share Posted May 20, 2013 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. Quote Link to comment Share on other sites More sharing options...
Smokey Posted May 20, 2013 Author Share Posted May 20, 2013 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. Quote Link to comment Share on other sites More sharing options...
Spudinski Posted May 20, 2013 Share Posted May 20, 2013 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* Quote Link to comment Share on other sites More sharing options...
Smokey Posted May 20, 2013 Author Share Posted May 20, 2013 Partial texts Id User Host db Command Time State Info Kill Kill 18796133 -removed- localhost -removed- Query 0 NULL SHOW PROCESSLIST This is all I get when i run that query Quote Link to comment Share on other sites More sharing options...
Spudinski Posted May 20, 2013 Share Posted May 20, 2013 ...and you're hitting max connections, how? Quote Link to comment Share on other sites More sharing options...
Smokey Posted May 20, 2013 Author Share Posted May 20, 2013 I have no idea, I have been trying to find the source of it for several weeks now lol Quote Link to comment Share on other sites More sharing options...
Dave Posted May 21, 2013 Share Posted May 21, 2013 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. Quote Link to comment Share on other sites More sharing options...
Spudinski Posted May 21, 2013 Share Posted May 21, 2013 I suggest you pay extra for his losses as well. Quote Link to comment Share on other sites More sharing options...
Smokey Posted May 26, 2013 Author Share Posted May 26, 2013 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 Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 6, 2013 Author Share Posted August 6, 2013 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. Quote Link to comment Share on other sites More sharing options...
Dave Posted August 9, 2013 Share Posted August 9, 2013 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. Quote Link to comment Share on other sites More sharing options...
Bennyh789 Posted August 9, 2013 Share Posted August 9, 2013 When you release an 'updated' version Dave will it be free to those who have already purchased this one? Quote Link to comment Share on other sites More sharing options...
Dave Posted August 9, 2013 Share Posted August 9, 2013 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. Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 9, 2013 Author Share Posted August 9, 2013 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. Quote Link to comment Share on other sites More sharing options...
Bennyh789 Posted August 9, 2013 Share Posted August 9, 2013 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? Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 10, 2013 Share Posted August 10, 2013 (edited) 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 August 10, 2013 by rockwood Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 10, 2013 Author Share Posted August 10, 2013 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. Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 10, 2013 Share Posted August 10, 2013 (edited) 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 August 10, 2013 by rockwood Quote Link to comment Share on other sites More sharing options...
Dave Posted August 10, 2013 Share Posted August 10, 2013 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. Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 12, 2013 Author Share Posted August 12, 2013 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. Quote Link to comment Share on other sites More sharing options...
rockwood Posted August 12, 2013 Share Posted August 12, 2013 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 . Quote Link to comment Share on other sites More sharing options...
Barrikor Posted August 12, 2013 Share Posted August 12, 2013 I have bluehost shared That might explain it. EIG moved all the Bluehost and HostGator customers to a small data center and is trying to stuff them onto not enough servers. (I'm in the process of moving my sites from HostGator right now) Quote Link to comment Share on other sites More sharing options...
Smokey Posted August 12, 2013 Author Share Posted August 12, 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. 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? Quote Link to comment Share on other sites More sharing options...
grant Posted August 12, 2013 Share Posted August 12, 2013 its bluehost i had the same issue with them about 2 years ago and i changed to ah vps and never had any issue since then change your hosting 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.