企业网站建设

建站知识

今日已发布信息: 231244
累计注册用户: 78042675

MySQL通用优化手册

硬件系统优化 利用特点

概述: 缓解I/O负载。MySQL 优化了解完MySQL各方面的特点后,我们可以开始进行优化工作了。在开始之前,我们需要先明确几点:为何而优化?领导指派用户投诉\监控预警没事找事?当前跑得好好的话,就没必要折腾神马优化没事找抽,即便想练手,也要悠着点,防止误操作;

导读:


本文根据 MySQL中文网创始人叶金荣在DevOps华南运维圈@UCloud微信群的「运维在线」栏目的嘉宾分享整理而成。分享主题为“MySQL通用优化”,涵盖硬件、系统优化,MySQL优化、配置优化、SCHEMA优化等内容,值得学习。


本文转自UCloud云计算公众号,点击阅读原文 查看网页版文章。


内容提纲

MySQL的特点;

硬件、系统优化;

MySQL 配置优化;

SCHEMA设计优化;

SQL 优化;

其他优化。

MySQL 的特点

首先,需要明确的是。想要做好MySQL优化,需要先了解MySQL都有哪些特点:

简言之,MySQL一般用于互联网业务的数据持久化存储,并且用于保证数据的一致性、可靠性,而不是用于:

复杂查询;

复杂运算;

大二进制存储。

等奇葩用途。

CPU的利用特点

看看MySQL不同版本对CPU多核的支持、利用情况:

建议:

采用最新MySQL版本,以提升其CPU利用率;

每个SQL足够简单,不要太过复杂;

每个连接足够快速完成,不要“恋战”。

内存利用特点

内存利用、管理方面有什么特点呢?

建议:

关闭query cache;

采用InnoDB;

采用PerconaMariaDB分支版本;

简单KV数据用NOSQL存储,不使用MySQL。

磁盘的利用特点

最后看下磁盘I/O方面的特点:


建议:

使用多盘提升整体I/O性能;

多使用高速I/O设备;

尽量加大内存,缓解I/O负载。

MySQL 优化

了解完MySQL各方面的特点后,我们可以开始进行优化工作了。

在开始之前,我们需要先明确几点:

为何而优化?领导指派用户投诉\监控预警没事找事?当前跑得好好的话,就没必要折腾神马优化没事找抽,即便想练手,也要悠着点,防止误操作;

优化的目标是什么,说白了,就是要解决什么瓶颈,切忌在过程中偏离初心;

 

  成都建站服务  大连钢板切割   今日推荐免费建站   分类信息   赤峰网站建设公司

 

计算投入产出比,比如为了让性能提升1%而投入1人月,基本上是非常不划算了,还不如去干点别的;

优化前做好现场信息采集,优化后再次采集做对比,确认优化成果(用来邀功啊,让老板看到你的成绩,年底加加薪什么的,最起码也能锻炼总结归纳文档能力吧)。

通常,我们进行MySQL优化工作的套路是这样的:

确认需求,先明确当前的运行状态,是否真的需要进行优化,别没事找事;

常见瓶颈:

绝大多数瓶颈在于I/O子系统;

若CPU很高,90%以上是因为索引不当;

发生swap时,可能因为内存分配太小或过大;

iowait太高时,想办法从索引角度入手优化,以及提高I/O设备性能,增加内存,减少排序,减少SELECT一次性读取数据量。

常用优化策略

瞬间并发很高,采用thread pool;

频繁order bygroup by,索引入手;

适当调整内存,不要太大或太小。一般ibp设置为50% ~ 70%为宜;

iowait高,加内存,提高iops,减少数据读写。

制定方案时,重点解决发生频率高的问题(量变更容易引起质变);回顾反馈,整理文档,回顾总结,从零散资料中总结出规律,预防风险再次出现。

一般采用下面几个瓶颈分析工具:

绝大多数情况下,有经验的工程师靠sysstat工具集中的就足够了,很多问题一看现象大概就能知道瓶颈何在。

在MySQL层面,有哪些确认瓶颈的手段呢?

硬件、系统优化

我们继续MySQL优化之旅。先来看看从硬件以及OS层面,都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整,其次是RAID方面的优化。

再来看看几个参考配置图:

1、CPU选择最大性能模式,避免节能模式导致性能不足。



2、关闭NUMA,降低swap概率。

3、采用RAID-10,并且选择FORCE WB。


在OS层面,可以有几个优化手段:

调整IO Scheduler

使用XFS

调整其他内核选项备

备注:

vm.swappiness,降低发生swap的几率;

vm.dirty_background_ratio & vm.dirty_ratio,避免瞬间大量I/O请求导致系统卡死。

从这个压测结果可以看到noop/deadline有明显优势。

这个io scheduler还可以在线修改的哦,还等神马?

echo deadline > /sys/block/sdc/queue/scheduler

在用PCIe SSD设备做测试时,XFS的IOPS能跑到ext4的4倍,表现非常好。

还有什么理由不用XFS呢?

xfs挂载参数:

/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

格式化参数不用特别指定,默认的即可。

MySQL配置优化

前面讲到,给MySQL分配的内存不要太大或太小,那么多少合适呢。

首先,要搞清楚MySQL的内存都由哪些部分组成:

global buffers和oracle的SGA一个意思,就是全局一次分配,多个线程间共享。

thread buffers和oracle的PGA一个意思,每个线程单独分配,线程间不能相互共享,因此不要分配过大,避免内存不够使用,发生OOM。



原则: 对这些选项调整时,不要照猫画虎随便调整,要先做到心里有数,了解其具体作用才动手。

看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如:


如果再启用binlog后的对比:

最后,再加上sync_binlog选项不同设置的对比:

备注: 这3个测试结果图均来自Percona。

结论&建议:

想要保证数据安全,就设置 trx_commit =1 & sync_binlog = 1

在slave上或非关键场景,可以都改成0

SCHEMA设计优化

接下来看看MySQL的模式(SCHEMA)设计优化要点:

http://cd.kvov.com.cn/jzxx34447.html