Purging an sqlite database - RC 1.3.8

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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

_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

A.L.E.C
On 11/7/18 11:04 PM, Sophie Loewenthal wrote:
> 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
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

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





> On 8 Nov 2018, at 07:36, A.L.E.C <[hidden email]> wrote:
>
> On 11/7/18 11:04 PM, Sophie Loewenthal wrote:
>> 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
> [hidden email]
> http://lists.roundcube.net/mailman/listinfo/users
_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

Sophie Loewenthal


> On 8 Nov 2018, at 19:47, Reindl Harald <[hidden email]> wrote:
>
>
>
> Am 08.11.18 um 19:44 schrieb Sophie Loewenthal:
>> 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
>
> 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.
_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

Sophie Loewenthal
> On 8 Nov 2018, at 19:55, Reindl Harald <[hidden email]> wrote:
>
>
>
> Am 08.11.18 um 19:49 schrieb Sophie Loewenthal:
>>> On 8 Nov 2018, at 19:47, Reindl Harald <[hidden email]> wrote:
>>>
>>>
>>>
>>> Am 08.11.18 um 19:44 schrieb Sophie Loewenthal:
>>>> 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
>>>
>>> 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.
_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

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.



> On 8 Nov 2018, at 19:59, Sophie Loewenthal <[hidden email]> wrote:
>
>> On 8 Nov 2018, at 19:55, Reindl Harald <[hidden email]> wrote:
>>
>>
>>
>> Am 08.11.18 um 19:49 schrieb Sophie Loewenthal:
>>>> On 8 Nov 2018, at 19:47, Reindl Harald <[hidden email]> wrote:
>>>>
>>>>
>>>>
>>>> Am 08.11.18 um 19:44 schrieb Sophie Loewenthal:
>>>>> 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
>>>>
>>>> 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.
> _______________________________________________
> Roundcube Users mailing list
> [hidden email]
> http://lists.roundcube.net/mailman/listinfo/users
_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Purging an sqlite database - RC 1.3.8

B. Reino
On Sun, 11 Nov 2018, Sophie Loewenthal wrote:

> Update: Solution was to optimise the dB:
> 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.
_______________________________________________
Roundcube Users mailing list
[hidden email]
http://lists.roundcube.net/mailman/listinfo/users