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).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 Database setup for Stand Alone Dashboard
-
- Site Admin
- Posts: 4269
- Joined: Fri Jun 04, 2010 9:39 am
Re: MySQL Database setup for Stand Alone Dashboard
Ben
Brultech Research Inc.
E: ben(at)brultech.com
Brultech Research Inc.
E: ben(at)brultech.com
-
- Posts: 25
- Joined: Tue Jan 06, 2015 1:18 pm
Re: MySQL Database setup for Stand Alone Dashboard
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.
-
- Posts: 25
- Joined: Tue Jan 06, 2015 1:18 pm
Re: MySQL Database setup for Stand Alone Dashboard
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.
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.
-
- Site Admin
- Posts: 4269
- Joined: Fri Jun 04, 2010 9:39 am
Re: MySQL Database setup for Stand Alone Dashboard
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.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.
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
Brultech Research Inc.
E: ben(at)brultech.com
-
- Site Admin
- Posts: 4269
- Joined: Fri Jun 04, 2010 9:39 am
Re: MySQL Database setup for Stand Alone Dashboard
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
Brultech Research Inc.
E: ben(at)brultech.com
-
- Posts: 5
- Joined: Mon Jul 20, 2015 2:24 pm
Re: MySQL Database setup for Stand Alone Dashboard
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.
-
- Posts: 25
- Joined: Tue Jan 06, 2015 1:18 pm
Re: MySQL Database setup for Stand Alone Dashboard
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: 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?
-
- Site Admin
- Posts: 4269
- Joined: Fri Jun 04, 2010 9:39 am
Re: MySQL Database setup for Stand Alone Dashboard
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).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 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
Brultech Research Inc.
E: ben(at)brultech.com
-
- Posts: 25
- Joined: Tue Jan 06, 2015 1:18 pm
Re: MySQL Database setup for Stand Alone Dashboard
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.
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.
-
- Site Admin
- Posts: 4269
- Joined: Fri Jun 04, 2010 9:39 am
Re: MySQL Database setup for Stand Alone Dashboard
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.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.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
Brultech Research Inc.
E: ben(at)brultech.com