MySQL Database setup for Stand Alone Dashboard

Post any 3rd party software here.
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Tue Aug 04, 2015 9:13 am

shortwh wrote:How is it going with MYSQL? shortbg ended up leaving the database as sqlite but now that 6months have gone by the database is huge and entering pages is very slow. We are still wondering if the MYSQL route would make the usage with large databases better. Also, it seems that there is a lot of file IO on SQLITE version for GEM pushes.
MySQL will work a lot better with larger databases. We used SQLite as its much easier to manage for the average user (its just a flat file).
Ben
Brultech Research Inc.
E: ben(at)brultech.com
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Sat Aug 15, 2015 6:06 pm

well, we took the code and have tried it on CentOS 5.11, CentOS 7 and haven't had any luck. The CentOS 7 has mariaDB and the sql create script worked fine. However, the Apache version is newer and the website is dying for what looks like a relative path problem. The CentOS 5.11 had to have the SQL create script reworked but after that it seems to work at first but the data didn't change and most of it looked invalid. Investigation shows that the incoming packet push (which is actually a HTTP GET) is being mishandled but some form of the data is making it to the database every minute. Also, it seems if we can get the CentOS5.11 to insert the data correctly the rest of the webpages might work ok.
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Sat Aug 15, 2015 10:51 pm

Ok, the biggest problems actually are that CentOS7 is too new for the php scripts.
CentOS5.5 seemed to work with some changes to the SQL initializer.
1. Watt-sec fields need to be "bigint(16)"
2. PAGE_CHECKSUM which works in mariaDB needs to be CHECKSUM in this version of MySQL
We are trying the first run now. Tomorrow will try to import old SQLite data.

**update**
Watt-second makes it to the database ok now but there are four fields at the end of each minutedata push that are not making it correctly on elements with large watt-second fields.
We have found some "if" statements that are checking ranges and are zero'ing out the values because sometimes the logic doesn't have the previous watt-second field setup so the value is very large and removed because of that. At this point it appears that the databases other than SQLITE haven't been maintained in so long that they just don't work with large watt-second values which are very likely since that field is a 5byte integer (40bit).

Ben,
Any help would be appreciated as this is really becoming hard to solve. Is the Dashbox using any of this code? I noticed that one of the other database modules already had the "bigint" issue updated. Would we have better luck with a different database than mysql? Can you tell me what the Dashbox is using for a database? I noticed I couldn't just unzip the Dashbox firmware to see how it is doing things so I'm sure that was on purpose. We would be glad to push the changes once it's working so maybe you could have something for you time.
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Mon Aug 17, 2015 9:45 am

shortwh wrote: **update**
Watt-second makes it to the database ok now but there are four fields at the end of each minutedata push that are not making it correctly on elements with large watt-second fields.
We have found some "if" statements that are checking ranges and are zero'ing out the values because sometimes the logic doesn't have the previous watt-second field setup so the value is very large and removed because of that. At this point it appears that the databases other than SQLITE haven't been maintained in so long that they just don't work with large watt-second values which are very likely since that field is a 5byte integer (40bit).

Ben,
Any help would be appreciated as this is really becoming hard to solve. Is the Dashbox using any of this code? I noticed that one of the other database modules already had the "bigint" issue updated. Would we have better luck with a different database than mysql? Can you tell me what the Dashbox is using for a database? I noticed I couldn't just unzip the Dashbox firmware to see how it is doing things so I'm sure that was on purpose. We would be glad to push the changes once it's working so maybe you could have something for you time.
We use Postgres with the DashBox. Mainly because MySQLs licensing isn't too friendly if you're not self-hosting. The standalone software is just a simple version of what the DashBox offers for those who want to do basic monitoring (feel free to build on-top of it however). They won't be merged as the DashBox is a premium product.

Bigint should work fine, it's an 8 byte field.

Check the /btech/logs folder. Any errors should be logged there. If not, check your MySQL log folder. If anything appears in there post it here and I'll see if I can figure it out.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Mon Aug 17, 2015 9:51 am

If you're using decimal, try switching to float or double. A quick search shows that decimal may not treat numbers with scientific notation properly, which PHP sometimes outputs.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
jcowens
Posts: 5
Joined: Mon Jul 20, 2015 2:24 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by jcowens » Mon Aug 17, 2015 10:30 am

A couple of days ago my main channels started reporting 0 energy used. Found out they maxed out the Watt-sec fields. I changed all "INT" fields to "BIGINT" and its running ok again.
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Tue Aug 18, 2015 8:04 am

jcowens: Let us know what happens when you cross the 4GB threshold. My watt-sec values on the trouble making fields are around 32000000000.

Ben: We will look into the float/double as I noticed that SQLITE logging debug contained values in scientific notation. At this point maybe it would just be easier to buy the Dashbox. How many years of data can it handle?
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Tue Aug 18, 2015 1:41 pm

shortwh wrote:jcowens: Let us know what happens when you cross the 4GB threshold. My watt-sec values on the trouble making fields are around 32000000000.

Ben: We will look into the float/double as I noticed that SQLITE logging debug contained values in scientific notation. At this point maybe it would just be easier to buy the Dashbox. How many years of data can it handle?
The newer DashBoxes support up to 2 years of minute data for a single GEM, indefinite hour/day data if you don't mind maintaining the minute data (we're improving this as you can now trim minute data by the day instead of having to clear it all).

The Micro-SD card holding your data is now exposed also through a slit in the case, we're working on a method to swap in a new one.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Sun Aug 23, 2015 9:43 pm

Well, we have it working. I missed the WattSecond values that were stored in "channel" table. Once they were converted to bigint it all seemed to fall into place. I no longer believe that dashboard lite will not work with the newer CentOS7 and plan to try that again. The other problems seemed to be permissions issues that I caused in the html directory structure.

The site seems very fast(with no history) but only time will tell as I will need 8-12months of data into the database before we make any speed judgements.
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Mon Aug 24, 2015 3:41 pm

shortwh wrote:Well, we have it working. I missed the WattSecond values that were stored in "channel" table. Once they were converted to bigint it all seemed to fall into place. I no longer believe that dashboard lite will not work with the newer CentOS7 and plan to try that again. The other problems seemed to be permissions issues that I caused in the html directory structure.

The site seems very fast(with no history) but only time will tell as I will need 8-12months of data into the database before we make any speed judgements.
Shouldn't speed down much, if at all, with proper indexing. SQLite tends to get slower as the auto-vacuum/analyzing features aren't too great.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
Post Reply