Lazy loading data

[ultima-originem]

My database contains the data for 4 sensors with a resolution of 1 min. The page load performance became problematic after a week [currently several minutes]. Adding 5 more sensors over the weekend forced me to start with a new database [without the temperature in F to save space].

Performance issues like this can be avoided by employing asynchronous server loading such as in this example

The default period to load [e.g. one day] should be specified in a configuration file.

[mstenta]

I've run into the same issue with ever-slowing performance. The number one reason is the way that the list of devices is loaded, and this will be greatly improved when #10 is done. I am working on that right now, and I plan to release v0.2.0 when it is ready. It will require database updates to upgrade, though, so keep an eye peeled for the release notes.

There are two other ways to ameliorate the issue:

1. Make sure you have database indices! See README.md manual database setup for the "ADD INDEX" commands. This will ensure that queries are more performant.

2. Limit the amount of data that is loaded to a period of time. I am doing this myself with a simple patch to Atmospi/data.py to limit to a week, but this should be a setting in settings.py. Let's break that out into it's own issue and work on it separately.

In addition to that, there is also the "lazy loading" feature you mentioned, which will allow for data to be loaded quickly, and then automatically reloaded in greater detail as needed. I started laying some groundwork for this a while back, but haven't finished it. Let's use this issue to continue with that feature specifically.

[mstenta]

I renamed this issue to "Lazy loading data" to make it more clear (technically we are already loading data asynchronously... but that can have a few meanings...)

The link you posted is broken... can you find the URL you meant to post again?

Here is the one that I was working from earlier:

http://www.highcharts.com/stock/demo/lazy-loading http://www.highcharts.com/component/content/article/2-articles/news/48-loading-millions-of-points-in-highcharts

[ultima-originem]

Fixed the link! It's the same example as yours.

[mstenta]

Please update to v0.2.0, and be sure to follow the instructions in the release notes: https://github.com/mstenta/atmospi/releases/tag/v0.2.0

You will see a HUGE performance benefit!

I am going to add a setting to settings.py for how much data to load at a time (default to 1 week's worth).

[mstenta]

Boom! https://github.com/mstenta/atmospi/releases/tag/v0.2.1

[ultima-originem]

I did the update as suggested with the following changes:

1. I had to remove any reference to degrees Fahrenheit from the database upgrade code as my production database doesn't have that column [see also #25]
2. I didn't yet port any of my enhancements, so database only as this new Pi doesn't have any sensors connected to it.

Performance:

Raspberry ----- time model ---------- load complete database [14 days; 7,326KiB]

B+ -------------> 80 sec 2 ---------------> 20 sec

So quite an improvement!

[mstenta]

It takes 20 seconds to load the page?? That's too long. Mine loads instantly with the new version (showing only the past week of data). Previously it took me about 20 seconds, and that was about the longest.

Did you run all of the recommended update code? Are the database tables indexed properly? That's critical to get good performance.

[ultima-originem]

The time reported is valid under the following conditions:

1. I set the amount of data to be loaded in the preferences file to 30 days [currently I have 14 days worth of data]
2. I request a page view for ALL data, not just the last 24hrs. If I only request the last 24hrs. the time drops to 9 sec.

I'm certain I've created the indexes:

sqlite> CREATE INDEX temperature_dt ON Temperature(DeviceID, Timestamp); sqlite> CREATE INDEX humidity_dt ON Humidity(DeviceID, Timestamp); sqlite> CREATE INDEX flag_dt ON Flag(DeviceID, Timestamp);

[ultima-originem]

Additional note: timing the performance is tricky. Loading one or 2 weeks data took less than a second the second time. It seems that there's a bit of caching going on.

[ultima-originem]

I'v tried to perform a number of repeatable performance measurements:

On average a 16x improvement. Does this agree with your experience?

I could directly compare my performance with your's if you can get me a test database.

[mstenta]

That's great! Thanks for the benchmarks! They look about right to me. Glad to see those changes sped it up so much! :-)