Spring JPA and Hibernate slow query log

Question

I have a Spring JPA application which uses hibernate and mysql. I would like to log slow running database queries on this application. Hibernate provides an option to configure 'hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS' (with log level org.hibernate.SQL_SHOW: INFO). However using this option will log SQL with parameter values set.

Is there an option in hibernate to replace the parameter values with '?'.

ex: insert into customer (name) values (?)

instead of

insert into customer (name) values ('John')

FYI, Setting the log level on org.hibernate.SQL to DEBUG will log the SQL with ? instead of parameter values. This will print all the SQL in the logs, In my case I only want to log slow running queries

Appreciate any help.

Rick James · Accepted Answer · 2022-02-17 20:50:42Z

0

(Maybe Hibernate is doing more than necessary...)

MySQL optionally creates "slowlog" that contains details (in including 'John') on the slow query. However, the next thing that can be done is to "digest" the log. This will replace strings and numbers with ? or S (for string). So, if you can bypass Hibernate and get the log, use pt-query-digest or mysqldumpslow -s t to do the digesting. This gives a much shorter output file (by combining queries with the same 'digest' or 'signature').

The underlying setting is long_query_time; it is a number of "seconds" and can be a fractional value such as "0.5" for half a second. Setting it to 0 would capture all queries, which it sounds like you don't want. (I almost never go much below 1.)

answered Feb 17, 2022 at 20:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

achyuthgurram Over a year ago

Thanks for your response. Unfortunately this approach will not work for us due to the compliance reasons. Based on my research it sounds like neither MySQL or hibernate has options for what I am looking for. I will have to write my own code to find the slow running queries.

Collectives™ on Stack Overflow

Spring JPA and Hibernate slow query log

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related