Troubleshooting MySQL 5.7 OOM

He recently reviewed Mr. Jin Yong's Swordsman three times and felt a lot of emotion. Many questions about work, management, life, and learning can be traced to the ground, which is a kind of sentiment that cannot be understood when you are young. For example:

Feng Qingyang said: "In the case of martial arts, these magic elders cannot say that they have truly begun to learn martial arts. They don't know how to do it. The tricks are dead, but the recruiters are active. The dead tricks are even better. When we encounter active tricks, we will inevitably have to put our hands to death. You must remember the word "active. You need to learn from each other, and to learn from each other. If you are stuck, you will be familiar with tens of millions of tricks. When you encounter a real master, you will eventually be able to give it to others ."

Today, let's talk about the diagnosis of out of memory in MySQL. Previously, the positioning of such problems was not easy for common users. However, in MySQL 5.7, it is extremely easy to locate the OOM problem. If you are not familiar with it, let's take a look. Generally, a similar log prompt can be found in the system log when OOM occurs:

The database performance_schema of MySQL 5.7 adds the following tables to view memory consumption from various dimensions:

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
To put it simply, the memory can be monitored based on the user, host, thread, account, and global dimensions. At the same time, the library sys further formatted these tables so that users can easily observe the memory overhead of each object:

Mysql> select event_name, current_alloc
-> From memory_global_by_current_bytes limit 10;
+ ---------------------------------------------------------------------------- + --------------- +
| Event_name | current_alloc |
+ ---------------------------------------------------------------------------- + --------------- +
| Memory/performance_schema/events_statements_history_long | 13.66 MiB |
| Memory/performance_schema/events_statements_history_long.sqltext | 9.77 MiB |
| Memory/performance_schema/events_statements_history_long.tokens | 9.77 MiB |
| Memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB |
| Memory/performance_schema/table_handles | 9.00 MiB |
| Memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.80 MiB |
| Memory/performance_schema/memory_summary_by_thread_by_event_name | 5.62 MiB |
| Memory/performance_schema/events_statements_summary_by_digest | 4.88 MiB |
| Memory/performance_schema/events_statements_summary_by_user_by_event_name | 4.40 MiB |
| Memory/performance_schema/events_statements_summary_by_account_by_event_name | 4.40 MiB |
+ ---------------------------------------------------------------------------- + --------------- +
10 rows in set (0.00 sec)
Careful students may find that performance_schema only performs memory overhead statistics on performance_schema by default. However, when diagnosing OOM, you need to monitor the memory of all possible objects. Therefore, you need to make the following settings:

Mysql> update performance_schema.setup_instruments
-> Set enabled = 'yes' where name like 'memory % ';
Query OK, 310 rows affected (0.00 sec)
Rows matched: 380 Changed: 310 Warnings: 0

Mysql> select * from performance_schema.setup_instruments where name like 'memory % innodb % 'limit 5;
+ ----------------------------------------- + --------- + ------- +
| NAME | ENABLED | TIMED |
+ ----------------------------------------- + --------- + ------- +
| Memory/innodb/adaptive hash index | YES | NO |
| Memory/innodb/buf_buf_pool | YES | NO |
| Memory/innodb/dict_stats_bg_recalc_pool_t | YES | NO |
| Memory/innodb/dict_stats_index_map_t | YES | NO |
| Memory/innodb/dict_stats_n_diff_on_level | YES | NO |
+ ----------------------------------------- + --------- + ------- +
5 rows in set (0.00 sec)
However, this method of enabling memory statistics online is only valid for newly added memory objects:

Mysql> select event_name, current_alloc from memory_global_by_current_bytes
-> Where event_name like '% innodb % ';
+ ------------------------ + --------------- +
| Event_name | current_alloc |
+ ------------------------ + --------------- +
| Memory/innodb/mem0mem | 36.52 KiB |
| Memory/innodb/trx0undo | 704 bytes |
| Memory/innodb/btr0pcur | 271 bytes |
+ ------------------------ + --------------- +
3 rows in set (0.01 sec)
To perform memory statistics on objects in the global lifecycle, you must set the parameters in the configuration file and restart the system:

[Mysqld]
Performance-schema-instrument = 'memory/% = counted'

Mysql> select event_name, current_alloc from memory_global_by_current_bytes limit 5;
+ ---------------------------- + --------------- +
| Event_name | current_alloc |
+ ---------------------------- + --------------- +
| Memory/innodb/os0file | 1.42 GiB |
| Memory/innodb/buf_buf_pool | 1.05 GiB |
| Memory/innodb/os0event | 51.15 MiB |
| Memory/innodb/hash0hash | 41.44 MiB |
| Memory, innodb, and log0log | 32.01 MiB |
+ ---------------------------- + --------------- +
5 rows in set (0.00 sec)
Based on the above results, have some friends found suspicious memory usage? The memory/innodb/os0file object uses 1.42G memory, while the Buffer Pool of the entire database instance is only 1.05 GB. Then you can go to bugs.mysql.com to search. Sure enough, it is an official bug and has been fixed in 5.7.14. In a similar way, Inside has located five OOM problems. Of course, here, the Inside gentleman just throws out a thought to learn and use it, in order to reach the realm of no-action or no-action.