MySql教程

MySQL优化策略

本文主要是介绍MySQL优化策略，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录

  • 优化哲学
  • 优化工具
    • 操作系统层
    • 数据库实例层
  • 优化思路
    • 硬件优化
    • 系统优化
    • 应用优化
    • MySQL参数优化
      • MySQL参数优化细节
        • Max_connections √
        • back_log
        • wait_timeout & interactive_timeout
        • key_buffer_size √
        • query_cache_size
        • max_connect_errors
        • sort_buffer_size
        • max_allowed_packet √
        • join_buffer_size
        • thread_cache_size √
        • innodb_buffer_pool_size √
        • innodb_flush_log_at_trx_commit √
        • innodb_thread_concurrency
        • innodb_log_buffer_size √
        • innodb_log_file_size √
        • innodb_log_files_in_group √
        • read_buffer_size
        • read_rnd_buffer_size
        • bulk_insert_buffer_size
        • binary log √
        • 安全参数 √
      • 参数优化结果
  • 锁的监控
    • 模拟锁等待
    • 监控锁状态
    • 确定锁源
    • 死锁监控
    • 优化项目：锁的监控及处理
  • 优化主从

优化哲学

  • 为什么优化？
    从业务需求出发，事实证实优化的结果往往会与期待值相反。优化有风险，涉足需谨慎！

  • 优化的风险？

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

  • 谁参与优化？

    1. 数据库管理员
    2. 业务部门代表
    3. 应用程序架构师
    4. 应用程序设计人员
    5. 应用程序开发人员
    6. 硬件及系统管理员
    7. 存储管理员
      参与优化

  • 优化的方向？

    1. 安全优化 → 业务持续性
    2. 性能优化 → 业务高效性

  • 优化的范围及思路？
    解耦 & 切片 & 自愈

    1. 存储、主机和操作系统：
          主机架构稳定性
          I/O规划及配置
          Swap
          OS内核参数
          网络问题
    2. 应用程序：（Index，lock，session）
          应用程序稳定性和性能
          SQL语句性能
          串行访问资源
          性能欠佳会话管理
    3. 数据库优化：（内存、数据库设计、参数）
          内存
          数据库结构(物理&逻辑)
          实例配置
      优化效果和结果评估

优化工具

操作系统层

top - 13:37:57 up 3 min,  1 user,  load average: 0.52, 0.74, 0.33Tasks: 160 total,   2 running, 158 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.7 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 stKiB Mem :  2895196 total,  1848656 free,   615172 used,   431368 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.  2115012 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                   710 root      20   0  295376   5200   3960 S  0.3  0.2   0:00.32 vmtoolsd                                               
  8679 mysql     20   0 1123976 182680  11168 S  0.3  6.3   0:01.75 mysqld                                                   8725 root      20   0  162100   2268   1580 R  0.3  0.1   0:00.12 top                                                    
     1 root      20   0  125772   4196   2604 S  0.0  0.1   0:01.68 systemd                                                     2 root      20   0       0      0      0 S  0.0  0.0   0:00.00 kthreadd                                               
     3 root      20   0       0      0      0 S  0.0  0.0   0:00.00 kworker/0:0     
...

  • CPU

    1. %Cpu(s)：可能由有个CPU，1个CPU最高占比100%，2个CPU最高占比200%
    2. us：用户程序占用CPU的时间百分比（该占比越高越好，尽量控制在90%）
    3. sy：系统程序占用CPU的时间百分比（sys高的原因：中病毒，锁的问题）
    4. ni：改变过优先级的用户程序占用CPU的时间百分比
    5. id：CPU空闲百分比
    6. wa：等待IO的CPU时间百分比，超过百分之十说明有问题（wa高的原因：锁、IO[raid，过度条带化]、索引）
    7. hi：硬中断占用CPU的时间百分比
    8. si：软中断占用CPU的时间百分比

    多CPU监控：主要判断CPU多核心有没有被充分利用。例如单颗CPU很忙，其他CPU很闲，对于MySQL来讲，有可能是并发参数设定不合理导致的。

  • MEM

    1. total：总内存大小
    2. free：空闲内存大小
    3. used：已使用的内存大小
    4. buff/cache：缓冲和缓存（写buffer，读cache）

  • SWAP

    1. total：总交换分区大小
    2. free：空闲交换分区大小
    3. used：已使用的交换分区大小
    4. avail Mem：可使用的内存大小（结合tmpfs）

    在swappiness表示内存剩余量在30%时使用swap，即内存使用70%时使用swap。

    [root@db01 ~]# cat /proc/sys/vm/swappiness 
30

    对于MySQL来说，建议将swappiness设置为0（即内存使用完才会使用swap），因为使用swap会影响IO性能。

    # 临时修改
[root@db01 ~]# echo 0 >/proc/sys/vm/swappiness
# 永久修改
[root@db01 ~]# vim /etc/sysctl.conf
vm.swappiness=0
[root@db01 ~]# sysctl -p

  • IO

    1. IO 高，CPU us 也高，属于正常现象；
    2. CPU us 高，IO很低 ，MySQL 不在做增删改查，有可能是存储过程、函数、排序、分组、多表连接；
    3. CPU wa、sys 高 ， IO低：IO出问题（锁等待过多的几率比较大）；
    4. IOPS：每秒磁盘最多能够发生的IO次数（硬盘出厂设定的定值） 。由于频繁小事务导致IOPS很高达到阈值，可能IO吞吐量没超过IO最大吞吐量就无法产生新的IO → 存储规划有问题。

数据库实例层

  • show status：查看数据库状态
  • show variables： 查看系统变量
  • show index：查看索引
  • show processlist： 查看连接线程
  • show slave status：查看主从复制状态
  • show engine innodb status： 查看存储引擎状态
  • desc /explain：查看执行计划
  • slowlog：工具日志

扩展类深度优化

  • information_schema
  • performance_schema
  • sys
  • pt系列工具
  • mysqlslap
  • sysbench

优化思路

硬件优化

  • 主机

    1. 真实的硬件（PC Server）： DELL R系列 、华为、浪潮、HP、联想
    2. 云产品 ：ECS、数据库RDS、DRDS
    3. IBM 小型机 ：P6 、570 、595 、P7、720 、750 、780 、P8（适合Oracle）

  • CPU

    1. IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
    2. IO密集型： E系列（至强），主频相对低，核心数量多
    3. CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（I系列，IBM power系列）
    4. CPU密集型： I 系列的，主频很高，核心少

  • 内存：建议2-3倍CPU核心数量 （ECC）

  • 磁盘：SATA-III 、SAS 、 Fc 、SSD（sata）、pci-e 、 SSD 、 Flash
               注意：主机 RAID卡的BBU(Battery Backup Unit)关闭

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

    1. RAID 0 :条带化 ,性能高
    2. RAID 1 :镜像，安全
    3. RAID 5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
    4. RAID 10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）

  • 网络

    1. 硬件买好的（单卡单口）
    2. 网卡绑定（bonding），交换机堆叠

系统优化

  • SWAP调整

    # 临时修改
[root@db01 ~]# echo 0 >/proc/sys/vm/swappiness
# 永久修改
[root@db01 ~]# vim /etc/sysctl.conf
vm.swappiness=0
[root@db01 ~]# sysctl -p

    swappiness参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，swappiness数值越低越倾向于释放文件系统cache。当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

    修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多。

  • IO调度策略

    # centos 7 默认是deadline
[root@db01 ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

    CentOS 6修改为deadline

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

    IO ：提前规划好以下所有问题，减轻MySQL优化的难度

    1. RAID
    2. NO LVM
    3. EXT4 or XFS
    4. SSD
    5. IO调度策略

应用优化

  1. 开发过程规范，标准
  2. 避免业务逻辑错误，避免锁争用
  3. 减少烂SQL（不走索引，复杂逻辑，切割大事务）

MySQL参数优化

MySQL参数优化细节

Max_connections √

  • 简介
    Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

  • 查看

    # 查看最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

# 启动以来最大连接数量
mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |
+----------------------+-------+
1 row in set (0.04 sec)

  • 判断

    1. 开启数据库时，可以临时设置一个比较大的测试值；
    2. 观察show status like ‘Max_used_connections’;变化；
    3. 如果max_used_connections跟max_connections相同，那么就是max_connections设置过低或者超过服务器的负载上限，低于10%则就是max_connections设置过大。

  • 配置

    # 临时修改
mysql> set GLOBAL max_connections=1024;

# 永久修改
[root@db01 ~]# vim /etc/my.cnf 
Max_connections=1024

back_log

  • 简介
    mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
    back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中，只有在期望短时间内有很多连接的时候需要增加它。
  • 判断
    mysql> show full processlist;
    当发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值。
  • 配置
    [root@db01 ~]# vim /etc/my.cnf 
back_log=1024

wait_timeout & interactive_timeout

  • 简介

    1. wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
      mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.26 sec)
    2. interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数
      mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.02 sec)

    例如：在终端上进行mysql管理，使用的即时交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开（默认的是28800，可调优为7200）。

  • 建议
    wait_timeout：如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用。如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低。

    一般将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长连接应用（长连接应用：为了不去反复的回收和分配资源，降低额外的开销）是否很多。如果需要，那么这个值可以不调整。另外还可以使用类外的参数弥补。

  • 配置

    wait_timeout=60
interactive_timeout=1200

key_buffer_size √

  • 简介
    key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。

    mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.58 sec)
    1. 此参数与MyISAM表的索引有关；
    2. 此参数与临时表的创建有关（多表链接、子查询中、union）。临时表有两种创建方式：
      1. 内存中 → key_buffer_size
      2. 硬盘中 → ibdata1（MySQL 5.6） ibtmp1（MySQL 5.7）

  • 判断
    通过key_read_requests和key_reads可以判断key_baffer_size设置是否合理。

    # 一共10个索引读取请求，有5个请求在内存中没有找到就直接从硬盘中读取索引。	
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 10    |
| Key_reads         | 5     |
+-------------------+-------+
2 rows in set (1.62 sec)

    注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
    可以使用检查状态值created_tmp_disk_tables得知：

    mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |   # 磁盘创建临时表的个数
| Created_tmp_files       | 6     |   # 临时文件（暂时忽略）
| Created_tmp_tables      | 1     |   # 内存创建临时表的个数
+-------------------------+-------+
3 rows in set (0.10 sec)

    通常习惯以

    Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 内存创建比例

    Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 磁盘创建比例（控制在5%-10%以内）

    或者各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。

注意：mysqldump时会用到临时表。

  • 配置
    key_buffer_size=64M

query_cache_size

  • 简介
    查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。
        MySQL 5.7 默认关闭该参数；
        MySQL 8.0摒弃该参数。

    存储方式：

    select * from t1 where id=10;

    1. 将select * from t1 where id=10; 进行hash运算计算出一串hash值，称之为SQL_ID；
    2. 将存储引擎返回上来的表的内容+SQLID存储到查询缓存中

    使用方式：

    1. 一条SQL执行时，进行hash运算，得出SQLID，去查找query cache；
    2. 如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程。

    一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
    注意：两个sql语句，只要相差哪怕是一个字符（列如大小写不一样；多一个空格等），那么这两个sql将使用不同的一个cache。

  • 判断

    mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 9       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.02 sec)

    1. Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
      注意：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks

    2. Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是减少。

    3. Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

    4. Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

    5. Qcache_lowmem_prunes：多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告知属于哪种情况）

    6. Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

    7. Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；

    8. Qcache_total_blocks：当前Query Cache 中的block 数量；。
      Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
                       90/        10000                         0                            90

    如果出现hits比例过低，可以关闭查询缓存，使用redis专门缓存数据库。

    1. Qcache_free_blocks 来判断碎片
    2. Qcache_free_memory + Qcache_lowmem_prunes 来判断内存
    3. Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

  • 配置

    mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

    配置说明：query_cache_type为off表示不缓存任何查询

    字段解释：

    1. query_cache_limit：超过此大小的查询将不缓存
    2. query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
    3. query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
    4. query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
      如果设置为0，缓存根本就没有用，相当于禁用；
      如果设置为1，将会缓存所有的结果，除非select语句使用SQL_NO_CACHE禁用查询缓存；
      如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

    修改/etc/my.cnf

    query_cache_size=128M
query_cache_type=1

max_connect_errors

  • 简介
    max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 。max_connect_errors的值与性能并无太大关系。
  • 配置
    max_connect_errors=2000

sort_buffer_size

  • 简介
    每个需要进行排序的线程都分配sort_buffer_size设置大小的一个缓冲区（消耗内存）。

    1. ORDER BY
    2. GROUP BY
    3. distinct
    4. union

  • 判断
    Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

  • 配置

    sort_buffer_size=1M

max_allowed_packet √

  • 简介
    mysql根据配置文件max_allowed_packet 限制server接受的数据包大小。
  • 判断
    有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数。
  • 配置
    max_allowed_packet=32M

join_buffer_size

  • 简介
    用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
  • 建议
    尽量在SQL方面进行优化，效果较为明显。优化方法：在on条件列加索引，至少应当是有MUL索引。

thread_cache_size √

  • 简介
    服务器线程缓存个数，这个值表示可以重新利用保存在缓存中线程的数量。当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)。如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建。如果有很多新的线程，增加这个值可以改善系统性能。

  • 判断
    通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
    设置规则：1GB 内存配置为8，2GB内存配置为16，3GB内存配置为32，4GB或更高内存，可配置更大线程数。

    mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 2     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.27 sec)
    1. Threads_cached ：当前此时此刻线程缓存中有多少空闲线程。
    2. Threads_connected：当前已建立连接的数量（因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数）。
    3. Threads_created：从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗CPU sys资源，可以适当增加配置文件中thread_cache_size值。
    4. Threads_running ：当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

  • 配置

    thread_cache_size=32

  • 总结

    1. Threads_created ：一般在架构设计阶段，设置一个测试值，做压力测试。
    2. 结合zabbix监控，监测一段时间内此状态的变化。
    3. 如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK的。
    4. 如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）。

innodb_buffer_pool_size √

  • 简介
    对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。

  • 判断
    InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%（一般建议不要超过物理内存的70%）。

  • 配置

    innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit √

  • 简介
    主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
    1. 0表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
    2. 1每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；
    3. 2每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。
  • 判断
    实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
    根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
  • 配置
    双1标准中的一个1
    innodb_flush_log_at_trx_commit=1

innodb_thread_concurrency

  • 简介
    此参数用来设置innodb线程的并发数量，默认值为0表示不限制。

  • 判断
    官方建议：

    1. 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
    2. 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数；
    3. 如果不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现）；
    4. 如果目标是将MySQL与其他应用隔离，可以考虑绑定mysqld进程到专有的虚拟CPU。但是需要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
    5. 在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
    6. 定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。

    设置标准：

    1. 当前系统cpu使用情况，均不均匀（top）
    2. 当前的连接数，有没有达到顶峰
      show status like ‘threads_%’;
      show processlist;

  • 配置

    innodb_thread_concurrency=8

  • 方法

    1. 查看top状态 ,观察每个cpu的各自的负载情况；
    2. 发现不平均，先设置参数为cpu个数，然后不断增加(一倍)；
    3. 一直观察top状态，直到达到比较均匀时。

innodb_log_buffer_size √

  • 简介
    此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大可以提高性能，对于较大的事务，可以增大缓存大小。
  • 判断
    1. 大事务
    2. 多事务
  • 配置
    innodb_log_buffer_size=128M

innodb_log_file_size √

  • 简介
    此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
  • 配置
    innodb_log_file_size=100M

innodb_log_files_in_group √

  • 简介
    为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3。
  • 配置
    innodb_log_files_in_group=3

read_buffer_size

  • 简介
    MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
  • 配置
    read_buffer_size=1M

read_rnd_buffer_size

  • 简介
    MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
    注意：
    1. 顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。
    2. 随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
  • 配置
    read_rnd_buffer_size=1M

bulk_insert_buffer_size

  • 简介
    批量插入数据缓存大小，可以有效提高插入效率，默认为8M。
  • 配置
    bulk_insert_buffer_size=8M

binary log √

log-bin=/data/mysql-bin
binlog_format=row 
binlog_cache_size=2M                # 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是0-1M，后者建议是 2-4M
max_binlog_cache_size=8M            # binlog 能够使用的最大cache 内存大小
max_binlog_size=512M                # 指定binlog日志文件的大小，默认值是1GB。如果当前的日志大小达到max_binlog_size，会自动创建新的二进制日志。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，且建议定期做删除。
expire_logs_days=7                  # 定义mysql清除过期日志的时间，默认值为0。
# 双1标准(基于安全的控制)
sync_binlog=1                  
innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;
show status like 'com_%';

安全参数 √

Innodb_flush_method=(O_DIRECT, fsync)

  • fsync ：
    1. 在数据页需要持久化时，首先将数据写入OS buffer中，然后由OS决定什么时候写入磁盘；
    2. 在redo buffuer需要持久化时，首先将数据写入OS buffer中，由OS决定什么时候写入磁盘；但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘。
  • Innodb_flush_method=O_DIRECT
    1. 在数据页需要持久化时，直接写入磁盘
    2. 在redo buffuer需要持久化时，首先将数据写入OS buffer中，由OS决定什么时候写入磁盘；但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘。

最安全模式

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能模式

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

一般情况下，更偏向于安全模式 “双一标准”

innodb_flush_log_at_trx_commit=1     
sync_binlog=1                                   
innodb_flush_method=O_DIRECT

参数优化结果

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock

锁的监控

模拟锁等待

  • session1

    mysql> use pressure;
Database changed

mysql> UPDATE t_100w SET k1='av' WHERE id=10;
Query OK, 1 row affected (8.93 sec)
Rows matched: 1  Changed: 1  Warnings: 0

  • session2

    mysql> use pressure;
Database changed

mysql> UPDATE  t_100w SET k1='az' WHERE id=10;
Query OK, 1 row affected (6.79 sec)
Rows matched: 1  Changed: 1  Warnings: 0

监控锁状态

  1. 查看有没有锁等待：SHOW STATUS LIKE ‘innodb_row_lock%’;

    mysql> SHOW  STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 15729 |
| Innodb_row_lock_time_avg      | 3145  |
| Innodb_row_lock_time_max      | 5221  |
| Innodb_row_lock_waits         | 5     |
+-------------------------------+-------+
5 rows in set (0.37 sec)

  2. 查看哪个事务在等待（阻塞）：SELECT * FROM information_schema.INNODB_TRX WHERE trx_state=‘LOCK WAIT’\G

    mysql> SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'\G
*************************** 1. row ***************************
                    trx_id: 10003066                                  # 事务ID   
                 trx_state: LOCK WAIT                                 # 当前事务状态 
...
       trx_mysql_thread_id: 1114                                      # 连接线程ID（show processllist）
                 trx_query: UPDATE  t_100w SET k1='az' WHERE id=10    # 当前被阻塞的操作
...

确定锁源

  1. 查看锁源 ：SELECT * FROM sys.innodb_lock_waits\G

    mysql> SELECT * FROM sys.innodb_lock_waits\G
                locked_table: `pressure`.`t_100w`                       # 出现锁的表
              waiting_trx_id: 10003066                                  # 等待的事务ID
                 waiting_pid: 1114                                      # 等待的线程ID
             blocking_trx_id: 10003069                                  # 锁源的事务ID
                blocking_pid: 5                                         # 锁源的线程号

  2. 查看锁源thread_id (通过连接线程ID获得SQL线程ID) ：SELECT * FROM performance_schema.threads WHERE processlist_id=blocking_pid;

    mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=5\G

          THREAD_ID: 30
...

  3. 查看锁源SQL语句（通过SQL线程ID获得SQL语句）

    当前在执行SQL语句：SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=30;
历史执行的SQL语句：SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=30;

    mysql> SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=30\G
*************************** 1. row ***************************
              THREAD_ID: 30
               EVENT_ID: 60000102
           END_EVENT_ID: 60000102
             EVENT_NAME: statement/sql/update
                 SOURCE: socket_connection.cc:101
            TIMER_START: 16584453793783000
              TIMER_END: 16596271775695000
             TIMER_WAIT: 11817981912000
              LOCK_TIME: 165000000
               SQL_TEXT: UPDATE t_100w SET k1='av' WHERE id=10
                 DIGEST: a53700ae3d083482445ba277e43f7eee
            DIGEST_TEXT: UPDATE `t_100w` SET `k1` = ? WHERE `id` = ? 
         CURRENT_SCHEMA: pressure
            OBJECT_TYPE: NULL

死锁监控

# 死锁监控（只能查看最后一次死锁状态）
mysql> show engine innodb status\G

# 开启死锁日志
mysql> show variables like '%deadlock%';

[root@db01 ~]# vim /etc/my.cnf 
innodb_print_all_deadlocks = 1

优化项目：锁的监控及处理

  • 背景：硬件环境 DELL R720，E系列16核，48G MEM SAS900G6，RAID10
               例行巡检时，发现9-11点时间段的CPU压力非常高(80-90%)

  • 项目职责

    1. 通过top详细排查，发现mysqld进程占比达到了700-800%；
    2. 其中大量的CPU是被用作的sys和wait，us处于正常状态；
    3. 怀疑是MySQL 锁 或者SQL语句出了问题；
    4. 经过排查slowlog及锁等待情况，发现有大量锁等待及少量慢语句。
      (1) pt-query-diagest 查看慢日志
      (2) 查看有无锁等待
               情况一：100多个current_waits，说明当前很多锁等待情况
               情况二：1000多个lock_waits，说明历史上发生过的锁等待很多
    5. 查看哪个事务在等待(被阻塞)
    6. 查看锁源事务信息(谁锁的)
    7. 找到锁源的thread_id
    8. 找到锁源的SQL语句
    9. 找到语句之后,和应用开发人员进行协商
      (1) 开发人员描述，此语句是事务挂起导致，我们提出建议是临时kill 会话,最终解决问题。
      (2) 开发人员查看后，发现是业务逻辑问题导致的死锁，产生了大量锁等待
           临时解决方案,将阻塞事务的会话kill掉
           最终解决方案,修改代码中的业务逻辑

  • 项目结果
    经过排查处理，锁等待的个数减少80%，解决了CPU持续峰值的问题。

锁监控相关命令：

show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

优化主从

MySQL 5.7 从库多线程MTS

  • 基本要求

    1. 5.7以上的版本
    2. 必须开启GTID
    3. binlog必须是row模式
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16      # cpu核心数作为标准
这篇关于MySQL优化策略的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航