你的位置:首页 > 数据库

[数据库]MySQL优化聊两句


原文地址:http://www.cnblogs.com/verrion/p/mysql_optimised.html

MySQL优化聊两句

 

MySQL不多介绍,今天聊两句该如何优化以及从哪些方面入手,很多运维从业者一说起优化就不知所措,当运营过程中某个参数值到达一定阀值之后,就会出现各种问题,很多运维工程师这时不知所措,第一可能也从来没有处理过类似情况,另一方面业务又紧张,系统不正常,首要任务是解决问题,那没办法只能重启了,我们先不说重启是否可行,比如有些应用可以重启并且解决了问题,但如没有解决问题,或者爆发出新的问题怎么办,比如血崩情况。所以我们应该从问题本身出发,第一查看日志,然后再具体问题具体分析等。

 

上面说了故障处理的一方面解决办法,同样适合于优化处理之道,根据本人学习和经验,这里说一下我的优化思路:首先要有这点认同感,任何系统应用都一定能达到瓶颈的时候,那我们为什么不事先去想想瓶颈在哪里,而不是当问题出现了,才去临时抱佛脚。尤其对于运维来说,任何事情都要具有可把控性,预先想好大部分情况的预案和解决措施。

 

为什么要优化:

系统在现有的环境中达到处理能力的最大限制,可能多一个请求或者连接都能对业务造成影响

 

如何优化:

问题关键在于:硬件资源(CPU、内存、磁盘、网络)等已经负荷啦。这样我们不很明了了,找出是哪个部分使硬件资源超负荷了,然后采取相应的措辞去调整,优化是个过程,直至系统已经优化足够好了,还是没有解决问题,只有横向扩展加硬件资源啦。

 

根据上面所说的,我总结一下,mysql(其他的也一样)优化应该从以下几点出发,从体系架构层面一层一层的往上讲解:

1、底层硬件层:最基础的也是最重要的,下面几层的优化最终是解决这层瓶颈;

2、操作系统层:搭载怎样的操作系统、文件系统、网络参数等选型和调优;

3、集群架构层:业务发展架构也要随之发展;

4、数据应用层:mysql安装、配置文件参数设置等;

5、SQL调优层:SQL语句优化,好的sql语句成功案例能提高70%的性能;

6、行为模式层:安全、流程、制度等优化;

 

下面一一说道,由于本人水平有限,有错误之处,请批评指正

 

一:底层硬件层

1、硬件资源列表

硬件资源

采购或购买云服务器配置参考

CPU

64位,2-16颗粒,L2越大越好

内存

96/128G跑3/4个实例;32/64G跑1-2个实例为佳

硬盘

机械硬盘(SAS),数量越多越好,转速越高越好

单盘能力对比:SAS(300IOPS)>SSD(35000IOPS)

性能:SSD>SAS>SATA

磁盘阵列

性能:RAID0>RAID10(推荐)>RAID5(少用)>RAID1

注意:主库选择raid10,从库可选raid0/raid5/raid10,从库配置等于或大于主库 

网卡

至少千兆网卡及千兆万兆交换机 
多块网卡bond设置

注意:数据库属于IO密集型服务,硬件尽量避免使用虚拟化。 
      Slave硬件资源要等于或大于Master的

 

2、硬件配置列表(云服务跳过这一步)

设置选项

具体操作

BIOS系统

CPU优化设置(以DELL系列服务器为例):

1.打开Perfirmance Per Watt Optimeized(DAPC)模式,提高CPU运算能力

2.打开CIE和C States等选项,减少cpu调度算法时间,提升效率

内存优化设置

1. Memory Frequency(内存频率)选择Maximum Performance(最佳性能)

2.内存设置菜单中,启动Node Interleaving,避免NUMA问题

磁盘阵列卡

1.阵列卡要具有CACHE及BBU模块

2.设置写策略为write back(感兴趣可以了解Cache两种写策略:write-through与write-back ),并且关闭阵列预读策略

 

二:操作系统层

1、文件系统相关

优化层级

具体操作

操作系统

无疑选择x86_64架构,(RedHat>CentOS)基于6.8稳定版

数据规划

基于物理块层面分别存放操作系统文件、mysql应用和数据文件

交换分区

不要使用swap空间,酌情使用共享内存/dev/shm

软件磁盘阵列

不要使用

LVM逻辑卷

不要使用

注意:下面几点设置目的是为了提升I/O性能

I/O调度算法

使用deadline调度参数,建议 read_expire = 1/2 write_expire

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

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

xfs文件系统

业务量不是很大可采用ext4,业务量很大推荐使用xfs:并且调整XFS文件系统日志和缓冲变量

数据目录挂载

挂载设置以下参数:

sync:往硬盘写数据时先写入内存缓冲区,待硬盘空闲时再同步下来,大大提升效率,缺点就是如果服务器宕机或不正常,会损失缓冲区中未写入磁盘的数据,解决办法:利用主板电池或UPS不间断电源供电;

noatime:access文件时不更新inode的时间戳,高并发环境下,可以提高系统I/O性能,对select操作尤为重要;

nodiratime:不更新系统上的directory inode时间戳,可以提高系统I/O性能

nobarrier:不建议使用raid卡电池

 

2、网络参数相关

内核参数

具体操作

swappiness

1.cat /proc/sys/vm/swappiness 默认为60

2.echo “vm.swappiness=10” >> /etc/sysctl.conf(一般设置0-10)

3.sysctl -p

ratio

vm.dirty_background_ratio设置为5-10

vm.dirty_ratio设置为它的两倍左右,

目的:确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待 ,方法如上

TCP相关参数

注意:熟悉TCP11种状态转换原理机制

1、减少TIME_WAIT(基本所有应用都要配置)

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

2、减少FIN-WAIT-2状态时间

net.ipv4.tcp_fin_timeout=10

3、减少TCP KeepAlived连接侦测的时间

net.ipv4.tcp_keepalived_time=600

4、提高系统最大SYN半连接数(默认1024)排队长度

net.ipv4.tcp_max_syn_backlog = 16384

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

net.ipv4.tcp_synack_retries = 1

6、抛弃在内核里建立的连接之前发送SYN包的数量

net.ipv4.tcp_sync_retries = 1

7、允许系统打开的端口范围

net.ipv4.ip_local_prot_range = 4500 65535

网络相关参数

注意:熟悉网络相关知识

1、调整socket套接字缓冲区

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

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

net.core.wmem_default = 8388608

net.core.rmem_default = 8388608

2、调整TCP接收/发送缓冲区

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

3、调整网络设备接收队列

net.core.netdev_max_backlog=3000

其他优化

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

 

三:集群架构层

架构设计

注意事项

实例

根据服务器硬件资源和业务需求,一般跑2-4个

方案

Mysql架构多种多样,扩展性极强,这里不详细介绍

如:主从复制一主多从架构,采用mixed模式

业务量数据量大的优化架构:

1、读写分离:通过程序或者dbproxy,主写从读;

2、垂直分库,水平分表(一般建议单表不超过4000万)

数据扫描

周期性使用pt-table-checksum、pt-table-sync来检查并修复主从复制的数据差异

缓存机制

DB层前端添加cache层,比如memcached/redis,用来存储session、token、好友任务排行榜等某些业务

静态化

动态的数据库静态化:比如整个文件静态化,页面字段静态化

规避选项

比如:SQL有大量模糊查询业务,尽量避免使用mysql数据库

注意:数据库架构是一门很大的学问,水平有限只是简单聊两句

 

四:数据应用层

注意:下面参数值的优化主要针对innodb引擎,如果要使用MyISAM引擎,需要调整key_buffer_size值

参数项

设置参考

innodb_buffer_pool_size

物理内存的50-70%左右

inno_flush_log_at_trx_commit

不丢失数据设置为1,根据业务需求来

sync_binlog

不丢失数据设置为1,根据业务需求来

innodb_file_per_table

设置为1:使用独立表空间

innodb_data_file_path

ibdata1:1G:autoextend

innodb_log_file_size

256M,这个参数和下面那个参数搭配使用

innodb_log_files_in_group

2

innodb_log_file_size

不要设置过大,第一保证记录日志更快,第二保障增量恢复数据库时间更短

long_query_time

设置慢查询sql的时间

max_connection

最大连接数,根据业务场景选择

max_connection_error

最大链接错误数,官方建议10万以上

open_files_limit

10倍max_connection

innodb_open_files

同上

table_open_cache

同上

table_definition_cache

同上

tmp_table_szie

Session分配情况,根据业务来设置

max_heap_table_size

同上

sort_buffer_size

同上

join_buffer_size

同上

read_buffer_size

同上

read_rnd_buffer_size

同上

query cache

官方建议关闭,要设置不要超过512M

提示:更多内核参数有时间详细介绍,一切来源于官方文档,我只是千万运维的一枚搬运工而已。

 

五:SQL调优层

1、数据库表设计的一些想法,DBA必须参数开发

设计思路

具体操作

字符集

建议使用utf-8(中文影响),官方默认用Latin1,因后者快

字符串

1.固定字符串使用定长char,尽量避免varchar

2.变长字符串varchar,不要用char(因UTF8忽略此影响)

上面两者如果能预测业务存储长度,长度能短则短

约束

字段属性添加not、null,并且表结构设计无关的自增列做主键

特殊类型

1.文本字段(备注,博文内容等)设置为enum类型

2.text/blob类型尽量不用,对select查询性能影响极大

随机I/O

严禁使用select *语句,查询特定需要的列即可

索引

1.多用复合索引(除特殊建独立索引),尤其cardinality很小时候

  如:该列唯一值总数少于255个,就不要建立独立索引

2.对于varchar类型,通常取其50%或更少长度创建前缀索引就能满足90%左右查询需求,没必要整个长度创建反而性能降低

 

2、语句优化

设计思路

具体操作

前期准备

项目开发阶段,DBA必须参与设计SQL,并查找慢sql

配置my.cnf

long_query_time = 2

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

log_queries_not_using_indexs = true

工具使用

日志慢sql分析工具mysqlsla或pt-query-digest,还有很多工具

  1. 根据报告结果自己写脚本在某个固定时间抓取当天慢sql
  2. 计划任务某个固定时间点把上面结果发邮件至相关人事分析

索引检测工具

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

2.定期使用pt-index-usage检查并删除使用频率很低的索引 
3.使用pt-online-schema-change来实现大表的online ddl需求 

SQL设计

1.搜索业务,比如like查询,不建议使用MySQL

2.不要使用count(*),可能锁表

3.多表连接查询,关联字段使用索引而且尽量一致

4.多表连接查询,结果集小的表作为驱动表

5. where子句尽量使用union代替子查询 

SQL拆分

大的复杂的sql语句拆分为各个功能性小sql

比如:join连表查询,子查询,单表超过4000万条记录等

瓶颈问题

sql解决方法:

  1. show processlist:找出特定SQL占用的资源影响性能
  2. 使用explain或者set profile打印报表
  3. 具体问题具体分析,有时研发参与

 

六:行为模式层

标准规范

具体操作

启动程序

文件权限700,属主和用户组都为mysql

超级用户

MySQL超级用户root设置复杂的密码,比如:我生成环境干掉root用户,创建其他管理用户并且名字不是大众类型

登录行为

  1. 命令行不要暴露密码
  2. 备份脚本设置密码保护比如加密(转换成MD5值)

运维思想

运维有种约定:有种配置叫默认,导致很多运维人员不去关注该参数的作用和来源

  1. 删除默认存在的test库
  2. 删除无用的用户,只保留root@127.0.0.1和root@localhost

责任问题

禁止一个用户管理所有的DB,建立起一一服务关系

权限问题

  1. 禁止开发人员得到生成环境程序连接的密码并且限制连接
  2. 禁止开发程序过程中使用不带子句的DDL语句
  3. Phpmyadmin管理工具安全问题

环境问题

  1. 服务器禁止设置外网IP
  2. 定期清理MySQL操作记录文件(.mysql_history)
  3. 防SQL注入攻击:比如nginx+lua编写WAF控制等

上线行为

  1. 必须制定上线变更流程制度(开发—测试—运维)
  2. 测试流程:开发环境—测试环境—线上执行—验证