compress SQLITE in place

Posted on
Tue Jun 21, 2016 7:27 pm
kw123 offline
User avatar
Posts: 5998
Joined: May 12, 2013
Location: Dallas, TX

compress SQLITE in place

is there a way to compress the SQLITE3 database while indigo is running?

I am at 45+ GBYTE now and things are getting slow (Sql plugin takes 15 minutes to finish at startup)

Old data has been pruned, but the size has not changed, as expected. Can I do a "vacuum" command while indigo is running?

Or I could do a database dump and rebuild, but that will take many hours, but I would like like to loose several hours of data.

thanks

Karl

Posted on
Tue Jun 21, 2016 8:27 pm
jay (support) offline
Site Admin
User avatar
Posts: 14872
Joined: Mar 19, 2008
Location: Austin, Texas

Re: compress SQLITE in place

According to this website:

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy.

[SNIP]

The VACUUM command will fail if there is an active transaction.


So sounds like it has to be shut down. I suspect you could start a new db and let it run while you're compressing the old one. Then merge the old one back in to the new one.

If you really need a lot of historical data, I'd recommend that you de-normalize the transactional data in the logger db into something more optimized for the kinds of data mining you want to do.

Alternatively, switch to PostgreSQL which is likely to do much better with larger data sets, and probably has better tools for truncating tables once you're done with the data.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest

cron