View Issue Details

IDProjectCategoryView StatusLast Update
0005297SOGoBackend Generalpublic2021-04-16 02:12
Reporterzhb Assigned To 
PrioritynormalSeverityminorReproducibilityN/A
Status newResolutionopen 
Product Version5.1.0 
Summary0005297: Is it safe to truncate SQL table `sogo_cache_folder`?
Description

Dear all,

We have a sogo server running for a long time, but recently the sogo_cache_folder table growing very quickly, from 10 GB to 150GB in a few days.

sogo=# SELECT pg_size_pretty( pg_total_relation_size('sogo_cache_folder') );
pg_size_pretty
----------------
156 GB
(1 row)

The question is: Is it safe to truncate all data in sogo_cache_folder from SQL command line? sogo-tool seems not have an argument to delete old records.

TagsNo tags attached.

Activities

francis

francis

2021-04-14 10:53

administrator   ~0015200

Which version of PostgreSQL are you using? Is the row count also increasing?

zhb

zhb

2021-04-14 11:11

reporter   ~0015201

  • Old SOGo nightly build 4.0.4.20181204-1.
  • Row count seems not increasing:
sogo=# select count(*) from sogo_cache_folder ;
 count 
-------
  5301
  • sogo_cache_folder.c_uid has duplicate values.

Did a fresh backup of the sogo_cache_folder table, then drop it and restore, the size reduced to 13MB, but it's quickly growing to 1693MB in less than 30 minutes, still growing.

zhb

zhb

2021-04-14 11:12

reporter   ~0015202

To be precise:

  • Row count does NOT increase.
  • Table size is growing quickly.
francis

francis

2021-04-14 11:26

administrator   ~0015203

Check your PostgreSQL setup. Tuneup the autovacuum configuration.

zhb

zhb

2021-04-14 11:27

reporter   ~0015204

Upgraded SOGo to the latest nightly build 5.1.0.20210414-1, but seems no suitable sope packages for this sogo version? It's 4.9.r1664.SHORTDATE and sogo doesn't work anymore, complaining:

2021-04-14 17:26:09.198 sogod[77230] EXCEPTION: <NSException: 0x55ca43abb4f0> NAME:NSInvalidArgumentException REASON:NSURL(instance) does not recognize queryComponents INFO:(null)
zhb

zhb

2021-04-14 11:32

reporter   ~0015205

Server OS is Ubuntu 16.04.7 LTS (Xenial).

francis

francis

2021-04-14 11:32

administrator   ~0015206

You'll need to force an upgrade to the latest SOPE packages. The ones you have installed (ending with .SHORTDATE) have a bad versioning.

zhb

zhb

2021-04-14 11:37

reporter   ~0015207

Removing and reinstalling all sope + sogo packages fixes the does not recognize queryComponents error.

zhb

zhb

2021-04-14 12:12

reporter   ~0015208

Updated global autovauum settings. But is it a good idea to configure individual table level autovacuum settings for sogo.sogo_cache_folder by default?

francis

francis

2021-04-14 12:26

administrator   ~0015209

It depends on your global settings. Maybe you'll want more aggressive settings for the sogo_cache_folder table if you have disk space issues.

zhb

zhb

2021-04-15 21:44

reporter   ~0015215

Update after tuned autovacuum for 2 days:

  • postgresql.conf has settings like below, should be fast enough to trigger the autovacuum:
track_counts = on
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
  • The situation: It still grows fast, 70GB in 2 days. Any idea?
sogo=# select relname,n_dead_tup,last_autovacuum,now() from pg_stat_user_tables;
        relname         | n_dead_tup |        last_autovacuum        |              now              
------------------------+------------+-------------------------------+-------------------------------
 sogo_user_profile      |         25 | 2021-04-15 07:09:14.391686+02 | 2021-04-16 03:41:04.018066+02
 sogo_alarms_folder     |          0 |                               | 2021-04-16 03:41:04.018066+02
 sogo_quick_appointment |        119 | 2021-04-14 17:52:48.863971+02 | 2021-04-16 03:41:04.018066+02
 sogo_acl               |          0 |                               | 2021-04-16 03:41:04.018066+02
 sogo_sessions_folder   |         17 | 2021-04-15 21:45:22.963037+02 | 2021-04-16 03:41:04.018066+02
 sogo_quick_contact     |         13 |                               | 2021-04-16 03:41:04.018066+02
 sogo_cache_folder      |         22 | 2021-04-16 03:22:33.693857+02 | 2021-04-16 03:41:04.018066+02
 sogo_store             |         47 | 2021-04-14 17:52:16.922995+02 | 2021-04-16 03:41:04.018066+02
 sogo_folder_info       |          0 |                               | 2021-04-16 03:41:04.018066+02
(9 rows)

sogo=# SELECT pg_size_pretty( pg_total_relation_size('sogo_cache_folder') );
 pg_size_pretty 
----------------
 70 GB
(1 row)

Issue History

Date Modified Username Field Change
2021-04-14 10:13 zhb New Issue
2021-04-14 10:53 francis Note Added: 0015200
2021-04-14 11:11 zhb Note Added: 0015201
2021-04-14 11:12 zhb Note Added: 0015202
2021-04-14 11:26 francis Note Added: 0015203
2021-04-14 11:27 zhb Note Added: 0015204
2021-04-14 11:32 zhb Note Added: 0015205
2021-04-14 11:32 francis Note Added: 0015206
2021-04-14 11:37 zhb Note Added: 0015207
2021-04-14 12:12 zhb Note Added: 0015208
2021-04-14 12:26 francis Note Added: 0015209
2021-04-15 21:44 zhb Note Added: 0015215