如何使用Postgres和Tomcat7 JDBC池配置PreparedStatement缓存？ [英] How to configure PreparedStatement Caching with Postgres and Tomcat7 JDBC Pool?

问题描述

我使用Tomcat7和Postgres 9.1以及JPA和Hibernate，我想配置准备好的语句池。

通过查看postgres查询日志，我看到的正常默认行为是查看大量的PARSE，BIND，执行相同的查询，所以似乎即使使用jdbc预处理语句，它们不会被postgres jdbc驱动程序或服务器缓存。

在线阅读似乎表明我必须在postgres jdbc驱动程序上配置准备语句池，因此我添加了 connectionProperties =prepareThreshold = 3到我的应用程序的context.xml资源定义中，但我仍然没有看到任何缓存继续进行下去，在日志文件中仍然有很多PARSE，BIND消息，即使我得到一个准备好的语句来执行更多比3倍。

我知道查询规划器无法像传入参数的语句那样将预准备语句的计划做得更好。由于我使用的是Hibernate发送到数据库的语句将是JDBC准备的语句，所以我无能为力。

我正在使用的应用程序尚未投入使用，因此我没有关于配置准备好的语句缓存的价值的现场测量？当使用postgres和hibernate时，它通常会提高
真实世界的性能吗？

任何人都知道使用postgres和tomcat7成功配置准备好的语句缓存jdbc pool不是dbcp池？

 < 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
 /> 
  

解决方案

我认为tomcat不支持准备语句的池化jdbc池。 文档对poolPreparedStatements说：[...]

也许这对你有用：关于Tomcat 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"
/>

解决方案

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

这篇关于如何使用Postgres和Tomcat7 JDBC池配置PreparedStatement缓存？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！