[RCU] Purging an sqlite database - RC 1.3.8

[Sophie Loewenthal]

Hi,

I noticed a roundcube sqlite dB slowly growing after each login.

in this example the user just logged, ran a search for an email address on Sent folder and logged out. Yet there was an increase in size:

Before login
-rw-r----- 1 www www 21295104 Nov 7 21:47 roundcube.db
After login
-rw-r----- 1 www www 21303296 Nov 7 21:57 roundcube.db

What sort of information could be being added to the dB? And how could I purge this data, or otherwise reduce the sqlite dB? Perhaps though some regular cleaning?


Roundcube 1.3.8 and sqlite 3.16.2.

Best, Sophie

[A.L.E.C]

Post by Sophie Loewenthal
Hi,
I noticed a roundcube sqlite dB slowly growing after each login.
What sort of information could be being added to the dB? And how could I purge this data, or otherwise reduce the sqlite dB? Perhaps though some regular cleaning?

Maybe just session data. It should clean automatically, but depends on php configuration.
Try bin/gc.sh.

[Sophie Loewenthal]

Hi,

I tried the gc.sh after stopping nginx & backed up the file:

# bin/gc.sh
ERROR: [13] database or disk is full (SQL Query: DELETE FROM session WHERE changed < datetime('now','-1200 seconds'))

The disc has 8Mb free.


Sophie

Post by A.L.E.C

Post by Sophie Loewenthal
Hi,
I noticed a roundcube sqlite dB slowly growing after each login.
What sort of information could be being added to the dB? And how could I purge this data, or otherwise reduce the sqlite dB? Perhaps though some regular cleaning?

Maybe just session data. It should clean automatically, but depends on php configuration.
Try bin/gc.sh.
--
Aleksander 'A.L.E.C' Machniak
Kolab Groupware Developer [http://kolab.org]
Roundcube Webmail Developer [http://roundcube.net]
----------------------------------------------------
PGP: 19359DC1 # Blog: https://kolabian.wordpress.com
_______________________________________________
Roundcube Users mailing list
http://lists.roundcube.net/mailman/listinfo/users

[Sophie Loewenthal]

Post by Sophie Loewenthal
# bin/gc.sh
ERROR: [13] database or disk is full (SQL Query: DELETE FROM session WHERE changed < datetime('now','-1200 seconds'))
The disc has 8Mb free

you simply can't optimize a 20 MB database file when there are only 8 MB
free - who in the world has 8 MB free in 2018?

Who in the world has a 20Mb sqlite db for RC? I do. You should have guessed :-)

I run a lean machine.

[Sophie Loewenthal]

Post by Sophie Loewenthal

Post by Sophie Loewenthal
# bin/gc.sh
ERROR: [13] database or disk is full (SQL Query: DELETE FROM session WHERE changed < datetime('now','-1200 seconds'))
The disc has 8Mb free

you simply can't optimize a 20 MB database file when there are only 8 MB
free - who in the world has 8 MB free in 2018?

Who in the world has a 20Mb sqlite db for RC? I do. You should have guessed :-)
I run a lean machine.

and?
my smartphone has 140 GB free space
the 20 MB sqlite file are not big
but 8 MB free is a bad joke

Not a problem:

lvresize -L 50M /dev/vg00/sqlite
resize2fs !$

run the optimise

resize2fs /dev/vg00/sqlite 30M
lvresize -L 32M /dev/vg00/sqlite
resize2fs !$

add some e2fsck’s in there too.

Not an issue, but I’ll do this during the day because it’s late.

[Sophie Loewenthal]

Dear Reind,

Update: Solution was to optimise the dB:
find -type f -name roundcube.sq -exec sqlite3 {} 'VACUUM;' \;

Size dropped from 21Mb down to 280kB.

Thanks.

Post by Sophie Loewenthal

Post by Sophie Loewenthal

Post by Sophie Loewenthal
# bin/gc.sh
ERROR: [13] database or disk is full (SQL Query: DELETE FROM session WHERE changed < datetime('now','-1200 seconds'))
The disc has 8Mb free

you simply can't optimize a 20 MB database file when there are only 8 MB
free - who in the world has 8 MB free in 2018?

Who in the world has a 20Mb sqlite db for RC? I do. You should have guessed :-)
I run a lean machine.

and?
my smartphone has 140 GB free space
the 20 MB sqlite file are not big
but 8 MB free is a bad joke

lvresize -L 50M /dev/vg00/sqlite
resize2fs !$

run the optimise

resize2fs /dev/vg00/sqlite 30M
lvresize -L 32M /dev/vg00/sqlite
resize2fs !$
add some e2fsck’s in there too.
Not an issue, but I’ll do this during the day because it’s late.
_______________________________________________
Roundcube Users mailing list
http://lists.roundcube.net/mailman/listinfo/users

[B. Reino]

Post by Sophie Loewenthal
find -type f -name roundcube.sq -exec sqlite3 {} 'VACUUM;' \;
Size dropped from 21Mb down to 280kB.

Nice!

I just tried that and went from 3MB to ~800kb. Not that dramatic, but nice
to know :)

Cheers,
Bernardo.