在 MariaDB 连接器中设置 aurora 后数据库连接增加 [英] DB connections increase after setting aurora in MariaDB connector
问题描述
我们正在使用 MariaDB JDBC 连接器 Aurora 特定功能测试故障转移行为.
We're testing the failover behaviour using the MariaDB JDBC connector Aurora specific features.
我们已将 JDBC URL 设置为 文档建议:
We've set the JDBC URL as the documentation suggest:
jdbc:mysql:aurora://cluster.cluster-xxxx.us-east-1.rds.amazonaws.com/db
问题是，一旦我们在 URL 模式中添加 aurora: 部分，我们就可以看到与数据库编写器的连接增加，直到我们必须回滚变化(它甚至达到了 3.000 个连接).
The problem is that as soon as we add the aurora: part in the URL schema, we can see an increase in the connections to the database writer until the point that we've to rollback the change (it even reaches 3.000 connections).
版本:
配置:
master {
profile = "slick.jdbc.MySQLProfile$"
db {
driver = "org.mariadb.jdbc.Driver"
url = "jdbc:mysql:aurora://cluster-name.cluster-xxx.us-east-1.rds.amazonaws.com/db_name?characterEncoding=utf8mb4&rewriteBatchedStatements=true&usePipelineAuth=false"
user = "rw_user"
password = "rw_user_pass"
numThreads = 20
queueSize = 1000000
}
}
slaves = [
{
profile = "slick.jdbc.MySQLProfile$"
db {
driver = "org.mariadb.jdbc.Driver"
url = "jdbc:mysql:aurora://cluster-name.cluster-ro-xxx.us-east-1.rds.amazonaws.com/db_name?characterEncoding=utf8mb4&usePipelineAuth=false"
user = "ro_user"
password = "ro_user_pass"
numThreads = 20
queueSize = 1000000
}
}
]
我们尝试在升级 MariaDB 连接器版本后将 aurora: 部分添加到 JDBC URL 模式，但与 Reader 的连接数再次开始增加:
We'd tried to add the aurora: part to the JDBC URL schema after upgrading the MariaDB connector version, but the number of connections to the Reader started to increase again:
如果我们在只读端点上运行一个show processlist，我们可以看到所有打开的连接处于清理"状态和睡眠"命令.
If we run a show processlist on the read only endpoint, we can see all the opened connections in "cleaned up" state, and "Sleep" command.
我们从只读端点中删除了 aurora: 部分，只是为了稳定与其的连接数.驱动程序是否有可能在打开连接时搜索集群主节点?这可以解释这种行为.
We'd removed the aurora: part from the read only endpoint just in order to stabilize the number of connections to it. Is it possible that the driver searches for the cluster master while opening connections? That would explain this kind of behaviour.
推荐答案
当使用aurora"关键字时，driver ，在幕后，创建 2 个连接:
When using the "aurora" keyword, driver , under the hood, create 2 connections:
- 与主服务器的连接，
- 与其中一个副本的连接(如果有).
目标始终是节省主服务器上的资源.一般只配置一个池.然后，驱动程序根据 [Connection.setReadOnly] [1] 使用与主/副本的连接.
The goal is always to save resources on the main server. Generally, only one pool is configured. The driver then uses the connection to the primary / replica according to [Connection.setReadOnly] [1].
当您有单独的写入"/读取"池时，使用故障转移"配置将解决您的问题:驱动程序将仅使用一个真实连接.这样，就不会有浪费"的连接.
When you have separate "write" / "read" pools, using the configuration "failover" will solve your issue: Driver will use only one real connection. This way, there will be no "wasted" connection.
随后将以不同的方式处理故障转移，但具有相同的结果(例如，不在事务中的查询将发送到刚刚崩溃的副本，不会像使用aurora"时那样直接使用主连接配置，驱动程序将在执行查询之前重新创建到另一个副本的新连接).
Failover will then be handled differently, but with the same results (for example, a query not in a transaction that is to be sent to a replica that just crashed will not directly use the primary connection as when using the "aurora" configuration, the driver will recreate a new connection to another replicas before executing the query).
这篇关于在 MariaDB 连接器中设置 aurora 后数据库连接增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
