Where is MySQL memory consumed?
When talking about the memory used by MySQL, you may think of various buffers. The most famous is innodb buffer pool, which is a large memory user, as well as sort buffer and so on. In addition to these buffers, there may be some details. Let's summarize them today.
First, various types of buffer s and cache s can be found by using 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)
It can be seen that many of the results are often used. Of course, not all of them will be used. For example, the parameters starting with max are the maximum values, which may not be used at all. There are also block related parameters, which refer to the memory allocation unit.
Here, we first briefly summarize the important buffer s and cache s:
01
Various buffer s and cache s
Global shared memory:
innodb_buffer_pool_size:InnoDB buffer pool size.
innodb_additional_mem_pool_size: additional cache pool, MySQL 8.0 has been discarded
key_ buffer_ Size: memory size of MyISAM cache index block.
query_cache_size: query cache, MySQL 8.0 has been discarded
innodb_log_buffer_size: redo log buffer, used to record the intermediate status of transaction execution
Thread level memory:
read_buffer_size: MyISAM table sequential read buffer size
sort_buffer_size: sort buffer size
join_buffer_size: connection query buffer size
read_rnd_buffer_size: connection query MRR optimization buffer size, MyISAM random read buffer size
tmp_table_size: size of temporary memory table
binlog_cache_size: thread level binlog buffer size
thread_stack: stack size per thread
Memory allocator:
Like other databases, MySQL's memory allocator also needs to hold some memory for a long time for normal memory destruction and allocation, so as to realize memory reuse.
Therefore, we can simply deduce a formula, that is, the memory usage in MySQL is roughly equal to:
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 )
Where connections represents the current number of connections.
It is not difficult to see from the formula that when the basic configuration remains unchanged and the number of connections is large, the memory data occupied by MySQL will rise faster.
02
performance_schema dimension analysis
Performance_ The schema feature can monitor the operation indicators of MySQL. Usually, it is enabled by default. When enabled, it will cause about 10% MySQL performance loss.
Turn on Performance_ After the schema attribute is, the performance_ There are five memory related tables under the system database schema:
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)
Of which:
memory_summary_by_account_by_event_name
Represents the memory monitoring table related to the account
memory_summary_by_host_by_event_name
Memory monitoring on behalf of the host dimension
memory_summary_by_thread_by_event_name
Thread dimension memory monitoring table
memory_summary_by_user_by_event_name
User dimension memory monitoring table
memory_summary_global_by_event_name
Global dimension memory monitoring table
Each table has many fields, which will not be introduced one by one here.
You can use the following SQL to query memory usage in PS database:
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)
Time, reason, more details, let's talk about it next time.