我应该激活 c3p0 语句池吗？
我们正在运行 java6/hibernate/c3p0/postgresql 堆栈。 我们的 JDBC 驱动程序是 8.4-701.jdbc3
我有一些关于准备语句的问题。我已读过 关于 的优秀文档准备好的语句
但我仍然有一个问题如何使用 postgresql 配置 c3p0。
目前，
c3p0.maxStatements = 0
c3p0.maxStatementsPerConnection = 0
根据我的理解，准备好的语句和语句池是两个不同的东西：
我们的 hibernate 堆栈使用准备好的语句。 Postgresql 正在缓存 执行计划。下次使用相同的语句时，postgresql 会重用 执行计划。这节省了在数据库内规划语句的时间。
另外，c3p0 可以缓存“java.sql.PreparedStatement”的 java 实例 这意味着它正在缓存 java 对象。所以使用时
c3p0.maxStatementsPerConnection = 100 它最多缓存 100 个不同的
对象。它节省了创建对象的时间，但这与 postgresql 数据库及其准备好的语句。
正确的？
当我们使用大约 100 种不同的语句时，我会设置 c3p0.maxStatementsPerConnection = 100
但是c3p0文档在c3p0已知缺点中说
语句池的开销是 太高了。对于不这样做的司机 执行重要的预处理 准备好的语句，池化 管理费用超过任何节省。 语句池因此被关闭 默认情况下。如果您的司机这样做 预处理PreparedStatements， 特别是如果它通过 IPC 这样做的话 在 RDBMS 中，您可能会看到 显着的性能提升 打开语句池。 （这样做 通过设置配置属性 最大语句数或 maxStatementsPerConnection 到一个值 大于零。）。
那么：使用 c3p0 和 Postgresql 激活 maxStatementsPerConnection 是否合理？ 激活它有真正的好处吗？
亲切的问候 詹宁
we are running java6/hibernate/c3p0/postgresql stack.
Our JDBC Driver is 8.4-701.jdbc3
I have a few questions about Prepared Statements. I have read
excellent document about Prepared Statements
But i still have a question how to configure c3p0 with postgresql.
At the moment we have
c3p0.maxStatements = 0
c3p0.maxStatementsPerConnection = 0
In my understanding the prepared statements and statement pooling are two different things:
Our hibernate stack uses prepared statements. Postgresql is caching the
execution plan. Next time the same statement is used, postgresql reuses the
execution plan. This saves time planning statements inside DB.
Additionally c3p0 can cache java instances of "java.sql.PreparedStatement"
which means it is caching the java object. So when using
c3p0.maxStatementsPerConnection = 100 it caches at most 100 different
objects. It saves time on creating objects, but this has nothing to do with
the postgresql database and its prepared statements.
Right?
As we use about 100 different statements I would set
c3p0.maxStatementsPerConnection = 100
But the c3p0 docs say in c3p0 known shortcomings
The overhead of Statement pooling is
too high. For drivers that do not
perform significant preprocessing of
PreparedStatements, the pooling
overhead outweighs any savings.
Statement pooling is thus turned off
by default. If your driver does
preprocess PreparedStatements,
especially if it does so via IPC with
the RDBMS, you will probably see a
significant performance gain by
turning Statement pooling on. (Do this
by setting the configuration property
maxStatements or
maxStatementsPerConnection to a value
greater than zero.).
So: Is it reasonable to activate maxStatementsPerConnection with c3p0 and Postgresql?
Is there a real benefit activating it?
kind regards
Janning
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不记得 Hibernate 是否实际存储了PreparedStatement 实例本身，或者依赖连接提供程序来重用它们。 （快速扫描 BatcherImpl 表明，如果连续多次执行相同的 SQL，它会重用最后一个PreparedStatement）
我认为 c3p0 文档试图说明的一点是，对于许多 JDBC 驱动程序来说，PreparedStatement 没有用：驱动程序最终将简单地在客户端拼接参数，然后将构建的 SQL 语句传递到数据库。对于这些驱动程序来说，PreparedStatements 根本没有任何优势，任何重用它们的努力都是浪费。 （Postgresql JDBC FAQ 表示，在服务器协议版本 3 之前的 Postgresql 就是这种情况，并且有文档中提供了更多详细信息。
对于确实有效处理PreparedStatement 的驱动程序，仍然可能需要实际重用PreparedStatement 实例才能获得任何好处。例如，如果驱动程序实现：
鉴于此，如果应用程序总是打开一个准备好的语句，执行一次然后再次关闭它，那么仍然没有任何好处；事实上，情况可能会更糟，因为现在可能有更多的往返航班。因此应用程序需要挂起PreparedStatement 实例。当然，这会导致另一个问题：如果应用程序挂起太多，并且每个服务器端语句都会消耗一些资源，那么这可能会导致服务器端问题。在有人直接使用 JDBC 的情况下，这可能会通过一些已知可重用的语句来管理，因此要做好准备；有些不是，只是使用瞬态 Statement 实例来代替。 （这跳过了准备好的语句的另一个好处：处理参数转义）
所以这就是为什么 c3p0 和其他连接池也有准备好的语句缓存 - 它允许应用程序代码避免处理所有这些。这些语句通常保存在一些有限的LRU池中，因此常见的语句会重用PreparedStatement实例。
最后一个难题是 JDBC 驱动程序自己可能决定聪明地执行此操作；服务器本身也可能决定变得聪明，并检测客户端提交的语句在结构上与先前的语句相似。
鉴于 Hibernate 本身并不保留PreparedStatement 实例的缓存，因此您需要让 c3p0 执行此操作才能从它们中受益。 （由于重用缓存的计划，这应该减少常见语句的开销）。如果 c3p0 不缓存准备好的语句，那么驱动程序只会看到应用程序准备一条语句，执行它，然后再次关闭它。看起来 JDBC 驱动程序有一个 "threshold" 设置，用于避免准备/execute 服务器开销（在应用程序始终执行此操作的情况下）。所以，是的，您需要让 c3p0 进行语句缓存。
希望有帮助，抱歉有点啰嗦。答案是是。
I don't remember offhand if Hibernate actually stores PreparedStatement instances itself, or relies on the connection provider to reuse them. (A quick scan of BatcherImpl suggests it reuses the last PreparedStatement if executing the same SQL multiple times in a row)
I think the point that the c3p0 documentation is trying to make is that for many JDBC drivers, a PreparedStatement isn't useful: some drivers will end up simply splicing the parameters in client-side and then passing the built SQL statement to the database anyway. For these drivers, PreparedStatements are no advantage at all, and any effort to reuse them is wasted. (The Postgresql JDBC FAQ says this was the case for Postgresql before sever protocol version 3 and there is more detailed information in the documentation).
For drivers that do handle PreparedStatements usefully, it's still likely necessary to actually reuse PreparedStatement instances to get any benefit. For example if the driver implements:
Given this, if the application always opens a prepared statement, executes it once and then closes it again, there's still no benefit; in fact, it might be worse since there are now potentially more round-trips. So the application needs to hang on to PreparedStatement instances. Of course, this leads to another problem: if the application hangs on to too many, and each server-side statement consumes some resources, then this can lead to server-side issues. In the case where someone is using JDBC directly, this might be managed by hand- some statements are known to be reusable and hence are prepared; some aren't and just use transient Statement instances instead. (This is skipping over the other benefit of prepared statements: handling argument escaping)
So this is why c3p0 and other connection pools also have prepared statement caches- it allows application code to avoid dealing with all this. The statements are usually kept in some limited LRU pool, so common statements reuse a PreparedStatement instance.
The final pieces of the puzzle are that JDBC drivers may themselves decide to be clever and do this; and servers may themselves also decide to be clever and detect a client submitting a statement that is structurally similar to a previous one.
Given that Hibernate doesn't itself keep a cache of PreparedStatement instances, you need to have c3p0 do that in order to get the benefit of them. (Which should be reduced overhead for common statements due to reusing cached plans). If c3p0 doesn't cache prepared statements, then the driver will just see the application preparing a statement, executing it, and then closing it again. Looks like the JDBC driver has a "threshold" setting for avoiding the prepare/execute server overhead in the case where the application always does this. So, yes, you need to have c3p0 do statement caching.
Hope that helps, sorry it's a bit long winded. The answer is yes.
请记住，每个连接都必须缓存语句，这意味着您将不得不消耗大量内存，并且需要很长时间才能看到任何好处。因此，如果您将其设置为使用 100 个要缓存的语句，则实际上是 100*连接数，或者 100/无连接，但您仍然需要花费相当长的时间才能使缓存产生任何有意义的效果。
Remember that statements have to be cached per connection which will mean you're going to have to consume quite a chunk of memory and it will take a long time before you'll see any benefit. So if you set it to use 100 statements to be cached, that's actually 100*number of connections or else 100/no of connections but you will still need to take quite some time until your cache will have any meaningful effect.