Buffer pool 设置优化参考(一次数据库负载异常升高的排查)

Buffer pool 设置优化参考

问题描述

• 发现线上数据库服务器的负载居高不下， 查看Mysql processlist 发现并没有异常耗时的查询语句，有没有大量建立的查询请求。 但是此时数据库完成一个普通的查询都非常慢。

• 排查过程

  # 查看进程情况  - 发现一切正常
  show processlist;
  
  
  # 查看是否有事物等待
  select * from information_schema.INNODB_TRX
  
  
  # 查看内部线程情况统计
  show status like 'thread%'
  
  
  # 查看innodb 状态情况
  show engine innodb status\G
  # 并未发现死锁，但看到如下内容： 
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total large memory allocated 137363456
  Dictionary memory allocated 769342
  Buffer pool size   8192
  Free buffers       0
  Database pages     8163
  Old database pages 2993
  Modified db pages  3735
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 1
  Pages made young 2008830076, not young 44429641920
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 9503723023, created 16454381, written 222453223
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 862 / 1000, young-making rate 4 / 1000 not 346 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 8163, unzip_LRU len: 0
  I/O sum[74651]:cur[700], unzip sum[0]:cur[0]
  --------------
  ROW OPERATIONS
  --------------
  0 queries inside InnoDB, 0 queries in queue
  0 read views open inside InnoDB
  Process ID=25932, Main thread ID=139874978158336 , state=sleeping
  Number of rows inserted 19580082, updated 4416944, deleted 14201699, read 554144947684
  1.25 inserts/s, 0.00 updates/s, 0.00 deletes/s, 23.24 reads/s
  
  

  • 从提供的信息中，可以分析出Buffer Pool的情况。以下是对信息的分析：
    Buffer pool size: 8192 - 这是Buffer Pool的大小，表示在内存中分配给InnoDB存储引擎的缓冲池的大小，单位为页（通常为16KB）。- 因此这里的Buffer pool实际上才128MB

    Free buffers: 0 - 这是空闲的缓冲区数量，表示当前没有可用的空闲缓冲区。
    Database pages: 8163 - 这是数据库页的数量，表示当前在缓冲池中的数据库页数量。
    Old database pages: 2993 - 这是旧的数据库页的数量，表示在缓冲池中的旧的数据库页数量。
    Modified db pages: 3735 - 这是已修改的数据库页的数量，表示在缓冲池中已被修改但尚未写回磁盘的数据库页数量。
    Buffer pool hit rate: 862 / 1000 - 这是缓冲池命中率，表示从缓冲池中读取数据的命中率。在这种情况下，命中率为862/1000，即86.2%。

    Pages read: 9503723023 - 这是从磁盘读取的数据库页的数量。
    Pages created: 16454381 - 这是在缓冲池中创建的新数据库页的数量。
    Pages written: 222453223 - 这是写回磁盘的数据库页的数量。

    通过这些信息，可以了解到Buffer Pool的大小、使用情况以及读写操作的情况。 其中，缓冲池命中率是一个重要的指标，表示从缓冲池中读取数据的效率。在这种情况下，命中率为86.2%，可以认为缓冲池的使用效果较好。但是，需要注意的是，如果Free buffers为0，表示当前没有可用的空闲缓冲区，可能需要调整Buffer Pool的大小以适应负载需求。

    Buffer Pool的大小为8192页，而数据库页的数量为8163页。这意味着Buffer Pool已经接近或达到了最大容量，没有可用的空闲缓冲区。

    Pages read的数量非常高，远远超过了Buffer Pool的大小。这表示有大量的数据需要从磁盘读取，而不是从缓冲池中获取。
    缓冲池命中率为86.2%，这是一个相对较高的命中率，表示大部分的数据读取操作都能够从缓冲池中获取。
    综合以上分析，可以得出以下建议：

    由于Pages read的数量非常高，超过了Buffer Pool的大小，

  • 从上面的分析结果可知：
    Free buffers 为0 说明Buffer pool 没有空闲。
    Pages read为 9503723023 非常高，说明需要大量的从磁盘加载数据。
    因此建议将Buffer Pool的大小增加到能够容纳更多常用数据的程度。

解决方案

1. 动态调整Buffer pool 大小 - 立即生效

   # 查看现有大小
   SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
   
   # 如果要设置到4GB
   SET GLOBAL innodb_buffer_pool_size = 4294967296;
   

   • 注意： 请注意，这个设置只会影响新的连接和新的查询，对于已经存在的连接和正在运行的查询，它们仍然使用之前的Buffer Pool大小。如果您希望立即应用新的Buffer Pool大小，可以尝试重启MySQL服务。重启MySQL将导致所有连接和查询重新初始化，并使用新的Buffer Pool大小。 在执行重启之前，请确保您已经备份了重要的数据，并在低峰期进行操作，以避免对系统的影响。

2. 在配置文件中修改 - 重启后生效

   [mysqld]
   innodb_buffer_pool_size = 4G
   

背景知识

• 执行 show processlist; 我们就可以查看出当前所有的进程，并且得到最耗时的进程。

  在当前数据库中，看到处于 Sleep 状态的SQL非常多，而这也是占用CPU过高的重大原因，休眠线程太多，于是配置了一个 wait_time_out 为 600 秒的一个解决方案。

  为什么配置600秒，因为我们应用超时时间配置的最大时间就是 600秒，10分钟，这里的配置需要根据业务来具体配置。

• MySQL的默认Buffer Pool大小取决于您所使用的MySQL版本和配置。在MySQL 5.7及以上版本中，默认的Buffer Pool大小为128MB。在之前的版本中，默认的Buffer Pool大小为8MB。这些默认值可能不适用于所有的应用场景，因此建议根据实际负载需求进行调整。

• 要查看当前MySQL负载高的原因，可以使用以下语句和工具来获取相关信息：

  SHOW PROCESSLIST;：显示当前MySQL实例中的活动进程列表，包括正在执行的查询和锁定的事务。通过检查进程列表，可以确定是否有长时间运行的查询或锁定的事务导致负载升高。

  SHOW ENGINE INNODB STATUS;：显示InnoDB存储引擎的状态信息，包括等待锁、事务和缓冲池的使用情况。通过分析InnoDB状态信息，可以了解是否存在锁等待、事务阻塞或缓冲池压力等问题。

  SHOW GLOBAL STATUS;：显示MySQL实例的全局状态变量，包括连接数、查询数、缓存命中率等。通过查看全局状态变量，可以了解MySQL实例的整体负载情况。

  SHOW GLOBAL VARIABLES;：显示MySQL实例的全局配置变量，包括缓冲池大小、并发连接数等。通过查看全局配置变量，可以了解MySQL实例的配置情况，是否与负载需求相匹配。

  EXPLAIN ;：使用EXPLAIN命令分析查询语句的执行计划，包括索引使用情况、表扫描次数等。通过分析查询的执行计划，可以确定是否存在查询性能问题。

  MySQL性能监控工具：使用MySQL性能监控工具（如Percona Toolkit、MySQL Enterprise Monitor等）来收集和分析MySQL的性能指标。这些工具可以提供更详细的性能数据和图表，帮助您更好地了解负载情况和性能瓶颈。

  通过以上语句和工具，您可以获取当前MySQL负载高的原因。请根据具体情况使用适当的语句和工具，并根据获取的信息采取相应的优化措施。

• Pages read的数量为9503723023，表示从磁盘读取的数据库页的数量。这个数据可以用来初步评估Buffer Pool是否足够。

  如果Pages read的数量非常高，说明有大量的数据需要从磁盘读取，而不是从Buffer Pool中获取。这可能意味着Buffer Pool的大小不足以容纳常用的数据，导致频繁地从磁盘读取数据。

• 要确定Buffer Pool是否不够，可以考虑以下几个因素：

  Pages read的数量与Buffer Pool的大小进行比较。如果Pages read的数量远远超过Buffer Pool的大小，那么很可能Buffer Pool不够大。

  观察Buffer Pool的使用率。可以通过计算缓冲池命中率来评估Buffer Pool的使用情况。如果缓冲池命中率较低，说明Buffer Pool无法容纳常用的数据，需要增加其大小。

  监控系统的I/O负载。如果系统的I/O负载非常高，特别是磁盘I/O，那么可能是因为Buffer Pool不够大，导致频繁地从磁盘读取数据。

  综合考虑以上因素，可以初步判断Buffer Pool是否不够大。如果发现Buffer Pool不够大，可以考虑增加其大小以提高缓冲效果，减少从磁盘读取数据的次数。请注意，在调整Buffer Pool大小之前，确保系统有足够的可用内存来支持所需的Buffer Pool大小，并且考虑到其他内存需求（如操作系统和其他应用程序）。