windows azure mysql data disk_性能最佳做法 - Azure Database for MySQL | Microsoft Docs

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

获得 Azure Database for MySQL 单一服务器的最佳性能的最佳做法Best practices for optimal performance of your Azure Database for MySQL - Single server

1/28/2021

本文内容

了解如何在使用 Azure Database for MySQL 单服务器时获得最佳性能。Learn how to get best performance while working with your Azure Database for MySQL - Single server. 随着我们向平台添加新功能，我们将在本部分中继续改进我们的建议。As we add new capabilities to the platform, we will continue refining our recommendations in this section.

物理邻近性Physical Proximity

请确保在同一区域中部署应用程序和数据库。Make sure you deploy an application and the database in the same region. 在开始任何性能基准测试运行之前执行的快速检查是使用简单的 SELECT 1 查询确定客户端和数据库之间的网络延迟。A quick check before starting any performance benchmarking run is to determine the network latency between the client and database using a simple SELECT 1 query.

加速网络Accelerated Networking

如果使用的是 Azure 虚拟机、Azure Kubernetes 或应用服务，请使用适用于应用程序服务器的加速网络。Use accelerated networking for the application server if you are using Azure virtual machine, Azure Kubernetes, or App Services. 使用加速网络可以实现对 VM 的单根 I/O 虚拟化 (SR-IOV)，大幅提升其网络性能。Accelerated Networking enables single root I/O virtualization (SR-IOV) to a VM, greatly improving its networking performance. 这种高性能路径会绕过数据路径中的主机，降低延迟、抖动，以及受支持 VM 类型上的最苛刻网络工作负荷的 CPU 利用率。This high-performance path bypasses the host from the datapath, reducing latency, jitter, and CPU utilization, for use with the most demanding network workloads on supported VM types.

连接效率Connection Efficiency

建立新的连接始终是一项昂贵而耗时的任务。Establishing a new connection is always an expensive and time-consuming task. 当某个应用程序请求数据库连接时，它会优先分配现有的空闲数据库连接，而不是创建新的连接。When an application requests a database connection, it prioritizes the allocation of existing idle database connections rather than creating a new one. 以下是一些用于良好连接做法的选项：Here are some options for good connection practices:

ProxySQL：使用 ProxySQL(提供内置连接池)并 将工作负载负载均衡到多个只读副本，如应用程序代码更改所需要的那样。ProxySQL: Use ProxySQL which provides built-in connection pooling and load balance your workload to multiple read replicas as required on demand with any changes in application code.

Heimdall 数据代理：此外，还可以利用 Heimdall 数据代理，这是一个与供应商无关的专用代理解决方案。Heimdall Data Proxy: Alternatively, you can also leverage Heimdall Data Proxy, a vendor-neutral proprietary proxy solution. 它支持查询缓存和读/写拆分以及复制滞后检测。It supports query caching and read/write split with replication lag detection.

持久或长期连接：如果应用程序具有短期事务或查询，执行时间通常 < 5-10 ms，则用持久连接替换短期连接。Persistent or Long-Lived Connection: If your application has short transactions or queries typically with execution time < 5-10 ms, then replace short connections with persistent connections. 将短期连接替换为持久性连接只需对代码进行轻微的更改，但在许多典型的应用方案中，这种做法可以大幅提高性能。Replace short connections with persistent connections requires only minor changes to the code, but it has a major effect in terms of improving performance in many typical application scenarios. 请确保设置事务完成时超时或关闭连接。Make sure to set the timeout or close connection when the transaction is complete.

副本：如果使用副本，请使用 ProxySQL 在主服务器和可读辅助副本服务器之间进行负载均衡。Replica: If you are using replica, use ProxySQL to balance off load between the primary server and the readable secondary replica server.

数据导入配置Data Import configurations

在开始数据导入操作之前，可以暂时将实例缩放到较高的 SKU 大小，然后在导入成功时将其纵向缩减。You can temporarily scale your instance to higher SKU size before starting a data import operation and then scale it down when the import is successful.

通过使用 Azure 数据库迁移服务 (DMS) 进行联机或脱机迁移，可以以最少的停机时间导入数据。You can import your data with minimal downtime by using Azure Database Migration Service (DMS) for online or offline migrations.

Azure Database for MySQL 内存建议Azure Database for MySQL Memory Recommendations

Azure Database for MySQL 性能最佳做法是分配足够的 RAM，使工作集几乎完全在内存中。An Azure Database for MySQL performance best practice is to allocate enough RAM so that you’re working set resides almost completely in memory.

使用 MySQL 服务器的指标检查使用的内存百分比是否达到限制。Check if the memory percentage being used in reaching the limits using the metrics for the MySQL server.

设置针对此类值的警报，确保当服务器达到限制时，你可以立即执行操作来修复它。Set up alerts on such numbers to ensure that as the servers reaches limits you can take prompt actions to fix it. 根据定义的限制，检查是否扩展数据库 SKU，以提高计算规模或更高的定价层，从而提高性能。Based on the limits defined, check if scaling up the database SKU—either to higher compute size or to better pricing tier, which results in a dramatic increase in performance.

进行纵向扩展，直到缩放操作后性能值不再急剧下降。Scale up until your performance numbers no longer drops dramatically after a scaling operation. 有关监视 DB 实例的指标的信息，请参阅 MySQL DB 指标。For information on monitoring a DB instance's metrics, see MySQL DB Metrics.

使用 InnoDB 缓冲池预热Use InnoDB Buffer Pool Warmup

重新启动 Azure Database for MySQL server 后，驻留在存储中的数据页将在查询表时加载，这会导致第一次执行查询时的延迟和性能下降。After restarting Azure Database for MySQL server, the data pages residing in storage are loaded as the tables are queried which leads to increased latency and slower performance for the first execution of the queries. 对于延迟敏感的工作负荷，这可能是不可接受的。This may not be acceptable for latency sensitive workloads.

利用 InnoDB 缓冲池预热，可以在重新启动之前通过重新加载缓冲池中的磁盘页来缩短预热期，而不是等待 DML 或 SELECT 操作访问相应的行。Utilizing InnoDB buffer pool warmup shortens the warmup period by reloading disk pages that were in the buffer pool before the restart rather than waiting for DML or SELECT operations to access corresponding rows.

可以通过配置 InnoDB 缓冲池服务器参数，在重新启动 Azure Database for MySQL 服务器后减少预热期，这表示性能优势。You can reduce the warmup period after restarting your Azure Database for MySQL server, which represents a performance advantage by configuring InnoDB buffer pool server parameters. InnoDB 在服务器关闭时保存每个缓冲池最近使用过的页面的百分比，并在服务器启动时还原这些页面。InnoDB saves a percentage of the most recently used pages for each buffer pool at server shutdown and restores these pages at server startup.

另外，请务必注意，在服务器的启动时间较长的情况下，性能得到改进。It is also important to note that improved performance comes at the expense of longer start-up time for the server. 启用此参数时，服务器启动时间和重新启动时间应根据服务器上设置的 IOPS 而增加。When this parameter is enabled, server startup and restart time is expected to increase depending on the IOPS provisioned on the server.

建议测试并监视重新启动时间，以确保在该时间段内服务器不可用时可以接受启动/重新启动性能。We recommend testing and monitor the restart time to ensure the start-up/restart performance is acceptable as the server is unavailable during that time. 如果预配的存储空间小于 335 GB) ，不建议使用小于1000的预配 IOPS (或换言之。It is not recommended to use this parameter with less than 1000 provisioned IOPS (or in other words, when storage provisioned is less than 335 GB).

若要在服务器关闭时保存缓冲池的状态，请将 server 参数设置 innodb_buffer_pool_dump_at_shutdown 为 ON 。To save the state of the buffer pool at server shutdown, set server parameter innodb_buffer_pool_dump_at_shutdown to ON. 同样，将 "服务器参数" 设置 innodb_buffer_pool_load_at_startup 为， ON 以在服务器启动时还原缓冲池状态。Similarly, set server parameter innodb_buffer_pool_load_at_startup to ON to restore the buffer pool state at server startup. 可以通过减少和微调服务器参数的值来控制启动/重新启动时间的影响 innodb_buffer_pool_dump_pct 。You can control the impact on start-up/restart time by lowering and fine-tuning the value of server parameter innodb_buffer_pool_dump_pct. 默认情况下，此参数设置为 25。By default, this parameter is set to 25.

备注

InnoDB 缓冲池预热参数仅在具有高达 16 TB 存储的常规用途存储服务器中受支持。InnoDB buffer pool warmup parameters are only supported in general purpose storage servers with up to 16-TB storage.

后续步骤Next steps