在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).

版本:

• MariaDB连接器:2.0 .1
• HikariCP连接池:2.6.1
• Play-Slick: 2.1.0
• 光滑:3.2.0

• MariaDB connector: 2.0.1
• HikariCP connection pool: 2.6.1
• Play-Slick: 2.1.0
• Slick: 3.2.0

配置:

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屋！