hi folks. i searched around a bit but didn't find anything on this topic.

i have this running in a script and scheduled using alarmed to run on a daily basis at 2am.

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db 'VACUUM;'
sqlite3 /home/user/.rtcom-eventlogger/e1.db 'VACUUM;'
sqlite3 /home/user/.qmf/qmailstore.db 'VACUUM;'
sqlite3 /home/user/.calendar/calendardb 'VACUUM;'
sqlite3 /home/user/.config/hildon-desktop/notifications 'VACUUM;'
sqlite3 /home/user/.fmms/mms.db 'VACUUM;'

i have an uptime of 3 days now and it usually starts to lag noticeably (i use optimize n900). but the above seems to have made the phone quicker to respond. the fmms entry probably doesn't do anything for the phone overall but i just included it for fun.

here are some examples of phone being snappier:

1. microb is faster in opening multiple windows
2. launching xterm is faster
3. opening settings - there used to be a slight delay of about 2 to 3 seconds while the top of the window has a spinning icon. but it is now instant.
4. opening sms and email apps is much faster.
5. deleting smses is very much faster.

i don't understandm the effect of those dbs being compressed on overall performance and some of them don't seem related to e.g. microb or launching the settings app but this is what i observe.

anyway, just thought i'd share this and see if anyone else can benefit from it.

thanks.

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created. However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database. When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM.

VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

As of SQLite version 3.1, an alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma. When auto_vacuum is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using VACUUM. However, using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.

...so..is it good for a user to run this comand lines in the first post once in a while?

I haven't tested this, but noticed the second entry, i.e.
sqlite3 /home/user/.rtcom-eventlogger/e1.db 'VACUUM;'

should be el.db and not e1.db.

In any case, you can completely remove it since it is not been used by any program anymore.

thanks for the tip, Saturn. as for running it being safe, i figure it should be safe as long as we use the sqlite client since it should know how to respect locks, concurrency etc. also since we are not modifying the data in the dbs, there shouldn't be consistency issues for apps that depend on the dbs. e.g. no listeners will be affected by sms db being compressed etc.

also, to avoid any other issues, i run this at 2am when there is unlikely to be any activity on the phone.

I hope it is not off-topic here, as it is mean to increase uptime without lags - using swap on microSD (even class 2) and re-enabling it after total amount of data written to swap partition exceeds 600 (*not* data currently on swap, but data written totally, check iostat), result in absolutely 0 negative effects of long uptime.

Using the above (re-swapping by semi-automatic script that I've based on shadowjk's work) I used to get uptimes like 30 days (sic!) without any negative effect on performance (used, because now I'm installing/tweaking etc. things, that many times require reboot to take effect).

Yet, it is interesting catch about vacuuming databases. If confirmed to not bring risk of corrupting anything (in some particular cases of execution time), it may be nice addition to reswapping.

/Estel

This vacuum trick is also used with Firefox, with good benefits in old computers.

Yeah, but my sources said one should first reindex, then vacuum a database file.

Would you be willing to share the script here? Or at least tell me -- 600 what?

Estel's reply has really piqued my interest! so i've created a swap partition on my microsd and set both the default swap and the microsd swap locations as having the same priority to spread load out evenly.

i'll monitor these over the next couple of days.

Estel - do you mean using iostat to monitor total blocks written to the microsd swap partition? if so, this should be quite easy to script and have it run via alarmed every 24 hours.