Announcement

Collapse
No announcement yet.

Database size question.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database size question.

    How would you guys recommend solving the increasing history database issue? Should it be pruned every 2 months since that's the range of the default graphs? Should the pruning be saved to a new database or deleted?
    -Rupp
    sigpic

  • #2
    ideally, you would develop summary tables for historical data. I guess in the interim, you would just 'delete from <table> where <date> < somevalue' After doing that, you would need to compact the database to remove the 'space' left.

    Are you trying to come up with an interim solution until the plugin is repaired?
    HS3Pro Running on a Raspberry Pi3
    64 Z-Wave Nodes, 168 Events, 280 Devices
    UPB modules via OMNI plugin/panel
    Plugins: Z-Wave, BLRF, OMNI, HSTouch, weatherXML, EasyTrigger
    HSTouch Clients: 3 Android, 1 Joggler

    Comment


    • #3
      I'm trying to come up with a fix to the plugin for the slow graph display issue. Trying to see what users would like to see as a fix.
      -Rupp
      sigpic

      Comment


      • #4
        You might persuade the ticket owner(s) to give you a copy of their SQLite database and run some before/after queries to get an idea of what it would take.
        HS3Pro Running on a Raspberry Pi3
        64 Z-Wave Nodes, 168 Events, 280 Devices
        UPB modules via OMNI plugin/panel
        Plugins: Z-Wave, BLRF, OMNI, HSTouch, weatherXML, EasyTrigger
        HSTouch Clients: 3 Android, 1 Joggler

        Comment


        • #5
          One of the respondents in this thread http://board.homeseer.com/showthread.php?t=148584 suggested creating individual monthly databases.
          This would limit the size of any single database but would make the existing chart query some what more complex. Older data files could be then archived off-line to free up disk space as required. As it stands now, there is no real alternative but to let the database grow forever.

          Comment


          • #6
            That might be the best approach - have the 'online' database contain the smallest amount of data needed for the current graphing capabilities, then monthly databases. HST would then need to develop separate pages/graphs to show the monthly history...
            HS3Pro Running on a Raspberry Pi3
            64 Z-Wave Nodes, 168 Events, 280 Devices
            UPB modules via OMNI plugin/panel
            Plugins: Z-Wave, BLRF, OMNI, HSTouch, weatherXML, EasyTrigger
            HSTouch Clients: 3 Android, 1 Joggler

            Comment


            • #7
              The way I see it is you only need the current month and previous month information for plotting. Any month prior offers Monthly cost History.
              If this infomation was kept in a table - which would be a row for KWH and Cost per each month, this would suffice the historical data.
              Have it operate like a FIFO... while purging the oldest plottable data after a calculation of KWH and Cost.

              Robert
              HS3PRO 3.0.0.500 as a Fire Daemon service, Windows 2016 Server Std Intel Core i5 PC HTPC Slim SFF 4GB, 120GB SSD drive, WLG800, RFXCom, TI103,NetCam, UltraNetcam3, BLBackup, CurrentCost 3P Rain8Net, MCsSprinker, HSTouch, Ademco Security plugin/AD2USB, JowiHue, various Oregon Scientific temp/humidity sensors, Z-Net, Zsmoke, Aeron Labs micro switches, Amazon Echo Dots, WS+, WD+ ... on and on.

              Comment


              • #8
                I was coming into this section to seek info on the growing data size and how to prune it down if it can be. I am over 70 megs at this point and I am on a PRO100 which have limited growth space. I will run out of space for sure and HS will not be happy then.

                So any movement on this? I would love to not only see these file sizes be able to be automatically controlled, but would love for the chart information to work as it should with the long overdue update/fix. (See other thread... http://board.homeseer.com/showthread.php?t=146186 )

                Thank you.

                David

                Comment


                • #9
                  I haven't tried this procedure (I'm at 90meg but the HT2 has space left)!

                  Caveat Emptor

                  I'd look for an SQLite environment which could attach to the database and allow the selective deletion of rows.

                  Perhaps this might do http://sqlitebrowser.sourceforge.net/

                  I don't know if you could run this directly on a PRO100, you may have to copy the database off to another machine to perform the pruning.

                  Always do backups first!

                  Mark

                  Comment


                  • #10
                    Yeah, I would rather HS come up with a solution as it should have already had vs trying to make something work. Not having thought though the file grow was kind of an over site and it just needs to be corrected.

                    Can I delete the file and will it auto re-spawn does anyone know? Or what needs to be done to more or less restart it?

                    Comment


                    • #11
                      Originally posted by David Bott View Post
                      Yeah, I would rather HS come up with a solution as it should have already had vs trying to make something work. Not having thought though the file grow was kind of an over site and it just needs to be corrected.

                      Can I delete the file and will it auto re-spawn does anyone know? Or what needs to be done to more or less restart it?
                      You can delete the database and allow it to recreate but you will loose your month history. I deleted mine and everything loads much faster now.
                      The problem is no 2 people agree on how to manage a database and what data they want to keep so at the present it's up to the user to manage their database. This will be changing in a future release but it may be a while before this release is available.
                      -Rupp
                      sigpic

                      Comment


                      • #12
                        Ok, so I can remove the database file and it will remake itself, but yes, of course no history.

                        My vote...if you can monitor file size...is have a MAX FILE SIZE and let the person set how big to allow it to get and just keep the file pruned back to that max size. (Auto delete oldest data.) Yes, you loose data, but you can control how far you care to go back. I for one really more or less look at this as a tool to see my energy use month to month. So a year is fine for me. But that's just me. I actually use this more when we travel as I can keep an eye on how the home is performing while we are gone. If I see a weird spike, something changed.

                        As of now...And I did not delete...I have no past data anyway it seems and the this month last month cart is not right as you area aware.

                        Month KWH Cost
                        Nov -1 $0.00
                        Dec -1 $0.00
                        Jan -1 $0.00
                        Feb -1 $0.00
                        Mar -1 $0.00
                        Apr -1 $0.00
                        May -1 $0.00
                        Jun -1 $0.00
                        Jul -1 $0.00
                        Aug -1 $0.00
                        Sep -1 $0.00
                        Oct * -1 $0.00

                        Comment


                        • #13
                          Originally posted by David Bott View Post
                          As of now...And I did not delete...I have no past data anyway it seems and the this month last month cart is not right as you area aware.

                          Month KWH Cost
                          Nov -1 $0.00
                          Dec -1 $0.00
                          Jan -1 $0.00
                          Feb -1 $0.00
                          Mar -1 $0.00
                          Apr -1 $0.00
                          May -1 $0.00
                          Jun -1 $0.00
                          Jul -1 $0.00
                          Aug -1 $0.00
                          Sep -1 $0.00
                          Oct * -1 $0.00
                          I wonder if your database is corrupted as this should be working. I would backup your existing database and start a new one and see if this information starts working.
                          -Rupp
                          sigpic

                          Comment


                          • #14
                            Ok, will do.

                            BTW...On a side note...with 70 megs of data on a PRO100, it only took about 4 seconds to pull up the charts from the Plugin's Screen when I click Current Costs. I did not find this to be an issue.

                            Comment


                            • #15
                              "I wonder if your database is corrupted as this should be working"

                              Probably not. On the HT2, there is a 1 in 3 chance that when you call up the chart, it will come up blank and show 0's. If you call it up a 2nd time it will usually be Ok, but not always.

                              I suspect an exception of some sort occurs during the callup and you get 0's.

                              Mark

                              Comment

                              Working...
                              X