性能优化:MySQL 性能提升之降龙十八掌

作者 | 张甦, 数据库领域的专家和知名人士、图书《MySQL王者晋级之路》作者,51CTO 专家博主。近10年互联网线上处理及培训经验,专注于 MySQL 数据库,对 MongoDB、Redis 等 NoSQL 数据库以及 Hadoop 生态圈相关技术有深入研究,具备非常丰富的理论与实战经验。

技术老铁们,工作累了,我们就一起来放松一下!老张我呢是个金庸迷,在金庸小说中,降龙十八掌无愧巅峰外功,它的威力之大可想而知。而今儿,老张要给大家介绍18招式,来优化我们的 MySQL 数据库,让它跑起来更快,更稳定!

之前老有学生问我,张老师该如何优化我们的 MySQL 数据库呢?这个问题太泛泛了,不是很具体!因为数据库的优化要从多个角度去考虑,通过不同的维度模型去排查问题。为此我整理了下思路,大概可以从18个角度,大致四个方向去给大家一些建议。

第一掌---亢龙有悔

要想保证数据库能够高效,稳定地运行在服务器上面,我们首先要保证有充足的内存,只有内存足够大了,我们才能缓存住那些我们经常访问的热数据,一些 update 语句的操作当然也可以在内存中优先完成。但是我们要考虑内存使用黄金分割法则,由于不同业务的存在,对内存的需求当然也就不一样了。

举个例子来说,用户经常访问的热数据,对于内存的分配就要尽可能达到达到数据库内存的 70-80% 左右。众所周知,我们知道 MySQL 数据库内存主要靠 innodb_buffer_pool,redo log buffer,double write buffer,binlog cache 等组成。如果服务器上面只跑着 MySQL一个应用,那大概 innodb_buffer_pool 可以分配到物理内存的 50-80% 左右。

TIPS:我们要根据实际物理内存的大小,具体是什么业务类型,去考虑数据库内存的分配。

第二掌---飞龙在天

要优化 MySQL 数据库,首先要很了解对手,随着版本的升级,MySQL 用到的 CPU 核数就越多,自从 MySQL 5.6 之后可以使用到 64 个核。MySQL 连接特点的是这样,每个连接对应一个线程,每个 sql/ 查询只能使用到一个 cpu 核心,所以需要越多的 CPU,并且更快的 CPU。这样才能有利于提高数据库性能,提高我们数据库的并发能力!

TIPS:使用多核 CPU。

第三掌---见龙在田

众所周知,IO 对数据库来说,一直都是瓶颈,并且有可能将来一段时间还会是。所以对存储介质的要求就非常高,对于 IO 系统比较高的情况下,建议我们要使用更快的存储设备 SSD 固态硬盘可提高上百倍的数据读写性能或者是 PCIE-SSD 固态硬盘可提高上千倍的数据读写能力。像现在的一些电商网站,在搞店庆或者促销活动的时候,都需要借助此设备,来满足大量用户的影响请求。

TIPS:建议上高转速硬件设备,SSD 或者 PCIE-SSD

第四掌---鸿渐与陆

自从 web2.0 时代开启,基本所有的,我们使用的软件都是基于 linux 平台自主研发的。我们知道,MySQL 数据库也是跑在 linux 操作系统上面的。在官方建议估计最推荐的是 Solaris,但从实际生产中的角度来看 CentOS 和 REHL 都是不错的选择,个人建议推荐使用 CentOS, 如果非要使用 REHL,建议 版本为6以后的,这里就不推荐使用在 windows 下跑 MySQL 数据库了,虽然随着 MySQL 版本提升,对 windows 有了相关的优化,但是对于高并发,高负载的环境来说,依旧不建议使用。

TIPS:推荐使用 CentOS,或者 REHL 操作系统类型

第五掌---潜龙勿用

操作系统层面的优化,我们要考虑个可能大家会比较忽略的问题,首先就是 swappiness 的问题。swappiness 的值大小对如何使用 swap 分区有着密切的联系。有两个极限值,一个为 0,另一个为 100,查看可执行 cat /proc/sys/vm/swappiness。

0 代表:最大限度地使用物理内存,然后才是 swap 分区,这种行为有可能导致系统内存溢出,从而导致mysql被意外kill掉。不建议这样去设置。

100 则为:积极地使用使用 swap 分区,并且把内存上面的数据及时搬到 swap 分区里。

TIPS:这里比较建议使用默认 60 就可以。

第六掌---利涉大川

与 swappiness 对应的,另一个操作系统层面的优化,还有一个小细节点就是 IO 调度。这里有 cfq,noop 和 deadline,系统默认使用 cfq,这里老师建议使用 deadline。查看方法:

cat /sys/block/sda/queue/scheduler/

TIPS:deadline 可以调整读写时间,避免写完没有被读取的饿死场景。

第七掌---突如其来

Oracle 11g 之后多了一个 result_cache,来缓存数据结果集。MySQL 里面通过 innodb_buffer_pool 里面有个 query cache 来缓存静态结果集。我们都希望热数据都保存在内存里面,我们读取数据快速便捷,数据库的缓存率也很高!但数据库中的 query cache 里面的数据一但发生更改,此缓存区毫无意义,就会变成鸡肋。而且如果开启 Query Cache,更新与写入都要去检查 query cache 反而增加了写入的开销。

TIPS:建议关闭 query cache

第八掌---震惊百里

对于磁盘阵列,我们再熟悉不过了,但是对于阵列卡的 cache 策略,我们又该如何选择呢。首先对于qps,tps,业务高的系统,一定要配置阵列卡,配 cache 模块,和 BBU 模块(用于提供后备电量)。

cache 策略有两种,一种为:write through(WT);另一种为:write back;

个人强烈建议使用 write back(WB)。WT含义,数据直接写入磁盘,WB含义:数据先写阵列卡的 cache,再由 cache 写入磁盘,这样对于写入的性能有所提高。并且对于加速 redo log ,binlog, data file都有好处。

TIPS:强烈建议阵列卡的 cache 策略使用 write back。

第九掌---或跃在渊

前面也涉及到了,尽可能大的给 innodb_buffer_pool 分配空间,在服务器只跑数据库一个应用前提下大概为物理内存 50-80%。

TIPS:建议应用与数据库分开部署在服务器上面,后期好排查问题。

第十掌---双龙取水

MySQL 数据库的一些核心参数,我们要在心里铭记。比如双一的含义,直接影响日志的刷新机制。影响 redo log buffer 的刷新机制

innodb_flush_log_at_trx_commit = 1(最安全)

innodb_flush_log_at_trx_commit = 2 (性能一般)

innodb_flush_log_at_trx_commit = 0 (性能最好)。

影响binlog cache的刷新机制~sync_binlog=0,当事务提交之后,MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘,而让 Filesystem 自行决定什么时候来做同步,或者 cache满了之后才同步到磁盘。sync_binlog=n,当每进行 n 次事务提交之后,MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。为了确保安全性,我们可以将 sync_binlog=1。为了获得最佳性能我们可以将 sync_binlog=0。

TIPS:对于不同业务的公司,保障的点不一样,所有我们要考虑好,是业务最重要,还是数据最重要!然后分别去设置不同的参数 value

第十一掌---鱼跃于渊

MySQL 数据库区别于其他数据库最主要就是插件式存储引擎,最为著名就是 myisam 还有 innodb。它们都有各自的特点,这里强烈建议使用 innodb 存储引擎表,无论是对于事务的支持,还是在线 DDL 语句快速操作,它都是目前最优秀的存储引擎!MySQL 5.5 之后默认使用的存储引擎都是 innodb

TIPS:生产环境中,如果还有 myisam 这种存储引擎的表,建议全部做 myisam-->innodb 存储引擎的转换!不过MySQL 5.7之后,系统表也都是 innodb 了!

第十二掌---时乘六龙

文件系统强烈推荐使用 xfs,不要再使用 ext3,ext4 之类的,因为 xfs 这种文件系统也是 B-tree 结构最接近于数据库的树状结构。

第十三掌---密云不雨

生产环境中,经常会出现对大表进行 delete,或者 update 这类的操作。数据碎片随之产生,我们要经常去整理主要业务表的碎片,让查询检索更快。可以通过 pt-ioprofile 监控与磁盘交互最为紧密的表,然后通过 alter table 或者导入导出数据的方法对表进行碎片整理。尽可能回收表空间。

第十四掌---损则有孚

利用天兔(lepus)或者 zabbix 做好对数据库的监控。监控事项可以从服务器的状态,内存的使用情况,cpu的负载。数据库中每秒的增删改查信息,架构中的延迟和复制状态信息去作为监控的核心点。

第十五掌---龙战于野

配合开发人员合理地设计表结构,秉着越简单越好的原则,去选择合适字段的数据类型。对于 ipv4,时间类型的字段,我们完全可以通过整型 int 来存取!通过函数转换就可以了!

ip涉及到两个函数:inet_aton和inet_ntoa

时间类型的两个函数:from_unixtime和unix_timestamp

第十六掌---事务隔离级别的选择

mysql数据库中,有四种事务隔离级别。它们分别是Read Uncommitted(RU),Read Committed(RC),Repeatable Read(RR),Serializable(SR)。对于交易类型系统的网站,对于事务要求比较高,我们建议使用RR这种隔离级别。

第十七掌---羝羊触藩

更改文件句柄 ulimit –n 默认1024 太小

进程数限制 ulimit –u 根据不同版本来决定

禁掉NUMA numctl –interleave=all

第十八掌---神龙摆尾

做过数据库的同学们,可以经常会遇到 too many connections 这样的问题,对于这样的问题,我们一定要做好配置数据库内部并发的情况。innodb_thread_concurrency 这个参数来决定 innodb 的并发情况。默认的大小是0。在 mysql5.7 版本中,增加了 thread pool,连接复用的存在,可以取默认值就 ok。但是5.7之前的版本,就需要考量一下取值了,个人建议 mysql5.6版本中设置为 36。mysql5.6 之前可以 8-32。

降龙十八掌已经打完,希望对于数据库爱好者,从事数据库工作中的同学来说有帮助。让我们每天学习一点点,把自己的内功练得越来越深厚,打出属于自己的武功。让我们的数据库飞起来!

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏WeTest质量开放平台团队的专栏

iOS Push详述,了解一下?

本文主要对iOS Push的在线push、本地push及离线(远程)push进行梳理,介绍了相关逻辑,测试时要注意的要点以及相关工具。

6316
来自专栏前端开发

PHP制作 微信、支付宝、QQ支付多合一收款二维码

4694
来自专栏架构师小秘圈

出行领域架构设计

作者:王小雪。滴滴出行架构师,原快的打车架构师。 来源:程序员杂志 某知名打车平台从随着业务的发展,系统访问量迅速膨胀,很多复杂的问题要在短时间内解决,且不能影...

4114
来自专栏PhpZendo

事件驱动架构设计

这篇文章是 软件架构演进 一个有关 软件架构 系列文章中的一篇。这些文章,主要是我学习软件架构、对软件架构的思考及使用方法的记录。相比于这个系列的前几篇文章,本...

5872
来自专栏刘望舒

知乎 Android 客户端组件化实践

知乎 Android 客户端最早使用的是最常见的单工程 MVC 架构,所有业务逻辑都放在了主工程 Module 里,网络层和一些公共代码分别被抽成了一个 Mod...

4224
来自专栏JAVA高级架构

浅谈 MySQL 集群高可用架构

新年快乐 前言 高可用架构对于互联网服务基本是标配,无论是应用服务还是数据库服务都需要做到高可用。对于一个系统而言,可能包含很多模块,比如前端应用,缓存,数据库...

37915
来自专栏Golang语言社区

系统架构-基础篇-(高性能基础建设说明与选型条件)

本文牵扯的面积可能会比较泛,或者说比较大,在这个层面很多人也有自己的见解,所以我这也仅仅是抛砖引玉,结合前面讲述的一些基础技术,从思想中阐述更为深入的架构思想基...

3535
来自专栏数据和云

没有宫廷内斗,数据库界的延禧攻略

作者 | 张甦, 数据库领域的专家和知名人士、图书《MySQL王者晋级之路》作者,51CTO 专家博主。近10年互联网线上处理及培训经验,专注于 MySQL 数...

1172
来自专栏后端技术探索

纯干货--秒杀系统架构分析与实战

(1)查询商品;(2)创建订单;(3)扣减库存;(4)更新订单;(5)付款;(6)卖家发货

3294
来自专栏云计算-私有云

Windows Server 2019前瞻

十一假期马上就过完了,不知道各位小伙伴玩的怎么样啊,是否有遇到“人在囧途”或者是否看到了处处大海。微软于2018年9月24日-28日在美国召开了Ignite 2...

1.4K0

扫码关注云+社区

领取腾讯云代金券