mysql常用存储方案及基本原理

详解分析MySQL8.0的内存消耗

  在MySQL8.0在启动的时候,会配置各种各样的buffer和cache来提高数据库的性能。如果我们在一台服务器上配置了MySQL8.0的服务,那么这台服务器的内存会同时被操作系统、MySQL8.0服务、以及其他应用程序所共享。

   生产环境中,经常会遇到内存的报警,在处理这些报警之前,你需要知道MySQL本身消耗内存最多的点在哪里,这样才能比较直观的判断出来你的MySQL服务占用的内存有多少,以及如何降低MySQL本身的内存消耗。

   在MySQL配置文件中,最常用的两个内存相关的参数是innodb_buffer_pool_size、innodb_log_buffer_size,我们来看这两个参数。

 

1、innodb_buffer_pool_size

 

这个参数定义了buffer pool的大小,大家可能都比较熟悉,buffer pool中的内容包含innodb 表、索引、以及其他的辅助buffer,buffer pool的大小对MySQL系统性能影响比较大,默认情况下,MySQL8.0配置的buffer pool大小是128MB,通常情况下,如果是单机单实例,没有其他业务,那么MySQL官方建议配置的大小为系统内存的50%到75%之间。当然,如果你的服务器上还部署了其他的应用程序,那么你需要酌情减小这个比例,从而腾出内存。

如果你的操作系统的内存很充裕,你可以设置多个innodb buffer pool实例,可以使用下面的参数来调整这个实例的个数:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • mysql> show variables like '%innodb_buffer_pool_instances%';
  • +------------------------------+-------+
  • | Variable_name    | Value |
  • +------------------------------+-------+
  • | innodb_buffer_pool_instances | 1  |
  • +------------------------------+-------+
  • 1 row in set (0.00 sec)
  •  

    2、innodb_log_buffer_size

     

    这个参数定义了innodb存储引擎向磁盘上写redo log之前,最多在内存中缓存数据的大小,默认是16MB。这个值增加之后,大的事务可以不用在事务提交之前将redo log落盘。如果你的update、delete和insert操作影响行数比较多,那么你需要考虑增大这个值。

    重点来了:

    在操作系统里面,MySQL占用的内存不仅仅是上述两个内存配置参数有关,通常情况下,我们计算MySQL占用的内存的时候,会使用下面4个值相加的方式:

    1、innodb_buffer_pool_size

    2、key_buffer_size  (这个参数通常是myisam表占用内存的关键参数)

    3、max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) (这三个是连接级别的buffer)

    4、max_connections*2MB

    所以当你使用top命令看到你的MySQL占用的内存远远超过innodb_buffer_pool_size的时候,你需要考虑的另外一个关键因素是连接数是否超标了,一旦连接数过高,那么上述3、4这两部分消耗的内存将会非常多。

    当然,上面列举的,是MySQL最主要占用内存的几个因素,除此之外,其他的内存消耗的地方,可以查看官方文档:

    https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

    上述文档中,还有介绍我们如何使用performance_schema来监控MySQL的内存使用,这里我提一下整个流程,详细的细节以及参数介绍请参看官方文档。

     

    1、查看

    performance_schema.setup_instruments

    这张表,找到你关注的内存变量的名称(直接搜索,结果有490多条,分为好几个大类,一定记得过滤自己关注的参数)。举个例子,我们搜索memory/innodb相关参数,代表innodb存储引擎占用的内存,结果如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • mysql> SELECT * FROM performance_schema.setup_instruments  WHERE NAME LIKE '%memory/innodb%';
  • +-------------------------------------------+---------+-------+-------------------+------------+---------------+
  • | NAME          | ENABLED | TIMED | PROPERTIES  | VOLATILITY | DOCUMENTATION |
  • +-------------------------------------------+---------+-------+-------------------+------------+---------------+
  • | memory/innodb/adaptive hash index   | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/log and page archiver  | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/buf_buf_pool    | YES  | NULL | global_statistics |   0 | NULL   |
  • | memory/innodb/buf_stat_per_index_t  | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/clone      | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/dict_stats_bg_recalc_pool_t | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/dict_stats_index_map_t  | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/dict_stats_n_diff_on_level | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/other      | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/partitioning    | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/row_log_buf     | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/row_merge_sort    | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/std       | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/trx_sys_t::rw_trx_ids  | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/undo::Tablespaces   | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/ut_lock_free_hash_t   | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/api0api      | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/api0misc     | YES  | NULL |     |   0 | NULL   |
  • | memory/innodb/btr0btr      | YES  | NULL |     |   0 | NULL   |
  •  

    2、在配置文件中写上相关的参数,开启统计,以memory/innodb/row_log_buf为例,配置文件修改的如下:

  • ?
  • 1
  • performance-schema-instrument='memory/innodb/row_log_buf=COUNTED'
  •  

    3、启动实例,并在performance_schema数据库的memory_summary_global_by_event_name表中查看内存统计结果。

  • ?
  • 1
  • SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G
  • 当然,你还可以根据sys表中的结果,查看每个大类的聚合结果,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
  •   code_area, FORMAT_BYTES(SUM(current_alloc))
  •   AS current_alloc
  •   FROM sys.x$memory_global_by_current_bytes
  •   GROUP BY SUBSTRING_INDEX(event_name,'/',2)
  •   ORDER BY SUM(current_alloc) DESC;
  • +---------------------------+---------------+
  • | code_area     | current_alloc |
  • +---------------------------+---------------+
  • | memory/innodb    | 843.24 MiB |
  • | memory/performance_schema | 81.29 MiB  |
  • | memory/mysys    | 8.20 MiB  |
  • | memory/sql    | 2.47 MiB  |
  • | memory/memory    | 174.01 KiB |
  • | memory/myisam    | 46.53 KiB  |
  • | memory/blackhole   | 512 bytes  |
  • | memory/federated   | 512 bytes  |
  • | memory/csv    | 512 bytes  |
  • | memory/vio    | 496 bytes  |
  • +---------------------------+---------------+
  • 更详细的信息,请参见官方文档。

    以上就是详解分析MySQL8.0的内存消耗的详细内容,更多关于MySQL8.0 内存消耗的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1800973