c3p0 maxIdleTime与mysql的wait_timeout相同？ [英] c3p0 maxIdleTime is same as wait_timeout of mysql?

问题描述

我有一个Spring MVC + Mysql（JDBC 4）+ c3p0 0.9.2项目。

I am having an Spring MVC + Mysql (JDBC 4) + c3p0 0.9.2 project.

在c3p0 maxIdleTime 值为240（即4分钟）和 wait_timeout 在Mysql的my.ini到30秒。

In c3p0 maxIdleTime value is 240 (i.e 4 mins.) and wait_timeout in my.ini of Mysql to 30 seconds.

根据c3p0

maxIdleTime：
（默认值：0）
秒连接可以保持池之前被丢弃。零表示空闲连接永不过期。

maxIdleTime: (Default: 0) Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

根据Mysql

wait_timeout：服务器在关闭
非交互式连接之前等待其活动的秒数。

wait_timeout: The number of seconds the server waits for activity on a noninteractive connection before closing it.

1. 我已经知道了一些答案>未使用的连接是指根据mysql（？） sleep 状态的连接


2. 什么是交互式和非交互式连接？ li>
   
3. 未使用的连接和非交互式连接是否相同？因为我的DBA将 wait_timeout 设置为30秒（他通过观察DB服务器，以便非常少量的连接在睡眠模式下达到此值），这意味着连接可以在 sleep 模式30秒后，它将被关闭，但另一方面c3p0的 maxIdleTime 设置为240秒， c> c> c> c>

1. unused connection means the connection which are in sleep state according to mysql(?)
2. What is interactive and noninteractive connections?
3. Is unused connections and noninteractive coonections are same? because my DBA set wait_timeout to 30 seconds (he come to this value by observing DB server so that very less amount of connections be in sleep mode) this means an connection can be in sleep mode for 30 seconds after that it will be closed but at the otherhand c3p0's maxIdleTime is set to 240 seconds so whats this maxIdleTime setting playing role in this case.
4. What is interactive_timeout?

推荐答案

首先让我们来了解mysql属性。

First Let's understand the mysql properties.

• interactive_timeout ：交互式超时mysql shell会话
  在几秒钟像mysqldump或mysql命令行工具。连接处于睡眠状态。大多数时候，这是设置为更高的价值，因为你不希望它在mysql cli做某事时断开连接。


• wait_timeout
  ：MySQL将在
  之前等待的秒数，它将在
  秒内关闭非交互式连接的连接。示例：connected from java。

• interactive_timeout : interactive time out for mysql shell sessions in seconds like mysqldump or mysql command line tools. connections are in sleep state. Most of the time this is set to higher value because you don't want it to get disconnected while you are doing something on mysql cli.
• wait_timeout : the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection in seconds. example: connected from java. connections are in sleep state.

现在让我们来了解c3po属性和它与DB props的关系（我只是要从你的问题中复制）

Now let's understand c3po properties and it's relation with DB props.(I am just gonna copy from your question)

• maxIdleTime :(默认值：0）秒连接可以保留池，但在被丢弃之前未使用。零表示空闲连接从不
  过期。

• maxIdleTime: (Default: 0) Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

可以使用，并将在池中可用。一旦超时结束c3po会毁了它或回收它。

This refers to how long a connection object can be usable and will be available in pool. Once the timeout is over c3po will destroy it or recycle it.

现在问题出现在你有 maxIdleTime 高于 wait_timeout 。
让我们说如果 mxIdleTime：50 secs和 wait_timeout：40 s 如果您尝试在最近10秒内执行任何操作，将会获得连接超时异常：Broken Pipe 。因此 maxIdelTime 应始终小于 wait_timeout 。

Now the problem comes when you have maxIdleTime higher then the wait_timeout. let's say if the mxIdleTime : 50 secs and wait_timeout : 40 s then there is a chanse that you will get Connection time out exception: Broken Pipe if you try to do any operation in last 10 seconds. So maxIdelTime should always be less then wait_timeout.

您可以使用以下属性来代替maxIdleTime。

Instead of maxIdleTime you can you the following properties.

• idleConnectionTestPeriod 连接多长时间，
  在测试之前保持空闲。没有 preferredTestQuery ，默认
  是 DatabaseMetaData.getTables（） - 是数据库不可知，
  虽然是一个相对昂贵的调用，可能是一个
  相对较小的数据库。如果你对性能有偏见，请使用特定于你的数据库的
  查询（ie preferredTestQuery =SELECT 1）


• maxIdleTimeExcessConnections 会在活动达到峰值后将connectionCount返回
  降低到minPoolSize。

• idleConnectionTestPeriod sets a limit to how long a connection will stay idle before testing it. Without preferredTestQuery, the default is DatabaseMetaData.getTables() - which is database agnostic, and although a relatively expensive call, is probably fine for a relatively small database. If you're paranoid about performance use a query specific to your database (i.e. preferredTestQuery="SELECT 1")
• maxIdleTimeExcessConnections will bring back the connectionCount back down to minPoolSize after a spike in activity.

请注意，任何池属性（例如 maxIdleTime ）只影响池中的连接，即if hibernate已经获得了一个连接，并保持空闲比maxIdleTime，然后尝试做任何操作，那么你会得到破碎的管道

Please note that any of the pool property(eg. maxIdleTime) only affects to connection which are in pool i.e if hibernate has acquired a connection and keeps it idle for than maxIdleTime and then tries to do any operation then you will get "Broken Pipe"

这是很好的降低 wait_timeout 在mysql，但它并不总是正确的，当你有一个应用程序已经构建。
你必须确保在你的应用程序中，你没有保持连接打开更多 wait_time 之前。

It is good to have lower wait_timeout on mysql but It's not always right when you have an application already built. You have to make sure before reducing it that in your application you are not keeping connection open for more that wait_time out.

您还必须考虑获取连接是昂贵的任务，如果有等待时间太低，那么它会跳过连接池的整个目的，因为它会频繁尝试获取连接。

You also have to consider that acquiring a connection is expensive task and if have wait time out too low then it beats the whole purpose of having connection pool, as it will frequently try to acquire connections.

当你不是手动进行连接管理时，例如当你使用Spring跨国API时，这是特别重要的。当您输入 @Transaction 注释方法时，Spring会启动事务，因此它从池获取连接。如果你正在进行任何web服务调用或读取一些文件，这将花费更多的时间比wait_time出来，那么你会得到异常。

This is especially important when you are not doing connection management manually for example when you use Spring transnational API. Spring starts transaction when you enter an @Transaction annotated method so it acquires a connection from pool. If you are making any web service call or reading some file which will take more time than wait_time out then you will get exception.

我遇到这个问题一次。

在我的一个项目中，我有一个cron，加工为客户。为了使它更快我使用批处理。现在一旦我检索一批客户并做一些处理（没有db调用）。当我尝试保存所有的订单，我用来得到破碎的管道异常。问题是我的wait_timeout是1分钟，订单处理需要更多的时间。所以我们不得不增加到2分钟。我可以减少批量大小，但这使整体处理速度更慢。

In one of my projects I had a cron which would do order processing for customers. To make it faster I used batch processing. Now once I retrieve a batch of customers and do some processing(no db calls). When I try to save all the orders I used to get broken pipe exception. The problem was my wait_timeout was 1 minute and order processing was taking more time then that. So We had to increase it to 2 minutes. I could have reduced the batch size but that was making the overall processing slower.

这篇关于c3p0 maxIdleTime与mysql的wait_timeout相同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！