Stream: ibm
Topic: bulk import performance
Teva Riou (Jan 12 2021 at 10:21):
Hello everyone!
I'm using the IBM fhir server docker image along with a static file server which I use to import ndjson files from via HTTPS in a Scaleway DEV1-L instance (4 vCPU 8 Go 80 Go SSD NVMe). The import is working fine without error. When monitoring my postgres database though I'm seeing an import rate of around 1 Mb/s (~100 resources / s). Do you think it is expected or should it be higher ? Please tell me if you need more details and thank you!
Lee Surprenant (Jan 12 2021 at 14:26):
Importing from a single file or multiple in parallel?
Teva Riou (Jan 12 2021 at 15:00):
I have one file per resource type (patient, observation, etc...) which I import in one single $import request. So far i've tested importing 1, 4 and 24 files in parallel with a steady rate of ~100 imported resources / resource type / second. Screenshot-2021-01-12-at-16.03.43.png
Lee Surprenant (Jan 12 2021 at 15:55):
thats a bit below the numbers we had collected in our environment: https://github.com/ibm/fhir/issues/673#issuecomment-601794798
but in our case, we were importing from ndjson files that existed on IBM cloud object storage.
is your postgresql on the same local network or is it hosted/remote? and what are the specs on that?
are you using the same db for the batch jobs?
Robin Arnold (Jan 12 2021 at 17:01):
A couple of things to look at. Are you using pure random ids (UUIDs) for the resources? This causes issues because it dirties so many random blocks in the b-tree indexes. This then exacerbates the issue with full-page-writes in PostgreSQL which results in a lot of log traffic. One solution we use is to prefix the UUID string with a time-based component. This results in right-hand inserts into the indexes and far fewer blocks being dirtied. Here's some example code: https://github.com/IBM/FHIR/blob/master/fhir-persistence-jdbc/src/main/java/com/ibm/fhir/persistence/jdbc/util/TimestampPrefixedUUID.java
Have you tuned the PostgreSQL configuration?
Teva Riou (Jan 12 2021 at 17:53):
Everything is on the same docker network in the same instance. The web file server serves the ndjson files stored in a docker volume. Thank you for the link to the benchmarks. And yes I am using pure uuids v4 for the resources, I will look into that ! And I will look into my default pg config as well since I didn't touch it so far. Do you have any advice on that? Thank you a lot for your help.
Robin Arnold (Jan 12 2021 at 18:30):
We've found that increasing shared_buffers to be 50% of the available memory can be helpful (although you have to be a little careful on smaller servers because you need to leave enough space for the rest of the system (OS plus other PostgreSQL needs). Also, take a look at checkpoint intervals and if they are occurring too frequently, consider increasing max_wal_size. But I wouldn't recommend doing this until you've addressed the UUID issue (because dirtying so many random pages greatly increases the amount of work needed for checkpointing. After addressing the UUID issue, you should see the checkpoint load drop significantly.
Last updated: Apr 12 2022 at 19:14 UTC
