Performance with large database

[mikemack]

Hi,

I have been using your plugin to log most everything in HS, including my 32 channels of energy meter data every 8 seconds. Everything worked fine for a while, but then it got slower and slower to create a chart until it was not working at all. The only thing I could do was to kill the db file and start over and everything is again working fine.

Would it be possible to add an option to the plugin to connect to a remote database like mysql/MariaDB?

If not, maybe you could store the data in a different DB file monthly; of course this would complicate the query logic, but might work around the performance issues.

Thanks,
Mike

[reidfo]

Just curious, what is the size of your DB? I also use this plugin and am pretty obsessive about logging nearly everything. Just about to add 40+ channels of energy monitoring every 8 seconds as well.

[mikemack]

Oops; I left out that important piece of info; The database was about 1.3G when I finally nuked it, but it has been slowing down for the past few months - I am not really sure at what size it started to become noticeably slower.

Mike

[reidfo]

My DB isn't anywhere near that size yet, but sorry to hear it slows down. I was hoping just to let mine grow forever (disk space isn't an issue for me). Since it's a sqlite database you could in theory run something monthly that prunes it and/or moves the data to an external database.

[reidfo]

Also, I have been thinking of making my own plugin that logs to MySQL. Since you mentioned that in your post, what features would you want in that type of plugin? One thing I'm interested in is better graphing with the ability to run comparison graphs (e.g. outside temperature vs. watts used for cooling vs. occupancy).

[shill]

Hi,

I have been using your plugin to log most everything in HS, including my 32 channels of energy meter data every 8 seconds. Everything worked fine for a while, but then it got slower and slower to create a chart until it was not working at all. The only thing I could do was to kill the db file and start over and everything is again working fine.

Would it be possible to add an option to the plugin to connect to a remote database like mysql/MariaDB?

If not, maybe you could store the data in a different DB file monthly; of course this would complicate the query logic, but might work around the performance issues.

Thanks,
Mike

I'm not opposed to providing an option to switch DB back ends; I only went with this because the drivers were already there and it was simple. I'll investigate for a future release!

[waynehead99]

I also use this plugin mainly because its quick and easy to make a graph. Personally I keep every single data change in a separate sql database and only use this plugin with minimal data points for graphing.

I have a script that catches all the device changes each time and puts it into a database. It's kicked off from the HS startup script. From there I can do whatever I want with it and use this to track historical data for any need, but I tried to do graphing and my skills there are lacking.

I too would like to see an ability to use this plugin with an external database. I do the same thing with weatherxml plugin that allows for logging historical data to an external DB.

[macrho]

I'm not opposed to providing an option to switch DB back ends; I only went with this because the drivers were already there and it was simple. I'll investigate for a future release!

Just installed your plugin and so far so good. I'd be very interested in being able to switch DB back ends, any progress here?

[shill]

Just installed your plugin and so far so good. I'd be very interested in being able to switch DB back ends, any progress here?

Not at this time. I've been fighting with the thermostat api and echo integration for the last couple of weeks. As soon as I release that update for my other plugin, I'll start on the next update of this plugin.