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

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

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

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

MySQL 引擎提供了可以用来配置和优化引擎行为的许多不同的服务器变量/参数。The MySQL 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 MySQL 公开了通过 Azure 门户Azure CLIPowerShell 根据工作负荷需求更改各种 MySQL 服务器参数值的功能。Azure Database for MySQL exposes the ability to change the value of various MySQL 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.

线程池Thread pools

MySQL 通常会为每个客户端连接分配一个线程。MySQL traditionally assigns a thread for every client connection. 随着并发用户数量的增加,性能会相应下降。As the number of concurrent users grows, there is a corresponding drop in performance. 由于上下文切换增加、线程争用以及 CPU 缓存位置不正确,许多活动线程会严重影响性能。Many active threads can impact the performance significantly due to increased context switching, thread contention, and bad locality for CPU caches.

线程池是服务器端的一项功能。与连接池不同,它通过引入工作线程的动态池来最大限度提高性能,该动态池可用于限制服务器上运行的活动线程数,并最大程度地减少线程变动。Thread pools which is a server side feature and distinct from connection pooling, maximize performance by introducing a dynamic pool of worker thread that can be used to limit the number of active threads running on the server and minimize thread churn. 这有助于确保连接突发不会导致服务器资源用尽或因内存不足错误而崩溃。This helps ensure that a burst of connections will not cause the server to run out of resources or crash with an out of memory error. 对于短查询和 CPU 密集型工作负荷(例如 OLTP 工作负荷),线程池最有效。Thread pools are most efficient for short queries and CPU intensive workloads, for example OLTP workloads.

若要详细了解线程池,请参阅在 Azure Database for MySQL 中引入线程池To learn more about thread pools, refer to Introducing thread pools in Azure Database for MySQL

备注

MySQL 5.6 版本不支持线程池功能。Thread pool feature is not supported for MySQL 5.6 version.

配置线程池Configuring the thread pool

若要启用线程池,请将 thread_handling 服务器参数更新为“pool-of-threads”。To enable thread pool, update the thread_handling server parameter to "pool-of-threads". 默认情况下,此参数设置为 one-thread-per-connection,这意味着 MySQL 会为每个新连接创建一个新线程。By default, this parameter is set to one-thread-per-connection, which means MySQL creates a new thread for each new connections. 请注意,这是一个静态参数,需要重启服务器才能应用。Please note that this is a static parameter and requires a server restart to apply.

还可通过设置以下服务器参数,配置池中的最大和最小线程数:You can also configure the maximum and minimum number of threads in the pool by setting the following server parameters:

  • thread_pool_max_threads:此值可确保池中的线程数不超过此数目。thread_pool_max_threads: This value ensures that there will not be more than this number of threads in the pool.
  • thread_pool_min_threads:此值设置即使在连接关闭后也会保留的线程数。thread_pool_min_threads: This value sets the number of threads that will be reserved even after connections are closed.

为了改善对线程池的短查询的性能问题,Azure Database for MySQL 允许你启用批处理执行,其中的线程会在短时间内保持活动状态,以便通过此连接等待下一个查询,而不是在执行查询后立即返回线程池。To improve performance issues of short queries on the thread pool, Azure Database for MySQL allows you to enable batch execution where instead of returning back to the thread pool immediately after executing a query, threads will keep active for a short time to wait for the next query through this connection. 然后,线程会快速执行查询,完成后,便会等待下一个查询,直到此进程的总时间消耗超过阈值。The thread then executes the query rapidly and once complete, waits for the next one, until the overall time consumption of this process exceeds a threshold. 批处理执行行为使用以下服务器参数确定:The batch execution behavior is determined using the following server parameters:

  • thread_pool_batch_wait_timeout:此值指定线程等待另一个查询进行处理的时间。thread_pool_batch_wait_timeout: This value specifies the time a thread waits for another query to process.
  • thread_pool_batch_max_time:此值确定线程重复查询执行周期并等待下一个查询的最长时间。thread_pool_batch_max_time: This value determines the max time a thread will repeat the cycle of query execution and waiting for the next query.

重要

请在生产环境中启用线程池之前对其进行测试。Please test thread pool before turning it ON in production.

innodb_buffer_pool_sizeinnodb_buffer_pool_size

查看 MySQL 文档详细了解此参数。Review the MySQL 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.

MySQL 根据你在表创建期间提供的配置,将 InnoDB 表存储在不同的表空间中。MySQL 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 MySQL 支持最大 1TB。Azure Database for MySQL supports at largest, 1 TB, in a single data file. 如果数据库大小超过 1TB,应在 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

查看 MySQL 文档详细了解此参数。Review the MySQL 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 1010 5050
基本Basic 22 100100 1010 100100
常规用途General Purpose 22 300300 1010 600600
常规用途General Purpose 44 625625 1010 12501250
常规用途General Purpose 88 12501250 1010 25002500
常规用途General Purpose 1616 25002500 1010 50005000
常规用途General Purpose 3232 50005000 1010 1000010000
常规用途General Purpose 6464 1000010000 1010 2000020000
内存优化Memory Optimized 22 625625 1010 12501250
内存优化Memory Optimized 44 12501250 1010 25002500
内存优化Memory Optimized 88 25002500 1010 50005000
内存优化Memory Optimized 1616 50005000 1010 1000010000
内存优化Memory Optimized 3232 1000010000 1010 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.

创建与 MySQL 的新客户端连接需要时间,一旦建立,这些连接就会占用数据库资源,即使在空闲时也是如此。Creating new client connections to MySQL 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

查看 MySQL 文档详细了解此参数。Review the MySQL 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

默认会禁用查询缓存。The query cache is turned off by default. 若要启用查询缓存,请配置 query_cache_type 参数。To enable the query cache, configure the query_cache_type parameter.

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

备注

查询缓存从 MySQL 5.7.20 开始已遭弃用,并且已在 MySQL 8.0 中删除The query cache is deprecated as of MySQL 5.7.20 and has been removed in MySQL 8.0

定价层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

lower_case_table_nameslower_case_table_names

lower_case_table_name 默认设置为 1,你可以在 MySQL 5.6 和 MySQL 5.7 中更新此参数The lower_case_table_name is set to 1 by default and you can update this parameter in MySQL 5.6 and MySQL 5.7

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

备注

在 MySQL 8.0 中,lower_case_table_name 默认设置为 1 且无法更改。In MySQL 8.0, the lower_case_table_name is set to 1 by default and you cannot change it.

innodb_strict_modeinnodb_strict_mode

如果收到类似于“行大小太大(> 8126)”的错误,则可能需要关闭 innodb_strict_mode 参数。If you receive an error similar to "Row size too large (> 8126)" then you may want to turn OFF the parameter innodb_strict_mode. 不允许在服务器级别全局修改服务器参数 innodb_strict_mode,因为如果行数据大小大于 8k,该数据将会被截断,且不显示错误,这样就会导致有可能丢失数据。The server parameter innodb_strict_mode is not allowed to be modified globally at the server level because if row data size is larger than 8k, the data will be truncated without an error leading to potential data loss. 建议修改架构以适应页面大小限制。We recommend to modify the schema to fit the page size limit.

可以使用 init_connect 在会话级别设置此参数。This parameter can be set at a session level using init_connect. 若要在会话级别设置 innodb_strict_mode,请参阅设置未列出的参数To set innodb_strict_mode at session level, refer to setting parameter not listed.

备注

如果有只读副本服务器,在主服务器上的会话级别将 innodb_strict_mode 设置为 OFF 将会中断复制。If you have a read replica server, setting innodb_strict_mode to OFF at the session-level on a master server will break the replication. 如果有只读副本,建议将该参数始终设置为 OFF。We suggest keeping the parameter set to OFF if you have read replicas.

sort_buffer_sizesort_buffer_size

查看 MySQL 文档详细了解此参数。Review the MySQL 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

查看 MySQL 文档详细了解此参数。Review the MySQL 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 MySQL 服务器包含用于时区信息的系统表,但这些表没有填充。Upon initial deployment, an Azure for MySQL 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

此处未列出的其他变量将设置为默认的 MySQL 现成值。Other variables not listed here are set to the default MySQL out-of-the-box values. 有关默认值,请参阅适用于版本 8.05.75.6 的 MySQL 文档。Refer to the MySQL docs for versions 8.0, 5.7, and 5.6 for the default values.

后续步骤Next steps