History.db file size/display call-up time

[Mark42]

After about 9 months of operation, the history.db file is about 54MB on my HT2. The call-up time for the current cost history display varies between 30 and 60 seconds. I don't believe (but have no hard data) the call-up time was anywhere this slow initially; I have to assume the ever increasing size of history.db is slowing down the call-up time.

1. Is there any automatic mechanism in place to restrict the size of history.db?

2. Are there any table optimizations in place (similar to Indices in Oracle)?

3. What call-up times are others seeing?

TIA

Mark

27-April-2011 : Submitted as bug #80

[twa8000]

i have the same issue, sometimes it just times out and shows a blank display page and all the charts just come up "-1"

Sometimes it loads but it can take forever...

-Tom-

[langenet]

While I've never had a timeout in displaying it, like Mark, I've reached just over 55 MB.

Hum... good question....

Robert

[Mark42]

<< sometimes it just times out and shows a blank display page and all the charts just come up "-1">>

I see this as well; I included this problem in my bug report #80. The HT2 is no speed demon, I'd guess the 'timeout' is just due to file size and lack of horsepower. If there are no table optimizations in place (or possible?), the performance will continue to degrade with increasing file size.

Mark

[langenet]

I wish the application would allow for an alternate db application - much like Ultralog. I'd prefer MySql personally.... This would provide better control with an increase in performance.

[Mark42]

I pulled the data into Excel using an SQLite ODBC connection. It appears that the driver is averaging the incoming (6 second) data and making one table update per minute i.e. 1440 entries per day/525600 entries per year.

I believe SQLite supports indexes to speed up data retrieval but, by their nature, the indexes have to be updated after a table modification thus slowing down data insertion.

While a relational database has many good attributes, it is not the database structure of choice (IMO) in a realtime environment especially with limited hardware and software resources.

Hopefully they can address the call-up issues when they other ENVI problems are addressed.

Mark

[Mark42]

As a test I upgraded to 2.5.0.20 today which also requires a restart (my HT2 had been up for 61 days).

The Current Cost History display call-up time is unchanged at 50 seconds.

Mark

[Rupp]

The issue appears to be the data extensive graphs like the one that shows the usage for each month for the year. The more data that is stored the longer this query and graph are going to take to create.

[Ultrajones]

This is an interesting thread. My plug-in (UltraECM) that supports the Brultech Energy Monitor experienced similar issues when dealing with a large amount of data. The databases for UltraECM were reaching over 1.5 GB in size. Even though indexing was properly defined, the queries were taking greater than 30 seconds to return the requested data. I tried everything I could think of to resolve the issue. The problem is that SQLite just does not efficiently deal with large databases. I ended up implement monthly databases. I found keeping 2,880 records per day (one record every 30 seconds) or about 90,000 records per month allowed decent performance for graphing purposes. However, if the user still wanted 1 record per second, the monthly database will store a maximum of 1.5 million records. Using monthly databases also means I don't have to purge records or attempt to run a database vacuum (which can take up to 30 minutes to complete).

Regards,
Ultrajones

[Mark42]

<< I ended up implement monthly databases >>

I believe this (or similar) approach would probably resolve the issue; I don't recall the call-up time being an issue on the HT2 until the history.db file contained 'several' months of data. The display in question only needs access to the last two complete months of data.

30 years ago (i.e. not enough CPU cycles, memory, disk space, or bandwidth - not unlike the HT2), the Honeywell industrial process control realtime history systems maintained multiple time based (flat file) databases. A one minute snapshot database, hourly average, shift average, daily average, and monthly average. Of course once you start averaging you lose data, but that was the compromise that had to be made in those (assembler!) days.

Mark

[langenet]

Has there been any development activity with this issue from the plugin prospective? I would like to suggest to the authors that perhaps a prunning feature be added so that the user can select how long they want to maintain data for... i.e 2 months, 4 months etc.
I started with this plugin last July and am noticing the call up time taking longer and longer as well. What can I expect in July of this year? Will the size the the db be restricted to 12 months?

Additionally, I'm thinking that if/when the individual sensors become available, that once implemented, this issue would get worse.

Any ideas

Edit: Another thought - why not just display the past 2 months and keep historical data in an ini file or something while maintaining the size of the db. This way, we can note the historical consumption which can be called up as part of the web page.

[Mark42]

As far as I can tell no progress has been made on either of the ENVI bugs I have logged into Bugzilla (57 and 80).