Azure Database for MariaDB 中的服务器参数Server parameters in Azure Database for MariaDB

本文提供在 Azure Database for MariaDB 中配置服务器参数的注意事项和指南。This article provides considerations and guidelines for configuring server parameters in Azure Database for MariaDB.

什么是服务器参数?What are server parameters?

MariaDB 引擎提供了许多不同的服务器变量/参数,用于配置和优化引擎行为。The MariaDB engine provides many different server variables/parameters that can be used to configure and tune engine behavior. 某些参数可在运行时动态设置,另外一些参数则为“静态”参数,需要重启服务器才能应用。Some parameters can be set dynamically during runtime while others are "static", requiring a server restart in order to apply.

Azure Database for MariaDB 提供了通过 Azure 门户Azure CLIPowerShell 更改各种 MariaDB 服务器参数值的功能,以满足工作负荷的需要。Azure Database for MariaDB exposes the ability to change the value of various MariaDB server parameters using the Azure portal, Azure CLI, and PowerShell to match your workload's needs.

可配置的服务器参数Configurable server parameters

受支持服务器参数的列表还在不断增加。The list of supported server parameters is constantly growing. 在 Azure 门户中使用服务器参数选项卡可查看完整列表并配置服务器参数值。Use the server parameters tab in the Azure portal to view the full list and configure server parameters values.

请参阅以下各部分,详细了解多个经常更新的服务器参数的限制。Refer to the following sections below to learn more about the limits of the several commonly updated server parameters. 这些限制取决于服务器的定价层和 vCore 数。The limits are determined by the pricing tier and vCores of the server.

innodb_buffer_pool_sizeinnodb_buffer_pool_size

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

支持高达 4 TB 存储的服务器Servers supporting up to 4 TB storage

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) 最大值(字节)Max value (bytes)
基本Basic 11 872415232872415232 134217728134217728 872415232872415232
基本Basic 22 26843545602684354560 134217728134217728 26843545602684354560
常规用途General Purpose 22 37580963843758096384 134217728134217728 37580963843758096384
常规用途General Purpose 44 80530636808053063680 134217728134217728 80530636808053063680
常规用途General Purpose 88 1610612736016106127360 134217728134217728 1610612736016106127360
常规用途General Purpose 1616 3274912563232749125632 134217728134217728 3274912563232749125632
常规用途General Purpose 3232 6603512217666035122176 134217728134217728 6603512217666035122176
常规用途General Purpose 6464 132070244352132070244352 134217728134217728 132070244352132070244352
内存优化Memory Optimized 22 75161927687516192768 134217728134217728 75161927687516192768
内存优化Memory Optimized 44 1610612736016106127360 134217728134217728 1610612736016106127360
内存优化Memory Optimized 88 3221225472032212254720 134217728134217728 3221225472032212254720
内存优化Memory Optimized 1616 6549825126465498251264 134217728134217728 6549825126465498251264
内存优化Memory Optimized 3232 132070244352132070244352 134217728134217728 132070244352132070244352

innodb_file_per_tableinnodb_file_per_table

备注

innodb_file_per_table 只能在“常规用途”和“内存优化”定价层中更新。innodb_file_per_table can only be updated in the General Purpose and Memory Optimized pricing tiers.

MariaDB 根据你在创建表期间提供的配置,将 InnoDB 表存储在不同的表空间中。MariaDB stores the InnoDB table in different tablespaces based on the configuration you provided during the table creation. 系统表空间是 InnoDB 数据字典的存储区域。The system tablespace is the storage area for the InnoDB data dictionary. file-per-table 表空间包含单个 InnoDB 表的数据和索引,并存储在文件系统内它自己的数据文件中。A file-per-table tablespace contains data and indexes for a single InnoDB table, and is stored in the file system in its own data file. 此行为由 innodb_file_per_table 服务器参数控制。This behavior is controlled by the innodb_file_per_table server parameter. innodb_file_per_table 设置为 OFF 会导致 InnoDB 在系统表空间中创建表。Setting innodb_file_per_table to OFF causes InnoDB to create tables in the system tablespace. 否则,InnoDB 将在 file-per-table 表空间中创建表。Otherwise, InnoDB creates tables in file-per-table tablespaces.

在单个数据文件中,Azure Database for MariaDB 支持最大 1 TBAzure Database for MariaDB supports at largest, 1 TB, in a single data file. 如果数据库大小超过 1 TB,应在 innodb_file_per_table 表空间中创建表。If your database size is larger than 1 TB, you should create the table in innodb_file_per_table tablespace. 如果单个表的大小超过 1 TB,应使用分区表。If you have a single table size larger than 1 TB, you should use the partition table.

join_buffer_sizejoin_buffer_size

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) 最大值(字节)Max value (bytes)
基本Basic 11 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
基本Basic 22 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
常规用途General Purpose 22 262144262144 128128 268435455268435455
常规用途General Purpose 44 262144262144 128128 536870912536870912
常规用途General Purpose 88 262144262144 128128 10737418241073741824
常规用途General Purpose 1616 262144262144 128128 21474836482147483648
常规用途General Purpose 3232 262144262144 128128 42949672954294967295
常规用途General Purpose 6464 262144262144 128128 42949672954294967295
内存优化Memory Optimized 22 262144262144 128128 536870912536870912
内存优化Memory Optimized 44 262144262144 128128 10737418241073741824
内存优化Memory Optimized 88 262144262144 128128 21474836482147483648
内存优化Memory Optimized 1616 262144262144 128128 42949672954294967295
内存优化Memory Optimized 3232 262144262144 128128 42949672954294967295

max_connectionsmax_connections

定价层Pricing Tier vCore(s)vCore(s) 默认值Default value 最小值Min value 最大值Max value
基本Basic 11 5050 10 个10 5050
基本Basic 22 100100 10 个10 100100
常规用途General Purpose 22 300300 10 个10 600600
常规用途General Purpose 44 625625 10 个10 12501250
常规用途General Purpose 88 12501250 10 个10 25002500
常规用途General Purpose 1616 25002500 10 个10 50005000
常规用途General Purpose 3232 50005000 10 个10 1000010000
常规用途General Purpose 6464 1000010000 10 个10 2000020000
内存优化Memory Optimized 22 625625 10 个10 12501250
内存优化Memory Optimized 44 12501250 10 个10 25002500
内存优化Memory Optimized 88 25002500 10 个10 50005000
内存优化Memory Optimized 1616 50005000 10 个10 1000010000
内存优化Memory Optimized 3232 1000010000 10 个10 2000020000

当连接数超出限制时,可能会收到以下错误:When connections exceed the limit, you may receive the following error:

错误 1040 (08004):连接过多ERROR 1040 (08004): Too many connections

重要

为了获得最佳体验,建议使用 ProxySQL 等连接池程序来高效地管理连接。For best experience, we recommend that you use a connection pooler like ProxySQL to efficiently manage connections.

创建与 MariaDB 的新客户端连接需要时间,一旦建立,这些连接就会占用数据库资源,即使在空闲时也是如此。Creating new client connections to MariaDB takes time and once established, these connections occupy database resources, even when idle. 大多数应用程序会请求许多生存期短的连接,这加剧了这种情况。Most applications request many short-lived connections, which compounds this situation. 其结果是可用于实际工作负荷的资源减少,从而导致性能下降。The result is fewer resources available for your actual workload leading to decreased performance. 连接池程序不仅会减少空闲连接,还会重用现有连接,因而有助于避免这种情况。A connection pooler that decreases idle connections and reuses existing connections will help avoid this. 若要了解如何设置 ProxySQL,请访问我们的博客文章To learn about setting up ProxySQL, visit our blog post.

备注

ProxySQL 是一个开源社区工具。ProxySQL is an open source community tool. Microsoft 尽最大努力为它提供支持。It is supported by Microsoft on a best effort basis. 若要获得包含权威指导的生产支持,可以评估并联系 ProxySQL 产品支持In order to get production support with authoritative guidance, you can evaluate and reach out to ProxySQL Product support.

max_heap_table_sizemax_heap_table_size

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) 最大值(字节)Max value (bytes)
基本Basic 11 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
基本Basic 22 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
常规用途General Purpose 22 1677721616777216 1638416384 268435455268435455
常规用途General Purpose 44 1677721616777216 1638416384 536870912536870912
常规用途General Purpose 88 1677721616777216 1638416384 10737418241073741824
常规用途General Purpose 1616 1677721616777216 1638416384 21474836482147483648
常规用途General Purpose 3232 1677721616777216 1638416384 42949672954294967295
常规用途General Purpose 6464 1677721616777216 1638416384 42949672954294967295
内存优化Memory Optimized 22 1677721616777216 1638416384 536870912536870912
内存优化Memory Optimized 44 1677721616777216 1638416384 10737418241073741824
内存优化Memory Optimized 88 1677721616777216 1638416384 21474836482147483648
内存优化Memory Optimized 1616 1677721616777216 1638416384 42949672954294967295
内存优化Memory Optimized 3232 1677721616777216 1638416384 42949672954294967295

query_cache_sizequery_cache_size

默认情况下,MariaDB 中的查询缓存是使用 have_query_cache 参数启用的。The query cache is enabled by default in MariaDB with the have_query_cache parameter.

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) **最大值 ****Max value **
基本Basic 11 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
基本Basic 22 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
常规用途General Purpose 22 00 00 1677721616777216
常规用途General Purpose 44 00 00 3355443233554432
常规用途General Purpose 88 00 00 6710886467108864
常规用途General Purpose 1616 00 00 134217728134217728
常规用途General Purpose 3232 00 00 134217728134217728
常规用途General Purpose 6464 00 00 134217728134217728
内存优化Memory Optimized 22 00 00 3355443233554432
内存优化Memory Optimized 44 00 00 6710886467108864
内存优化Memory Optimized 88 00 00 134217728134217728
内存优化Memory Optimized 1616 00 00 134217728134217728
内存优化Memory Optimized 3232 00 00 134217728134217728

sort_buffer_sizesort_buffer_size

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) 最大值(字节)Max value (bytes)
基本Basic 11 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
基本Basic 22 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
常规用途General Purpose 22 524288524288 3276832768 41943044194304
常规用途General Purpose 44 524288524288 3276832768 83886088388608
常规用途General Purpose 88 524288524288 3276832768 1677721616777216
常规用途General Purpose 1616 524288524288 3276832768 3355443233554432
常规用途General Purpose 3232 524288524288 3276832768 3355443233554432
常规用途General Purpose 6464 524288524288 3276832768 3355443233554432
内存优化Memory Optimized 22 524288524288 3276832768 83886088388608
内存优化Memory Optimized 44 524288524288 3276832768 1677721616777216
内存优化Memory Optimized 88 524288524288 3276832768 3355443233554432
内存优化Memory Optimized 1616 524288524288 3276832768 3355443233554432
内存优化Memory Optimized 3232 524288524288 3276832768 3355443233554432

tmp_table_sizetmp_table_size

查看 MariaDB 文档详细了解此参数。Review the MariaDB documentation to learn more about this parameter.

定价层Pricing Tier vCore(s)vCore(s) 默认值(字节)Default value (bytes) 最小值(字节)Min value (bytes) 最大值(字节)Max value (bytes)
基本Basic 11 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
基本Basic 22 在基本层中不可配置Not configurable in Basic tier 空值N/A 空值N/A
常规用途General Purpose 22 1677721616777216 10241024 6710886467108864
常规用途General Purpose 44 1677721616777216 10241024 134217728134217728
常规用途General Purpose 88 1677721616777216 10241024 268435456268435456
常规用途General Purpose 1616 1677721616777216 10241024 536870912536870912
常规用途General Purpose 3232 1677721616777216 10241024 10737418241073741824
常规用途General Purpose 6464 1677721616777216 10241024 10737418241073741824
内存优化Memory Optimized 22 1677721616777216 10241024 134217728134217728
内存优化Memory Optimized 44 1677721616777216 10241024 268435456268435456
内存优化Memory Optimized 88 1677721616777216 10241024 536870912536870912
内存优化Memory Optimized 1616 1677721616777216 10241024 10737418241073741824
内存优化Memory Optimized 3232 1677721616777216 10241024 10737418241073741824

time_zonetime_zone

初始部署后,Azure for MariaDB 服务器包含用于时区信息的系统表,但这些表没有填充。Upon initial deployment, an Azure for MariaDB server includes systems tables for time zone information, but these tables are not populated. 可以通过从 MySQL 命令行或 MySQL Workbench 等工具调用 mysql.az_load_timezone 存储过程来填充时区表。The time zone tables can be populated by calling the mysql.az_load_timezone stored procedure from a tool like the MySQL command line or MySQL Workbench. 若要了解如何调用存储过程并设置全局时区或会话级时区,请参阅 Azure 门户Azure CLI 一文。Refer to the Azure portal or Azure CLI articles for how to call the stored procedure and set the global or session-level time zones.

不可配置的服务器参数Non-configurable server parameters

以下服务器参数不可在服务中配置:The below server parameters are not configurable in the service:

参数Parameter 固定值Fixed value
基本层中的 innodb_file_per_tableinnodb_file_per_table in Basic tier OFFOFF
innodb_flush_log_at_trx_commitinnodb_flush_log_at_trx_commit 11
sync_binlogsync_binlog 11
innodb_log_file_sizeinnodb_log_file_size 256 MB256MB
innodb_log_files_in_groupinnodb_log_files_in_group 22

MariaDB 中,上表中未列出的其他服务器参数将设置为其 MariaDB 现成默认值。Other server parameters that are not listed here are set to their MariaDB out-of-box default values for MariaDB.

后续步骤Next steps