MySQL优化（十二）

2017年11月28日16:09:09 发表评论 552 views

第1章 优化哲学

1.1 优化问题

  • 优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统。
  • 优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
  • 任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
  • 对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。
  • 保持现状或出现更差的情况都是失败！
  • 稳定性和业务可持续性通常比性能更重要！（OLTP）
  • 优化不可避免涉及到变更，变更就有风险！
  • 优化使性能变好，维持和变差是等概率事件！
  • 优化不能只是数据库管理员担当风险，但会所有的人分享优化成果！
  • 所以优化工作是由业务需要驱使的！

1.2 参与优化的人员

  • 数据库管理员
  • 业务部门代表
  • 应用程序架构师
  • 应用程序设计人员
  • 应用程序开发人员
  • 硬件及系统管理员
  • 存储管理员

1.3 优化方向

1.3.1 优化角度

  • 稳定性优化（业务持续性）
  • 性能优化（业务高效性）

1.3.2 优化顺序（范围）

  • 存储、主机和操作系统：
  1. 主机架构稳定性
  2. I/O规划及配置
  3. Swap
  4. OS内核参数和网络问题
  • 应用程序：
  1. 应用程序稳定性
  2. SQL语句性能
  3. 串行访问资源
  4. 性能欠佳会话管理
  5. 应用是否适合使用MySQL
  • 数据库优化：
  1. 内存
  2. 数据库结构(物理&逻辑)
  3. 实例配置

1.4 数据库优化思路

MySQL优化（十二）

1.4.1 数据库优化维度

硬件(主机、存储、网络设备)—>操作系统—>应用层—>数据库实例—>逻辑结构—>SQL

1.4.2 优化成本

硬件>系统配置>数据库表结构>SQL及索引

1.4.3 优化效果

硬件<系统配置<数据库表结构<SQL及索引

第2章 优化工具

2.1 硬件设备

  • 通过状态灯判断设备状态
  • 自带管理设备：远程控制卡（FENCE设备：ipmi/ilo/idarc，可实现开关机、硬件监控等功能）。
  • 第三方的监控软件、设备（snmp、agent）
  • 存储设备：自带的监控平台。
  1. EMC2（已经被HP收购）
  2. 日立（hds）
  3. IBM低端OEM hds（日立的），高端存储是自己技术
  4. 华为存储

2.2 操作系统

2.2.1 top（查看CPU状态）

[root@db01 ~]# top
Tasks: 120 total,   1 running, 119 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.8%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    485984k total,   434100k used,    51884k free,     1876k buffers
Swap:   786428k total,   134744k used,   651684k free,    44796k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                               
     1 root      20   0 19348  404  208 S  0.0  0.1   0:01.60 init                          
     2 root      20   0     0    0    0 S  0.0  0.0   0:00.01 kthreadd                      
     3 root      RT   0     0    0    0 S  0.0  0.0   0:00.34 migration/0                   
     4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0

2.2.2 iostat

[root@db01 ~]# iostat
Linux 2.6.32-696.el6.x86_64 (db01) 11/28/2017 _x86_64_    (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.04    0.16    0.00   99.80

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.80        43.92        20.30    1287046     594740

  • 命令解析：
  1. tps：该设备每秒的传输次数：“一次传输”意思是“一次I/O请求”，多个逻辑请求可能会被合并为“一次I/O请求”。
  2. iops ：硬件出厂的时候厂家定义的一个每秒最大的IO次数，“一次传输”请求的大小是未知的。
  3. Blk_read/s：每秒从设备（drive expressed）读取的数据量；
  4. Blk_wrtn/s：每秒向设备（drive expressed）写入的数据量；
  5. Blk_read：读取的总数据量；
  6. Blk_wrtn：写入的总数量数据量；这些单位都为Kilobytes。
  7. iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））

2.2.3 vmstat

[root@db01 ~]# vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 134744  52520   1612  44436    0    2     6     3    9   14  0  0 100  0  0  
 0  0 134744  52472   1612  44436    0    0     0     0   55   65  0  0 100  0  0  
 0  0 134744  52472   1612  44436    0    0     0     0   35   58  0  0 100  0  0  
 0  0 134744  52472   1612  44436    0    0     0     0   31   56  0  0 100  0  0  
 0  0 134744  52472   1612  44436    0    0     0     0   35   58  0  0 100  0  0

  • 命令解析：
  1. Procs：r显示有多少进程正在等待CPU时间；b显示处于不可中断的休眠的进程数量在等待I/O
  2. Memory：swpd显示被交换到磁盘的数据块的数量；free未被使用的数据块；buff用户缓冲数据块；cache用于操作系统的数据块的数量
  3. Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量，s1和s0最好是0
  4. Io：每秒从设备中读入b1的写入到设备b0的数据块的数量，反映了磁盘I/O
  5. System：显示了每秒发生中断的数量(in)和上下文交换(cs)的数量
  6. Cpu：us显示用于运行用户代码；sy系统代码；id空闲；wa和st等待I/O的CPU时间

2.2.4 sar

[root@db01 ~]# sar
Linux 2.6.32-696.el6.x86_64 (db01) 11/28/2017 _x86_64_    (4 CPU)

08:50:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:00:01 AM     all      0.00      0.00      0.03      0.00      0.00     99.97
09:10:03 AM     all      0.00      0.00      0.06      0.75      0.00     99.19
09:20:01 AM     all      0.00      0.00      0.02      0.08      0.00     99.90
09:30:01 AM     all      0.00      0.00      0.02      0.06      0.00     99.92
09:40:01 AM     all      0.00      0.00      0.02      0.04      0.00     99.94
09:50:01 AM     all      0.00      0.00      0.02      0.01      0.00     99.97
10:00:01 AM     all      0.00      0.00      0.02      0.00      0.00     99.97
10:10:01 AM     all      0.00      0.00      0.02      0.00      0.00     99.97
10:20:01 AM     all      0.00      0.00      0.02      0.00      0.00     99.97
10:30:02 AM     all      0.04      0.00      0.11      1.29      0.00     98.56
Average:        all      0.00      0.00      0.04      0.22      0.00     99.74

2.2.5 dstat

[root@db01 ~]# yum install -y dstat
[root@db01 ~]# dstat
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
 usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
   0   0 100   0   0   0|  29k   12k|   0     0 | 489B   11k|  36    57 
   0   0  99   0   0   0|   0     0 |  60B  854B|   0     0 |  36    61 
   0   0 100   0   0   0|   0     0 |  60B  358B|   0     0 |  32    56 
   0   0 100   0   0   0|   0     0 |  60B  358B|   0     0 |  33    59 
   0   0 100   0   0   0|   0     0 |  60B  358B|   0     0 |  32    60 
   0   0 100   0   0   0|   0    64k|  60B  358B|   0     0 |  44    69 
   0   0 100   0   0   0|   0     0 |  60B  358B|   0     0 |  29    52 
   0   0 100   0   0   0|   0     0 |  60B  358B|   0     0 |  38    65

2.2.6 nmon

[root@db01 ~]# yum install -y nmon
[root@db01 ~]# export TERM=xterm       # 修改环境变量，防止出现乱码，原export TERM=linux
[root@db01 ~]# nmon

MySQL优化（十二）

提示：其他可参考：http://blog.csdn.net/a7442358/article/details/50186283

2.2.7 问题分析

【问题现象】：cpu很繁忙但IO很空闲

【原因分析】：

  • IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）
  • tps过高: 大量的小数据IO、大量的全表扫描

2.3 数据库

2.3.1 基础优化命令工具

mysql
SHOW [SESSION | GLOBAL] STATUS 
SHOW ENGINE INNODB STATUS 
SHOW PROCESSLIST
show index
Information Schema mysqldumpslow
explain
msyqladmin
mysqlshow

2.3.2 深度优化命令工具

mysqlslap sysbench
mysql profiling Performance Schema

第3章 企业基础优化实战

3.1 优化思路

  • 定位问题：硬件、系统、应用、数据库、架构（高可用、读写分离、分库分表）
  • 处理问题：明确优化目标、性能和安全的折中、防患未然
  • 最高境界：防患于未然，避免安全问题出现，避免性能问题出现，尽量做到主动优化，避免被动优化。

3.2 硬件优化

  • 主机：
  1. 根据数据库类型，主机CPU选择、内存容量选择、磁盘选择
  2. 平衡内存和磁盘资源
  3. 随机的I/O和顺序的I/O
  4. 主机 RAID卡的BBU(Battery Backup Unit)关闭
  • 存储：
  1. 根据存储数据种类的不同，选择不同的存储设备
  2. 配置合理的RAID级别(raid5、raid10、热备盘)
  • 网络设备：
  1. 使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
注意：这些规划应该在初始设计系统时就应该考虑好

3.2.1 CPU的选择

  • 选择cpu的两个关键因素：
  1. 核数
  2. 主频
  • 根据业务类型选择：
  1. cpu密集型：计算比较多（OLTP），选择主频很高的cpu、核数还要多
  2. IO密集型：查询比较（OLAP ），核数要多

3.2.2 内存的选择

  • OLAP类型数据库需要更多内存，和数据获取量级有。
  • OLTP类型数据一般内存是cpu核心数量的2倍到4倍即可

3.2.3 IO设备选择

  • 硬盘：对于操作系统来讲不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）
  • raid卡：主机raid卡，实现操作系统磁盘的冗余（raid1）并且主机 RAID卡的BBU(Battery Backup Unit)建议关闭
  • 平衡内存和磁盘资源：随机的I/O和顺序的I/O

3.2.4 存储的选择

  • 根据存储数据种类的不同，选择不同的存储设备
  • 配置合理的RAID级别(raid5、raid10、热备盘)

3.2.5 网络设备的选择

  • 使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）

3.3 系统优化

  • Cpu：基本不需要调整，在硬件选择方面下功夫即可。
  • 内存：基本不需要调整，在硬件选择方面下功夫即可。
  • SWAP：MySQL尽量避免使用swap。
  • /proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl.conf上添加swappiness=0（永久）
  • 这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
  • 这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
  • IO：raid、不要使用lvm、ext4或xfs、ssd、IO调度策略

[root@db01 ~]# echo deadline>/sys/block/sda/queue/scheduler    # 临时修改为deadline
[root@db01 ~]# vim /boot/grub/grub.conf                        # 永久修改为deadline
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

3.4 操作系统优化

  • Linux系统内核参数优化：

[root@db01 ~]# vim /etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535  # 用户访问的端口范围
net.ipv4.tcp_max_syn_backlog = 4096        # 可以不设置，只能微调
net.ipv4.tcp_fin_timeout = 30              # 可以不设置，只能微调
fs.file-max=65535                          # 系统最大文件句柄，控制的是能打开文件最大数量

  • 用户限制参数（mysql可以不设置以下配置）：

[root@db01 ~]# vim /etc/security/limits.conf
soft nproc 65535
hard nproc 65535
soft nofile 65535
hard nofile 65535

3.5 应用优化

  • 业务应用和数据库应用独立（例如LNMP中MySQL单独分离）
  • 关闭防火墙和selinux
  • 思考业务是否真的需要MySQL，还是使用其他种类的数据库
  • 不需要使用图形界面（或只使用runlevel 3）
  • 其他(关闭无用服务)：

chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
chkconfig --level 23456 avahi-daemon off
chkconfig --level 23456 bluetooth off
chkconfig --level 23456 cups off
chkconfig --level 23456 firstboot off
chkconfig --level 23456 haldaemon off
chkconfig --level 23456 hplip off
chkconfig --level 23456 ip6tables off
chkconfig --level 23456 iptables off
chkconfig --level 23456 isdn off
chkconfig --level 23456 pcscd off
chkconfig --level 23456 sendmail off
chkconfig --level 23456 yum-updatesd off

3.6 数据库优化

3.6.1 参数调整

  • 实例整体（优化后可能会产生其他问题，所以优化需谨慎）：

thread_concurrency     # 并发线程数量个数
sort_buffer_size       # 排序缓存
read_buffer_size       # 顺序读取缓存
read_rnd_buffer_size   #　随机读取缓存
key_buffer_size        # 索引缓存
thread_cache_size (1G —> 8, 2G —> 16, 3G —> 32, >3G —> 64)

  • 连接层（基础优化）：

设置合理的连接客户和连接方式：

max_connections        # 最大连接数，2000-3000即可
max_connect_errors     # 最大错误连接数
connect_timeout        # 连接超时
max_user_connections   # 最大用户连接数，2000-3000即可
skip-name-resolve      # 跳过域名解析
wait_timeout           # 等待超时
back_log               # 可以在堆栈中的连接数量

  • SQL层（基础优化）：

query_cache_size       # 查询缓存

参数说明：

  • OLAP类型数据库,需要重点加大此内存缓存，但是一般不会超过GB
  • 对于经常被修改的数据，缓存会立马失效
  • 可以实用内存数据库（redis、memecache）替代他的功能
  • 存储引擎层（innodb基础优化参数）：

default-storage-engine
innodb_buffer_pool_size                    # 很重要，没有固定大小。可以先给50%测试值
                                           # 看情况进行微调，但是尽量设置不要超过物理内存的70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2)     # 1是最安全的，0是性能最高，2是折中方案，推荐使用1
innodb_log_buffer_size                     # 推荐100M以下
innodb_log_file_size                       # 推荐100M以下
innodb_log_files_in_group                  # 推荐5个成员以下
innodb_max_dirty_pages_pct                 # 会影响重启速度，越低启动速度越快，默认75%
log_bin
max_binlog_cache_size                      # 尽可能的小，可以不设置
max_binlog_size                            # 可以不设置
innodb_additional_mem_pool_size            # 2G内存的机器推荐值是20M，32G内存的100M
transaction_isolation                      # 默认值即该选项
Innodb_flush_method=(O_DIRECT, fdatasync)  # O_DIRECT情况下InnoDB的buffer pool会直接绕过文件系统# cache来访问磁盘，但是redo log依旧会使用文件系统
                                           # cache，值得注意的是Redo log是覆写模式的，即使使用了
                                           # 文件系统的cache，也不会占用太多

3.6.2 SQL优化

  • 执行计划（基础优化）
  • 索引（基础优化）
  • SQL改写（高级优化）

3.6.3 架构优化

  • 高可用架构
  • 高性能架构
  • 分库分表
weinxin
我的微信
如果有技术上的问题可以扫一扫我的微信
版权声明
1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。
liyang