查看原文
其他

MySQL 性能提升之降龙十八掌

张甦 数据库江湖 2024-03-03


技术老铁们,工作累了,我们就一起来放松一下!老张我呢是个金庸迷,在金庸小说中,降龙十八掌无愧巅峰外功,它的威力之大可想而知。而今儿,老张要给大家介绍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。


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


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


MySQL OCP 知多少?Linux 命令行生成随机密码的十种方法

TNS-12535|TNS-00505 错误解决办法

Oracle 19c|使用 DBUA 升级数据库到 19c 的完整核对清单

最全最详细 Oracle 19c 19.10DBRU 最新补丁升级步骤(收藏版)



继续滑动看下一个

MySQL 性能提升之降龙十八掌

向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存