Scalability: single DB instance, multiple servers

[chgl]

In order to improve resource throughput (in an ETL-like scenario), is it feasible to run multiple instances of this server using a single (Postgres) database backend?

I've done some benchmarks using 5 instances of this server and the same database backend (official Postgres 11.5 Docker image) using datasource.driver=org.postgresql.Driver. The servers are running on Docker Swarm Mode and are load-balanced using Traefik (with simple round robin). I achieved throughputs of creating around 1000 resources per second (sending transactions containing 500 Observation resources in parallel to all servers).

This actually worked well for many benchmark runs but after some time I received the following exception:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates 
unique constraint "hfj_spidx_quantity_pkey" Detail: Key (sp_id)=(2) already exists.

I assume this is the result of a race condition when creating IDs for the resources between the multiple servers. Which is hard to reproduce and only occurred after already transferring around half a million resources.

Do you have any insights into what parts of the server may need to be tweaked to ensure Resource-integrity when multiple server instances are accessing the same database? - if this is feasible at all.

hapi-fhir-jpastarter-logs.txt

[seanmcilvenna]

I have encountered similar issues when creating multiple resources asynchronously that have the same Resource.meta.security tags. If the security tag hasn't been registered before, HAPI creates a new record in the HFJ_SECURITY_TAG table. That table is intended to contain unique security tags, but in some cases (hard to reproduce) it creates duplicate security tags when dealing with multiple resources asynchronously. I've notified @jamesagnew of this issue while at DevDays, but I'm not sure whether or not he was able to take action on it... Can you confirm, @jamesagnew?

[jamesagnew]

Hmm... The security tag one that @seanmcilvenna mentions is definitely known issue still. If two threads manage to create the exact same tag/security label for the first time at the same time (which is rare but definitely possible) one of them can end up rolled back with a 500 error.

The bug here though doesn't sound like the same thing.

Certainly HAPI's design very deliberately keeps no state in an individual instance that would be required for clustered operation (excluding Lucene, but that is a separate thing too).

The PK error you are getting suggests that a duplicate PK was generated on two nodes in your cluster. The PKs are LONG columns with values being drawn from a sequence by Hibernate. Hibernate uses a HiLo strategy and should be completely safe to work in a cluster (and always is in my experience) so something curious is happening here.

Just to confirm- Are you on the latest version of HAPI FHIR and the latest version of the PG JDBC driver? And is my understanding correct that this didn't happen after many successive runs, and only occured once after a while out of the blue (mostly wondering how reproducible this is).

[chgl]

Thank you for the feedback and the confirmation that HAPI should generally work in such a scenario!

Just to confirm- Are you on the latest version of HAPI FHIR and the latest version of the PG JDBC driver?

I amusing the very latest version of this repo (cde8981c83d0ed2a08faff3340a878276994857f). The PG JDBC driver (https://mvnrepository.com/artifact/org.postgresql/postgresql) where this issue occurred is version 42.2.5, I've just upgraded to 42.2.6. The Postgres DB is version 11.5 from Docker Hub (I've edited my original issue).

And is my understanding correct that this didn't happen after many successive runs, and only occurred once after a while out of the blue (mostly wondering how reproducible this is).

For many runs it worked well, then suddenly at the beginning of a new run (you can see from the logs that the server has just started) the Exception occurred. So maybe some initialization wasn't quite done at this point? Here's the benchmark code: https://gist.github.com/chgl/724f9d04839bd918ac482f464f3a1d93

Reproducing it is going to be challenging, I am currently stress-testing the setup and report back if it occurred again.

[psavva]

Hi @chgl @jamesagnew

I am a special scientist for the University of Cyprus, and currently starting a new implementation of the Cyprus national electronic national health system.

We are reviewing the use HAPI-FHIR as the FHIR server for medical health data storage.

Did you ever manage to resolve the issue?

[chgl]

Hi @psavva,

not really, the issue is quite hard to reproduce. The best solution is to implement some logic in your client code which will retry failed requests. I have run some additional benchmarks on single-instance HAPI FHIR servers and plan on re-trying the benchmarks at horizontal scale some time soon - I'll also make my findings available then!

[psavva]

Thank you for your comments @chgl

I would investigate this further during the development. @jamesagnew If you have any further updates, it would be great. If a race condition is identified in postgres sql only, then perhaps we can try investigate other DB adaptors as well, and identify if this is a postgres only issue, or if it's applicable to any data storage system.

[keerthivasan-r]

Hi Everyone,

Very nice discussion going on here. We are working on FHIR implementation. so, we will be working on huge data in terabytes. We would also need to run multiple docker nodes to scale well. I'm looking forward on any updates to related to this.

Any other updates? @chgl

[chgl]

hi @keerthivasan-r

unfortuntely I haven't had the time to further benchmark the hapi fhir server and test its scalability. I am currently doing some load testing using IBM's FHIR Server over at https://github.com/IBM/FHIR/issues/1425, but hope to eventually be able to compare both implementations (and maybe also Microsoft's FHIR server if it supports Postgres at some point).

[keerthivasan-r]

Thanks @chgl for letting me know

[tevariou]

@chgl did you manage to compare both HAPI and IBM servers performance? I'm very interested by your conclusions!

[chgl]

@tevariou unfortunately nothing yet :/ The hand-wavy response I can give for now is that none of the servers seem particularly fast, but there's constant work on improving it. I've also not yet tested any kind of bulk-import.

I am also still looking for the fastest and most feature-complete FHIR server there is. But I feel like the best way forward is to compare the servers (IBM, Microsoft, HAPI, etc.) based on their features and then start contributing performance-fixes to the server of choice (which is of course always much easier said than done...).

[psavva]

And of course, you should give preference to HAPI for all it's awesomeness. :)

Jokes aside, the testing on HAPI should be repeated with the rewrite of the Native SQL generation. Apparently like a 30% boost in performance... Correct me if I'm wrong @jamesagnew

[jamesagnew]

Yeah, the native SQL generation has been great so far, I'm really happy with how much it improves some queries.

FWIW it feels like we're more or less constantly agonizing on some aspect of performance in the server- these days much of it is around getting write speeds up.

[tevariou]

Thank you all for your answers! @psavva @jamesagnew Is a bulk import (https://github.com/smart-on-fhir/bulk-import/blob/master/import.md) feature in your roadmap ?

[jamesagnew]

Definitely is, although timing isn't quite certain yet.

On Thu, Feb 4, 2021 at 1:03 PM triou [email protected] wrote:

Thank you all for your answers! @psavva https://github.com/psavva @jamesagnew https://github.com/jamesagnew Is a bulk import ( https://github.com/smart-on-fhir/bulk-import/blob/master/import.md) feature in your roadmap ?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/hapifhir/hapi-fhir-jpaserver-starter/issues/48#issuecomment-773499670, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2N7HPSFWH2IT3PTT25FILS5LONTANCNFSM4IL64DXA .

[jkiddo]

Certainly HAPI's design very deliberately keeps no state in an individual instance that would be required for clustered operation (excluding Lucene, but that is a separate thing too).

@jamesagnew what about eg. the internal reindex job?

[jkiddo]

@chgl have you had a chance to have a look at https://www.smilecdr.com/benchmarking-smile-cdr in terms of performance?

[github-actions[bot]]

This issue is stale because it has been open 730 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 5 days with no activity.