MYSQL企业常用架构与调优经验分享

MYSQL企业常用架构与调优经验分享

传闻:2016艾薇Linux高薪岗位实践运维管理强化班全新升级，课程大纲:http://www.iivey.com/666-2

一、选择Percona服务器、MariaDB或MYSQL

1.Mysql三个存储引擎

MySQL提供了两个存储引擎:MyISAM和InnoDB。MySQL4和5中应用了默认设置的MyISAM存储引擎。从MYSQL5.5开始，MYSQL将默认设置存储引擎从MyISAM改为InnoDB。

MyISAM不提供事务管理应用程序，而InnoDB提供。

XtraDB是InnoDB存储引擎的改进版本号，它是为更强的应用程序设计的，用于升级计算机系统软件的性能，还包括一些高性能自然环境中的新功能。

2.Percona服务器分支

Percona服务器是由领先的MySQL咨询公司Percona发布的。

PerconaServer是一个独立的数据库查询商品，完全兼容MySQL，不需要修改代码就可以把存储引擎改成XtraDB。是最接近官网的MySQL企业发行版的版本号。

Percona提供了一个高性能的XtraDB引擎、一个PXC高可用性解决方案和额外的DBA管理工具，如percona-toolkit。

3、玛丽雅迪

MariaDB是MySQL的创始人开发设计的。MariaDB的目标是完全兼容MySQL，包括API和cmd，使其可以轻松成为MySQL的替代品。

MariaDB提供了MySQL提供的规范存储引擎，即MyISAM和InnoDB。从10.0.9版本开始，XtraDB(名称编号Aria)被应用于替代MySQL的InnoDB。

4.如何选择？

经过多年的综合应用经验和性能比较，首选Perconabranch，其次是MariaDB。如果不想冒一点风险，那就选择MYSQL官网版本号。

二。常见的MYSQL调优对策

1。硬件配置层的改进

更改网络服务器的BIOS设置

选择性能功耗比优化(dapc)模式，充分发挥CPU的更大性能。

内存频率(MemoryFrequency)选择最大性能(最佳性能)。

运行内存设置菜单，并打开节点交错以防止NUMA问题。

2、硬盘I/O相关

应用SSD计算机硬盘

如果是磁盘阵列存储，建议阵列卡额外配备缓存和BBU控制模块，可以显著提高IOPS。

Raid级别应该尽可能是raid10而不是raid5。

3。系统文件层提升

使用deadline/noop、两个I/O生产调度程序，并且永远不要使用cfq。

应用xfs系统文件时，千万不要用ext3；Ext4可以工作，但是如果订单量很大，就必须用xfs；

升级了系统文件挂载的主要参数:noatime、nodiratime、nobarrier等多个选项(nobarrier是xfs系统文件特有的)；

4。核心主要参数的改进

更改vm.swappiness的主要参数以降低交换空间利用率。在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:2gb:autoextend，不能使用默认设置10M。在分布式系统场景下，性能会有很大的提升。

设置innodb_log_file_size=255M，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建议减少到32M。此外，建议关闭查询缓存。

mp_table_size和max_heap_table_size设置不需要太大，sort_buffer_size、join_buffer_size、read_buffer_size和read_rnd_buffer_size设置不需要太大。

三、  MYSQL普遍的应用架构共享 1、主从复制解决方法

是MySQL自己提供的高可用性解决方案，采用MySQL复制技术进行数据库同步。MySQL是从服务器获取二进制日志文件到主网络服务器，然后将日志文件解析成相对的SQL，在从服务器上再次实现主网络服务器的实际 *** 作，按照这种方法保证数据的一致性。

为了更易于使用，在具体应用场景中，一般采用MySQL复制技术和高可用集群软件keepalived来完成自动故障转移，可以达到95.000%的SLA。

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

MMM提供了一套可伸缩的脚本模块，用于MySQL主副本的监管、常见故障迁移和管理方法。在MMM高可用性计划中，典型的应用是双主从架构。根据MySQL复制技术，可以完成两台网络服务器之间的主从关系，任何时候只能加载一个连接点，防止了加载更多数据信息的矛盾。另外，当应该写入的主连接点出现常见故障时，MMM模块可以立即监测到，然后自动将服务项切换到另一个主连接点，重新提供服务项，从而完成MySQL的高可用。

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

在这个计划中，故障转移的解决方案是高可用性集群软件Heartbeat，它监督和管理每个连接点上相互连接的互联网，并监督集群服务项目。当连接点出现常见故障或服务项目无法使用时，集群服务项目可以在其他连接点自动启动。在信息共享层面，按照SAN(存储区域网)存储共享资源数据信息，这种规划方案可以达到99.990%的SLA。

4、Heartbeat/DRBD高可用解决方法

在这个计划中，仍然使用Heartbeat来解决故障转移问题。不同的是，在信息共享层面，采用了基于块级的数据信息同步软件DRBD来完成。

DRBD是一个存储和复制集群服务器镜像系统块机器和设备内容的解决方案，由手机软件完成，没有共享资源。与SAN互联网不同，它不共享资源存储，而是根据集群服务器的互联网来复制数据信息。

四、MYSQL經典应用架构

其中包括:

Dbm157是mysql主，dbm158是mysql主的备用机，dbs159/160/161是mysql从。

编写MySQL的实际 *** 作一般选择根据心跳DRBDMySQL构建高可用集群的方案。根据heartbeat检测mysql的主开发，而DRBD完成dbm157数据库到dbm158的同步。

在实际的读取 *** 作中，基于LVSKeepalived构建高可用、高扩展集群的方案被广泛采用。前端AS根据推广用LVS连接readVIP开发，LVS用keepliaved做成高可用模式，完成相互备份。

最后mysqlmaster的从连接点dbs159/160/161根据mysql的主从复制功能提供mysqlmaster的数据信息，根据lvs功能提供给前端开发AS进行实际的读取 *** 作，完成三层切换。