6

We are refactoring the persistence layer of a Java application from JDBC Template to JPA/Hibernate.

I am profiling the SQL statements being issued to the database and I see "SHOW WARNINGS" is issued many, many times. According to JProfiler the "SHOW WARNINGS" is accounting for a considerable amount of 'inherent time'.

What could cause SHOW WARNINGS to be issued so frequently?

This SHOW WARNINGS was NOT previously issued when using Jdbc Template.

Below is the part of our stack relevant to persistence. The only change here is the introduction of JPA/Hibernate.

  • JPA / Hibernate: 4.3.6
  • MySQL driver: 5.1.33
  • MySQL database: 5.6.20
  • JDBC Connection Pool: HikariCP-2.3.2

EDIT: Here's a sample stack trace of when SHOW WARNINGS is issued.

com.mysql.jdbc.StatementImpl.getWarnings()
com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.getWarnings()
org.hibernate.jpa.internal.QueryImpl.getSingleResult()
com.mysema.query.jpa.impl.AbstractJPAQuery.getSingleResult(javax.persistence.Query)
com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult()
com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult(com.mysema.query.types.Expression)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[ ])
com.sun.proxy.$Proxy115.findOne(com.mysema.query.types.Predicate)
Justin
  • 6,031
  • 11
  • 48
  • 82
  • did you stumble into how to suppress warnings ? – Drew Jun 18 '15 at 22:07
  • Are you running MySQL with the `--show-warnings` option? – DuncanKinnear Jun 18 '15 at 23:04
  • Can you post the xml configuration of your persistence-unit? – DuncanKinnear Jun 18 '15 at 23:07
  • @Duncan, I don't think this is related to MySQL config because when I run on same DB with previous persistence strategy the SHOW WARNINGS are not issued. I'll see if I can add pu xml. but are you aware of any JPA/Hibernate setting which could influence this? – Justin Jun 19 '15 at 03:57
  • I think it could be your auto-commit setting. In JDBC you probably explicitly set this to false, but Hibernate sets this to true by default. The SHOW WARNINGS could be Hibernate performing the auto-commits. – DuncanKinnear Jun 19 '15 at 04:04
  • In the configuration of our datasource for our PostgreSQL database we have the property: ``. Not sure if the same property could be used for your MySQL driver. – DuncanKinnear Jun 19 '15 at 04:07

2 Answers2

10

This is WHY show warnings is being issued: org.hibernate.engine.jdbc.spi.SqlExceptionHelper.handleAndClearWarnings() is calling com.mysql.jdbc.StatementImpl.getWarnings().

A comment in the Hibernate source code says:

"See HHH-9174. Statement#getWarnings can be an expensive call for many JDBC libs. Don't do it unless the log level would actually allow a warning to be logged."

I upped the logging level on logback.xml on "org.hibernate" to ERROR. Profiling shows SHOW WARNINGS query is NO LONGER being issued.

This has made a marginal improvement in performance.

I would appreciate any input as to whether it is actually a good idea to disable SHOW WARNINGS here.

Justin
  • 6,031
  • 11
  • 48
  • 82
  • 2
    **I would suggest it's a good idea to disable for a production server.** It could prove valuable in a dev/test environment and catch things like truncation due to data type mismatches but if that's happening you have app/schema mismatches and probably should look at preventing that from happening rather than catching it after you've lost data. For some context/data on the performance issues that I was seeing which lead me to find this answer: [pt-query-digest analysis via tcpdump, load @ 7.8k QPS](http://pastebin.com/N2i1uBHQ) – pickles Jun 14 '16 at 05:25
  • Hi Justin, any chance you could share the important part of your logback.xml ? I have the same problem while is present in my logback.xml – Patrik Beck Jan 12 '17 at 12:37
  • – Justin Jan 14 '17 at 16:44
  • Do you know how to raise the log level for the [plain old default vanilla Hibernate settings](https://stackoverflow.com/q/44211525/616460) (i.e. not using Logback, so no `logback.xml`)? Btw, of interest: https://hibernate.atlassian.net/browse/HHH-9174; that's where the ability for `SHOW WARNINGS` to be disabled based on log level was originally added. Also re: your last paragraph, I don't know, but lots of blogs about this issue talk about disabling it in production servers in general, and comments on said blogs support that as a best practice, too. – Jason C May 27 '17 at 01:10
0

Nothing of this works on payara & log4j, hibernate is still polluting DB with 'show warnings' statements

Tomaž Majerhold
  • 149
  • 5