reindex process slows after some hours when Postgres vacuuming falls behind

[punktilious]

After some time, queries against LOGICAL_RESOURCES slow down. After a couple of hours, the reindex status update on this table ends up dominating the overall processing time. It turns out this is a byproduct of doing so many updates to this table and the way Postgres handles those updates. The update leaves behind dead records which need to be cleaned up by the vacuum process. This is supposed to be run in the background but it looks like the automatic vacuum can't keep up.

After stopping the reindex and run a manual vacuum the performance recovers (from 30s per call to 10s):

VACUUM (ANALYZE,VERBOSE) fhirdata.logical_resources;

After restarting reindex, the time slowly creeps up again, so this has to be repeated.

Investigate other solutions which avoid the need to perform an update against an indexed column. One possible solution is to copy all the rows needing to be processed into a working queue table and delete each row from there as it is processed. This table probably does not even require an index, because processing can be done in row order (somewhat arbitrary, but that is OK).

[lmsurpre]

options:

• make vacuuming more aggressive
• manual (code-driven vs db-driven) vacuuming
• other?

need investigation as too which is best

[lmsurpre]

The LOGICAL_RESOURCES table will still be updated as part of reindex after #2524 but we don't expect it to cause the same contention as what we were seeing from updating the tstamp.

[lmsurpre]

After trying to reindex a large postgres db using the new client-driven reindex behavior, I don't think this one is fully addressed.

It ran for over a day, but after some amount of time we ran out of disk space. I don't know if it was vacuuming related or just that the space was needed for the new tables/indices in 4.9.0.

However, after increasing disk capacity, client-driven reindex repeatedly failed with error like:

2021-07-11 22:37:13.879 fhir-test-server-86b656757d-9drdx fhir-test-server SEVERE inserting parameters
org.postgresql.util.PSQLException: ERROR: multixact "members" limit exceeded
  Detail: This command would create a multixact with 2 members, but the remaining space is only enough for 1 member.
  Hint: Execute a database-wide VACUUM in database with OID 16478 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings.
  Where: SQL statement "SELECT 1 FROM ONLY "fhirdata"."parameter_names" x WHERE "parameter_name_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

I found the following command from the postgresql forums for finding tables with the oldest xids:

SELECT oid::regclass, relminmxid, mxid_age(relminmxid) FROM pg_class WHERE relminmxid <> '0' ORDER BY mxid_age(relminmxid) DESC;

Our observation_resource_token_refs was on the top of this list and so I performed a manual vacuum of that table:
vacuum fhirdata.observation_resource_token_refs

The next morning I noticed that the reindex response times were much faster.

[lmsurpre]

Discussed with Robin and we think that we can further mitigate this issue by setting the properties mentioned at https://ibm.github.io/FHIR/guides/FHIRPerformanceGuide#412-tuning-auto-vacuum on the postgresql tables that see a lot of update/deletes. Specifically:

• logical_resources
• all search parameter tables

So basically everything other than the xx_RESOURCES and COMMON_TOKEN_VALUE tables :-)

[prb112]

PR #2628

[d0roppe]

Ran the latest reindex of 79 million resources, and it finished without error in 39 hours. this was with the latest fixes for vacuum settings. I am closing this issue based on last run.