Stream: hapi
Topic: HAPI, Postgres, Azure - performance tuning
Veliyan Georgiev (Feb 11 2019 at 17:43):
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:
..what seems to break it is the _has param
LWLock buffer_io count is above 250K for the following SQL
select resourceta0_.RES_TYPE as col_0_0_, count(resourceta0_.RES_TYPE) as col_1_0_ from HFJ_RESOURCE resourceta0_ group by resourceta0_.RES_TYPE
Here is the stack trace as well
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'
Last updated: Apr 12 2022 at 19:14 UTC