JPA server performance

[Nick Peterson]

Hey all,

I'm using the example implementation of the FHIR server, and having lots of performance problems with it.  I'm trying to push a fair amount of data at it (as DSTU2 bundles of resources), and it frequently seems to choke and freeze up.

I'm running using the built-in jetty server.  I already swapped in a Postgres database instead of the built-in Derby database.  That helped a little, but not quite enough. 

Does anybody have any experiences tuning the server to handle some throughput that they'd be willing to share?  

Thank you!

Best,

Nick

[James Agnew]

Hi Nick,

Sorry for the slow response. I started a reply and saved it and forgot to finish it!

For our part, we haven't done a lot of load testing on the writing (create/update) side yet. That's an interesting observation. Do you have any more specifics on what you're seeing? E.g. how often does this happen, how many resources are in a typical transaction and how many transactions are you sending to see this slowdown?

Just to get these thoughts down: There are a few things that could probably be tweaked to get writing a good bit faster, but all would require coding:

* We currently GZip each resource before saving it to the DB. It might make sense to not do this, or to defer doing this.

* We also index resources as a part of the store routine. One thing we could do is to set it up to do the indexing later. In other words, make saving resources an atomic operation, but have a separate thread that goes through and indexes saved/updated resources after the fact.

Cheers,

James

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.
To post to this group, send email to hapi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/3ddc099b-a653-4543-ac27-cbfe82d9fec2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sanjeev...@gmail.com]

Hey ALL

Can anyone got bench marking information for HAPPI FHIR SERVER. Following are below points.

1.  Whats DB configuration  required ( shared memory / data space/ DB execution  )

2.  Leucine search index need to keep in disk or any NAS

3.  Whats memory requirement / Machine configuration requirement.( FOR DB/ HAPPI FHIR SERVER INSTALLATION)

4.  For Tomcat deployment can we remove DBCP and use Tomcat connection pool.

5.  Any other performance configuration like thread pool required , as we could find 10 concurrent  thread are processing insert statement.

 Thanks 

Sanjeev Chakraborty

[emmanuel....@gmail.com]

Hi,

I also use hapi-fhir (1.4-snapshot) with Postgres and search and update operations can be slow, i think that's because some queries are using non indexed fields with filters. These operations became faster on my setup (2 GB RAM, Java 1.8, Linux Ubuntu 15.10) after adding indexes on these elements :
 - hfj_spidx_token(res_id)
 - HFJ_SPIDX_STRING(res_id)
 - HFJ_SPIDX_DATE(res_id)
 - HFJ_SPIDX_URI(res_id)
I do not use these tables : hfj_spidx_number, hfj_spidx_quantity so i did not investigated about them...

I haven't done precise measures, i just found the "slow" queries by running SELECT * FROM pg_stat_activity ; on the Postgres database when the server was searching/updating my resources and analysing the returned running queries with explain.

And I also created an index (this one seems to be postgres specific) to do like 'something%' searches on hfj_spidx_string(sp_value_normalized). This subject of indexes optimized for "like" queries is discussed here : http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations.

Note : Indexes become more helpful when the system is loaded with a lot of data (Database does not fit in RAM).

Sorry if my English is bad.
Emmanuel

[James Agnew]

Hi Emannuel,

Thanks for the analysis here, this is great.

I'm going to add default RES_ID indexes to all of the search param tables. Any chance you could post in the complete SQL for adding the PG index you are using for the like queries? I'd like to add that to the documentation too.

Cheers,

James

--

You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.
To post to this group, send email to hapi...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/9cc1e74a-3c9c-4b07-b35f-3bcd1e6323b9%40googlegroups.com.

[emmanuel....@gmail.com]

Here are the queries i used for postgres.

/*Optimize "like xxx%" queries, probably postgres specific*/
CREATE INDEX idx_sp_string_like on public.hfj_spidx_string(sp_value_normalized text_pattern_ops)
/*TODO : create index to optimize "like %xxx%" queries if needed (see link in my previous post)*/

/*Optimize some queries used by Hapi/JPA during Resources updates*/
CREATE INDEX idx_sp_res_id on public.hfj_spidx_token(res_id)
CREATE INDEX idx_sp_string_res_id on public.HFJ_SPIDX_STRING(res_id)
CREATE INDEX idx_sp_date_res_id on public.HFJ_SPIDX_DATE(res_id)
CREATE INDEX idx_sp_uri_res_id on public.HFJ_SPIDX_URI(res_id)
CREATE INDEX idx_sp_number_res_id on public.hfj_spidx_number(res_id)
CREATE INDEX idx_sp_quantity_res_id on public.hfj_spidx_quantity(res_id)

/*Perform maintenance operation : these operations take some time and can block database operations. I think that i will schedule one query each night (all queries need about 10 minutes now with about 600000 resources on my setup, will probably be faster with a modern computer)*/
reindex table public.hfj_spidx_string /*~4 minutes*/
reindex table public.hfj_spidx_token /*~4 minutes*/
reindex table public.HFJ_SPIDX_DATE /*~30 seconds*/
reindex table public.HFJ_SPIDX_URI /*~30 seconds*/
reindex table public.hfj_resource /*~30 seconds*/
reindex table public.hfj_res_link /*~30 seconds*/
reindex table public.hfj_spidx_number
reindex table public.hfj_spidx_quantity