howto clear changes table?

mclu

Hi together

I want to setup a testsystem with real data. So I copied my kopano production database to the testsystem and removed lots of mails /contacts/ notes , other users and complete stores. After purge-softdelete the attachment directory is nearly empty as well as most of the database tables.
I also set the sync_lifetime to “1” so the sync table is also clean now.
The only table which is still huge is the changes table (>2,2 GB).
What is this table used for and how can I clean it up?
It seams it still contains old entries of deleted stored, mails and users…

Thx in advance
Markus

Manfred

Hi Markus,

the changes table contains the changes of an item (create, edit, delete) for the incremental change system (ICS). E.g. ActiveSync uses the ICS to detect the changes since last sync of a mobile device.

As far as I know there’s no easy way to clean this table up. The only solution I can think of is to compare the sourcekey of changes table with val_binary of indexedproperties table and if it’s not in the indexedproperties, you could remove the entry. I’m not a core developer though, so there’s no guarantee that’s the right way.

As it’s a testsystem, maybe you could just purge the data from the changes table and see how it affects the system.

Manfred

You should be able to just truncate (abchanges, changes, syncs, syncedmessages) if you do not care about the synchronization states.

mclu

@jengelh said in howto clear changes table?:

… abchanges, changes, syncs, syncedmessages …

Great… thx…
I already started to get relational table knowledge based on the kopano source code in ECICS.cpp :-)…
Thats of course faster …

I just wondered that I could not find any cleanup sql script in the sources for this table. What if I remove a user and its store.
Are the changes of the deleted user items still in the table? Forever?
And why?

chrisk

I think I should reopen this issue. Currently we have huge problems with growing tables. For example we have these big tables:

changes 15 GB
properties 380 GB
tproperties 74 GB
syncedmessages 3 GB

And these are only the biggest tables. The system is currently hard working and gets slower every day. And this is a powerful machine (64 Core, 250 GB Memory).

We really need to clean those tables.

Thanks for any help.

Christian

Btw. we have a subscription.

fbartels

@chrisk said in howto clear changes table?:

Btw. we have a subscription

Then please open a support case. Your system is probably only missing some tuning. Depending on the general size of your installation your tables could very be within expected values.