MySQL keeps re-establishing connections

[tigerzhang]

RBatis 版本：4.5.19
mysql: 5.7

我们的业务每秒钟可能更新 MySQL 数据库几十次，多个任务会并发发起更新。

现在通过系统工具观察 TCP 连接，发现每过几十秒钟，应用跟 MySQL 的 TCP 连接，就会更换一批，系统也看到大量的 TCP 连接处在 TIMEWAIT 状态。

请教的问题：

1. 这是因为 RBatis 工作在短连接状态吗？
2. 怎么才能让 RBatis 工作在长连接状态？现在这个短连接状态，建立连接的开销开起来比较大，系统也有 10k 多个 TCP TIMEWAIT 的连接。

谢谢

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

RBatis version: 4.5.19
mysql: 5.7

Our business may update the MySQL database dozens of times per second, and multiple tasks will initiate updates concurrently.

Now I use system tools to observe TCP connections and find that every few tens of seconds, the TCP connections between the application and MySQL will be replaced. The system also sees that a large number of TCP connections are in the TIMEWAIT state.

Questions to ask:

1. Is this because RBatis works in a short connection state?
2. How to make RBatis work in a long connection state? In the current short connection state, the cost of establishing a connection is relatively high, and the system also has more than 10k TCP TIMEWAIT connections.

Thanks

[zhuxiujia]

RBatis 版本：4.5.19 mysql: 5.7

我们的业务每秒钟可能更新 MySQL 数据库几十次，多个任务会并发发起更新。

现在通过系统工具观察 TCP 连接，发现每过几十秒钟，应用跟 MySQL 的 TCP 连接，就会更换一批，系统也看到大量的 TCP 连接处在 TIMEWAIT 状态。

请教的问题：

1. 这是因为 RBatis 工作在短连接状态吗？
2. 怎么才能让 RBatis 工作在长连接状态？现在这个短连接状态，建立连接的开销开起来比较大，系统也有 10k 多个 TCP TIMEWAIT 的连接。

谢谢

RBatis 默认是以连接池工作的，就是说Table::insert(&rb) 的时候rb实际上是连接池对象的引用（从连接池随机获取和创建）。
我建议你可以提前拿到链接，例如

//提前新建连接，而不是从连接池随机获取和创建
let conn = rb.acquire().await?;
loop{
   Table::insert(&conn).await?;
}

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

RBatis version: 4.5.19 mysql: 5.7

Our business may update the MySQL database dozens of times per second, and multiple tasks will initiate updates concurrently.

Now observe the TCP connections through system tools and find that every few tens of seconds, a batch of TCP connections between the application and MySQL will be changed. The system also sees that a large number of TCP connections are in the TIMEWAIT state.

Questions to ask:

1. Is this because RBatis works in a short connection state?
2. How to make RBatis work in a long connection state? In the current short connection state, the cost of establishing a connection is relatively high, and the system also has more than 10k TCP TIMEWAIT connections.

Thank you

RBatis works with a connection pool by default, which means that when Table::insert(&rb) is used, rb is actually a reference to the connection pool object (randomly obtained and created from the connection pool).
I suggest you get the link in advance, e.g.

//Create a new connection in advance instead of randomly obtaining and creating it from the connection pool
let conn = rb.acquire().await?;
loop{
   Table::insert(&conn).await?;
}

[tigerzhang]

RBatis 版本：4.5.19 mysql: 5.7
我们的业务每秒钟可能更新 MySQL 数据库几十次，多个任务会并发发起更新。
现在通过系统工具观察 TCP 连接，发现每过几十秒钟，应用跟 MySQL 的 TCP 连接，就会更换一批，系统也看到大量的 TCP 连接处在 TIMEWAIT 状态。
请教的问题：

1. 这是因为 RBatis 工作在短连接状态吗？
2. 怎么才能让 RBatis 工作在长连接状态？现在这个短连接状态，建立连接的开销开起来比较大，系统也有 10k 多个 TCP TIMEWAIT 的连接。

谢谢

RBatis 默认是以连接池工作的，就是说Table::insert(&rb) 的时候rb实际上是连接池对象的引用（从连接池随机获取和创建）。 我建议你可以提前拿到链接，例如

//提前新建连接，而不是从连接池随机获取和创建
let conn = rb.acquire().await?;
loop{
   Table::insert(&conn).await?;
}

谢谢你的回复。

我的理解调用 acquire 后，当前任务会独占一个连接。
我们有上万个任务，都需要写数据库，如果每个都要独占一个连接，数量级有点大。

有没有办法让连接不要一直重建，如果所有的连接都被占用了，让新发起的调用排队等待。

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

RBatis version: 4.5.19 mysql: 5.7
Our business may update the MySQL database dozens of times per second, and multiple tasks will initiate updates concurrently.
Now observe the TCP connections through system tools and find that every few tens of seconds, the TCP connections between the application and MySQL will be replaced. The system also sees that a large number of TCP connections are in the TIMEWAIT state.
Questions to ask:

1. Is this because RBatis works in a short connection state?
2. How to make RBatis work in a long connection state? In the current short connection state, the cost of establishing a connection is relatively high, and the system also has more than 10k TCP TIMEWAIT connections.

Thank you

RBatis works with a connection pool by default, which means that when Table::insert(&rb) is used, rb is actually a reference to the connection pool object (randomly obtained and created from the connection pool). I suggest you get the link in advance, e.g.

//Create a new connection in advance instead of randomly obtaining and creating it from the connection pool
let conn = rb.acquire().await?;
loop{
Table::insert(&conn).await?;
}

Thank you for your reply.

My understanding is that after calling acquire, the current task will exclusively own a connection.
We have tens of thousands of tasks, all of which need to be written to the database. If each one has to occupy an exclusive connection, the order of magnitude is a bit large.

Is there a way to not keep re-establishing connections, but to have newly initiated calls queued up if all connections are occupied.