Datastore table clean up or optimize

Experience Manager (PaaS/OnPrem)
1

Hi All,

We have created an SQL dump from one of our testing environment and trying to reduce the size by deleting content, images , assets etc via console.
Post deletion the row count and size of the table is not changed. We have also ran optimize query.

Could you please let us know is there any other approach for cleaning up this table ?

Thanks,
Yeshwanth.

1 Like
2

Hi Yeshwanth,

Could you measure which tables are big now? Or could you find how the data store size pattern looks like there?
For example, does your system have a bigger binary table (*DATASTORE), or bigger version table (*VERSION_BUNDLE). See p7 and p15 for each pattern in [1].

Regards,

Woonsan

[1] Hidden gems in Apache Jackrabbit and BloomReach Forge

3

Hi Woonsanko,

This is how our table size looks.

| Tables | Size in MB |
±---------------------------±-----------+
| DATASTORE | 16421.84 |
| REPOSITORY_JOURNAL | 228.52 |
| VERSION_BUNDLE | 174.75 |
| DEFAULT_BUNDLE | 77.63 |
| DEFAULT_REFS | 1.52 |
| VERSION_REFS | 1.52 |

Thanks,
Yeshwanth.

4

So, more than 95% is for binaries - gallery image sets and assets (pdf, doc, webfiles, …) in brXM.
All binaries are stored in DATASTORE table in Apache Jackrabbit and entries are not deleted by default (even if you remove them through CMS or CMS Console UI or JCR API). It’s a manual task. [2]

One possible solution could be to create a utility class doing like the example in [2] and invoke the utility class in a goorvy updater script at night.

Regards,

Woonsan

[2] Apache Jackrabbit : DataStore

5

Groovy updater scheduler support in a BR Forge project: Bloomreach Forge Utilities plugin – XM Utilities Repository - Groovy Updater Execution Scheduler Job Module

6

Hi Woonsanko,

Thank you for your response.

We are trying to run the suggested response via groovy script.
DIR value in below snippet should be replaced with “{repo.path}” value ?

prop.setProperty(“org.apache.jackrabbit.repository.home”, DIR);
prop.setProperty(“org.apache.jackrabbit.repository.conf”, DIR + “/repository.xml”);

Thanks,
Yeshwanth.

7

There are different ways, but suppose you configured your system like [3].

In that case, for the first property, you should set the same directory path as the repository-directory context parameter [3], or System.getProperty("repo.path") instead if set it through the Java system property.

For the second property, it should be the same path as you configured through -Drepo.config=... [3].
(I assume you can use the file system path, instead of file: url from the example.)

Regards,

Woonsan

[3] Configure the Application Server (Apache Tomcat on Linux) - Bloomreach Experience - Headless Digital Experience Platform