Discussion Forums  >  Self Hosted Control Panels

Replies: 9    Views: 106

Caslor
Android Fan
Profile
Posts: 191
Reg: Jul 26, 2013
athens
1,910
02/13/14 03:31 AM (11 years ago)

slow MySQL queries (Server canceled)

Hi i am hosted to a free webhost server i am working my app for one month now without any major problem (still building it) yesterday as i was almost finished i make 4 copies with the app copier tool from chris my app has 520 screens and 590 items So after that i am getting this message from the host company "We have detected that your account executing a slow MySQL queries. Slow MySQL queries are not allowed, because you share server resources with thousands of other users and your long lasting MySQL queries are slowering the server and other users are having MySQL performance and connectivity problems. Please optimize your MySQL queries as soon as possible." i went to "PhpmyAdmin" control panel and i find that have almost 6.000 "bt_ api _ requests" and 2.000 "bt_ items" ok the last one is the applications screens i guess.. (4 apps x 500screens ) what is safe to empty from there and dont ruine my app? Thanks in advance
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
02/13/14 04:16 AM (11 years ago)
"BT_items" could be considered an artery. Do not delete or modify anything in there. API requests on the other hand, are records of the device communication. You can delete those. The way it (sort of) works is, MySQL (and your BT database) is a relational database. It consists of many 'tables', and within those tables are 'records'. Here is a basic summary of the tables in your (typical) BT Database. I'm leaving off the prefix; mine is different than default. cpBackup - Control Panel Backup. I 'think' it's changes to your selfhosted control panel config. I wouldn't mess with it just in case. There shouldn't be that many records anyway. api_keys - these are your App "identification" numbers for BT. Do not alter or delete these. api_requests - these are requests from devices to the server. Config updates, 'phone home' procedures, etcetera. You may want to back these up so you can use the data, but for the most part, you can delete these. But the data could be valuable 'to your business' so go ahead and back them up locally just in case. apn_devices - these are your registered push thingys. I don't have any, but I wouldn't touch it if you do. Push is hard enough without cutting something important out of your database. apn_queue - this is a list of messages pushed, or waiting to be pushed, to your devices in the wild. Depending on the status, you can probably safely delete these, but similar to api_requests, the data could be valuable to your business, so you might want to back them up first. applications - the 'master' application record, containing the kind of info at the top of your BT config file… do not delete or modify any of these. app_users - if you have users for devices. You probably want to leave this one alone. config - the server config. Don't modify or delete any of these. cp_links - control panel links. controls how your BT looks/acts. Don't modify or delete any of these. files - a list of your BT app resources (images, files, etc), and their locations (on the server). You probably want to leave these alone, unless you have a really good reason to delete them. items - a list of all of your application configurations, in one huge table. Screen definitions, menu items, plugin details, the works. This could be the most important table in the database, except too many are tied together, and they're ALL important. plugins - a list of installed plugins. Used to create screen definitions that will be placed in 'xx_items' when you add something to the app. Don't touch this. users - control panel users. Might want to leave this alone too. About the only one you can safely delete records from is api_requests. The rest should be left alone, and backed up periodically. Cheers! -- Smug
 
Caslor
Android Fan
Profile
Posts: 191
Reg: Jul 26, 2013
athens
1,910
like
02/13/14 06:19 AM (11 years ago)
Thanks :) but they told me that as they canceled it.. it cant do anything about... good for me i have downloaded yesterday the sql So i have to upload it to a new server :( 2 months of work hope not to be lost :( :( :( now i am trying to find a way to upload my existing saved sql to a new server :( hope this is something that can be done...
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
02/13/14 07:04 AM (11 years ago)
If it's a full backup, you should have no problem. Sorry to hear about your host, but with Free there comes a bit of inconvenience… Hope you get that fixed soon! Look into Hostgator for BT Self Hosting; they're quickly becoming the host of choice around here. Cheers! -- Smug
 
Caslor
Android Fan
Profile
Posts: 191
Reg: Jul 26, 2013
athens
1,910
like
02/13/14 07:07 AM (11 years ago)
yes it was full backup i have two files one .tar.gz 34mb that has inside all the files and one just 500kb .sql.gz
 
ATRAIN53
Code is Art
Profile
Posts: 1755
Reg: Nov 17, 2011
Chicago
26,450
like
02/13/14 07:46 AM (11 years ago)
^ smart man to backup your files, esp with free hosting. If you need to go the free route - check out an Amazon EC2 setup. You get 1 year free and you'll be in more control of your 'server instance' than any other free host. After the 1st year I'm estimating it would be about $40-70 a month to run the server (they base it on usage), but maybe after your first year you'll reap some income and move to another option. As long as you're on the topic here regarding slow SQL Queries and BT Server- I've got someone using GoDaddy hosting with BT setup and they are experiencing a lot of slow downs and issues with having 2 users accessing the server. It's still a 2.1.9 setup that I'd like to migrate over to 3.0 - but I'm waiting to recommend they do that until BT Server works with PHP 5.4. They are getting a lot of An error ocurred in the db_connect() method in utilityFunctions.php (2) which is a fail in the //Db Process method in that php script. I suspect it's has more to do with multi users making requests to the server at the same time. Since the BT Server really isn't documented or has any verbage about multi users - do we know if it is really capable of handling simultaneous users making SQL requests? I'm wondering if it needs to be tweeked with a SET MULTI-USER query?
 
Caslor
Android Fan
Profile
Posts: 191
Reg: Jul 26, 2013
athens
1,910
like
02/13/14 08:02 AM (11 years ago)
yes the reason that i am not moving to a paid solution is to wait to see some results from my app/apps sure the best is to be in a paid server. about the slow SQL Queries i didnt have any problem as i was working in my only app (i was also and the only user of the server) my app had 590 screens when i coppied it 3 times in order to use different app for free users in android and ios then the problem started. i had 6.000 in "bt_ items" so every time i was working to the app... server was looking to the 6.000 entries to find the one i was asking.. ps: i was lucky that backup it yesterday.....very big app in order to start it over again
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
02/13/14 04:46 PM (11 years ago)
Actually it doesn't work 'quite' that way… The BT Database is largely just an information 'holder' that contains details about your app. Let's say you have 10 apps, with 100 screens each. This means you'll have 1000+ items in your 'XX_items' table. One for each screen, plus you'll have additional 'item' records for menu items, and other ancillary 'function' configurations. When your 'BT App' gets launched and the app 'phones home', it checks the XX_applications part of the database to see when the last update was performed/applied. If it thinks there is a new configuration for it to use, it will query the database based on the applicaition identifier, and will only pull records for *that* app (100 screens, and a few menu items… based on your app id) and not the entire database. However, the point is moot; you're not using that service anymore… But when you locate another good hosting provider, the same concepts will apply. Cheers! -- Smug
 
Niraj
buzztouch Evangelist
Profile
Posts: 2943
Reg: Jul 11, 2012
Cerritos
37,930
like
02/13/14 09:49 PM (11 years ago)
Whoa, Smug! Nice explanation of the database! Did that get into the BT Docs? -- Niraj
 
Susan Metoxen
buzztouch Evangelist
Profile
Posts: 1706
Reg: May 01, 2011
Hopkins, Minnes...
26,260
like
02/26/14 07:06 AM (11 years ago)
Nice explanation, Smug! Thank you!
 

Login + Screen Name Required to Post

pointerLogin to participate so you can start earning points. Once you're logged in (and have a screen name entered in your profile), you can subscribe to topics, follow users, and start learning how to make apps like the pros.