compacting sqlite databases for digikam and firefox

[rbr28]

Not sure if this is the proper place, since this isn't Gentoo specific, but I wanted to suggest to any heavy Digikam users, that they use vacuum in sqlite to compact their database on a regular basis. My database is currently at about 8MB, with thousands of photos. After just a month or so of use I notice that the display of thumbnails in an album slows down considerably. Sometimes this is file fragmentation, but more often than not it's the digikam database. I can add/delete hundreds of photos in a week, occasionally in a day, and if I don't vacuum for a month or so I can often notice a performance increase of 10x-30x, just in displaying thumbnails, after a vacuum. I haven't really seen any mention of this in the digikam documentation.

For those not familiar with this, your digikam sqlite database is going to be something like digikam4.db, usually in your album root. Assuming you have sqlite installed just do something like the following

sqlite3 digikam4.db
vacuum;
.exit

(there are several other ways to do this)

Since this made such a difference in digikam performance, I started looking into how much this would affect other things that use small sqlite databases. Firefox uses sqlite databases and you can vacuum those too. I saw up to a 50% reduction in the size of some of my firefox databases after doing this, but honestly didn't notice any big performance improvement like with Digikam. Seems like if there's a 50% reduction in size on disk, this might be somewhat worthwhile to do once a while anyway. Firefox actually has an sqlite manager extension that might make compacting the databases a bit easier for some.

[audiodef]

Sounds like a cool tip - one I might want to use.

What exactly is the vacuum command and how does it work?

[rbr28]

http://www.sqlite.org/lang_vacuum.html

This is the best info I found. It's not all that complete, but gives some idea of what you are doing with the command. As far as using it, the simplest way is as I mentioned previously. You need to have sqlite installed, which is no longer a requirement for digikam, because it has the sql code it needs embedded in the app. If you have sqlite installed, just type sqlite3 in a terminal. Type vacuum; and it usually only takes a second or two to compact the database. Then .exit will get you out of the sqlite command interface. As I said before, there are other ways to do this, you can even do it all in one line, script it, use graphical utilities, etc.

[valordux]

You've got a pretty good idea there. I'm starting to wonder if this would combine well with the "moving the profile to tmpfs" idea. It's located here in case you'd like to read it:
http://forums.gentoo.org/viewtopic-t-71 ... art-0.html

I'll try benchmarking load times this weekend with it.

[nordic bro]

just curious if anyone knows, I've been using reindex and analyze then vacuum on ffox dbs (places, lazarus, etc.) - would vacuum alone be good enough for those? I use pgsql and there I routinely run cluster, analyze/vacuum (but the dbs are multi-gig) so thought I'd just do the same with the ffox dbs but don't know if anything other than vacuum is pointless.