Registry performance issue

[t83714]

Registry performance issue

With large database, registry might produce request timeout errors:

 [INFO] [10/18/2023 07:33:51.289] [registry-api-akka.actor.default-dispatcher-8] [akka.actor.ActorSystemImpl(registry-api)] Request timeout encountered for request [PUT /v0/records/dist-sa │
│ -c02af38a-904c-410a-ab44-7720f3a1e7b7/aspects/source-link-status Strict(40 bytes)]   

This issue seems related to broken-link-minion as well.

[t83714]

Logs in dev site shows seems webhook actor (and its children actors) are using default-dispatcher, which might starve the request serving

[INFO] [11/01/2023 23:11:02.940] [registry-api-akka.actor.default-dispatcher-13] [akka://registry-api/user/WebHookActor/WebHookActor-minion-broken-link-bf36156e-014f-418e-8ab4-dab48fe578f3] Skipping WebHook Some(minion-broken-link) as it's marked as waiting for response
[INFO] [11/01/2023 23:11:04.913] [registry-api-akka.actor.default-dispatcher-13] [akka://registry-api/user/WebHookActor/WebHookActor-minion-linked-data-rating-c687e4dd-d1a4-49bb-883f-af97ceb5194a] WebHook minion-linked-data-rating: Up to date at event Some(21127338)
[INFO] [11/01/2023 23:11:04.961] [registry-api-akka.actor.default-dispatcher-11] [akka://registry-api/user/WebHookActor/WebHookActor-minion-broken-link-bf36156e-014f-418e-8ab4-dab48fe578f3] Skipping WebHook Some(minion-broken-link) as it's marked as waiting for response
[INFO] [11/01/2023 23:11:05.094] [registry-api-akka.actor.default-dispatcher-14] [akka://registry-api/user/WebHookActor/WebHookActor-minion-format-c564f4b3-7971-4524-af29-8fd1d918732d] WebHook minion-format: Up to date at event Some(21014822)
[INFO] [11/01/2023 23:11:05.097] [registry-api-akka.actor.default-dispatcher-25] [akka://registry-api/user/WebHookActor/WebHookActor-minion-visualization-bc87ab28-ac7c-4c9e-bc09-7e57740e032c] WebHook minion-visualization: Up to date at event Some(21014822)
[INFO] [11/01/2023 23:11:06.936] [registry-api-akka.actor.default-dispatcher-25] [akka://registry-api/user/WebHookActor/WebHookActor-minion-linked-data-rating-c687e4dd-d1a4-49bb-883f-af97ceb5194a] WebHook minion-linked-data-rating: Up to date at event Some(21127338)
[INFO] [11/01/2023 23:11:06.981] [registry-api-akka.actor.default-dispatcher-25] [akka://registry-api/user/WebHookActor/WebHookActor-minion-broken-link-bf36156e-014f-418e-8ab4-dab48fe578f3] Skipping WebHook Some(minion-broken-link) as it's marked as waiting for response
[INFO] [11/01/2023 23:11:07.238] [registry-api-akka.actor.default-dispatcher-11] [akka://registry-api/user/WebHookActor/WebHookActor-minion-visualization-bc87ab28-ac7c-4c9e-bc09-7e57740e032c] WebHook minion-visualization: Up to date at event Some(21014822)
[INFO] [11/01/2023 23:11:07.243] [registry-api-akka.actor.default-dispatcher-15] [akka://registry-api/user/WebHookActor/WebHookActor-minion-format-c564f4b3-7971-4524-af29-8fd1d918732d] WebHook minion-format: Up to date at event Some(21014822)
[INFO] [11/01/2023 23:11:08.960] [registry-api-akka.actor.default-dispatcher-11] [akka://registry-api/user/WebHookActor/WebHookActor-minion-linked-data-rating-c687e4dd-d1a4-49bb-883f-af97ceb5194a] WebHook minion-linked-data-rating: Up to date at event Some(21127338)
[INFO] [11/01/2023 23:11:09.000] [registry-api-akka.actor.default-dispatcher-10] [akka://registry-api/user/WebHookActor/WebHookActor-minion-broken-link-bf36156e-014f-418e-8ab4-dab48fe578f3] Skipping WebHook Some(minion-broken-link) as it's marked as waiting for response
[INFO] [11/01/2023 23:11:09.020] [registry-api-akka.actor.default-dispatcher-14] [akka.actor.ActorSystemImpl(registry-api)] Request timeout encountered for request [PUT /v0/records/dist-sa-9d4c632a-bc25-40eb-bdde-8ef06b5c3edd/aspects/source-link-status Strict(40 bytes)]
[INFO] [11/01/2023 23:11:09.020] [registry-api-akka.actor.default-dispatcher-10] [akka.actor.ActorSystemImpl(registry-api)] Request timeout encountered for request [PUT /v0/records/dist-sa-cb6312a1-523d-4118-ac29-10533edb5ff6/aspects/source-link-status Strict(40 bytes)]
[INFO] [11/01/2023 23:11:09.020] [registry-api-akka.actor.default-dispatcher-10] [akka.actor.ActorSystemImpl(registry-api)] Request timeout encountered for request [PUT /v0/records/dist-sa-eea644e5-4918-4c8e-a7be-2e871897d046/aspects/source-link-status Strict(40 bytes)]
[INFO] [11/01/2023 23:11:09.022] [registry-api-akka.actor.default-dispatcher-11] [akka.actor.ActorSystemImpl(registry-api)] Request timeout encountered for request [PUT /v0/records/dist-sa-69190fb6-ea80-4070-8a2e-28d3a01eca90/aspects/source-link-status Strict(40 bytes)]

[t83714]

The following issues were related:

• block-io thread/dispatcher pool was not used when withBlockingTask directive was used
  • Why? Although the route execution context (ec) was changed, the existing ec won't be changed (unless run in a separate Future)
  • This has been solved by the new directive onCompleteBlockingTask
• Thread pool size is too big compared with DB connection pool max size
  • The total size of all pools involving DB operations should be smaller than DB connection pool max size
  • Also need to double check possible connection leak
• Transaction handling
  • Currently, a large operation (e.g. create a record) is done via a few separate small transactions. This defeats the purpose of having a transaction (as rollback won't roll all changes of the operations)
  • Rewrote the transaction handling here: https://github.com/magda-io/magda/blob/2a8d7506fd9f6dc0cc1a9b9f69511f9e0c848a07/magda-scala-common/src/main/scala/au/csiro/data61/magda/util/SQLUtils.scala#L150
  • Problem:
    • we need to change the constraint (foreign key) to DEFERRABLE and SET CONSTRAINTS to INITIALLY DEFERRED
    • Double-check the possibility of leaking db connection
  • The current implementation is not mature enough will leave to future for improvement

For the scope of this ticket, we will take care of item 1 & 2 but leave item 3 to future.

[t83714]

closed via PR: https://github.com/magda-io/magda/pull/3497