mysql服务优化参考

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql服务优化参考

技术小阿哥 2017-11-27 14:00:00 浏览970
展开阅读全文

http://www.cnblogs.com/xhyan/p/6530920.html

Mysql服务加速优化的6个阶段

  • 硬件层面优化

  • 操作系统层面优化

  • Mysql数据库层面优化

  • 网站集群架构层面优化

  • 安全优化

  • 流程、制度控制优化

1.硬件层面优化

  1. CPU          64CPU,至少2-4颗cpu,L2越大越好

  2. MEMORY        96-128G跑3-4个实例;32-64,跑1-2个实例

  3. DISK        SAS机械盘,数量越多越好

SSD(高并发)>sas(普通业务)>sata(线下)

  1. RAID       RAID0> RAID10(推荐)> RAID5(少用)

主库Raid10.从库Raid5或Raid10

  1. NETWORK      多网卡bond,buffer,tcp优化

千兆网线及千兆万兆交换机

  1. 数据库服务器数据IO密集型服务,尽量不要使用虚拟化

2.操作系统层面优化

操作系统及Mysql实例选择

  1. 一定选择x86_64位系统,推荐使用CentOS6.8,关闭NUMA特性

  2. 将操作系统喝数据分区分开

  3. 避免使用swap交换分区

  4. 避免使用软件磁盘阵列

  5. 避免使用LVM逻辑卷

  6. 删除服务器上未使用的安装包和守护进程

3.文件系统层优化

  1. 调整磁盘Cache mode

  1. 启用WCE=1(write cache Enable)RCD=0(Read cache Disable)模式 命令:sdparm -s WCE=1,RCD=0 -S /dev/sdb

  1. 采用Linux I/O scheuler算法deadline

  1. 采用deadline I/O调度起

  2. deadline调度参数,对于Centos 建议:

read_expire = 1/2 write_expire,

       echo 500>/sys/block/sdb/queue/iosched/read_expire

       echo 1000>/sys/block/sdb/queue/iosched/write_expire

  1. 业务量很大建议采用xfs文件系统,业务量不是很大可采用ext4

  2. mount挂载文件系统增加:async、noatime、nodiratime、nobarrier(不使用raid卡电池)等选项

4.Linux内核参数优化

  1. 将vm.swappiness设置为0.10

  2. 将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写入,产生严重等待

  3. 优化tcp协议栈,

  • 减少TIME_WAIT,提高tcp效率

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

  • 减少处于FIN0Await-2连接状态等时间,是系统可以处理更多的连接

net.ipv4.tcp_fin_timeout=2

  • 减少TCP KeepAlive连接侦测时间,是系统可以处理更多的连接

net.ipv4_tcp_keepalive_time=600

  • 提高系统支持的最大SYN半连接数(默认1024)

net.ipv4.tcp_max_syn_backlog = 16384

  • 减少系统SYN连接重试次数(默认5)

net.ipv4.tcp.synack_retries=1

5.系统网络优化

  • 优化系统套接字缓冲区

net.core.rmem_max=16777216              #最大socket读buffer

net.core.wmem_max=16777216            #最大socket写buffer

net.core.wmem.default = 8388608

net.core.rmem.default = 8388608

  • 优化TCP接受/发送缓冲区

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 87380 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

  • 优化网络设备接收队列

net.core.netdev_max_backlog=3000

net.core.somaxconn = 32768

  • 其他优化

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

6.mysql数据库层面优化(my.cnf)

  1. 如果使用MyISAM引擎,需要key_buffer_size调大

  2. 建议设置default-storage-engine=InnoDB,强烈建议不要再使用MyISAM引擎。

  3. 调整innodb_buffer_pool_size的大小,如果是单实例且绝大多数是InnoDB引擎表的话,可考虑设置为物理内存的50% -70%左右。

  4. 设置innodb_file_per_table = 1,使用独立表空间。

  5. 调整innodb_data_file_path = ibdata1:1G:autoextend,不要用默认的10M,在高并发场景下,性能会有很大提升。

  6. 设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可以满足大多数应用场景。

  7. 调整max_connection(最大连接数)、max_connection_error(最大错误数)设置,根据业务量大小进行设置。

  8. 另外,open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以设置大约为max_connection的10倍左右大小。

  9. key_buffer_size建议调小,32M左右即可

  10. 建议关闭query cache功能或降低设置不要超过512M(前端使用redis或memcached)

  11. mp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等设置也不要过大。

7.Mysql语句优化

Mysql语句优化的多种思路

  • 白名单机制-百度,项目开发,DAB参与,减少上线后的慢sql数量

  • 抓出慢sql:配置my.cnf

long_query_time = 2

log-slow-queries=/data/3306/slow-log.log

log_queries_not_using_indexes

按天轮询slow-log.log日志

慢查询日志分析工具——mysqlsla或pt-query-digest(推荐)

  • 定时分析慢查询,发到核心开封,DBA分析及高级韵味,CTO邮箱

  • 定期使用pt-duplicate-key-checker检查并删除重复的索引

  • 定期使用pt-index-usage工具检查并删除使用频率低低索引

  • 使用pt-online-schema-change来完成达标的online ddl需求

  • 搜索功能:like %baidu%,一般不要用Mysql数据主库

  • 避免在整表上使用count(*),他有可能导致锁表

  • 使用连接join来代替子查询

  • 多表连接查询时,把结果集笑的表作为驱动表

  • 多表连接并且有排序时,排序字段必须时驱动表例的,否则排序列无法用到索引

  • 使用explain及set profile优化sql语句

8.使用explain 优化sql语句

  • 慢查询sql语句方法(紧急处理)

使用show full processlist;(登录数据库后现场抓,连续执行2次,超过2秒)

mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show full processlist;"|grep -vi "sleep"

  • 记录并分析慢查询日志(日常处理)

配置my.cnf参数中记录慢查询语句

long_query_time = 2                                                       #查询语句超过2秒记录到日志

log_quries_not_using_indexes                                         #没有使用索引的查询记录到日志

log-slow-quries = /data/3306/slow.log                               #记录日志存放的路径

定时切割日志后使用mysqlsla分析

mv /data/3306/slow.log /opt/$(date +%F)_slow.log

mysqladmin -uroot -poldboy -S data/3306/mysql.sock flush-logs


  • explain语句检查索引执行情况

explain select * from test where name='oldboy'\G

通过对慢查询语句where后面的字段建立索引的条件建立索引(单索引及联合索引)

9.日常优化

由开发,DBA,总监等优化

使用profile优化sql语句

查看是否启用profile,如果值为0,表示没有启用

SELECT @@profiling;

+-------------+

| @@profiling |

+-------------+

|           0 |

+-------------+

1 row in set (0.00 sec)

打开profile功能

SET profiling = 1;

打开profile功能后执行一条查询语句

select count(user) from user;

+-------------+

| count(user) |

+-------------+

|           2 |

+-------------+

1 row in set (0.00 sec)

使用show profiles查看(可以看到每个执行细节所消耗的时间)

show profiles;

+----------+------------+------------------------------+

| Query_ID | Duration   | Query                        |

+----------+------------+------------------------------+

|       1 | 0.00007400 | select @@profiling            |

|       2 | 0.00016250 | select count(user) from user |

+----------+------------+------------------------------+

2 rows in set (0.00 sec)

查看sql占用cpu、io、内存等情况

show profile cpu,block io,memory,swaps for query 4;

+--------------------+----------+----------+------------+--------------+---------------+-------+

| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |

+--------------------+----------+----------+------------+--------------+---------------+-------+

| starting           | 0.000057 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| Opening tables     | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| System lock        | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| init               | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| optimizing         | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| statistics         | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| preparing          | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| executing          | 0.000159 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| Sending data       | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| end                | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| query end          | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| closing tables     | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| removing tmp table | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| closing tables     | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| freeing items      | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| logging slow query | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |     0 |

| cleaning up        | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |

+--------------------+----------+----------+------------+--------------+---------------+-------+

17 rows in set (0.00 sec)

 

10.网站集群架构优化

  1. 服务器上跑多实例,2-4个

  2. 主从复制一主五从,采用mixed模式,尽量不要夸机房同步(尽量远程写本地读)

  3. 定期pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异

  4. 业务拆分:搜索功能一般不要用Mysql数据库,某些业务应用应使用nosql持久化存储,如memcached、redis、ttserver等

  5. 数据库前端必须加cache

  6. 动态的数据静态化(整个文件静态化,而非文件中的片段静态化)

  7. 数据库集群与读写分离。

  8. 选择从库进行备份

  9. 对数据库进行分库分表备份


本文转自 liqius 51CTO博客,原文链接:http://blog.51cto.com/szgb17/1909636,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论