Stream: implementers
Topic: HAPI, Postgres and Azure - performance
Veliyan Georgiev (Feb 11 2019 at 17:31):
Does anyone have any performance tips on getting HAPI 3.6+ to work with Postgres in Azure?
I'm doing some initial testing and I'm getting really bad performance for queries like:
https://hapi-x5lnjt7yu5yqe.azurewebsites.net/baseDstu3/Patient?gender=female&birthdate=gt1950-07-01&birthdate=lt1960-07-01&_has:Condition:subject:code=http://snomed.info/sct%7C55822004
..what seems to break it is the _has param
Here is the stack trace
2019-02-11 10:25:56.785 [qtp182639397-27] INFO c.u.f.j.s.SearchCoordinatorSvcImpl [SearchCoordinatorSvcImpl.java:543] Proceeding, as we have 250 results
2019-02-11 10:25:57.385 [search_coord_1] INFO o.h.e.j.b.internal.AbstractBatchImpl [AbstractBatchImpl.java:193] HHH000010: On release of batch it still contained JDBC statements
2019-02-11 10:25:57.386 [search_coord_1] ERROR o.h.e.j.batch.internal.BatchingBatch [BatchingBatch.java:127] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Batch entry 0 insert into HFJ_SEARCH_RESULT (SEARCH_ORDER, RESOURCE_PID, SEARCH_PID, PID) values (0, 1228, 1152, 50252) was aborted: ERROR: insert or update on table "hfj_search_result" violates foreign key constraint "fk_searchres_search"
Detail: Key (search_pid)=(1152) is not present in table "hfj_search". Call getNextException to see other errors in the batch.], SQL: insert into HFJ_SEARCH_RESULT (SEARCH_ORDER, RESOURCE_PID, SEARCH_PID, PID) values (?, ?, ?, ?)
2019-02-11 10:25:57.386 [search_coord_1] WARN o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:137] SQL Error: 0, SQLState: 23503
2019-02-11 10:25:57.386 [search_coord_1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:142] Batch entry 0 insert into HFJ_SEARCH_RESULT (SEARCH_ORDER, RESOURCE_PID, SEARCH_PID, PID) values (0, 1228, 1152, 50252) was aborted: ERROR: insert or update on table "hfj_search_result" violates foreign key constraint "fk_searchres_search"
Detail: Key (search_pid)=(1152) is not present in table "hfj_search". Call getNextException to see other errors in the batch.
2019-02-11 10:25:57.387 [search_coord_1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:142] ERROR: insert or update on table "hfj_search_result" violates foreign key constraint "fk_searchres_search"
Detail: Key (search_pid)=(1152) is not present in table "hfj_search".
2019-02-11 10:25:57.391 [search_coord_1] ERROR o.h.i.ExceptionMapperStandardImpl [ExceptionMapperStandardImpl.java:39] HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute batch]
2019-02-11 10:25:57.442 [search_coord_1] ERROR c.u.f.j.s.SearchCoordinatorSvcImpl [SearchCoordinatorSvcImpl.java:702] Failed during search loading after 192175ms
org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [insert into HFJ_SEARCH_RESULT (SEARCH_ORDER, RESOURCE_PID, SEARCH_PID, PID) values (?, ?, ?, ?)]; constraint [fk_searchres_search]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
at ca.uhn.fhir.jpa.config.HapiFhirHibernateJpaDialect.convertHibernateAccessException(HapiFhirHibernateJpaDialect.java:59)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:540)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152)
at ca.uhn.fhir.jpa.search.SearchCoordinatorSvcImpl$BaseTask.call(SearchCoordinatorSvcImpl.java:676)
at ca.uhn.fhir.jpa.search.SearchCoordinatorSvcImpl$BaseTask.call(SearchCoordinatorSvcImpl.java:449)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute batch
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:128)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.addToBatch(BatchingBatch.java:88)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3165)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3686)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:90)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)
... 9 common frames omitted
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into HFJ_SEARCH_RESULT (SEARCH_ORDER, RESOURCE_PID, SEARCH_PID, PID) values (0, 1228, 1152, 50252) was aborted: ERROR: insert or update on table "hfj_search_result" violates foreign key constraint "fk_searchres_search"
Detail: Key (search_pid)=(1152) is not present in table "hfj_search". Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2184)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:481)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1538)
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:118)
... 27 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "hfj_search_result" violates foreign key constraint "fk_searchres_search"
Detail: Key (search_pid)=(1152) is not present in table "hfj_search".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
... 33 common frames omitted
2019-02-11 10:26:02.643 [qtp182639397-27] INFO ca.uhn.fhir.context.FhirContext [FhirContext.java:171] Creating new FHIR context for FHIR version [DSTU3]
2019-02-11 10:26:02.736 [qtp182639397-27] INFO ca.uhn.fhir.util.XmlUtil [DependencyLogImpl.java:75] FHIR XML procesing will use StAX implementation 'Woodstox XML-processor' version '4.4.1'
^C[INFO] Stopped ServerConnector@5eb1fd44{HTTP/1.1,[http/1.1]}{0.0.0.0:8080}
[INFO] Stopped scavenging
[INFO] Destroying Spring FrameworkServlet 'spring'
Lloyd McKenzie (Feb 11 2019 at 17:38):
You might want to raise this on the #hapi stream
Last updated: Apr 12 2022 at 19:14 UTC
