Database size question.

[Rupp]

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?

[rmasonjr]

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?

[Rupp]

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.

[rmasonjr]

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.

[Mark42]

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.

[rmasonjr]

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...

[langenet]

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

[David Bott]

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

[Mark42]

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

[David Bott]

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?

[Rupp]

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.

[David Bott]

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

[Rupp]

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.

[David Bott]

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.

[Mark42]

"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