博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
mysql服务器优化
阅读量:5020 次
发布时间:2019-06-12

本文共 3595 字,大约阅读时间需要 11 分钟。

摘自:叶金荣 老叶茶馆

 

在对一下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

你可能感兴趣的文章
hdu 2160 Sequence one(DFS)
查看>>
ATM实验感受
查看>>
csharp基础
查看>>
hdu4497 正整数唯一分解定理应用
查看>>
html5 拖曳功能的实现[转]
查看>>
[BZOJ 2049] [Sdoi2008] Cave 洞穴勘测 【LCT】
查看>>
java导出word[xml方式]
查看>>
mysql load_file()和 into outfile
查看>>
响应式布局编码
查看>>
微服务实战(三):深入微服务架构的进程间通信 - DockOne.io
查看>>
Android菜鸟的成长笔记(6)——剖析源码学自定义主题Theme
查看>>
Java:类与继承
查看>>
struts2-(2)HelloWorld
查看>>
python常用函数 库 转
查看>>
第一次爱你得是啥时候
查看>>
Redis4- llist的操作
查看>>
AJAX 实战【三级联动】分析
查看>>
日志收集思考
查看>>
asp.net 发布后,遇到的导出excel报错的问题
查看>>
(转)Spring boot——logback.xml 配置详解(四)<filter>
查看>>