Solutions to MySQL OOM (memory overflow)

OOM stands for "Out Of Memory", which means memory overflow.

Memory overflow has been a long-standing problem in software development for nearly 40 years. When running various software on the operating system, the memory required by the software far exceeds the size that the physical memory can bear, which is called memory overflow.

There are many reasons for memory overflow. When the memory is seriously insufficient, the kernel has two options:

1. Direct panic
2. Kill some processes to free up some cores.
   

In most cases, the process that caused the OOM will be killed and the system will recover. Usually we will add monitoring alarms for memory. For example, when the memory or swap usage exceeds 90%, an alarm notification is triggered and timely intervention is required.

If OOM has occurred, you can view it through the dmesg command. CentOS7 and above versions support the -T option, which can convert the timestamp into time format, making it easier to view the specific time:

[root@localhost ~]# free -m total used free shared buffers cachedMem: 128937 128527 409 1 166 1279-/+ buffers/cache: 127081 1855Swap: 16383 16252 131

The logs can show information such as which processes are running and how much memory they occupy, and processes that occupy a large amount of memory will be killed.

Troubleshooting memory issues

1. Operating system memory check

Taking MySQL as an example, after OOM, the mysqld process is killed and the memory will be released. The mysqld_safe process will start mysqld, and the system memory viewed at this time will be a normal value. If the memory usage is high but OOM has not occurred, the system memory usage may be as follows:

[root@localhost ~]# free -m total used free shared buffers cachedMem: 128937 128527 409 1 166 1279-/+ buffers/cache: 127081 1855Swap: 16383 16252 131

It can be seen that the memory usage is already very high at this time. The physical memory and swap virtual memory are almost used up. There are not many buffers and caches. OOM may occur at any time.

First, use top to view the process that takes up the most memory:

shift+o can select the sorting method, and n represents %MEM.

[root@localhost ~]# topMem: 132031556k total, 131418864k used, 612692k free, 212104k buffersSwap: 16777212k total, 0k used, 16777212k free, 14648144k cached
 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND14920 mysql 20 0 125g 109g 6164 S 6.6 87.0 27357:08 mysqld 

It can be seen that the mysqld process occupies the largest memory, which can also be checked like this:

130738976 2017 mysql 50032070 mysqld_safe /bin/sh /export/servers/mys 0.0 296 106308 2017 root 0

RSZ is the size of private memory occupied by the process, in Kb.
VSZ is the mapped virtual memory size in Kb.

The total memory usage ratio can also be calculated through RSZ/total.

2. Check the memory allocated to mysql

The main memory inside MySQL can be found through the following statement:

MYSQL >SET @giga_bytes = 1024*1024*1024;SELECT (@@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + (select count(HOST) from information_schema.processlist)/*@@max_connections*/*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)) / @giga_bytes AS MAX_MEMORY_GB;

Each parameter configures the size:

*************************** 1. row ****************************** * @@key_buffer_size: 67108864 @@query_cache_size: 0 @@tmp_table_size: 268435456 @@innodb_buffer_pool_size: 38654705664 @@innodb_additional_mem_pool_size: 134217728 @@innodb_log_buffer_size: 8388608 @@max_connections: 3000 @@read_buffer_size: 4194304 @@read_rnd_buffer_size: 4194304 @@sort_buffer_size: 2097152 @@join_buffer_size: 2097152 @@binlog_cache_size: 32768 @@thread_stack: 262144

Configuration description of each parameter:

How much memory you need to allocate to MySQL is directly related to the above parameters. If it is too large, it will lead to insufficient memory, and if it is too small, it will affect performance. How to allocate a reasonable value depends on the business situation. However, there are many business scenarios, and each business configuration is different, which will result in high operation and maintenance costs. Therefore, it is enough to customize a set of configuration templates that are suitable for most scenarios.

1. If the memory allocated by MySQL is larger than the system memory

For example, if the system memory is 128G, the memory allocated to MySQL is already greater than 128G, but the system itself and other programs also need memory, even mysqldump also needs memory, so it is easy to cause insufficient system memory, resulting in OOM. At this time, we need to find out which parameters are set too large and reduce memory allocation appropriately.

innodb_buffer_pool occupies the largest memory in MySQL. Reducing innodb_buffer_pool_size can effectively reduce OOM problems. However, if the value is set too small, the frequency of dirty page flushing in memory will increase, IO will increase, and performance will be reduced. We usually consider innodb_buffer_pool_size to be optimal at 60%~75% of system memory.

Check the usage of buffer_pool:

MYSQL >select POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG from information_schema.INNODB_BUFFER_POOL_STATS;+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+| POOL_ID | POOL_SIZE | FREE_BUFFERS | DATABASE_PAGES | OLD_DATABASE_PAGES | MODIFIED_DATABASE_PAGES | PAGES_MADE_YOUNG | PAGES_NOT_MADE_YOUNG |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+| 0 | 611669 | 1024 | 610644 | 225393 | 0 | 309881 | 0 || 1 | 611669 | 1024 | 610645 | 225393 | 0 | 309816 | 0 || 2 | 611669 | 1024 | 610645 | 225393 | 0 | 309756 | 0 |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+

It can be seen that buffer_pool is divided into 3 instances, POOL_SIZE is the size of each instance, here is the number of pages. We know that the default size of MySQL page is 16K, so the actual size of a single instance is 611669*16K. After 5.6, FREE_BUFFERS is required to retain at least 1024 pages. If there are less than 1024 pages, dirty data will be forced to be flushed. The following value can show the dirty page situation. In addition, if PAGES_MADE_YOUNG is much larger than PAGES_NOT_MADE_YOUNG, then the memory usage may be relatively large, and the innodb_buffer_pool_size can be appropriately reduced.

Another article also introduces buffer_pool: One command to interpret the InnoDB storage engine—show engine innodb status

If innodb_buffer_pool_size is not very large but the memory usage is still very high, it may be due to too many concurrent threads. You need to confirm whether it is an application anomaly or whether you need to adjust max_connections. If there are too many connections, each connection will occupy independent memory. The read, sort, and join caches are all at the session level. The more connections there are, the more memory is required, so these parameters cannot be set too large.

It should be noted that some parameters do not support dynamic modification. They can only take effect by modifying the configuration file and then restarting MySQL. Therefore, before starting MySQL, be sure to confirm the parameter value.

2. If the memory allocated by MySQL is smaller than the system memory

If the MySQL parameter settings are reasonable but OOM still occurs, it may be due to insufficient memory required by MySQL at the system level. Because when MySQL reads a table, if multiple sessions reference a table at the same time, multiple table objects will be created. Although this reduces the contention for internal table locks, it will increase memory usage.

First, you can use lsof -p pid to view the number of system files opened by the process, where pid is the process ID of mysqld.

[root@localhost ~]# ps -ef | grep mysqld[root@localhost ~]# lsof -p 3455COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 30012 mysql cwd DIR 8,3 12288 58982404 /mysql/datamysqld 30012 mysql mem REG 8,1 599392 272082 /lib64/libm-2.12.somysqld 30012 mysql mem REG 8,1 91096 272089 /lib64/libz.so.1.2.3mysqld 30012 mysql mem REG 8,1 93320 272083 /lib64/libgcc_s-4.4.7-20120601.so.1mysqld 30012 mysql mem REG 8,1 43392 272095 /lib64/libcrypt-2.12.somysqld 30012 mysql 10uW REG 8,3 536870912 59015176 /mysql/data/ib_logfile0mysqld 30012 mysql 11uW REG 8,3 536870912 59015177 /mysql/data/ib_logfile1mysqld 30012 mysql 12uW REG 8,3 536870912 59015178 /mysql/data/ib_logfile2mysqld 30012 mysql 13uW REG 8,3 675282944 59001816 /mysql/data/test/table6.ibdmysqld 30012 mysql 14uW REG 8,3 2155872256 58985613 /mysql/data/test/table487.ibdmysqld 30012 mysql 15u REG 8,3 0 58982414 /mysql/tmp/ibhNDzPM (deleted)mysqld 30012 mysql 16uW REG 8,3 2306867200 58983861 /mysql/data/test/table327.ibdmysqld 30012 mysql 17uW REG 8,3 4169138176 58985467 /mysql/data/test/table615.ibdmysqld 30012 mysql 18uW REG 8,3 79691776 59020641 /mysql/data/test/table_v199_20170920.ibdmysqld 30012 mysql 19uW REG 8,3 67108864 59015043 /mysql/data/test/table_v39_20170920.ibdmysqld 30012 mysql 20uW REG 8,3 75497472 59014992 /mysql/data/test/table_v7_20170920.ibdmysqld 30012 mysql 21uW REG 8,3 83886080 59019735 /mysql/data/test/table_v167_20170920.ibdmysqld 30012 mysql 22uW REG 8,3 1367343104 58997684 /mysql/data/popfin6/table_uuid6.ibdmysqld 30012 mysql 23uW REG 8,3 1275068416 58984491 /mysql/data/test/table_uuid7.ibd...[root@localhost ~]# lsof -p 3455 |grep ibd|wc -l54869

View the limit on the number of open files for the MySQL service:

MySQL >show global variables like 'open_files_limit';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 65535 |+------------------+-------+

Check the operating system's open file limit:

[root@localhost ~]# ulimit -amax memory size (kbytes, -m) unlimitedopen files (-n) 65535

If there are many files opened at this time, a lot of memory will be occupied.

Secondly, you need to look at table_open_cache. When a table is opened, the file descriptor of the table will be cached.

MYSQL >show global variables like 'table_open_cache';+------------------+-------+| Variable_name | Value |+------------------+-------+| table_open_cache | 16384 |+------------------+-------+MYSQL >show global status like '%open%tables%';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| Open_tables | 16384 || Opened_tables | 401374 |+------------------------+--------+

The above two values ​​are used to determine whether table_open_cache has reached a bottleneck.
When the value of open_tables in the cache is close to the value of table_open_cache, it means that the table cache pool is almost full, but Opened_tables is still growing, which means that there are still many tables that have not been cached.

Use the show open tables from schema command to view the cached tables in table_open_cache. Only one table is displayed for repeated opening.

MYSQL >show open tables from sysbenchtest;+--------------+----------+--------+-------------+| Database | Table | In_use | Name_locked |+--------------+----------+--------+-------------+| sysbenchtest | sbtest1 | 1 | 0 || sysbenchtest | sbtest2 | 0 | 0 || sysbenchtest | sbtest3 | 0 | 0 || sysbenchtest | sbtest4 | 0 | 0 || sysbenchtest | sbtest5 | 0 | 0 |

In_use shows the number of threads currently using this table. If it is greater than 0, it also means that this table is locked.

Name_locked only applies to DROP and RENAME. When you execute DROP or RENAME, the table file descriptor in table_open_cache will be removed, so you will not see any value other than 0.

Generally, when there are many libraries and tables (divided libraries and tables), it is easy for the memory usage to be large. If you want to solve the root cause, you still need to split the library table.

3. Other memory in MYSQL

All tables under information_schema use the MEMORY storage engine. Data is only retained in memory, loaded at startup, and released after shutdown.

Check whether there is a MEMORY engine table other than the system library:

MySQL >select * from information_schema.tables where engine='MEMORY' and TABLE_SCHEMA !='information_schema';

If your business uses the MEMORY storage engine, try to change it to the innodb engine.

4. MYSQL event memory indicators

Starting from MySQL 5.7, memory allocation is recorded in performance_schema.

Check which metrics have memory collection enabled:

MySQL >select * from performance_schema.setup_instruments where NAME LIKE 'memory/%';

Start collecting memory metrics:

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

The memory collection results of the indicator will be summarized in the view under the sys library:

MySQL root@[sys]>show tables like 'memory%';+-----------------------------------+| Tables_in_sys (memory%) |+-----------------------------------+| memory_by_host_by_current_bytes || memory_by_thread_by_current_bytes || memory_by_user_by_current_bytes || memory_global_by_current_bytes || memory_global_total |+-----------------------------------+

These views summarize memory usage, grouped by event type, in descending order by default:

MySQL >select event_name,current_count,current_alloc,high_alloc from sys.memory_global_by_current_bytes where current_count > 0;+--------------------------------------------------------------------------------+---------------+---------------+-------------+| event_name | current_count | current_alloc | high_alloc |+--------------------------------------------------------------------------------+---------------+---------------+-------------+| memory/performance_schema/table_handles | 10 | 90.62 MiB | 90.62 MiB || memory/performance_schema/events_statements_summary_by_thread_by_event_name | 3 | 26.01 MiB | 26.01 MiB || memory/performance_schema/memory_summary_by_thread_by_event_name | 3 | 16.88 MiB | 16.88 MiB || memory/performance_schema/events_statements_history_long | 1 | 13.66 MiB | 13.66 MiB || memory/performance_schema/events_statements_history | 3 | 10.49 MiB | 10.49 MiB || memory/performance_schema/events_statements_current | 3 | 10.49 MiB | 10.49 MiB |...

Summarize:

Through the above investigation, we can roughly know which areas occupy more memory, and then make specific optimizations for the areas that occupy more memory. As mentioned at the beginning of the article, memory overflow has been a "long-standing" problem in the history of software development for nearly 40 years. What's more, the database environment is more complex. Factors such as SQL syntax, data type, and data size are all related to memory, so you should think more about memory overflow problems in design and use.

The above is the detailed content of the solution to MySQL OOM (memory overflow). For more information on the solution to MySQL OOM (memory overflow), please pay attention to other related articles on 123WORDPRESS.COM!