Java c3p0 maxIdleTime 和 mysql 的 wait_timeout 一样吗？

c3p0 maxIdleTime is same as wait_timeout of mysql?

提问by Amogh

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

我有一个 Spring MVC + Mysql (JDBC 4) + c3p0 0.9.2 项目。

In c3p0 maxIdleTimevalue is 240 (i.e 4 mins.) and wait_timeoutin my.ini of Mysql to 30 seconds.

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

According to c3p0

根据 c3p0

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

maxIdleTime：（默认值：0）连接在被丢弃之前可以保持池化但未使用的秒数。零表示空闲连接永不过期。

According to Mysql

根据Mysql

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

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

Now i am having some douts on this:(some answers are known to me,Just wated to be sure I am correct or not)

现在我对此有一些疑虑：（我知道一些答案，只是想确定我是否正确）

1. unused connection means the connection which are in sleepstate according to mysql(?)
2. What is interactive and noninteractive connections?
3. Is unused connections and noninteractive coonections are same? because my DBA set wait_timeoutto 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 sleepmode for 30 seconds after that it will be closed but at the otherhand c3p0's maxIdleTimeis set to 240 seconds so whats this maxIdleTimesetting playing role in this case.
4. What is interactive_timeout?

1. 未使用的连接是指sleep根据 mysql(?)
2. 什么是交互式和非交互式连接？
3. 未使用的连接和非交互式连接是否相同？因为我的 DBA 设置wait_timeout为 30 秒（他通过观察数据库服务器得出这个值，因此处于睡眠模式的连接数量非常少）这意味着连接可以在sleep30 秒后处于模式状态，然后它将关闭，但另一方面c3p0'smaxIdleTime设置为 240 秒，那么这个maxIdleTime设置在这种情况下有什么作用。
4. 什么是interactive_timeout？

采纳答案by bitkot

First Let's understand the mysql properties.

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

• 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.

• interactive_timeout: mysql shell 会话的交互式超时（以秒为单位），如 mysqldump 或 mysql 命令行工具。连接处于睡眠状态。大多数情况下，它被设置为更高的值，因为您不希望它在您在 mysql cli 上执行某些操作时断开连接。
• wait_timeout：MySQL 在关闭非交互式连接上的连接之前将等待的不活动秒数（以秒为单位）。示例：从 java 连接。连接处于睡眠状态。

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

现在让我们了解 c3po 属性以及它与 DB 道具的关系。（我只是从你的问题中复制）

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

• maxIdleTime：（默认值：0）连接在被丢弃之前可以保持池化但未使用的秒数。零表示空闲连接永不过期。

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.

这是指连接对象可以使用多长时间，并将在池中可用。一旦超时结束，c3po 将销毁它或回收它。

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

现在问题来了，当你有maxIdleTime更高的wait_timeout. 假设如果您尝试在最后 10 秒内执行任何操作，那么您将获得mxIdleTime : 50secs 和wait_timeout : 40 schanse Connection time out exception: Broken Pipe。所以maxIdelTime应该总是少那么wait_timeout。

Instead of maxIdleTime you can you the following properties.

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

• idleConnectionTestPeriodsets 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")
• maxIdleTimeExcessConnectionswill bring back the connectionCount back down to minPoolSize after a spike in activity.

• idleConnectionTestPeriod对连接在测试之前保持空闲的时间设置限制。没有preferredTestQuery，默认是DatabaseMetaData.getTables()- 这是数据库不可知的，虽然调用相对昂贵，但对于相对较小的数据库来说可能没问题。如果您对性能感到偏执，请使用特定于您的数据库的查询(i.e. preferredTestQuery="SELECT 1")
• maxIdleTimeExcessConnections在活动激增后将 connectionCount 恢复到 minPoolSize。

Please note that any of the pool property(eg. maxIdleTime) only affects to connection which are in pooli.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"

请注意，任何池属性（例如maxIdleTime）仅影响池中的连接，即如果休眠已获取连接并使其空闲超过 maxIdleTime，然后尝试执行任何操作，那么您将获得“Broken Pipe”

It is good to have lower wait_timeouton 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_timeout.

wait_timeout在 mysql上使用较低是很好的，但是当您已经构建了一个应用程序时，它并不总是正确的。在减少它之前，您必须确保在您的应用程序中您没有保持连接打开以获取更多信息wait_time。

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.

您还必须考虑到获取连接是一项昂贵的任务，如果等待时间太短，那么它就无法实现连接池的全部目的，因为它会经常尝试获取连接。

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 @Transactionannotated 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.

这在您不手动进行连接管理时尤其重要，例如当您使用 Spring 跨国 API 时。当您输入带@Transaction注释的方法时，Spring 会启动事务，以便它从池中获取连接。如果您正在进行任何 Web 服务调用或读取某个文件，这将花费比 wait_time 更长的时间，那么您将收到异常。

I have faced this issue once.

我曾经遇到过这个问题。

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.

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

回答by Mayank_Thapliyal

unused connection means the connection which are in sleep state according to mysql(?)

未使用的连接是指根据 mysql(?)

According to mysql, this simply means that a connection was established with mysql/db, but there has been no activity here for the past amount of time and due to configuration / settings of mysql(which can be changed), the connection was destroyed.

根据 mysql，这只是意味着与 mysql/db 建立了连接，但在过去的一段时间内没有任何活动，并且由于 mysql 的配置/设置（可以更改），连接被破坏。

What is interactive and noninteractive connections? Interactive connections are when your input hardware(keyboard) interacts using command line with mysql. In short where you write the queries

什么是交互式和非交互式连接？交互式连接是指您的输入硬件（键盘）使用命令行与 mysql 进行交互。简而言之，您在哪里编写查询

Non interactive or rather wait_timeout queries are those for which your code establishes connection with mysql.

非交互式查询或 wait_timeout 查询是您的代码与 mysql 建立连接的查询。

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.

未使用的连接和非交互式连接是否相同？因为我的 DBA 将 wait_timeout 设置为 30 秒（他通过观察数据库服务器得出这个值，因此处于睡眠模式的连接数量非常少）这意味着连接可以在 30 秒后处于睡眠模式，然后它将关闭，但在另一方面，c3p0 的 maxIdleTime 设置为 240 秒，那么在这种情况下，这个 maxIdleTime 设置的作用是什么。

MaxIdleTime is done by your code at hibernateJpa Configuration where you ask your code itself to close a hibernate connection(for example) after a connection is unused. You have ownership of this as a coder.

MaxIdleTime 由您在 hibernateJpa 配置中的代码完成，您可以在其中要求代码本身在连接未使用后关闭休眠连接（例如）。作为编码员，您拥有此所有权。

Wait_timeout on other hand is from mysql side. So it is upon the DB administrator to set it up and change.

另一方面，Wait_timeout 来自 mysql 端。因此，由数据库管理员进行设置和更改。

What is interactive_timeout?

什么是interactive_timeout？

Again, interactive timeout is when you are writing queries after connecting to mysql from keyboard on command line and that time conf in mysql gets up.

同样，交互式超时是当您在命令行上从键盘连接到 mysql 后编写查询并且 mysql 中的 conf 启动时。

If you want to know more about how to change these values, go through this link: http://www.serveridol.com/2012/04/13/mysql-interactive_timeout-vs-wait_timeout/

如果您想了解有关如何更改这些值的更多信息，请访问此链接：http: //www.serveridol.com/2012/04/13/mysql-interactive_timeout-vs-wait_timeout/

Hope now it is clear to you.:)

希望现在你已经清楚了。:)