01 August 2008

How to run VACUUM

As explained in the last post I see no way to automatically run the "VACUUM" command of sqlite which more or less defragments the DB structure. Nonetheless for everyone who wants to run it manually here is how to do it:
  1. Shutdown Liferea
  2. Start the sqlite client by running: "sqlite3 ~/.liferea_1.4/liferea.db"
  3. At the prompt enter: "VACUUM;"
  4. Wait until the prompt reappears.
  5. Restart Liferea
Situations when you might want to VACUUM
  • When the DB file (~/.liferea_1.4/liferea.db) is very large (e.g. >50MB)
  • When you have only a few feeds with a low cache setting (e.g. 30 feeds and 100 items) and believe Liferea to be unreasonably slow.
  • When you have run Liferea for ages.
If you don't know what this is all about: please do not worry about it. In many cases you might not need to do anything.

20 comments:

Maik said...

Any particular reason you're suggesting to run sqlite3 interactively, instead of just giving the VACUUM command on the command line so it will just do it and exit when done?

Vacuuming just shaved 15MB off my DB, by the way.

Lars said...

No, I just didn't thought of it. Also the numbered list wouldn't have made sense with only one command. Looks more professional like this :-)

Anonymous said...

You could vacuum it in Liferea in a separate thread, when it's idle? Like many people, I leave Liferea active during my whole session, so after some time-out, it could safely do what is necessary, and in most cases, I won't even notice anything.

Lars said...

No, it cannot be run in a separate thread. First only one thread may use sqlite and second the DB must not be attached while running VACUUM.

Anonymous said...

> 3. # At the prompt enter: "VACCUM;"

You mean, "VACUUM;"

Lars said...

Thanks for the hint! I fixed the typo.

Bill said...

I'm really grateful for these instructions. On my 250 MB database initial load time has come down from >5 minutes to about 10 seconds.

supr_fgs said...

How about making it something similar to fsck on linux startup, but every 100 runs or once a month or any other schedule. either on startup OR at exit.

Or.. similar to once firefox extension, you can make option/button called [compact database].

Thanks for the tip, saved >12MB and gained faster startup.

Lars said...

@supr_fgs: Well to be honest I just hate fsck when it checks the disk when I boot my laptop to hold a presentation in front of my colleagues and my boss. Somehow this happens each time!

So I think automatic peridioc execution is a no go. Imagine you wanting to check just one feed really quickly, but Liferea then decides to do the peridioc DB cleanup... It is just not acceptable.

I'm not sure what would be a good way to expose a manually triggered option. Maybe a --db-cleanup command line switch or some deeply hidden menu option.

Rick at shrimp and grits said...

Imagine you wanting to check just one feed really quickly, but Liferea then decides to do the peridioc DB cleanup... It is just not acceptable

If the database needs cleanup, you can't do *anything* in liferea really quickly. The entire interface becomes unresponsive when the database gets large.

My solution was to simply run the cleanup command from a script that then starts liferea. For the number of feeds I have, it adds a few seconds to startup time - but I don't have to be annoyed by an interface that gets slower, and slower, and slower ...

What about automatically cleaning up the database when it passes a certain size threshold?

Lars said...

@rick: What is "slow"? Some user with 10 feeds might think 2s startup time is slow and he might be right. Someone with 300 feeds with 1000 items each might happily accept 10s startup duration.

I can only imagine a performance check (based on startup time) that maybe at most every 25 startups does give a hint to the user that performance might suffer and it could be improved by doing X. "X" could mean starting with --cleanup or something similar.

Anonymous said...

Maybe the amount of NULL bytes can give a hint?

My results were the following.

Before running VACUUM:

File size: 48833536
Null bytes: 25841690
Ratio: 0.53

After:

File size: 25049088
Null bytes: 2253941
Ratio: 0.09

Anonymous said...

Discovered a major problem today, thought vacuum would help bu didn't.
I have a couple of news bins in which I store the most important news. However, every time I start Liferea (1.4.18), all of the news bins are empty again.

I'm not sure if this is a bug in liferea or if my file permissions are wrong.

supr_fgs said...

Hi again

Well that's true for me too.
Another suggestion would be to add [cancel] button to the message that tell you, say, 'liferea is compacting database', when you exit liferea for example.

Just to test the basis, I tried vacuum single command-line and then killed its process. The original database file wasn't affected as all the work is done on temp file that was deleted.

I don't know, something along these lines.

Anonymous said...

Try this script:

#!/bin/sh
# call this script from .login
# count:20

# number of times to skip before vacuuming
MAXCOUNT=20
COUNT=`grep "^# count:[0-9]" $0 | cut -d: -f2`

if [ -z $COUNT ]; then
COUNT=$MAXCOUNT
echo "# count:$COUNT" >> $0
fi
if [ $COUNT = 0 ]; then
# echo "vacuum"
sqlite3 $HOME/.liferea_1.4/liferea.db "VACUUM;"
COUNT=$MAXCOUNT
else
COUNT=`expr $COUNT - 1`
fi

# echo "count = $COUNT"
STR=`cat $0 | sed -e "s/^# count:.*$/# count:$COUNT/"`
echo "$STR" > $0

Anonymous said...

@supr_fgs: Well to be honest I just hate fsck when it checks the disk when I boot my laptop to hold a presentation in front of my colleagues and my boss. Somehow this happens each time!

Wrong FS. Use XFS, then this won't ever happen again :)

BTW: Shrank my DB from 19 to 13MB. But Liferea is still pretty slow (idle, 1GIG free RAM, Core2Duo..), 7200 disk.

But VACUUMing made it definitely faster.

Thanks for Liferea!

Anonymous said...

It's not the filesystem itself. Ever heard of "fs_passno"? Although in case of XFS this is ignored simply because it has its own tool "xfs_check" for that and fsck.xfs does nothing.
Second of all sometimes one cannot/it is not a good idea to use XFS for /.

philbert said...

hi,

ich glaube es reicht einfach die datei "feedlist.opml" aus dem liferea ordner zu sichern und nach dem löschen des liferea ordners wieder rein zu kopieren :)

Andrew Z said...

For people who don't like the command line, BleachBit 0.6.0 vacuums Liferea (and Firefox) in a simple GUI. I especially notice a performance boost in Firefox in the "Awesome-bar."

Exteris said...

Cleaning up your cache folder will help significantly as well. Mine was 620 MB and after I deleted everything (It's just cache ;)) Liferea was fast again.