mysql常用存储方案及基本原理
详解分析MySQL8.0的内存消耗目录
- 1、innodb_buffer_pool_size
-
2、innodb_log_buffer_size
- 1、查看
- 2、在配置文件中写上相关的参数，开启统计，以memory/innodb/row_log_buf为例，配置文件修改的如下：
- 3、启动实例，并在performance_schema数据库的memory_summary_global_by_event_name表中查看内存统计结果。
在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实例，可以使用下面的参数来调整这个实例的个数：
|
|
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存储引擎占用的内存，结果如下：
|
|
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为例，配置文件修改的如下：
|
|
performance-schema-instrument='memory/innodb/row_log_buf=COUNTED' |
3、启动实例，并在performance_schema数据库的memory_summary_global_by_event_name表中查看内存统计结果。
|
|
SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G |
当然，你还可以根据sys表中的结果，查看每个大类的聚合结果，如下：
|
|
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