CODEWITHSUNDEEP
hibernatepostgresqltomcatjdbc
ams
ams

Reputation: 62642

How to configure PreparedStatement Caching with Postgres and Tomcat7 JDBC Pool?

I am using Tomcat7 with Postgres 9.1 and JPA with Hibernate and I want to configure prepared statement pooling.

The normal default behavior that I have seen by looking at the postgres query log is to see lots of PARSE, BIND, execute for the same query so it seems that even though jdbc prepared statements are used they are not being cached by the postgres jdbc driver or on the server.

Reading online seemed to indicate that I had to configure prepared statement pooling on the postgres jdbc driver so I added connectionProperties="prepareThreshold=3" to my application context.xml resource definition but I still don't see any caching going on it is still a lot of PARSE, BIND messages in the log file, even when I get a prepared statement to execute more than 3 times.

I am aware that the query planner can't make as good of a plan for a prepared statement as a statement with the parameters passed in. Since I am using Hibernate all the statements being sent to the db will be JDBC prepared statements so there is nothing I can do about that.

The application I am working on is not yet in production so I have no field measurements about the value of configuring prepared statement caching? Does it generally improve improve performance in the real world when using postgres and hibernate?

Anyone know to successfully configure prepared statement caching with postgres and tomcat7 jdbc pool not the dbcp pool? my config 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"
/>

Upvotes: 3

Views: 3921

Answers (1)

moj
moj

Reputation: 454

I think pooling of prepared statements is not supported by tomcat jdbc pool. Documentation says for "poolPreparedStatements": "[...] Property not used."

Maybe this is useful for you: News about Tomcat jdbc pool

Upvotes: 1

Related Questions