[horde] IMP IMAP DB Cache backend lifetime

[Federico Giannici]

On 08/30/17 10:09, Jan Schneider wrote:
>
> Zitat von Joao S Veiga <jsveiga at rf.com.br>:
>
>> ----- Message from Jan Schneider <jan at horde.org> ---------
>>
>>>> 1 - horde-clear-cache does not clear the IMP IMAP DB cache tables
>>>> (horde_imap_client_data, horde_imap_client_metadata,
>>>> horde_imap_client_message). Is there a script for clearing these
>>>> tables, or doing it via sql is the only way?
>>>
>>> imp-query-imap-cache
>>>
>>>> 2 - the Db.php backend
>>>> (framework/Imap_Client/lib/Horde/Imap/Client/Cache/Backend/Db.php)
>>>> does have a clear($lifetime) function, but I couldn't find where is
>>>> it called. Who is supposed to call that?
>>>
>>> See above.
>>>
>>>> 3 - it seems there's a missing ")" in that function at the DELETE
>>>> statement (line 320 at
>>>> https://github.com/horde/horde/blob/master/framework/Imap_Client/lib/Horde/Imap/Client/Cache/Backend/Db.php):
>>>>
>>>>
>>>> $purge = time() - $lifetime;
>>>> $sql = 'DELETE FROM %s WHERE messageid IN (SELECT messageid FROM %s
>>>> WHERE modified < ?';
>>>> foreach (array(self::MD_TABLE, self::MSG_TABLE) as $val) {
>>>>
>>>> I've searched for an sql error in the logs and couldn't find one for
>>>> this statement, so maybe it's never called?
>>>
>>> Indeed, fixed for the next release.
>>>
>>> It's only called when using the --lifetime parameter with
>>> imp-query-imap-cache.
>>>
>>
>>
>> Thank you Jan,
>>
>> So if I understood correctly, the imap cache old entries are only
>> cleaned manually with the imp-query-imap-cache script, so there is no
>> automatic "expiring" like for the horde cache files?
>>
>> Thanks again,
>>
>> Joao
>
> I don't think so.

I think that current implementation is not sufficient.

After almost an year of usage I had the following tables usages (first 
number are the rows the second the dimension):

horde_imap_client_data		9,825		1.3 MiB
horde_imap_client_metadata	9,822		503.8 KiB
horde_imap_client_message	6,038,538	11.7 GiB

Now I issued for the first time the imp-query-imap-cache to clear all 
cached data older than 30 days, and this is the result:

horde_imap_client_data		1,887		1.3 MiB
horde_imap_client_metadata	1,884		503.8 KiB
horde_imap_client_message	4,221,725	8.2.7 GiB

As you can see, the number of cached folders is decreased al lot (by 
80%) but the number of cached messages decreased a lot less (only 30%).

4 MILLIONS records and 8.2 GB are simply too much for the DB to be 
adequately fast.

I previously noticed that in the messages cache there are messages that 
actually are no longer in the folders. So, especially for the INBOX 
folders (that never expires but see a lot of messages), there remains in 
cache much more messages that needed.

In theory, we should take note every time a message cache is accessed so 
we could purge the ones not accessed any longer, but I'm afraid this 
would add too much UPDATEs and would slow down the entire mechanism.

I think that a possible simple solution could be to add a "created" 
field the horde_imap_client_message table automatically set to the 
current timestamp at the INSERT. Then we could use this field to purge 
the messages that were cached since a lot (maybe a month?). In this way 
no messages would remain in cache forever, but eventually the reload of 
the message would happen only after a lot of time (and in a "time 
distributed" way).

What do you think?