Thomas Posted March 1, 2012 Report Share Posted March 1, 2012 I've been running phpvms for about 2 years now for Buffalo Airways Virtual, last week our account was suspended as we were using over 25% of the RAM on the whole server! The web host said that there may be too many querys going to the database, the database for phpvms is over 170mb! What would I have to do to disable some querys? Would compressing and re-indexing the database make us use less of the server? If yes, do you know how I can do this? This is the first time we have used over our 5% allocated RAM space in 2 years. Really hope you can help me with this, I've looked high and low for answers and having no luck, I really want to get the website back up and running for our pilots more than anything. Thomas. Quote Link to comment Share on other sites More sharing options...
Tom Posted March 1, 2012 Report Share Posted March 1, 2012 170mb for phpVMS alone? Are you sure there's not something else in there? You can optimise your tables by clicking the Operations tab within a table in phpMyAdmin and then clicking the Optimize table link - not sure how much help that will be, but give it a go Quote Link to comment Share on other sites More sharing options...
Moderators Kyle Posted March 1, 2012 Moderators Report Share Posted March 1, 2012 What the! What kind of queries are going though the database? With phpVMS, there isn't too much queries unless they were executed. It's hard to tell what going on with the queries.Do you have any add-ons you use? BTW, who's host you with and are you on shared? Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 Tom; Yes, That's exactly my thought! Will give the optimisation a go. Thank you! Kyle; The addons I use are Top Pilot, Pilot Manager, Hub Stats, Status Badges, Events & Tours. Probably one or two more but I can't remember. Thomas. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 1, 2012 Administrators Report Share Posted March 1, 2012 Who's your host? I'm suspecting they're actually limiting you quite a bit. How many airlines/pilots/PIREPs on average? For 170MB, must be quite a few. Actually, if you're running the release version, there is one *really* expensive calculation done when PIREPs are filed, but doesn't become a problem until you have quite a few PIREPs being filed. The problem came down to how the time was being calculated - and it's quite slow since it was essentially a string-compare function. It's been fixed in the latest betas. I've tried to find some of the commits where this slow code was fixed, but honestly the better way to check would be to upgrade to the latest beta, since it's almost a hodge-podge of fixes. https://github.com/n...717e136cb7d58b7 https://github.com/n...6ca1c701#L0R236 (updateTotalHours) But those small changes made a HUGE difference both CPU and RAM usage. It's also funny that these commits are from almost a year ago... I need to get this next version out the door :\ Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 This was the error seen on the website just before everything went ti** up! Fatal error: Uncaught Last Error --[user flythere_bufmain already has more than 'max_user_connections' active connections (1203)] [] thrown in /home/flythere/public_html/buffaloairwaysvirtual.com/core/classes/ezdb/ezdb_mysql.class.php on line 99 Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 www.eclipse2000hosting.com 1 Airline, Over 330 Pilots, Over 11000 PIREPS - At least 50 submitted in a day. I will optimise the database tables and also upgrade to the latest beta, Will reply here in about 30 minutes once everything is done. Thomas. Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 Have about 600 schedules too. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 1, 2012 Administrators Report Share Posted March 1, 2012 This was the error seen on the website just before everything went ti** up! Fatal error: Uncaught Last Error --[user flythere_bufmain already has more than 'max_user_connections' active connections (1203)] [] thrown in /home/flythere/public_html/buffaloairwaysvirtual.com/core/classes/ezdb/ezdb_mysql.class.php on line 99 This sounds more like they're now allowing more than a few database connections, and not necessarily a phpVMS problem - you just have too much traffic. Can you ask them how many user connections to MySQL are allowed? Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 So how exactly can I cut down the traffic? Bearing in mind I've been running the database about 2 years and this is the first time i've had this problem and never had to do anything like this before. I've asked and will post the reply when they respond. Appreciate the help guys Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 Max users is 20 per database at a time. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 1, 2012 Administrators Report Share Posted March 1, 2012 You can't really cut down traffic, just means your VA is growing. You'll have to find a better host. You've outgrown them, be happy Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 So what about fivedev then? Will I have enough bandwith and storage space? To give you an idea, I used about 300GB Bandwith one month, yes the site does get a lot of hits, no exageration. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 1, 2012 Administrators Report Share Posted March 1, 2012 Shoot me an email, let's talk Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 1, 2012 Author Report Share Posted March 1, 2012 Sent, Thanks. Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted March 1, 2012 Moderators Report Share Posted March 1, 2012 Thomas, shoot your host out the window mate, you are being restricted, if you do a reverse ip check on your server you will probably see there is about 300 sites on there which is why your being restricted. Nabeel has the solution, speak with him as fivedev is tuned for phpVMS. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 1, 2012 Administrators Report Share Posted March 1, 2012 IMO, he's too big for any shared host - probably best for him to move to a VPS. We'll figure something out Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted March 2, 2012 Report Share Posted March 2, 2012 My 2 cents - This was more than likely due to the recent problems at VACentral. We also had our account limited and we are on a Cloud system. Rackspace informed us that we had thousands of sleeping connections to the DB and it was actually causing issues elsewere. Once we disconnected from VACentral (including the fuel look up) our sleepers disappeared. We were then restored full access. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 2, 2012 Administrators Report Share Posted March 2, 2012 My 2 cents - This was more than likely due to the recent problems at VACentral. We also had our account limited and we are on a Cloud system. Rackspace informed us that we had thousands of sleeping connections to the DB and it was actually causing issues elsewere. Once we disconnected from VACentral (including the fuel look up) our sleepers disappeared. We were then restored full access. Really? That's strange, vacentral wouldn't be hooking to the database, though I could see that the processes would be timing out. I put on the front-page some tips to disable vaCentral for now from within phpVMS to keep that from happening. I also need to implement a short 2/3 second time-out for that vaCentral connection; I think it's 90 seconds by default in PHP... eek. Quote Link to comment Share on other sites More sharing options...
piuozorio Posted March 2, 2012 Report Share Posted March 2, 2012 IMO, he's too big for any shared host - probably best for him to move to a VPS. We'll figure something out Hello, i dont know if i understand. I'm in shared host(i think), i just have one database and my phpvms database have 193MB, the full website + forums + phpvms have 463MB non gzipped. All is working good, no problems or warnings with the webhost. My question is, do you think one day i'm gonna have problems relative to the size of database? and have to move to VPS? or it depends from webhost to webhost? Thank you i'm glad phpvms is on again Quote Link to comment Share on other sites More sharing options...
Moderators Kyle Posted March 2, 2012 Moderators Report Share Posted March 2, 2012 Hello, i dont know if i understand. I'm in shared host(i think), i just have one database and my phpvms database have 193MB, the full website + forums + phpvms have 463MB non gzipped. All is working good, no problems or warnings with the webhost. My question is, do you think one day i'm gonna have problems relative to the size of database? and have to move to VPS? or it depends from webhost to webhost? Thank you i'm glad phpvms is on again Depending on your hosting's limits, how busy is your site. Once your hosting starts emailing you saying that you might have to move up a plan. But I think you should be okay for a while, but I can't tell what's your hosting's limits are like. Shared Hosting has some serious limits to per database, ours at Sun Country Virtual we're at 11MB right now and we have a limit up to 1GB. The SCX staff team already has laid out a plan for a VPS or Dedicated. (IMO, it's always better to be prepared) Really? That's strange, vacentral wouldn't be hooking to the database, though I could see that the processes would be timing out. I put on the front-page some tips to disable vaCentral for now from within phpVMS to keep that from happening. I also need to implement a short 2/3 second time-out for that vaCentral connection; I think it's 90 seconds by default in PHP... eek. Now's that a new stuff I learnt today...the first day phpVMS and vaCentral was down, I was logging into my site and it was still loading, and I thought what the heck is going on. 1 Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted March 2, 2012 Report Share Posted March 2, 2012 Really? That's strange, vacentral wouldn't be hooking to the database, though I could see that the processes would be timing out. I put on the front-page some tips to disable vaCentral for now from within phpVMS to keep that from happening. I also need to implement a short 2/3 second time-out for that vaCentral connection; I think it's 90 seconds by default in PHP... eek. What we found out was is looks like the DB::close() was never being run due to the script timing out. This left the connection hanging. When Rackspace found the issue they cleared all of the sleepers and then came right back up. We shutoff VACentral which reduced the sleepers quite a bit. But there were a few still hanging around. I tracked it to the fuel update on pirep filing. Once the fuel update was shut off the sleepers disappeared. For the future we are planning on running a cron for Vacentral and fuel updating. Removing the calls entirely from the normal scripts. Side Note - Our DB is 1.7 GB. And we have no issues now. Quote Link to comment Share on other sites More sharing options...
Moderators mark1million Posted March 2, 2012 Moderators Report Share Posted March 2, 2012 Its interesting how certain issues arise when the database is big, we live and learn and then we improve All makes for better software. Quote Link to comment Share on other sites More sharing options...
Administrators Nabeel Posted March 2, 2012 Administrators Report Share Posted March 2, 2012 Hello, i dont know if i understand. I'm in shared host(i think), i just have one database and my phpvms database have 193MB, the full website + forums + phpvms have 463MB non gzipped. All is working good, no problems or warnings with the webhost. My question is, do you think one day i'm gonna have problems relative to the size of database? and have to move to VPS? or it depends from webhost to webhost? Thank you i'm glad phpvms is on again Yeah, eventually you will have a problem. I recommend a VPS over a dedicated server tbh. What we found out was is looks like the DB::close() was never being run due to the script timing out. This left the connection hanging. When Rackspace found the issue they cleared all of the sleepers and then came right back up. We shutoff VACentral which reduced the sleepers quite a bit. But there were a few still hanging around. I tracked it to the fuel update on pirep filing. Once the fuel update was shut off the sleepers disappeared. For the future we are planning on running a cron for Vacentral and fuel updating. Removing the calls entirely from the normal scripts. Side Note - Our DB is 1.7 GB. And we have no issues now. Ahh, that's interesting. I need to see if that's a known bug (where connections remain open on a time-out) - but do you have control over your MySQL config files? I believe there's an "open time" in the my.cnf file for connections that are supposedly idle, probably turning that down is a good idea. There are a number of MySQL optimizations you can do; my favorite script for figuring some of these things out is the rackerhacker mysqltuner scripts (originally from rackspace, actually). These help if you can control the configuration of MySQL. On fivedev, I run this quite often to keep the database in tune based on average usage, and it works quite well. I tried to index columns the best I could; the majority of your database size is probably from the size of those indexes. But greater indexes means greater CPU to process and keep them up to date. Having a cron for vaCentral is the best idea, and should be relatively straight-forward to do. 1 Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 2, 2012 Author Report Share Posted March 2, 2012 Site sorted guys. Thank you very much for the help and replys! Lets hope this never happens again. Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 3, 2012 Author Report Share Posted March 3, 2012 Right guys, another couple of error's were having since the switch over... Number 1 happens when importing schedules or adding them manually... BFL9082 was not added, reason: Unknown column 'week1' in 'field list' BFL9083 was not added, reason: Unknown column 'week1' in 'field list' BFL9084 was not added, reason: Unknown column 'week1' in 'field list' BFL9085 was not added, reason: Unknown column 'week1' in 'field list' The import process is complete, added 0 schedules, updated 4, for a total of 0 Number 2 happens on BFL ACARS (kacars)... NOTIFY SYSTEM ADMINISTRATOR OF THE SWITCH ERROR DATA NOT RECIEVED SWITCH = VERIFY Please help Quote Link to comment Share on other sites More sharing options...
Moderators joeri Posted March 3, 2012 Moderators Report Share Posted March 3, 2012 the first is probably because you where running the beta version and you now installed the full version. second probably means you have version mismatch between your program and the version running on your server 1 Quote Link to comment Share on other sites More sharing options...
Thomas Posted March 3, 2012 Author Report Share Posted March 3, 2012 Thanks Joeri. So any idea how I can resolve these issues? Would like to get the site back up and running ASAP. Quote Link to comment Share on other sites More sharing options...
Moderators Kyle Posted March 4, 2012 Moderators Report Share Posted March 4, 2012 Right guys, another couple of error's were having since the switch over... Number 2 happens on BFL ACARS (kacars)... NOTIFY SYSTEM ADMINISTRATOR OF THE SWITCH ERROR DATA NOT RECIEVED SWITCH = VERIFY Please help Since you have the Custom kACARS, do you have the module from your custom kACARS Package? Be sure to upload it into core/modules. Because I went directly to the module, and it tells me the module isn't uploaded. Also, next time if you are having issues with your custom kACARS, please let the developer know in email. I don't know what's going on with your number 1 issue. Gotta leave that for the experts. Quote Link to comment Share on other sites More sharing options...
Moderators joeri Posted March 4, 2012 Moderators Report Share Posted March 4, 2012 the first issiue you probably done an export off your schedulles then installed a new phpvms elsewhere but this version isn't the same as the version you had on your first install and thus not having all the tables 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.