Stream: ibm
Topic: slow response on moderate dataset
Xiaocheng Luan (Nov 12 2019 at 14:59):
Hi @Lee Surprenant I'm seeing unusual slowness in responses, I'm wondering if I missed anything or whether you have any rough performance numbers? I understand Derby DB will be slow, but not sure if the numbers observed below are "normal".
Here are the details:
- The FHIR server was deployed with the default configurations (Derby DB), on a server with 8 CPU and 32G memory (hmm, did not explicitly set -Xmx).
- Uploaded some 300K resources, most of which are observations, total json data size ~300MB, took less than an hour to load
On the API calls, however, the responses were surprisingly slow:
- Getting (well, searching/retrieving) 1 (out of 300K observations), took 12 seconds: https://localhost:9443/fhir-server/api/v4/Observation?_count=1; repeat the same request multiple times and the response times were comparable.
- Getting 1 (out of 45) patient was ok, took some 250ms: https://localhost:9443/fhir-server/api/v4/Patient?_count=1
- Getting 1 (out of ~3000) condition was not too bad, took some 400ms: https://localhost:9443/fhir-server/api/v4/Condition?_count=1
- Searching (and get 1) observation of a given patient, took 22 seconds: https://localhost:9443/fhir-server/api/v4/Observation?_count=1&subject=Patient/xxxx
- Getting an observation by id, took ~700ms: https://localhost:9443/fhir-server/api/v4/Observation/8850efc8-c110-4e8b-a035-36f5e138e5db
Lee Surprenant (Nov 12 2019 at 18:46):
thanks Xiaocheng, we'll have a look. agree those numbers look bad. we'll try running a similar test against Derby and Db2 and report back.
Lee Surprenant (Nov 12 2019 at 18:49):
in the meantime, if you are curious to poke around, you can enable logging on our persistence layer to see the exact SQL getting executed. as you might expect, Getting by an id should be little more than a straight select from the corresponding table, so i'd be curious how long it takes if we execute the SQL directly (e.g. via the derby ij tool or similar)
Paul Bastide (Nov 13 2019 at 12:14):
Hi Xiaocheng, Did you use embedded derby? by default we set 4GB in jvm.options. If you used the Derby network server, it's default is 512M, you'd want to increase this.
Albert(Xu) Wang (Nov 13 2019 at 15:19):
hi XiaoCheng, we had some similar bad performance results when using derby, I would suggest you to try db2. you can use the db2 docker image here https://hub.docker.com/r/ibmcom/db2 for your testing, and make sure to use the the 90-day trail license to remove the memory limitation. I had created 300K observations and patients with db2, and all my search tests were finished in just few seconds. let us know if you need some help to set up db2 for your fhir server.
Lee Surprenant (Nov 13 2019 at 15:31):
Given this good feedfack on the poor performance of derby, we just added the following line in the docs:
https://github.com/IBM/FHIR/pull/404/files
Xiaocheng Luan (Nov 13 2019 at 15:47):
in the meantime, if you are curious to poke around, you can enable logging on our persistence layer to see the exact SQL getting executed. as you might expect, Getting by an id should be little more than a straight select from the corresponding table, so i'd be curious how long it takes if we execute the SQL directly (e.g. via the derby
ijtool or similar)
Will do when I get a chance and report back, thanks for the response.
Xiaocheng Luan (Nov 13 2019 at 15:48):
hi XiaoCheng, we had some similar bad performance results when using derby, I would suggest you to try db2. you can use the db2 docker image here https://hub.docker.com/r/ibmcom/db2 for your testing, and make sure to use the the 90-day trail license to remove the memory limitation. I had created 300K observations and patients with db2, and all my search tests were finished in just few seconds. let us know if you need some help to set up db2 for your fhir server.
Thanks for sharing the experience, and thanks for the tips.
Xiaocheng Luan (Nov 13 2019 at 15:49):
Hi Xiaocheng, Did you use embedded derby? by default we set 4GB in jvm.options. If you used the Derby network server, it's default is 512M, you'd want to increase this.
Yes, we use derby, and everything default. I just checked the process it's using 4GB, so should be reasonable.
Xiaocheng Luan (Nov 13 2019 at 15:53):
Thanks for the answers, @Albert(Xu) Wang , @Paul Bastide . For whatever reason "quote and reply" within your messages put my responses under Lee's message.
Lee Surprenant (Nov 13 2019 at 16:16):
For whatever reason "quote and reply" within your messages put my responses under Lee's message.
thats just how zulip works...no subthreads like slack has
Albert(Xu) Wang (Nov 13 2019 at 18:19):
hi XiaoCheng, we have just added a new testng test here https://github.com/IBM/FHIR/blob/master/fhir-server-test/src/test/java/com/ibm/fhir/server/test/SearchPerformaceTest.java Screen-Shot-2019-11-13-at-8.44.27-AM.png and the test results of db2 with 300K observations and patients.
Lee Surprenant (Nov 13 2019 at 18:51):
Albert, those results were from running the db2 container like you mentioned earlier in the thread? What were the limits there again? 4GB of RAM?
Albert(Xu) Wang (Nov 18 2019 at 02:06):
this is the instruction about how to use db2 docker container for integration test, the limitation is removed by enabling the 90 dail trail license in create-database.sh. https://github.com/IBM/FHIR/blob/master/fhir-install/README-DB2.md
Last updated: Apr 12 2022 at 19:14 UTC
