MySQL企业常见架构与调优

桃扇骨 2021-12-16 08:11 267阅读 0赞

一、常用的MySQL调优策略

1.  硬件层相关优化

修改服务器BIOS设置

1）、选择PerformancePerWattOptimized（DAPC）模式，发挥CPU最大性能

2）、MemoryFrequency（内存频率）选择MaxIMUNPerformce(最佳性能)

3）、内存设置菜单中，启用Node Interleaving，避免NUMA问题

2.磁盘I/O相关

1）、使用SSD硬盘

2）、如果是磁盘阵列存储，建议阵列卡同时配备CACHE及BBU模块，可明显提升IOPS。

3）、Raid级别尽量选择raid10，而不是raid5

3.文件系统层优化

1）、使用deadline/noop这两种I/O调度器，千万别用cfg；

2）、使用xfs文件系统，千万别用ext3；ext4勉强可用，但业务量很大的话，则一定要用xfs；

3）、文件系统mount参数中增加：noatime,nodiratime,nobarrier几个选项(nobarrier是xfs文件系统特有的)

4.内核参数优化

1）、修改vm.swappiness参数，降低swap使用率。RHEL7、centos以上则慎重设置为0，可能发生OOM

2)、调整vm.dirty\_background\_ratio（一般设置10%）,vm.dirty\_ratio（一般设置5%）内核参数，以确保能持续将脏数据刷线到磁盘，避免瞬间I/O写。产生等待。

3）、调整net.ipv4.tcp\_tw\_recycle、net.ipv4.tcp\_tw\_reuse都设置为1，减少TIME\_WAIT，提高TCP效率

5、MySQL参数优化建议

1）、建议设置defaults-storage-engine=InnoDB，强烈建议不要再使用MyISAM引擎

2）、调整innodb\_buffer\_pool\_size的大小，如果是单实例切绝大多数是InnoDB引擎表的话，可以考虑设置为屋里内存的50%-70%左右

3）、设置innode\_file\_per\_table=1，使用独立表空间

4）、调整innode\_data\_file\_path=ibdata1:1G:autoextend，不要使用默认的10M，在高并发场景下，性能会有很大的提升。

5）、设置innodb\_log\_file\_size=256M，设置innodb\_log\_files\_in\_group=2，基本可以满足大多数应用场景

6）、调整max\_connection（最大连接数）、max\_ connection \_error(最大错误数)设置,根据业务最大小进行设置

7）、另外,open\_files\_limit、innodb\_open-files、table\_open\_cache、table\_definition\_cache可以设置大约为max\_connection的10倍左右。

8）、Key\_buffer\_size建议调小，32M左右即可，另外建议关闭query cache。

9）、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。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 1][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 2][]

4、Heartbear/DRDB高可用解决方案

此方案处理failover的方式上依旧采用Heartbeat，不同的是，在数据共享方案，采用 了基于块级别的数据同步软件DRDB来实现。

DRDB是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不通，他不共享存储，而是通过服务器之间的网络复制数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 3][]

三、MySQL经典应用架构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 4][]

其中：dbm157是mysql主，dbm158是mysql主的备机，dbs159/160/161是mysql从。MySQL写操作一般采用基于heartbeat+DRDB+MySQL搭建高可用集群的方案。通过heartbeat实现对mysql主进行状态监测，而DRDB实现dbm157数据同步到dbm158。

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------

用人品去感动别人，用改变去影响别人，用状态去燃烧别人，用行动去带动别人，用阳光去照耀别人，用坚持去赢得别人，要求自己每天都去做与目标有关的事情，哪怕每天只进步一点点，坚持下来你就是最优秀卓越的！欢迎大家加入大数据交流群：725967421     一起交流，一起进步！！！

\---------------------------------------------------------------------------------------------------------------------------------------------------------------------  
\---------------------

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70]: /images/20211214/74fc55520e974dd397037d00b5b4f339.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 1]: /images/20211214/934dcb22046e4be6a16c4a5c399ff217.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 2]: /images/20211214/e562b659dbe2457bb47e5aa7ad99bb78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 3]: /images/20211214/e4515630ed044db48e09a673b4d26280.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6ejExNDE5Nzk1NzA_size_16_color_FFFFFF_t_70 4]: /images/20211214/40c9062a0ca747898a8d55f18b7740eb.png