Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the knowledge points of database memory". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, how to understand memory metrics

If you encounter memory problems, you can check them through free, vmstat, top and other commands. Free command, you can get the overall use of system memory; vmstat command, you can observe the changes of memory in real time; top command, you can sort and get processes that take up a lot of memory. Here's a brief introduction to the free command output (take CentOS 7 as an example):

Total used free shared buff/cache available Mem: 8008704 5234876 157920 640 2615908 2467292 Swap: 2047 0 2047

The first row is memory data.

1. Total: total memory size, corresponding to the MemTotal of / proc/meminfo

2. Used: the amount of memory used, corresponding to / proc/meminfo (MemTotal-MemFree-Buffers-Cached-Slab)

3. Free: unused memory size, corresponding to the MemFree of / proc/meminfo

4. Buff/cache: cache size used, corresponding to the Buffers+Cached of / proc/meminfo

5. Available: the amount of memory available, which is an estimate corresponding to the MemAvailable of / proc/meminfo

The second line is to exchange partition data.

1. Total: total size of the swap partition, corresponding to the SwapTotal of / proc/meminfo

2. Used: swap partition used, corresponding to / proc/meminfo 's (SwapTotal-SwapFree)

3. Free: unused memory size, corresponding to / proc/meminfo 's SwapFree

It is worth noting that the Linux operating system will maximize the use of memory, free memory free is less, does not mean that the system memory is not enough. Personal advice, on the one hand, we need to observe whether the overall trend of memory growth is gradually leveling off, and the changes of used and buff/cache; on the other hand, we need to observe the frequent use of swap partition swap, of course, to avoid interference caused by incorrect NUMA and swapiness settings.

How to use memory in MySQL

In MySQL, memory footprint mainly includes the following parts: global shared memory, thread exclusive memory, memory occupied by memory allocator, as follows:

Global sharing

1. The size of the innodb_buffer_pool_size:InnoDB buffer pool

2. Innodb_additional_mem_pool_size:InnoDB memory size for storing data dictionaries and other internal data structures, 5.7 has been removed

3. Size of innodb_log_buffer_size:InnoDB log buffer

4. Memory size of key_buffer_size:MyISAM cache index block

5. Query_cache_size: size of query buffer, 8.0 has been removed

Thread monopoly

1. Thread_stack: stack size allocated by each thread

2. Sort_buffer_size: the size of the sort buffer

3. Join_buffer_size: size of connection buffer

4. The size of the read_buffer_size:MyISAM sequential read buffer

5. The size of read_rnd_buffer_size:MyISAM random read buffer and MRR buffer

6. Tmp_table_size/max_heap_table_size: the size of the memory temporary table

7. Binlog_cache_size: the size of the binary log buffer

Memory allocator

In MySQL, the memory of buffer pool is allocated directly to the operating system through mmap (); in addition, most memory management needs to go through a memory allocator. In order to achieve more efficient memory management and avoid frequent memory allocation and recycling, the memory allocator will occupy a large amount of memory for internal reuse for a long time. With regard to the choice of memory allocator, it is recommended to use jemalloc, which can effectively solve memory fragmentation and improve overall performance.

Therefore, the reasons for the high memory consumption of MySQL may include: too large innodb_buffer_pool_size setting, high number of connections / concurrency, a large number of sorting operations, memory allocator usage, MySQL Bug, and so on. Generally speaking, in the whole running cycle of MySQL, the memory will rise faster when it starts, and it will gradually stabilize after running for a period of time, this situation does not require too much attention; if there is a sudden increase in memory and memory growth does not release after a stable operation, then we need to further analyze what is the cause.

3. Who occupies the memory

In the vast majority of cases, we don't need to pay too much attention to MySQL memory usage; however, we can't rule out the existence of memory footprint anomalies, so how should we conduct in-depth investigation at this time? In fact, MySQL officially provides a powerful real-time monitoring tool-the monitoring memory table under the performance_schema library. Through this tool, we can clearly see who and how much MySQL memory is occupied.

Turn on memory monitoring

Enabled when the instance is started

We can choose to enable the memory monitoring collector when the instance is started, as follows:

Vi my.cnf performance-schema-instrument='memory/%=ON'

The disabling method is as follows:

Vi my.cnf performance-schema-instrument='memory/%=OFF'

Enabled when the instance is running

We can also choose to enable the memory monitoring collector dynamically when the instance is running, as follows:

Mysql > UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE' memory/%'

The disabling method is as follows:

Mysql > UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE' memory/%'

Because the implementation principle of the collector is to update the data of the corresponding memory monitoring table when the memory is allocated / reclaimed; in other words, the collector can only monitor the memory usage after it is enabled; while a large part of the memory of MySQL is pre-allocated when the instance is started, so to accurately monitor the memory utilization of the instance, you need to turn on the memory collector when the instance is started.

Memory monitoring table

Under the performance_ schema library, provide memory monitoring tables for multiple dimensions, as follows:

Memory_summary_by_account_by_event_name: memory monitoring table of account latitude

Memory_summary_by_host_by_event_name: memory monitoring table of host latitude

Memory_summary_by_thread_by_event_name: memory monitoring table of thread dimension

Memory_summary_by_user_by_event_name: memory monitoring table of user latitude

Memory_summary_global_by_event_name: memory monitoring table for global latitude

The memory monitor table includes the following key fields:

COUNT_ALLOC: number of memory allocations

C OUNT_FREE: memory reclaim times

S UM_NUMBER_OF_BYTES_ALLOC: memory allocation size

SUM_NUMBER_OF_BYTES_FREE: memory reclaim size

CURRENT_COUNT_USED: the currently allocated memory, calculated by COUNT_ALLOC-COUNT_FREE

CURRENT_NUMBER_OF_BYTES_USED: the amount of memory currently allocated, calculated by SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE

LOW_COUNT_USED: the minimum value of CURRENT_COUNT_USED

HIGH_COUNT_USED: the maximum value of CURRENT_COUNT_USED

LOW_NUMBER_OF_BYTES_USED: the minimum value of CURRENT_NUMBER_OF_BYTES_USED

HIGH_NUMBER_OF_BYTES_USED: the maximum value of CURRENT_NUMBER_OF_BYTES_USED

Next, let's take a look at the memory usage of a running instance, as follows:

Mysql > select USER,HOST,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,CURRENT_COUNT_USED,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_account_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10 + -+ | USER | HOST | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | CURRENT_COUNT_USED | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | CURRENT_NUMBER_ OF_BYTES_USED | +-+- -+-- + | NULL | NULL | memory/innodb/buf_buf_pool | 32 | 0 | 32 | 4500488192 | 0 | 4500488192 | | NULL | NULL | memory/innodb/os0event | 1573559 | 0 | 1573559 | 214004024 | 0 | 214004024 | | NULL | NULL | memory/innodb/hash0hash | 82 | 6 | 76 | 397976480 | 227067024 | 170909456 | | NULL | NULL | memory/innodb/log0log | 10 | 0 | 10 | 33565840 | 0 | 33565840 | | root | localhost | memory/innodb/std | 3650638 | 3043111 | 607527 | 160778066 | 141334898 | 19443168 | | NULL | NULL | memory/mysys/KEY_CACHE | 3 | 0 | 3 | 8390768 | 0 | 8390768 | | NULL | NULL | memory/innodb/ut0pool | 2 | 0 | 2 | 4194480 | 0 | 4194480 | | NULL | NULL | memory/innodb/sync0arr | 3 | 0 | 3 | 2506184 | 0 | 2506184 | | NULL | NULL | memory/innodb/lock0lock | 33 | 0 | 33 | 2245040 | 0 | 2245040 | | root | localhost | memory/innodb/mem0mem | 9897784 | 9896793 | 991 | 8845389160 | 8843147749 | 2241411 | + -+-+ 10 rows in set (0.01 sec)

If you look at another scenario in Bug # 86821, the maximum memory consumed by buffer pool is normal, but the 3GB occupied by stored procedures is abnormal, and there is a risk of memory leakage. From this, we can quickly locate the problem of abnormal memory usage through the memory monitoring table.

Mysql > select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0 +-+ | event_name | | current_alloc | high_alloc | + +-+-+ | memory/innodb/buf_buf_pool | 7.29 GiB | 7.29 GiB | | memory/sql/sp_head::main_mem_root | 3 .21 GiB | 3.62 GiB | | memory/innodb/hash0hash | 210.16 MiB | 323.63 MiB | | memory/sql/TABLE | 183.82 MiB | 190.28 MiB | | memory/sql/Query_cache | | | 128.02 MiB | 128.02 MiB | | memory/mysys/KEY_CACHE | 64.00 MiB | 64.00 MiB | | memory/innodb/log0log | 32 | .08 MiB | 32.08 MiB | | memory/innodb/parallel_doublewrite | 30.27 MiB | 30.27 MiB | | memory/performance_schema/table_handles | 27.19 MiB | 27.19 MiB | | memory/innodb/mem0mem | | | 19.14 MiB | 20.79 MiB | | memory/performance_schema/events_statements_history_long | 13.66 MiB | 13.66 MiB | | memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB | 9.77 MiB |

In addition, if we see some relatively unfamiliar event in the memory monitor table, we can look through the official documentation or source code and continue to interpret it further, such as

Memory/innodb/os0event

/ * file include/os0event.h The interface to the operating system condition variables Created 2012-09-23 Sunny Bains (split from os0sync.h) * * /

Memory/innodb/hash0hash

/ * * @ file include/hash0hash.h The simple hash table utility Created 5 * * / 20 Heikki Tuuri 1997 * * / IV. Summary.

Generally speaking, as long as our operating system / database has a relatively reasonable configuration (NUMA, swapiness, jemalloc, innodb_buffer_pool_size, etc.), we do not need to pay attention to memory problems in most cases; if we are very unlucky to encounter abnormal memory occupancy problems, we can quickly locate them through the official real-time monitoring tool-memory monitoring table. However, it should be noted that enabling the memory collector will also bring some problems, such as additional memory footprint and performance loss. It is generally recommended to restart the instance after the memory problem occurs in the system and wait for it to be repeated.

This is the end of the content of "what are the knowledge points of database memory". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.