In this article, I will show you how to use the new version of MySQL (5.7), and how to more easily solve the problems that occur in MySQL memory allocation.
Troubleshooting has never been an interesting task, especially a failure like this where MySQL crashed due to insufficient memory.
First, there are three main situations where MySQL crashes due to insufficient memory:
MySQL tries to allocate more memory than available memory because the value set by the user in the settings is too high. For example: you have not set innodb_buffer_pool_size correctly, this kind of problem is easy to fix.
Other processes running on the server are allocating RAM. For example: it can be some kind of application (Java, Python, PHP), web server, or even a backup (ie mysqldump). When the source of the problem is determined, it can be repaired directly.
Memory leak in MySQL. This is the worst case and we need to troubleshoot.
Where to start troubleshooting MySQL memory leaks
Here are the steps we can start with ((assuming it is a Linux server)):
Part 1: Linux operating system and configuration check
1. Determine the cause of mysql crash by checking the MySQL error log and Linux log files (such as /var/log/messages or /var/log/syslog). For example: You may see a log entry saying that the OOM program killed the MySQL process. Whenever the MySQL process is killed by OOM "dmesg", the relevant details of the surrounding environment will also be displayed in the log.
2. Check the amount of available memory:
free -g
cat /proc/meminfo
3. avCheck checks which applications are using RAM: "top" or "htop" (see resident memory and virtual memory).
4. Check the MySQL configuration: check /etc/ MySQL .cnf or general /etc/my* (including /etc/mysql/* and other files).
MySQL may run with different my.cnf (run ps ax| grep MySQL)
5. Run vmstat 5 to see if the system is reading and writing through virtual memory and whether it is swapping
6. For non-production environments, we can use other tools (such as Valgrind, gdb, etc.) to check the usage of MySQL
Part 2: Check inside MySQL
Now, we can check the contents of MySQL to find potential MySQL memory leaks.
MySQL allocates memory in many places, especially:
Table cache
Performance_schema (Run the command: show engine performance ce_schema status, and check the last line, this may be the reason for the system to reduce RAM (ie 1G or less))
InnoDB (Run show engine InnoDB status and check the buffer pool part, memory allocated for buffer_pool and related caches)
Temporary tables in RAM (execute the query select * from information_schema.tables where engine='MEMORY' to find all temporary tables in memory)
Prepare a specific SQL statement, when it is not released (check the number of prepared commands through the deallocate command, run the statement that displays global variables:'Com_prepare_sql'; show global status like'Com_dealloc_sql')
The good news is that as of MySQL 5.7, there is memory allocation in performance_schema. Here is how we use it:
1. First, we need to enable collecting memory metrics.
run:
UPDATE setup_instruments SET ENABLED ='YES'WHERE NAME LIKE'memory/%';
2. Run the report from sys mode:
select event_name, current_alloc, high_allocfrom sys.memory_global_by_current_byteswhere current_count> 0;
3. Normally, this will give you the location in the code when allocating memory. It is usually self-explanatory. In some cases, we can search for bugs, or need to check the MySQL source code.
The largest block of memory is usually the buffer pool, but the 3G in the storage process seems too high.
According to the MySQL source code documentation, sp_head represents an instance of a stored procedure, which can be of any type (stored procedure, function, trigger, event). In the above example, we have a potential memory leak.
I hope these simple steps can help solve MySQL crash due to insufficient memory
