[FIXED] How to check long-runing queries in spring boot/postgresql application?

Issue

I have a Spring boot application with Hibernate, connecting to a PostgreSQL database. After some time, some users experience a problem with slow requests or requests without any response.

I suspect some long-running SQL queries, but how can I check which queries run long? I would like to log down execution times of queries. I know show-sql parameter for Hibernate, but it doesn't display arguments for SQL statements, nor it doesn't log execution times. Is there any other way to achieve that?

Solution

You can log slow queries in hibernate by setting the property:

spring.jpa.properties.hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS=100

Also, if you want to log SQL statements with parameters, you can use:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Answered By - Patrycja Wegrzynowicz