View previous topic :: View next topic |
Author |
Message |
rbr28 Tux's lil' helper
Joined: 09 Feb 2004 Posts: 126
|
Posted: Thu Jun 04, 2009 6:39 pm Post subject: compacting sqlite databases for digikam and firefox |
|
|
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 |
|
|
audiodef Watchman
Joined: 06 Jul 2005 Posts: 6639 Location: The soundosphere
|
|
Back to top |
|
|
rbr28 Tux's lil' helper
Joined: 09 Feb 2004 Posts: 126
|
Posted: Fri Jun 05, 2009 10:06 pm Post subject: vacuum |
|
|
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 |
|
|
valordux n00b
Joined: 30 May 2009 Posts: 1
|
|
Back to top |
|
|
nordic bro Guru
Joined: 25 Oct 2003 Posts: 585
|
Posted: Sun Jul 19, 2009 5:44 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
|