MySQL 企业常用架构与调优

MySQL 企业常用架构与调优

问题分类：

1. 选择 Percona Server、MariaDB 还是 MySQL
2. 常用的 MySQL 调优策略
3. MySQL 常见的应用架构分享
4. MySQL 经典应用架构

一、选择 Percona Server、MariaDB 还是 MySQL

1. MySQL 三种存储引擎

MySQL 提供了两种存储引擎：MyISAM 和 InnoDB，MySQL 4 和 5 使用默认的 MyISAM 存储引擎。从 MySQL 5.5 开始，MySQL 已将默认存储引擎从 MyISAM 更改为 InnoDB。MyISAM 没有提供事务支持，而 InnoDB 提供了事务支持。
XtraDB 是 InnoDB 存储引擎的增强版本，被设计用来更好的使用更新计算机硬件系统的性能，同时还包含有一些在高性能环境下的新特性。

2. Percona Server 分支

Percona Server 由领先的 MySQL 咨询公司 Percona 发布。
Percona Server 是一款独立的数据库产品，其可以完全与 MySQL 兼容，可以在不更改代码的情况下降存储引擎更换成 XtraDB。是最接近官方 MySQL Enterprise 发行版的版本。
Percona 提供了高性能 XtraDB 引擎，还提供 PXC 高可用解决方案，并且附带了 percona-toolkit 等 DBA 管理工具箱。

3. MariaDB

MariaDB 由 MySQL 的创始人开发，MariaDB 的目的是完全兼容 MySQL，包括 API 和命令行，使之能轻松成为 MySQL 的代替品。
MariaDB 提供了 MySQL 提供的标准存储引擎，即 MyISAM 和 InnoDB，10.0.9 版本起使用 XtraDB（名称代号为 Aria）来代替 MySQL 的 InnoDB。

4. 如何选择

综合多年使用经验和性能对比，首选 Percona 分支。其次是 MariaDB。如果你不想冒点风险，那就选择 MySQL 官方版本。

二、常见的 MySQL 调优策略

1. 硬件层相关优化

修改服务器 BIOS 设置

• 选择 Performance Per Watt Optimized（DAPC）模式，发挥 CPU 最大性能。
• Memory Frequency（内存频率）选择 Maximum Performance（最佳性能）。
• 内存设置菜单中，启用 Node Interleaving，避免 NUMA 问题。

2. 磁盘 I/O 相关

• 使用 SSD 硬盘（能够解决很多技术解决不到的问题）。
• 如果是磁盘阵列存储，建议阵列卡同时配备 CACHE 和 BBU 模块，可明显提升 IOPS。
• raid 级别尽量选择 raid10，而不是 raid5 （写效率一般，磁盘消耗大，安全性不高）。

3. 文件系统层优化

• 使用 deadline/noop 这两种 I/O 调度器，千万别用 cfq。
• 使用 xfs 文件系统，千万别用 ext3；ext4 勉强可用，但业务量很大的话，则一定要用 xfs。
• 文件系统 mount 参数中参加：noatime，nodiratime，nobarrier 几个选项（nobarrier 是 xfs 文件系统特有的）。

4. 内核参数优化

• 修改 vm.swappiness 参数，降低 swap 使用率。RHEL7/centos7 以上则慎重设置为 0，可能发生 OOM。
• 调整 vm.dirty_background_ratio、vm.dirty_ratio 内核参数，以确保能保持将脏数据刷新到磁盘，避免瞬间 I/O 写。产生等待。
• 调整 net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse 都设置为 1，减少 TIME_WAIT，提高 TCP 效率。

5. MySQL 参数优化建议

• 建议设置 default-storage-engine=InnoDB，强烈建议不要再使用 MyISAM 引擎。
• 调整 innodb_buffer_pool_size 的大小，如果是单实例且绝大多数是 InnoDB 引擎表的话，可考虑设置为物理内存的 50%~70% 左右。
• 设置 innodb_file_per_table=1，使用独立表空间。
• 调整 innodb_data_file_path=ibdata1:1G:autoextend，不要用默认的 10 M，在高并发场景下，性能会有很大提升。
• 设置 innodb_log_file_size=256M，设置 innodb_log_files_in_group=2，基本可以满足大多数应用场景。
• 调整 max_connection（最大连接数）、max_connection_error（最大错误数）设置，根据业务量大小进行设置。
• 另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache 可以设置大约为 max_connection 的 10 倍左右大小。
• key_buffer_size 建议调小，32 M 左右即可，另建议关闭 query cache。
• mp_table_size 和 max_heap_table_size 设置不要过大，另外 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size 等设置也不要过大。

三、MySQL 常见的应用架构

1. 主从复制解决方案

这是 MySQL 自身提供的一种高可用解决方案，数据同步方法采用的是 MySQL replication 技术。MySQL replication 就是从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的 SQL 在从服务器上重新执行一遍主服务器的操作，通过这种方式保证数据的一致性。
为了达到更高的可用性，在实际的应用环境中，一般都是采用 MySQL replication 技术配合高可用集群软件 keepalived 来实现自动 failover（故障转移），这种方式可以实现 95.000% 的 SLA。

2. MMM//MHA 高可用解决方案

MMM 提供了 MySQL 主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。
在 MMM 高可用方案中。典型的应用是双主多从架构，通过 MySQL replication 技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM 套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现 MySQL 的高可用。

3. Heartbeat/SAN 高可用解决方案

在这个方案中，处理 failover 的方式是高可用集群软件 Heartbeat，它监控和管理各个节点间连接的网络，并监控集群服务，当节点出现故障或者服务不可用时，自动在其他节点启动集群服务。在数据共享方面，通过 SAN（Storage Area Network）存储来共享数据，这种方案可以实现 99.990% 的 SLA。

4. Heartbeat/DRDB 高可用解决方案

此方案处理 failover 的方式依旧采用 Heartbeat，不同额是，在数据共享方面，采用了基于块级别的数据同步软件 DRDB 来实现。
DRDB 是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和 SAN 网络不同，它并不共享存储，而是通过服务器之间的网络复制数据。

四、MySQL 经典应用架构

AS 为 Web 前端，dbm157 是 主，dbm158 是 dbm157 的备机，dbm159/160/161 是 mysql 从。MySQL 写操作一般采用基于 Heartbeat + DRDB + MySQL 搭建高可用集群的方案。通过 heartbeat 实现对 MySQL 主进行状态监测，而 DRDB 实现 dbm157 数据同步到 dbm158，主从之间同步通过 binlog 日志实现。