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

Question

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?

question from:https://stackoverflow.com/questions/65646353/how-to-check-long-runing-queries-in-spring-boot-postgresql-application

Answer 1

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