mysql服务器优化

摘自：叶金荣 老叶茶馆

 

在对一下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主从复制的数据差异

 

注：依据自己的环境进行优化

转载于:https://www.cnblogs.com/fuyuntao/p/8183966.html