mysql5.7.23性能调优之innodb_buffer_pool_size

 

前言

      我的数据库版本是5.7.23，最近发现执行SQL越来越慢，一条SQL语句执行需要将近30s。

 

 

 

 

 

 

 

 

 

 

    对于原因，查询资料， https://www.cnblogs.com/qwangxiao/p/8921196.html一文中写道：

导致SQL执行慢的原因：

1. 硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。

我查询内存、磁盘利用率如下：

 

 

 

2. 没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）

我们所有数据都是做的假删除，索引也建了的。

3. 数据过多（分库分表）

 

 

 数据量确实比较大，所以我们也是选择的分表。

 

 

 

4. 服务器调优及各个参数设置（调整my.cnf）

 

调整参数innodb_buffer_pool_size 

　　以上三点，检查基本过关，那就只剩最后一点了，对应调整my.cnf的参数，我首先想到的是innodb_buffer_pool_size ，对于这个参数https://blog.csdn.net/kk185800961/article/details/79378313/一文中提到：用于缓存 索引 和 数据的内存大小, 这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用, 一般设置 buffer pool 大小为总内存的  3/4 至 4/5。 

　　并提出：较优设置：假设系统内存 = 128 GB, buffer pool 大小预计100GB(128GB*80%)

innodb_buffer_pool_instances = 8                #默认值,或者逻辑CPU数量
innodb_buffer_pool_chunk_size = 128MB    #默认值
innodb_buffer_pool_size = 100 GB               # N*8*128MG = N GB ,N 刚好为正整数。设 N=100使得 buffer pool 为总内存的 3/4 至 4/5。

　　我的服务器逻辑CPU数量为4，内存是15G。

 cat /proc/cpuinfo | grep "processor" |wc -l
 free -h

 

 

 当前innodb_buffer_pool_instances = 1，innodb_buffer_pool_chunk_size 和innodb_buffer_pool_size =134217728，也就是128M。

 

 

 

 

 

 

 我将上面的参数都提高4倍。

  SET GLOBAL innodb_buffer_pool_size=536870912‬;
  SET GLOBAL innodb_buffer_pool_chunk_size =536870912‬;

SELECT
    @@innodb_buffer_pool_instances,
    @@innodb_buffer_pool_chunk_size,
    @@innodb_buffer_pool_size

看到SQL执行快了7s

 

 于是我继续改配置：

 

 

 

 

 

 

 

 

 

 

 

 这时速度提升了一半。后经过反复测试，发现以下配置，速度最快，所以三个参数最终的的配置（/etc/my.cnf里）是：

innodb_buffer_pool_size = 1280M
innodb_buffer_pool_chunk_size= 1280M
innodb_buffer_pool_instances=1

 

 

 开启独立空间参数global innodb_file_per_table

    SET global innodb_file_per_table=1;

开启之后，快了一点点

 

 

 其他优化，参考：

https://www.cnblogs.com/erisen/p/6068265.html