优化 Azure Linux VM 上的 MySQL 性能

影响 Azure 上 MySQL 性能的因素有很多,主要体现在虚拟硬件选择和软件配置两个方面。本文重点介绍如何通过存储、系统和数据库配置优化性能。

Important

Azure 有两种用于创建和处理资源的不同部署模型:Azure Resource Manager 部署模型和经典部署模型。本文介绍使用经典部署模型的情况。Azure 建议大多数新部署使用 Resource Manager 模型。若要了解如何通过 Resource Manager 模型进行 Linux VM 优化,请参阅优化 Azure 上的 Linux VM

利用 Azure 虚拟机上的 RAID

存储是影响云环境中的数据库性能的关键因素。与单个磁盘相比,RAID 可以通过并发访问提供更快的访问速度。有关详细信息,请参阅 Standard RAID levels(标准 RAID 级别)。

通过 RAID 可改善 Azure 中的磁盘 I/O 吞吐量和 I/O 响应时间。我们的实验室测试表明,随着 RAID 磁盘数量的加倍(从 2 到 4,从 4 到 8,等等),磁盘 I/O 吞吐量平均增加一倍,I/O 响应时间平均缩短一半。有关详细信息,请参阅附录 A

增加 RAID 级别时,除了磁盘 I/O,MySQL 性能也会得到改善。有关详细信息,请参阅附录 B

可能还需要考虑区块大小。通常,区块越大,开销越低,对于大型写入操作尤其如此。不过,区块太大时,可能会有额外的开销,导致用户无法利用 RAID。当前的默认大小为 512 KB,已被证明是大多数常见生产环境的最佳大小。有关详细信息,请参阅附录 C

对于不同的虚拟机类型,可添加的磁盘数量存在多种限制。Azure 的虚拟机和云服务大小中详细介绍了这些限制。可以选择设置磁盘较少的 RAID,不过,在本文的 RAID 示例中,需要附加 4 个数据磁盘。

本文假定你已经创建 Linux 虚拟机,并且安装和配置了 MYSQL。有关入门的详细信息,请参阅“如何在 Azure 上安装 MySQL”。

在 Azure 上设置 RAID

以下步骤显示如何使用 Azure 经典管理门户在 Azure 上创建 RAID。也可以使用 Windows PowerShell 脚本设置 RAID。在本示例中,我们将使用 4 个磁盘配置 RAID 0。

向虚拟机添加数据磁盘

在 Azure 经典管理门户的虚拟机页上,单击要向其添加数据磁盘的虚拟机。在本示例中,该虚拟机为 mysqlnode1。

虚拟机

在该虚拟机的相应页面上,单击“仪表板”。

虚拟机仪表板

在任务栏中,单击“附加”。

虚拟机任务栏

然后单击“附加空磁盘”。

附加空磁盘

对于数据磁盘,“主机缓存首选项”应设置为“无”。

这将向虚拟机添加一个空磁盘。再重复此步骤三次,以便为 RAID 附加 4 个数据磁盘。

通过查看内核消息日志,可以看到虚拟机中添加的驱动器。例如,若要在 Ubuntu 上查看,请使用以下命令:

sudo grep SCSI /var/log/dmesg

创建具有更多磁盘的 RAID

以下步骤介绍如何在 Linux 上配置软件 RAID

Note

如果使用的是 XFS 文件系统,请在创建 RAID 后执行以下步骤。

若要在 Debian、Ubuntu 或 Linux Mint 上安装 XFS,请使用以下命令:

apt-get -y install xfsprogs  

若要在 Fedora、CentOS 或 RHEL 上安装 XFS,请使用以下命令:

yum -y install xfsprogs  xfsdump

设置新的存储路径

使用以下命令设置新的存储路径:

root@mysqlnode1:~# mkdir -p /RAID0/mysql

将原始数据复制到新的存储路径

使用以下命令将数据复制到新的存储路径:

root@mysqlnode1:~# cp -rp /var/lib/mysql/* /RAID0/mysql/

修改权限以便 MySQL 访问(读取和写入)数据磁盘

使用以下命令修改权限:

root@mysqlnode1:~# chown -R mysql.mysql /RAID0/mysql && chmod -R 755 /RAID0/mysql

调整磁盘 I/O 计划算法

Linux 实现了四种类型的 I/O 计划算法:

  • NOOP 算法(无操作)
  • 截止时间算法(截止时间)
  • 完全公平队列算法 (CFQ)
  • 预算期算法(预测)

在不同的情况下,你可以选择使用不同的 I/O 计划程序来优化性能。在完全随机的访问环境中,CFQ 算法和截止时间算法对性能的影响区别不大。为保持稳定性,建议将 MySQL 数据库环境设置为截止时间算法。如果有大量的顺序 I/O,CFQ 可能会降低磁盘 I/O 性能。

对于 SSD 和其他设备,NOOP 或截止时间算法可以比默认计划程序实现更好的性能。

在内核 2.5 之前,默认 I/O 计划算法为“截止时间”。从内核 2.6.18 开始,CFQ 成为默认 I/O 计划算法。可以在内核启动时指定此设置,或者在系统运行时动态修改此设置。

以下示例演示如何在 Debian 系列分发版本中检查默认计划程序并将其设置为 NOOP 算法。

查看当前的 I/O 调度器

若要查看计划程序,请运行以下命令:

root@mysqlnode1:~# cat /sys/block/sda/queue/scheduler

此时会显示以下输出,指示当前的计划程序:

noop [deadline] cfq

更改当前设备 (/dev/sda) 的 I/O 计划算法

运行以下命令以更改当前设备:

azureuser@mysqlnode1:~$ sudo su -
root@mysqlnode1:~# echo "noop" >/sys/block/sda/queue/scheduler
root@mysqlnode1:~# sed -i 's/GRUB_CMDLINE_LINUX=""/GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"/g' /etc/default/grub
root@mysqlnode1:~# update-grub
Note

对 /dev/sda 单独进行此设置毫无用处。必须对数据库所在的所有数据磁盘进行设置。

此时会显示以下输出,指示已成功重新生成 grub.cfg 并且默认计划程序已更新为 NOOP:

Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.13.0-34-generic
Found initrd image: /boot/initrd.img-3.13.0-34-generic
Found linux image: /boot/vmlinuz-3.13.0-32-generic
Found initrd image: /boot/initrd.img-3.13.0-32-generic
Found memtest86+ image: /memtest86+.elf
Found memtest86+ image: /memtest86+.bin
done

对于 Red Hat 系列分发版本,只需以下命令:

echo 'echo noop >/sys/block/sda/queue/scheduler' >> /etc/rc.local

配置系统文件操作设置

最佳做法之一是禁用文件系统上的 atime 日志记录功能。Atime 指文件的最后一次访问时间。无论何时访问文件,文件系统都会在日志中记录时间戳。但是,很少使用此信息。如果不需要,可以禁用它,这样将减少总体磁盘访问时间。

若要禁用 atime 日志记录,需修改文件系统配置文件 /etc/ fstab 并添加“noatime”选项。

例如,编辑 vim /etc/fstab 文件,按以下示例所示添加 noatime:

# CLOUD_IMG: This file was created/modified by the Cloud Image build process
UUID=3cc98c06-d649-432d-81df-6dcd2a584d41       /        ext4   defaults,discard        0 0
#Add the "noatime" option below to disable atime logging
UUID="431b1e78-8226-43ec-9460-514a9adf060e"     /RAID0   xfs   defaults,nobootwait, noatime 0 0
/dev/sdb1       /mnt    auto    defaults,nobootwait,comment=cloudconfig 0       2

然后,使用以下命令重新装载文件系统:

mount -o remount /RAID0

测试修改后的结果。修改测试文件时,系统不会更新访问时间。以下示例展现了代码在修改前后的样子。

之前:

进行访问权限修改之前的代码

之后:

进行访问权限修改之后的代码

增加系统句柄的最大数量以实现高并发

MySQL 是高并发数据库。对于 Linux,默认的并发句柄数量是 1024 个,这个数量有时候不够用。通过执行以下步骤来增加系统的最大并发句柄数,以此支持 MySQL 的高并发。

修改 limits.conf 文件

若要增加允许的最大并发句柄数,可在 /etc/security/limits.conf 文件中添加以下四行。请注意,65536 是系统可以支持的最大数量。

* soft nofile 65536
* hard nofile 65536
* soft nproc 65536
* hard nproc 65536

更新系统以实施新限制

若要更新系统,请运行以下命令:

ulimit -SHn 65536
ulimit -SHu 65536

确保在启动时更新限制

在 /etc/rc.local 文件中放置以下启动命令,以便其在启动时生效。

echo "ulimit -SHn 65536" >>/etc/rc.local
echo "ulimit -SHu 65536" >>/etc/rc.local

MySQL 数据库优化

若要在 Azure 上配置 MySQL,可以使用本地计算机上使用的同一性能优化策略。

主要 I/O 优化规则包括:

  • 增加缓存大小。
  • 减少 I/O 响应时间。

若要优化 MySQL 服务器设置,可以更新 my.cnf 文件,它是服务器和客户端计算机的默认配置文件。

以下配置项是影响 MySQL 性能的主要因素:

  • innodb_buffer_pool_size:缓冲池包含缓冲数据和索引。此值通常设置为物理内存的 70%。
  • innodb_log_file_size:这是重做日志大小。重做日志用于确保写入操作快速、可靠并且可在出现故障后恢复。此值设置为 512 MB,这将为记录写入操作提供大量空间。
  • max_connections:应用程序有时候不会正确关闭连接。值越大,服务器就有越多时间回收空闲的连接。最大连接数为 10,000,但建议的最大值为 5,000。
  • Innodb_file_per_table:此设置允许或禁止 InnoDB 将表存储在单独的文件中。启用该选项将确保可以有效地应用多项高级管理操作。从性能角度来看,它可以提高表空间传输的速度和优化碎片管理性能。此选项的推荐设置是 ON。
    从 MySQL 5.6 开始,默认设置为 ON,因此不需要任何操作。早期版本的默认设置为 OFF。应在加载数据之前更改此设置,因为只有新创建的表才会受影响。
  • innodb_flush_log_at_trx_commit:默认值为 1,范围设置为 0~2。默认值是最适合独立 MySQL DB 的选项。设置为 2 可确保最大程度的数据完整性,适用于 MySQL 群集中的主节点。设置为 0 允许数据丢失,这可能会影响可靠性(在某些情况下能提供更好的性能),适用于 MySQL 群集中的从属节点。
  • Innodb_log_buffer_size:借助日志缓冲区,即使在事务提交之前未将日志刷新到磁盘,事务也可以运行。但是,如果有大型的二进制对象或文本字段,将快速耗尽缓存,并触发频繁的磁盘 I/O。如果 Innodb_log_waits 状态变量不为 0,最好增加缓冲区大小。
  • query_cache_size:最好是从一开始就禁用。将 query_cache_size 设置为 0(这是 MySQL 5.6 中的默认设置)并使用其他方法来提高查询速度。

请参阅附录 D,了解优化前后的性能比较情况。

启用 MySQL 慢查询日志以进行性能瓶颈分析

MySQL 慢查询日志有助于识别 MySQL 的慢查询。在启用 MySQL 慢查询日志后,可以使用 mysqldumpslow 等 MySQL 工具来识别性能瓶颈。

默认情况下,此项未启用。启用慢查询日志可能会占用一些 CPU 资源。建议临时启用此选项来排查性能瓶颈问题。若要启用慢查询日志,请执行以下操作:

  1. 通过在末尾添加以下行来修改 my.cnf 文件:

    long_query_time = 2
    slow_query_log = 1
    slow_query_log_file = /RAID0/mysql/mysql-slow.log
    
  2. 重新启动 MySQL Server。

    service  mysql  restart
    
  3. 使用 show 命令检查该设置是否生效。

Slow-query-log ON

Slow-query-log 结果

在本示例中,可以看到慢查询功能已启用。然后可以使用 mysqldumpslow 工具来确定性能瓶颈和优化性能,比如添加索引。

附录

以下为在目标实验室环境中生成的性能测试数据示例。这些数据提供了在使用不同性能调优方法的情况下,性能数据趋势的常规背景。结果可能因环境或产品版本而异。

附录 A

不同 RAID 级别的磁盘性能 (IOPS)

不同 RAID 级别的磁盘 IOPS

测试命令

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=5G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite
Note

此测试的工作负荷使用 64 个线程,尝试达到 RAID 的上限。

附录 B

不同 RAID 级别的 MySQL 性能(吞吐量)比较 (XFS 文件系统)

不同 RAID 级别的 MySQL 性能比较 不同 RAID 级别的 MySQL 性能比较

测试命令

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write -engine=innodb

不同 RAID 级别的 MySQL 性能 (OLTP) 比较不同 RAID 级别的 MySQL 性能 (OLTP) 比较

测试命令

time sysbench --test=oltp --db-driver=mysql --mysql-user=root --mysql-password=0ps.123  --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-db=test --oltp-table-size=1000000 prepare

附录 C

不同区块大小的磁盘性能 (IOPS) 比较 (XFS 文件系统)

测试命令

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=30G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite
fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=1G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite  

用于此测试的文件大小分别为 30 GB 和 1 GB,并且使用的是 RAID 0(4 个磁盘)XFS 文件系统。

附录 D

优化前和优化后的 MySQL 性能(吞吐量)比较 (XFS 文件系统)

优化前和优化后的 MySQL 性能(吞吐量)比较

测试命令

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write -engine=innodb,misam

默认值和优化值的配置设置如下所示:

Parameters 默认 优化
innodb_buffer_pool_size 7 GB
innodb_log_file_size 5 MB 512 MB
max_connections 100 5000
innodb_file_per_table 0 1
innodb_flush_log_at_trx_commit 1 2
innodb_log_buffer_size 8 MB 128 MB
query_cache_size 16 MB 0

如需更多详细的优化配置参数,请参阅 MySQL 正式说明

测试环境

硬件 详细信息
CPU AMD Opteron(tm) 处理器 4171 HE/4 核
内存 14 GB
磁盘 10 GB/磁盘
操作系统 Ubuntu 14.04.1 LTS