2.37.8 taking much, much longer to complete Resource Tables

Support - Assistance technique
1

Hi All

I’m finding that in 2.37.8 our server is taking 67+ hours to complete resource tables (both the first time and in subsequent attempts). On 2.35.11, with the same server power and basically the same set-up (a few months outdated, but equal in terms of programs and metadata structure) takes around 45 minutes.

Is there something about 2.37.8 (or even 2.36, perhaps) that was changed that would account for the much longer load times for resource tables?

Almost the entirety of the time to load tables in 2.37.8 is based on one table:

INFO 2022-10-23T08:18:56,398 Resource table ‘_categorystructure’ update done: ‘66:03:02.892’ (JdbcResourceTableStore.java [taskScheduler-3])

We have a very robust CategoryOption, CategoryOptionCombintaion, and Category set-up, however this set-up is consistent across servers and only seemingly problematic for 2.37.8

Thanks for any information!

Update While looking to find out more about the _categorystructure table, I found this:

Category structure (_categorystructure)

This table provides information about which data elements are members of which categories. The table has one row for each data element, one column for each category and the names of the category options as values.

Our system doesn’t have any data elements associated with category options outside of default. I verified this through an API request looking at categoryOptionCombo.id. Given this, is there any reason that the _categorystructure should have anything in the table?

3 Likes
2

I’m confirming this behavior as well. @dhis2-backend @dhis2-analytics - were there any significant changes in late 2.37 and 2.38 versions that would affect resource tables or analytics processing relative to the existing process?

3 Likes
3

As we continue down this path of discovery, we’re now starting to circle postgres version as a potential factor. The same instance on 13.8 that takes 66 hours takes 9 minutes on 10.3, 14 minutes on 9.6 postgres versions.

4

Thanks for your post @Matthew_Boddie and thanks for the confirmation @chase.freeman!

5

On one instance (the one mentioned by @Matthew_Boddie ) we found that the the issue was related to the order of operations when upgrading dhis2 from 2.35.11 to 2.37.8 and THEN upgrading to postgres13 caused many of the issue.

When we loaded to the 2.35.11 DB into an already upgraded postgres13 and then upgraded dhis2 from 2.35.11 to 2.37.8, it resolved many of the issues.

My other case of these symptoms has yet to be resolved but I will look to see if it follows a similar pattern or if it is a different issue entirely.

2 Likes