摘自:叶金荣 老叶茶馆
在对一下mysql服务器进行优化时,需要优化的项目如下:
1、 硬件层相关优化
1.1、 cpu相关
在服务器BIOS中设置,可以调整的配置有如下几方面,
目的:发挥cpu的最大性能or避免NUMA问题
A、 选择Performance per watt Optimized(DAPC)模式,发挥cpu最大性能,作为DB的服务器通常需要高运算量的服务,这种服务器就不要考虑节约用电了
B、 关闭C1E和C states等选项
目的:提高cpu效率
C、 memory Frequency(内存频率)选择maximum performance(最佳性能)
目的:提高cpu处理能力
D、 启动node interleaving(内存设置菜单)
目的:避免numa问题
1.2、 磁盘I/O相关
如下几项是为了提升IOPS性能,如下排序是安装性能提升的幅度来排序
A、 使用SSD或者PCIe SSD设备,至少获得数百倍甚至是千万倍的IOPS提升
B、 购买磁盘阵列卡同时配置cache及bbu模块的健康状况,确保意外时不至于丢失数据。
C、 有阵列卡时,设置阵列写策略为WB,甚至force Wb(若有双电保护,或者是对数据安全性要求特别高的话),严禁使用WT策略,并且关闭阵列卡预读策略,基本上是鸡肋用处不大。
D、 尽可能选择raid-10,而不是raid-5
E、 使用机械硬盘的情况下,尽可能选择高转速的,例:15KRPM,而不是7.2KRPM的盘。
2、 系统层优化
2.1、文件系统层优化
在文件系统层,下面几个措施可以明显提升IOPS性能
A、 使用deadline/noop(这两种IO调度器),不要使用cfq(cfq不适合跑DB类服务)
echo “deadline”</sys/block/sdc/queue/scheduler
文件修改:
vim /etc/grub.conf
numa=off elevator=deadlin
B、 使用xfs文件系统,不要使用ext3;而ext4在业务量不是很大的情况下可以勉强使用,
C、 文件系统mount参数中增加:noatime、nodiratime,nobarrier等选项(nobarrier为xfs持有)例:mount –o noatime –o nodiratime –o remount /data也可以直接修改fstab
D、 扩大文件描述符
Shell>ulimit –n 65535
Shell>vim /etc/security/limits.conf
* hard nofile 65535
* soft nofile 65535
2.2、内核参数优化
针对关键内核参数设置合适的值,目的是为了减少swap的使用,并且让内存和磁盘IO不会出现大幅波动,导致瞬间波峰负载:
A、 将vm.swappiness设置为5-10内即可,甚至设置为0(rhle7以上系统版本则慎重设置为0,除非允许OOM-kill发生),降低使用swap的机会。
B、 将vm.dirt_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间IO写,产生严重等待(和innodb_max_dirty_pages_pct类似)。
C、 将net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1,减少time_wait提高tcp的效率(在微软云分发中不要设置此两项)
D、 Read_ahead_kb、nr_requests可斟酌设置
3、 mysql server层相关优化
3.1、版本选择
官方版本称为oracle mysql、percona及mariadb,强烈建议选择percona分支版本,它是一个相对比较成熟的、优秀的mysql分支,在性能提升机可靠性、管理性方面做了不少改善,与官方版本基本兼容,并且对比官方大约有20%的性能提升。而mariadb在10版本以后就不在和官方mysql相兼容了。
3.2、重要参数调整
建议调整以下参数以获得较好的性能
A、 percona、mariadb版本建议启动thread pool特性,可以在高并发情况下,性能不会发生大幅下降。此外还有extra_port功能(如果有分析性的长sql建议不要开枪线程池)。
B、 设置default-storage-engine=innodb 可以满足95%以上的业务场景
C、 调整innodb_buffer_pool_size大小,设置为物理内存的50%-75%
D、 根据需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。数据要求不能有丢失,则设置这两个参数都为1.如果允许丢失数据则可以设置为2、10.如果在从上则可以设置为0和0
E、 设置innodb_file_per_table=1,使用独立表空间。需要注意一点的是在使用独立表空间,请千万做好备份。
F、 设置innodb_data_file_path=ibdata1:1G:autoextend;不要使用默认的10M否则会在高并发事务时,会有很大影响(undolog 等)
G、 设置innodb_log_file_size=256M,并设置innodb_log_files_in_group=2基本满足90%以上的业务
H、 设置long_query_time=1,而咋5.5版本以上,则可以设置小于1,。可以根据自己的需要设置,记录执行较慢的sql用于分析排查。
I、 根据业务需要设置max_connections及max_connection_error(建议设置10w以上)。而open_file_limit、innodb_open_file、table_open_cache、table_definition_cache需要设置为max_connection*10倍的大小
J、 Tmp_table_size、max_heap_table_size此两个参数不要设置很大,此两个参数是属于会话参数,因此不要设置过大。否则会造成OOM的发生。例如以下也是会话参数:sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等。如果在你的应用中有很多group by distinct等很多并且你的内存也很多是可以设置这两个参数。(默认即可)
K、 强烈建议关闭query cache功能并且设置key_buffer_size为32M即可(不使用myisam)
3.3、schema涉及规范及sql建议
以下列举了常见的有助于提升mysql效率的schema设置规范和sql使用建议:
A、 所有innodb表都设置一个无用的自增列作为主键
B、 字段长度尽量选择长度小的。并且字段属性尽量不能为null
C、 将text/blob类型拆分到子表中
D、 查询时只读取需要的列
E、 对varchar列做索引时尽量只取50%(甚至更小)
F、 建议将子查询改为join
G、 多表关联查询,关联字段尽量一致并且要有索引
H、 多表查询,把结果集小的作为驱动表
I、 多表关联并且有排序时,排序字段必须是驱动表里的,否则无法使用到索引
J、 多用复合索引
K、 分页功能,建议先用主键关联,然后返回结果集
3.4、管理维护建议
关于mysql的管理维护建议:
A、 单表数据尽量不超过10G、行数不超过1亿条、行长度不超过8KB(此建议主要是考虑到online ddl代价)
B、 只要不发生OOM-kill,mysql占用内存太多也没有关系
C、 最好单机运行单实例
D、 定期使用pt-duplicate-key-checker检查删除重复的索引,使用pt-index-usage工具检查并删除使用频率很低的索引
E、 定期采集slow log文件,使用pt-query-digest工具,结合anemometer系统运行slow query管理以便分析slow query并进行后续优化
F、 使用pt-kill杀掉超长时间的sql请求
G、 使用pt-online-schema-change来完成大表的online ddl需求
H、 使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异
注:依据自己的环境进行优化