DB Clean Up process took very long time (around 7 days) -4835682 rows from table alf_prop_value

[jil]

Dear Sir,

As I checked alfrescotomcat51-stdout.log, I found that db clean up process took very long time and it took around 6.5 days to completed.

The process has been started 3:00 AM on Sat 24Mar18.

2018-03-30 13:52:37,436  INFO  [schema.script.ScriptExecutorImpl] [DefaultScheduler_Worker-8] Processing from 4330000 to 4340000 rows of 4835682 rows from table alf_prop_value.

Do you have the way to improve this to not take so long to finish?

Regards,

Jil

[afaust]

First you should find out what is the bottleneck of that cleanup. I.e. what amount of memory has been assigned to your database, is the IO performance sufficient?

I generally do not use that default cleanup process as it is a bulk "do-all-at-once" process. For a customer I have written an addon to do incremental cleanup so that it does not end up blocking the system.

[jil]

Dear Axel Faust,

      Thank you for your suggestion. As we tried to check the log and monitor the system performance, we thought that the problem might came from memory or  storage (I/O). So we add more memory from 16GB to 32 GB but the problem still occur. So we guessed that the problem might came from storage (I/O) but still not sure. 

      I also have some questions below related to DB clean up:

     1. For this process, is it necessary to run this process very week or can I disable it?

     2. If we restart Tomcat, this process will be ended without finish it, what is the impact to the system?

Thanks,

Jil

[afaust]

The process was not even enabled by default before Alfresco 5.2, so if you have an older version of Alfresco, you must have already modified the default cron expression in alfresco-global.properties. Anyway, you need to set attributes.propcleaner.cronExpression to an expression of your liking, either to disable it or set it to a different schedule. In Alfresco 5.2, the default is 0 0 3 ? * SAT

If you stop/restart Tomcat while the process is running, the transaction will be aborted and rolled back, which could but substantial strain on the database since the transaction is bound to be quite large.