Why does default max_connections differ in the default parameter groups for RDS MySQL and RDS MariaDB?

0

I'd like to understand why the default max_connections value differs between MySQL and MariaDB, and what that means for memory requirements of the different databases.

I tested this using the most recent versions available, MySQL Community 8.0.27 and MariaDB 10.5.12. By default, MySQL 8.0.27 will use the default.mysql8.0 parameter group, and MariaDB 10.5.12 uses default.mariadb10.5.

Those parameter groups use these calculations for setting the max_connections variable:

default.mysql8.0 : {DBInstanceClassMemory/12582880} default.mariadb10.5 : LEAST({DBInstanceClassMemory/25165760), 12000)

I tested them with db.t3.micro instances (1 GB RAM), and the observed max_connections values are in line with those formulas:

MySQL [(none)]>  SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 63    |
+-----------------+-------+
 
MariaDB [(none)]>  SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 31    |
+-----------------+-------+

Why does AWS choose to set max_connections differently between MySQL and MariaDB? I understand that the MariaDB and MySQL projects have diverged, but this seems like a pretty big difference. And related to this, is this an indication that MariaDB requires more memory than MySQL for similarly sized workloads?

已提問 2 年前檢視次數 1001 次
1 個回答
0

Hello, I see you would like to know more about the differences in the max_connections parameter between MariaDB engines and MYSQL. The differences between the two is intentional because with a number of instance classes and workloads, the typical configurations results in instance instability and system crashes. Which is why AWS sets the max_connections differently between MYSQL and MariaDB. To add, also notice that you will see less maximum number of DB connections(when viewing max connections), it is simply to avoid potentially maxing out the memory of the DB.[1]

The formula of RDS MariaDB is different because internal engine architecture is different with MySQL because it is related to memory latch logic.

If you would like to increase the max_connections you may have a look at the references [2].

The chosen parameter calculations is a direct relationship between three parameters in MYSQl, namely: open_files_limit [3][4], table_open_cache[5][6] and max_connections. The parameter calculation was designed to provide best assurances in regards to operating system stability and DB performance across all instance classes on the RDS platform. If you would like to know more you can contact AWS support[7].

References : .[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Limits.html#RDS_Limits.MaxConnections

.[2] https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-max-connections/

.[3] https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/

.[4]https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_open_files_limit

.[5] https://mariadb.com/kb/en/optimizing-table_open_cache/

.[6] https://dev.mysql.com/doc/refman/8.0/en/table-cache.html

.[7] https://aws.amazon.com/premiumsupport/knowledge-center/

Antonio
已回答 2 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南