mysql内存到底消耗在哪里?
一说起MySQL使用的内存,你可能会想到各种buffer,最著名的莫过于innodb buffer pool了,它是内存使用的大户,还有sort buffer等等。除了这些buffer之外,可能还有一些细枝末节,今天我们来总结一下。
首先是各种类型的buffer和cache,利用SQL可以查到:
mysql 22:57:49> show variables like '%buffer%size%'; +-------------------------------+-------------+ | Variable_name | Value | +-------------------------------+-------------+ | bulk_insert_buffer_size | 8388608 | | clone_buffer_size | 4194304 | | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_size | 21474836480 | | innodb_change_buffer_max_size | 25 | | innodb_log_buffer_size | 8388608 | | innodb_sort_buffer_size | 1048576 | | join_buffer_size | 16777216 | | key_buffer_size | 134217728 | | myisam_sort_buffer_size | 134217728 | | preload_buffer_size | 32768 | | read_buffer_size | 16777216 | | read_rnd_buffer_size | 33554432 | | sort_buffer_size | 16777216 | +-------------------------------+-------------+ 14 rows in set (0.00 sec) mysql 22:57:45> show variables like '%cache%size%'; +----------------------------+----------------------+ | Variable_name | Value | +----------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | host_cache_size | 1003 | | innodb_ft_cache_size | 8000000 | | innodb_ft_total_cache_size | 640000000 | | key_cache_block_size | 1024 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 18446744073709547520 | | thread_cache_size | 64 | +----------------------------+----------------------+ 9 rows in set (0.01 sec)
可以看到,结果中有很多都会经常用到。当然,这里面,不都是会被用到的,例如max开头的参数,都是值最大的值,平时可能根本用不到,还有block相关的参数,都是指内存分配单位,也用不到。
这里我们首先简单总结一下那些重要的buffer和cache:
01
各种buffer和cache
全局共享内存:
innodb_buffer_pool_size:InnoDB缓冲池大小。
innodb_additional_mem_pool_size: 额外缓存池,
key_buffer_size: MyISAM缓存索引块的内存大小。
query_cache_size:查询缓存,
innodb_log_buffer_size:redo log buffer,用来记录事务执行的中间状态
线程级别内存:
read_buffer_size:MyISAM表顺序读缓冲大小
sort_buffer_size: 排序缓冲区大小
join_buffer_size: 连接查询缓冲区大小
read_rnd_buffer_size:连接查询MRR优化缓冲大小、MyISAM随机读缓冲大小
tmp_table_size:内存临时表大小
binlog_cache_size: 线程级别binlog缓冲大小
thread_stack: 每个线程的堆栈大小
内存分配器:
和其他数据库一样,MySQL的内存分配器也要长时间持有一部分内存,以供正常的内存销毁和分配,从而实现内存重复使用。
因此,我们可以简单的推导一个公式,就是MySQL中内存的使用量,大概等于:
SELECT ( @@key_buffer_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ connections * (
@@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
其中,connections代表当前的连接数。
从公式中不难看出,当基础配置不变情况下,连接数很多的时候,MySQL占用的内存数据,就会上涨的比较快。
02
performance_schema维度分析
Performance_schema特性可以监控MySQL的运行指标,通常情况下,默认是开启的,开启时候,会带来10%左右的MySQL性能损耗。
开启Performance_schema特性后,会在performance_schema这个系统数据库下存在5张内存相关的表:
show tables like '%memory%'; +-----------------------------------------+ | Tables_in_performance_schema (%memory%) | +-----------------------------------------+ | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | +-----------------------------------------+ 5 rows in set (0.00 sec)
其中:
memory_summary_by_account_by_event_name
代表账号相关的内存监控表
memory_summary_by_host_by_event_name
代表主机维度的内存监控
memory_summary_by_thread_by_event_name
线程维度内存监控表
memory_summary_by_user_by_event_name
用户维度内存监控表
memory_summary_global_by_event_name
全局维度内存监控表
每张表都有很多字段,这里不再进行一一介绍。
可以在PS数据库中使用下面的SQL进行内存使用情况查询:
select substring_index(event_name,'/',2) as code_area, sys.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 | 21.11 GiB | | memory/performance_schema | 283.57 MiB | | memory/mysys | 130.72 MiB | | memory/sql | 74.98 MiB | | memory/temptable | 67.00 MiB | | memory/mysqld_openssl | 1.96 MiB | | memory/myisam | 696 bytes | | memory/csv | 88 bytes | | memory/blackhole | 88 bytes | | memory/vio | 8 bytes | +---------------------------+---------------+ 10 rows in set (0.01 sec)
时间原因,更多细节,下次再说吧。