Y
Y
yuharu2017-11-09 11:51:24
PostgreSQL
yuharu, 2017-11-09 11:51:24

How to avoid blocking parallel queries in Postgresql?

Starts 10 threads from the ExecutorService thread pool. In each thread, a connection is created and 1 sql query is executed and committed. By itself, a large selection query (select from several tables) takes 1-2 minutes.
After start requests hang and are not executed. Tracking was done from pg_stat_activity, where 10 threads (sometimes these statistics changed, sometimes blocked, sometimes not) have the following statuses:
5a041667f421d948147271.png
They can be completed in an hour. Previously, this problem was not due to less data in the tables with the same queries.

<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQL82Dialect</property>
    <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
    <property name="show_sql">true</property>
    <property name="log_statement">all</property>
    <property name="hibernate.hikari.dataSourceClassName">org.postgresql.ds.PGSimpleDataSource</property>
    <property name="hibernate.hikari.connectionTimeout">300000</property>
    <property name="hibernate.hikari.maximumPoolSize">35</property>
    <property name="hibernate.hikari.poolName">hicari</property>
    <property name="hibernate.hikari.dataSource.url"></property>
    <property name="hibernate.hikari.dataSource.user"></property>
    <property name="hibernate.hikari.dataSource.password"></property>
    <property name="hibernate.hikari.autoCommit">false</property>
    <property name="hibernate.current_session_context_class">thread</property>

    @Override
public List<SendObj> call() {
    List<SendObj> sendObjList = null;

    Session session = sessionFactory.getCurrentSession();
    session.getTransaction().setTimeout(200);
    session.getTransaction().begin();

...
//здесь происходит выполнение query.list()
...

    if (session.isOpen())
        session.getTransaction().commit();

    return sendObjList;
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Stanislav Makarov, 2017-11-09
@Nipheris

https://www.postgresql.org/docs/10/static/transact...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question