HUGE harvest_object table size

[dev-rke]

Hey there,

we are using CKAN 2.10.4 with harvest 1.5.6 and dcat 1.7.0.

We observe since half a year an incremental growing harvester_object table.
Currently it is around 7 Gigabytes, while our harvesters only run once a day and we handle around 30k datasets per day.
Our package table only is around 32MB, so we are irritated, if there is no automatic cleanup mechanism?

I checked the docs, checked various CLI commands with --help and also had a short insight into the code - without any further ideas.

How may i cleanup this table without crashing too much? Delete all records that finished older than 2 months?

I already saw #347, but it does not sound promising to me to redirect the data to redis, as redis will just flush inserted data when memory hits its given limit - even when the corresponding harvester run is not finished yet.

So i would like to see an automatic cleanup when jobs are finished, but keep records causing errors for comparison purposes.

Please give me some advice with best practices.

Thank you very much and have a nice week.

[seitenbau-govdata]

Hi @dev-rke As I know there is no automatic mechanism. Therefore we have had added an option to the harvester source CLI command (https://github.com/ckan/ckanext-harvest#command-line-interface) for cleaning up all or old harvest source jobs, including the harvest objects. With the option clear-history -k true all "old" harvest jobs will be deleted from the database. Only harvest objects will be deleted which are not has the status Running or the property current is true.

Related pull requests:

• Add clearsource history command #268
• Add option keep-current to clearsource_history command #484

With a cron job which calls the command, every two weeks, for example, the harvest object table can be kept small.

Perhaps this is also a viable option for your use case.

[dev-rke]

Hey @seitenbau-govdata ,

cool! Thank you!
That partially solves our problem.

I tested it and found out, that only the last harvesting job is kept then.
Would it be possible to customize that command to keep e.g. the last 10 harvesting jobs and delete everything except of these 10?
It would be great to have the number configurable.

The reason is, that it is very helpful to see changes in synchronisation behaviour. Sometimes data providers reduce their records over time, sometimes there is a huge drop due to configuration issues - keeping the history is not bad at all, but it's problematic to keep the data from beginning of all times.