SAN 环境下的 MySQL I/O 调优

Question

我有MySQL

MySQL version: 5.6.16-enterprise-commercial-advanced-log
MySQL Engine: InnoDB
MySQL Data Size: 35GB (including 9GB of indexes)

正在运行

VM: Red Hat Enterprise Linux Server release 5.9 (Tikanga)
File system: ext3
Storage technology: SAN
Disk data format: RAID-5
Disk type: SAS with Fibre channel

我发现很多 SELECT 查询由于 I/O 相关操作而花费时间(尽管必要的索引和缓冲区被添加到相同的)

mysql> show profile for query 1;
+----------------------+------------+
| Status               | Duration   |    
+----------------------+------------+
| starting             |   0.000313 |
| checking permissions |   0.000024 |
| checking permissions |   0.000018 |
| Opening tables       |   0.000086 |
| init                 |   0.000121 |
| System lock          |   0.000092 |
| optimizing           |   0.000079 |
| statistics           |   0.000584 |
| preparing            |   0.000070 |
| executing            |   0.000014 |
| Sending data         | 202.362338 |
| end                  |   0.000068 |
| query end            |   0.000027 |
| closing tables       |   0.000049 |
| freeing items        |   0.000124 |
| logging slow query   |   0.000135 |
| cleaning up          |   0.000057 |
+----------------------+------------+

以下网络延迟和吞吐量是否适合上述数据库实例？

$ time dd if=/dev/zero of=foobar bs=4k count=10000
10000+0 records in
10000+0 records out
40960000 bytes (41 MB) copied, 1.22617 seconds, 33.4 MB/s
real    0m1.233s
user    0m0.002s
sys 0m0.049s

$ time dd if=foobar of=/dev/null bs=4k count=10000
10000+0 records in
10000+0 records out
40960000 bytes (41 MB) copied, 0.026479 seconds, 1.5 GB/s
real    0m0.032s
user    0m0.004s
sys 0m0.024s

$ time dd if=/dev/zero of=foobar bs=128K count=10000
10000+0 records in
10000+0 records out
1310720000 bytes (1.3 GB) copied, 78.1099 seconds, 16.8 MB/s
real    1m18.241s
user    0m0.012s
sys 0m1.117s

$ time dd if=foobar of=/dev/null bs=128K count=10000
10000+0 records in
10000+0 records out
163840000 bytes (164 MB) copied, 0.084886 seconds, 1.9 GB/s
real    0m0.101s
user    0m0.002s
sys 0m0.083s

$ time dd if=/dev/zero of=foobar bs=1M count=10000
10000+0 records in
10000+0 records out
10485760000 bytes (10 GB) copied, 461.587 seconds, 22.7 MB/s
real    7m42.700s
user    0m0.017s
sys 0m8.229s

$ time dd if=foobar of=/dev/null bs=1M count=10000
10000+0 records in
10000+0 records out
10485760000 bytes (10 GB) copied, 4.63128 seconds, 2.3 GB/s
real    0m4.634s
user    0m0.003s
sys 0m4.579s

以下对 MySQL 系统变量的更改是否会在 MySQL I/O 调优的上下文中产生积极的结果？

1. innodb_flush_method:O_DSYNC(对于读取繁重的工作负载，称为 http://bugs.mysql.com/bug.php?id=54306)
2. 从 ext3 文件系统迁移到 XFS 文件系统

Answer 1

很难回答您的问题，因为对于性能问题，答案通常是“视情况而定”。对不起。

您需要做的第一件事是了解实际情况，以及您的表现低于预期的原因。有各种各样的工具，尤其是在 Linux 系统上。

首先，获取系统读写性能的基准。我倾向于使用的简单测试是计时 dd:

time dd if=/dev/zero of=/your/san/mount/point/testfile bs=1M count=100
time dd if=/your/san/mount/point/testfile of=/dev/null bs=1M count=100

(如果可以快速完成，则将 100 增加到 1000)。这将使您了解存储系统的持续吞吐量。

每秒测试 IO 操作是类似的事情 - 做同样的事情，但使用较小的 block 大小和较大的计数。 4k block 大小，计数为 10,000 - 同样，如果速度有点太快，请增加数量。

这将使您估算出存储子系统的 IOP 和吞吐量。

现在，您还没有具体说明所使用的磁盘类型和轴数。作为一个非常粗略的经验法则，在性能开始下降之前，SATA 驱动器的 IOPS 为 75，FC 或 SAS 驱动器的 IOPS 为 150，SSD 的 IOPS 为 1500。

但是，当您使用 RAID-5 时，您需要考虑 RAID-5 的写入惩罚 - 即 4。这意味着您的 RAID-5 需要 4 次操作来执行一次写入 IO。 (没有读取惩罚，但出于显而易见的原因，您的“奇偶校验”驱动器不算作主轴)。

您的工作量如何？主要是读，主要是写？多少眼压？多少锭？老实说，问题的根源更有可能是对存储子系统的期望。