mysql性能优化:整体性能调优参考(老叶推荐)
Modify by lyc at 2018-11-13:以下博文整理转自老叶茶馆博文。
参考博文:
老叶茶馆-比较全面的MySQL优化参考(上篇)
老叶茶馆-比较全面的MySQL优化参考(下篇)
一、服务器硬件层面(略)
服务器硬件层的优化(略),请参考老叶博客原文。
关闭 bios NUMA:NUMA(Non Uniform Memory Access Architecture),按我的理解就是让系统跨越CPU分配内存。
二、操作系统内核参数优化
1.锁住swap
针对关键内核参数设定合适的值,目的是为了减少swap的倾向,并且让内存和磁盘I/O不会出现大幅波动,导致瞬间波峰负载
1 | $ cat /proc/sys/vm/swappiness |
2.系统脏页
1 | $ vim /etc/sysctl.conf |
vm.dirty_background_ratio
这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时(如5%)就会触发pdflush/flush/kdmflush
等后台回写进程运行,将一定缓存的脏页异步地刷入外存。vm.dirty_ratio
指定了当文件系统缓存脏页数量达到系统内存百分之多少时(如10%),系统不得不开始处理缓存脏页(因为此时脏页数量已经比较多,为了避免数据丢失需要将一定脏页刷入外存);在此过程中很多应用进程可能会因为系统转而处理文件IO而阻塞。
三、my.cnf 最重要的参数选项调整建议
1 | default-storage-engine=InnoDB |
四、关于Schema设计规范及SQL使用建议
- 所有的InnoDB表都设计一个无业务用途的自增列做主键,对于绝大多数场景都是如此,真正纯只读用InnoDB表的并不多,真如此的话还不如用TokuDB来得划算;
- 字段长度满足需求前提下,尽可能选择长度小的。此外,字段属性尽量都加上NOT NULL约束,可一定程度提高性能;
- 尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT * 的时候读性能太差。
- 读取数据时,只选取所需要的列,不要每次都SELECT *,避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB列;
- 对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引;
- 通常情况下,子查询的性能比较差,建议改造成JOIN写法;
- 多表联接查询时,关联字段类型尽量一致,并且都要有索引;
- 多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表;
- 多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;
- 多用复合索引,少用多个独立索引,尤其是一些基数(Cardinality)太小(比如说,该列的唯一值总数少于255)的列就不要创建独立索引了;
- 类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多;
五、其他建议
- 通常地,单表物理大小不超过10GB,单表行数不超过1亿条,行平均长度不超过8KB,如果机器性能足够,这些数据量MySQL是完全能处理的过来的,不用担心性能问题,这么建议主要是考虑ONLINE DDL的代价较高;
- 不用太担心mysqld进程占用太多内存,只要不发生OOM kill和不用到大量的SWAP都还好;
- 在以往,单机上跑多实例的目的是能最大化利用计算资源,如果单实例已经能耗尽大部分计算资源的话,就没必要再跑多实例了;
- 定期使用
pt-duplicate-key-checker
检查并删除重复的索引。定期使用pt-index-usage
工具检查并删除使用频率很低的索引; - 定期采集
slow query log
,用pt-query-digest
工具进行分析,可结合Anemometer
系统进行slow query
管理以便分析slow query
并进行后续优化工作; - 可使用
pt-kill
杀掉超长时间的SQL请求,Percona版本中有个选项innodb_kill_idle_transaction
也可实现该功能; - 使用
pt-online-schema-change
来完成大表的ONLINE DDL需求; - 定期使用
pt-table-checksum
、pt-table-sync
来检查并修复mysql主从复制的数据差异;