Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
compacting sqlite databases for digikam and firefox
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
rbr28
Tux's lil' helper
Tux's lil' helper


Joined: 09 Feb 2004
Posts: 126

PostPosted: Thu Jun 04, 2009 6:39 pm    Post subject: compacting sqlite databases for digikam and firefox Reply with quote

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.
Back to top
View user's profile Send private message
audiodef
Watchman
Watchman


Joined: 06 Jul 2005
Posts: 5365

PostPosted: Fri Jun 05, 2009 6:40 pm    Post subject: Reply with quote

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

What exactly is the vacuum command and how does it work?
_________________
Gentoo Studio: http://gentoostudio.org
Facebook: http://www.facebook.com/gentoostudio
G+: https://plus.google.com/113947758237122861689/posts
Linux 'Tude Tees: http://moodytudetees.com/index.php/linux-tude-tees
Back to top
View user's profile Send private message
rbr28
Tux's lil' helper
Tux's lil' helper


Joined: 09 Feb 2004
Posts: 126

PostPosted: Fri Jun 05, 2009 10:06 pm    Post subject: vacuum Reply with quote

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.
Back to top
View user's profile Send private message
valordux
n00b
n00b


Joined: 30 May 2009
Posts: 1

PostPosted: Tue Jun 09, 2009 4:28 am    Post subject: Reply with quote

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:
https://forums.gentoo.org/viewtopic-t-717117-postdays-0-postorder-asc-start-0.html

I'll try benchmarking load times this weekend with it.
Back to top
View user's profile Send private message
nordic bro
Guru
Guru


Joined: 25 Oct 2003
Posts: 572

PostPosted: Sun Jul 19, 2009 5:44 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum