网站购买空间,网站代理建设,营销型网站建设的特色,单位门户网站转载自 史上最全的 MySQL 高性能优化实战总结
一、前言
MySQL 对于很多 Linux 从业者而言#xff0c;是一个非常棘手的问题#xff0c;多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程#xff0c;很…转载自 史上最全的 MySQL 高性能优化实战总结
一、前言
MySQL 对于很多 Linux 从业者而言是一个非常棘手的问题多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程很多的查询优化工作实际上就是遵循一些原则让MySQL 的优化器能够按照预想的合理方式运行而已。
今天给大家体验 MySQL 的优化实战助你高薪之路顺畅 图 - MySQL查询过程
二、优化的哲学
注意优化有风险涉足需谨慎
1. 优化可能带来的问题 优化不总是对一个单纯的环境进行还很可能是一个复杂的已投产的系统。 优化手段本来就有很大的风险只不过你没能力意识到和预见到 任何的技术可以解决一个问题但必然存在带来一个问题的风险 对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。 保持现状或出现更差的情况都是失败
2. 优化的需求 稳定性和业务可持续性,通常比性能更重要 优化不可避免涉及到变更变更就有风险 优化使性能变好维持和变差是等概率事件 切记优化应该是各部门协同共同参与的工作任何单一部门都不能对数据库进行优化 所以优化工作,是由业务需要驱使的
3. 优化由谁参与
在进行数据库优化时应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等业务相关人员共同参与。
三、优化思路
1. 优化什么
在数据库优化上有两个主要方面即安全与性能。 安全 --- 数据可持续性 性能 --- 数据的高性能访问
2. 优化的范围有哪些
存储、主机和操作系统方面: 主机架构稳定性 I/O 规划及配置 Swap 交换分区 OS 内核参数和网络问题
应用程序方面: 应用程序稳定性 SQL 语句性能 串行访问资源 性能欠佳会话管理 这个应用适不适合用 MySQL
数据库优化方面: 内存 数据库结构(物理逻辑) 实例配置
说明不管是在设计系统定位问题还是优化都可以按照这个顺序执行。
3. 优化维度
数据库优化维度有四个
硬件、系统配置、数据库表结构、SQL 及索引。 优化选择 优化成本:硬件系统配置数据库表结构SQL 及索引 优化效果:硬件系统配置数据库表结构SQL 及索引
四、优化工具有啥
1. 数据库层面
检查问题常用工具 不常用但好用的工具 2. 数据库层面问题解决思路
一般应急调优的思路
针对突然的业务办理卡顿无法进行正常的业务处理需要立马解决的场景 1、show processlist 2、explain select id ,name from stu where nameclsn; # ALL id name age sex select id,name from stu where id2-1 函数 结果集30; show index from table; 3、通过执行计划判断索引问题(有没有、合不合理)或者语句本身问题 4、show status like %lock%; # 查询锁状态 kill SESSION_ID; # 杀掉有问题的session
常规调优思路
针对业务周期性的卡顿例如在每天 10-11 点业务特别慢但是还能够使用过了这段时间就好了。《MySQL 调优/优化的 101 个建议》了解下。 1、查看 slowlog分析 slowlog分析出查询慢的语句。 2、按照一定优先级进行一个一个的排查所有慢语句。 3、分析 top sql进行 explain 调试查看语句执行时间。 4、调整索引或语句本身。
3. 系统层面
cpu方面
vmstat、sar top、htop、nmon、mpstat
内存
free 、ps -aux 、
IO 设备(磁盘、网络)
iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、
vmstat 命令说明 Procsr 显示有多少进程正在等待 CPU 时间。b 显示处于不可中断的休眠的进程数量。在等待 I/O Memoryswpd 显示被交换到磁盘的数据块的数量。未被使用的数据块用户缓冲数据块用于操作系统的数据块的数量 Swap操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1 和 s0 最好是 0 Io每秒从设备中读入 b1 的写入到设备 b0 的数据块的数量。反映了磁盘 I/O System显示了每秒发生中断的数量(in)和上下文交换(cs)的数量 Cpu显示用于运行用户代码系统代码空闲等待 I/O 的 CPU 时间
iostat 命令说明
实例命令 iostat -dk 1 5
iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await)) tps该设备每秒的传输次数。“一次传输”意思是“一次 I/O 请求”。多个逻辑请求可能会被合并为“一次 I/O 请求”。 iops 硬件出厂的时候厂家定义的一个每秒最大的 IO 次数,一次传输请求的大小是未知的。 kB_read/s每秒从设备(drive expressed)读取的数据量 KB_wrtn/s每秒向设备(drive expressed)写入的数据量 kB_read读取的总数据量 kB_wrtn写入的总数量数据量这些单位都为 Kilobytes。
4. 系统层面问题解决办法
你认为到底负载高好还是低好呢
在实际的生产中一般认为 cpu 只要不超过 90% 都没什么问题 。《MySQL数据库开发的 36 条军规》了解下。
当然不排除下面这些特殊情况
问题一cpu 负载高IO 负载低 内存不够 磁盘性能差 SQL 问题 ------去数据库层进一步排查 sql 问题 IO 出问题了(磁盘到临界了、raid 设计不好、raid 降级、锁、在单位时间内 tps 过高) tps 过高: 大量的小数据 IO、大量的全表扫描
问题二IO 负载高cpu 负载低 大量小的 IO 写操作 autocommit 产生大量小 IO IO/PS 磁盘的一个定值硬件出厂的时候厂家定义的一个每秒最大的 IO 次数。 大量大的 IO 写操作 SQL 问题的几率比较大
问题三IO 和 cpu 负载都很高
硬件不够了或 SQL 存在问题
五、基础优化
1. 优化思路
定位问题点:
硬件 -- 系统 -- 应用 -- 数据库 -- 架构(高可用、读写分离、分库分表)
处理方向
明确优化目标、性能和安全的折中、防患未然
2. 硬件优化
主机方面 根据数据库类型主机 CPU 选择、内存容量选择、磁盘选择 平衡内存和磁盘资源 随机的 I/O 和顺序的 I/O 主机 RAID 卡的 BBU(Battery Backup Unit)关闭
cpu 的选择 cpu 的两个关键因素核数、主频 根据不同的业务类型进行选择 cpu 密集型计算比较多OLTP 主频很高的 cpu、核数还要多 IO 密集型查询比较OLAP 核数要多主频不一定高的
内存的选择 OLAP 类型数据库需要更多内存和数据获取量级有关。 OLTP 类型数据一般内存是 cpu 核心数量的 2 倍到 4 倍没有最佳实践。
存储方面 根据存储数据种类的不同选择不同的存储设备 配置合理的 RAID 级别(raid 5、raid 10、热备盘) 对与操作系统来讲不需要太特殊的选择最好做好冗余(raid1)(ssd、sas 、sata)
raid 卡主机 raid 卡选择 实现操作系统磁盘的冗余(raid1) 平衡内存和磁盘资源 随机的 I/O 和顺序的 I/O 主机 RAID 卡的 BBU(Battery Backup Unit)要关闭
网络设备方面
使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA 卡)
注意以上这些规划应该在初始设计系统时就应该考虑好。
3. 服务器硬件优化 1、物理状态灯 2、自带管理设备远程控制卡(FENCE 设备ipmi ilo idarc)开关机、硬件监控。 3、第三方的监控软件、设备(snmp、agent)对物理设施进行监控 4、存储设备自带的监控平台。EMC2(hp 收购了) 日立(hds)IBM 低端 OEM hds高端存储是自己技术华为存储
4. 系统优化
Cpu
基本不需要调整在硬件选择方面下功夫即可。
内存
基本不需要调整在硬件选择方面下功夫即可。
SWAP
MySQL 尽量避免使用 swap。阿里云的服务器中默认 swap 为 0
IO raid、no lvm、 ext4 或 xfs、ssd、IO 调度策略 Swap 调整(不使用 swap 分区) 这个参数决定了 Linux 是倾向于使用 swap还是倾向于释放文件系统 cache。在内存紧张的情况下数值越低越倾向于释放文件系统 cache。当然这个参数只能减少使用 swap 的概率并不能避免 Linux 使用 swap。
修改 MySQL 的配置参数 innodb_flush_method开启 O_DIRECT 模式。这种情况下InnoDB 的 buffer pool 会直接绕过文件系统 cache 来访问磁盘但是redo log 依旧会使用文件系统 cache。值得注意的是Redo log 是覆写模式的即使使用了文件系统的 cache也不会占用太多。
IO 调度策略 5. 系统参数调整
Linux 系统内核参数优化 用户限制参数(MySQL 可以不设置以下配置) 6. 应用优化
业务应用和数据库应用独立,防火墙iptables、selinux 等其他无用服务(关闭) 安装图形界面的服务器不要启动图形界面 runlevel 3,另外思考将来我们的业务是否真的需要MySQL还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。《MySQL 调优/优化的 101 个建议》了解下。
六、数据库优化
SQL 优化方向
执行计划、索引、SQL 改写
架构优化方向
高可用架构、高性能架构、分库分表
1. 数据库参数优化
调整
实例整体(高级优化扩展) 连接层(基础优化)
设置合理的连接客户和连接方式 SQL 层(基础优化) query_cache_size 查询缓存 OLAP 类型数据库,需要重点加大此内存缓存 但是一般不会超过 GB 对于经常被修改的数据缓存会立马失效 我们可以实用内存数据库(redis、memecache)替代他的功能
2.存储引擎层(innodb 基础优化参数) 随着系统用户量的不断增加MySQL 索引的重要性不言而喻对于后端工程师只有在了解索引及其优化的规则并应用于实际工作中后才能不断的提升系统性能开发出高性能、高并发和高可用的系统。
