I am using Tomcat7 with Postgres 9.1 and JPA with Hibernate, and I want to configure a ready-made report build.
The usual default behavior that I saw when viewing the postgres request log is to see a lot of PARSE, BIND, execute for the same request, so it seems that although prepared jdbc statements are used, they are not cached by postgres jdbc or on server.
Reading on the Internet showed that I had to configure the prepared instruction pool for the postgres jdbc driver, so I added connectionProperties="prepareThreshold=3" to the application resource definition context.xml, but I still can’t see any caching going on. there are many PARSE, BIND messages in the log file, even when I get a prepared statement to execute more than three times.
I know that the query planner cannot make the plan for the prepared operator as an operator with the parameters passed. Since I use Hibernate, all statements sent to db will be prepared by JDBC statements so there is nothing I can do about it.
The application I'm working on has not yet been released, so I don’t have field measurements about the value of setting ready-made instruction caching? Does this improve performance in the real world when using postgres and hibernate?
Does anyone know to successfully configure caching of ready-made instructions using postgres and tomcat7 jdbc pool, rather than dbcp pool? my configuration is below.
<Resource name="jdbc/thedb" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" auth="Container" type="javax.sql.DataSource" jmxEnabled="true" logAbandoned="true" suspectTimeout="60000" jdbcInterceptors="StatementFinalizer;ResetAbandonedTimer" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://127.0.0.1:5432/thedb" username="theusername" password="thepassword" connectionProperties="prepareThreshold=3" maxActive="20" maxIdle="20" minIdle="3" maxWait="5000" testOnBorrow="true" validationInterval="30000" validationQuery="SELECT count(*) FROM data_source_test" />
ams
source share