Shulou(Shulou.com)06/01 Report--

Troubleshooting will not be interesting for everyone, especially if there is no crash report. What should I do if MySQL crashes due to insufficient memory? Peter Zaitsev gave a lot of useful tips in a blog post in 2012, and with the new version of MySQL (5.7and above) and performance_schema, we can more easily solve the MySQL memory allocation problem.

This article will show you how to solve the MySQL memory allocation problem.

First, let's take a look at the situations in which MySQL crashes due to insufficient memory:

When MySQL tries to allocate more memory than available memory, for example, innodb_buffer_pool_size is not set correctly

There are other processes on the server that can assign RAM, such as its application (java,python,php), Web server, or backup (that is, mysqldump).

Memory leak in MySQL. This is the worst-case scenario and we need to troubleshoot it.

These are the three common situations in which MySQL crashes due to insufficient memory, the first two of which are easier to solve, while the third is tricky.

Where to start troubleshooting MySQL memory leaks

Assuming this is a Linux server, first we need to check the Linux operating system and configuration

Check the mysql error log and Linux log files (that is, / var / log / messages or / var / log / syslog) to identify the crash. You may see the entry where OOM Killer kills MySQL, and you can use "dmesg" to display the relevant details.

Check the available RAM: free-g cat / proc / meminfo

Check which applications are using RAM: "top" or "htop"

Check the mysql configuration: / etc/my.cnf or general / etc/my* (including / etc/mysql/*, etc.). MySQL may be running a different my.cnf (run ps ax | grep mysql)

Run vmstat 5 5 to see if the system is reading / writing through virtual memory and swapping

For non-production environments, we can use other tools (such as Valgrind,gdb, etc.) to check MySQL usage.

Check the inside of MySQL

We can also detect potential MySQL memory leaks by examining the inside of MySQL. MySQL has memory allocation in many places, especially in the following situations:

Now we can examine what's inside MySQL to look for potential MySQL memory leaks.

MySQL allocates memory in many places. In particular:

Table cache

Performance_schema (run: show engine performance_schema status and view the last line).

InnoDB (run show engine innodb status and check the buffer pool portion, memory allocated for buffer_pool and related caches)

Temporary tables in RAM (find all memory tables by running the following statement: select * from information_schema. Tables where engine = 'MEMORY')

Prepared statements .

However, starting with MySQL version 5. 7, we can see the memory allocation in performance_schema. So, how to use it?

First, we need to enable the collection of memory metrics. Run:

UPDATE setup_instruments SET ENABLED = 'YES'WHERE NAME LIKE' memory/%'

Sys schema runs report:

Select event_name, current_alloc, high_allocfrom sys.memory_global_by_current_byteswhere current_count > 0

Typically, code is provided when memory is allocated, so in some cases we may need to check the MySQL source code when searching for certain errors. For example, for an error that overallocates memory in a trigger:

In some cases, when searching for certain errors, we may need to check the MySQL source code. For example, for an error that overallocates memory in a trigger:

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

The largest block in RAM is usually the buffer pool, but 3G in stored procedures also seems to be too high.

According to the MySQL source code documentation, SPHead represents an instance of a stored program that can be of any type (stored procedures, functions, triggers, events). In this case, there is a potential memory leak. In addition, if we want to know more about MySQL memory, we can get a higher-level general report.

Mysql > select substring_index (- > substring_index (event_name,'/', 2),->'/',->-1->) as event_type,-> round (sum (CURRENT_NUMBER_OF_BYTES_USED) / 1024,2) as MB_CURRENTLY_USED-> from performance_schema.memory_summary_global_by_event_name-> group by event_type-> having MB_CURRENTLY_USED > 0 +-+-+ | event_type | MB_CURRENTLY_USED | +-+-+ | innodb | 0.61 | | memory | | 0.21 | | performance_schema | 106.26 | | sql | 0.79 | +-+-+ 4 rows in set (0.00 sec) |

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.