MySQL memory leaks and swap problems need to be investigated from configuration, system resources, memory leak signs and external factors. 1. Check whether the configurations of innodb_buffer_pool_size, max_connections, etc. are reasonable, and avoid being too large or too small. 2. Use free -m, vmstat 1, top and other commands to observe the system memory and exchange behavior, and adjust the vm.swappiness parameters appropriately. 3. Analyze the error log and Performance Schema data, find the memory continuous growth module, and determine whether there is a memory leak. 4. Review the application layer SQL behavior, optimize inefficient operations such as full table scanning, JOIN, GROUP_CONCAT, etc., and reduce temporary memory overhead.

MySQL memory leaks and swapping problems are not uncommon in production environments, especially in scenarios where data volumes are large and concurrency are high. Once it occurs, it may cause the database to respond slowly or even crash. When encountering such problems, you cannot only look at the surface phenomenon, but check it from multiple perspectives such as configuration, operating status, and system resources.

1. Check the memory-related configuration of MySQL

Many memory problems actually stem from improper configuration. Several key parameters of MySQL determine how much memory it can use, such as:

• innodb_buffer_pool_size : This is the most important memory area of the InnoDB engine. It is usually recommended to set it to 50%~80% of the physical memory, but it should also be adjusted according to the actual load.
• max_connections : The more connections, the more memory it consumes. Each connection will allocate some thread-related memory, such as thread_stack and sort_buffer_size .
• query_cache_type and query_cache_size : Although query caches are removed in MySQL 8.0, if enabled and set too large in older versions, memory waste or contention may also result in memory waste or contention.

Suggested practices:

• Use tools such as mysqltuner.pl or tuning-primer.sh to evaluate whether the current configuration is reasonable.
• Don’t blindly increase the buffer pool, combine load monitoring to determine whether it is really needed.
• If you find that memory usage fluctuates greatly, you can consider enabling innodb_buffer_pool_instances to reduce lock competition.

2. Observe the memory and exchange behavior at the system level

Even if the MySQL configuration looks OK, the system-level memory pressure will trigger swapping. At this time, you can view it through the following command:

• free -m : Check the overall memory usage.
• vmstat 1 : Observe whether there are continuous swaps in and outs in the si/so column.
• top or htop : See if the RES (resident memory) and %MEM of the MySQL process grow abnormally.
• sar -r (if sysstat is installed): View historical memory trends.

Common phenomena:

• Even if there is enough memory, Linux may start swapping because of insufficient "available memory", which is related to Linux's memory management mechanism.
• The vm.swappiness parameter can be adjusted appropriately to reduce unnecessary swap usage (for example, set to 10 or lower).

3. Analyze whether there are any signs of memory leaks

Memory leaks usually manifest as the memory used by MySQL continues to grow over time and cannot be released. In this case, you can start from the following aspects:

• View error logs, especially OOM (Out of Memory)-related information.
• Use tables in Performance Schema or information_schema to analyze whether the operations such as join, temporary table, sort are abnormal.
• Use pmap -x <pid></pid> to view the memory map of the MySQL process and confirm whether there are unknown growth segments.
• If Performance Schema is enabled, you can execute a statement similar to the following to view the memory allocation:

 SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED 
FROM performance_schema.memory_summary_global_by_event_name 
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

If you find that the memory usage of a certain module continues to rise, such as memory/sql/String or memory/innobase , it may be a potential leak point.

4. Consider external factors: Application layer behavior and SQL statements

Sometimes the memory problem is not caused by MySQL itself, but because the application layer sends a large amount of inefficient SQL:

• Using a large amount of full-table scans and no indexes will increase the overhead of sorting and temporary tables.
• When using functions such as GROUP_CONCAT() and DISTINCT to process large data sets, if there is no limit on the length or result size, it may cause memory to skyrocket.
• Applications frequently establish short-lifetime connections, which not only affects performance, but also creates additional memory burden.

Solution suggestions:

• Regularly analyze slow query logs to find SQL that consumes a lot of resources.
• Use EXPLAIN to view the execution plan and make sure the index is used correctly.
• For applications with high concurrent writes, you can consider splitting the business logic to avoid processing too much data at once.

Basically that's it. Memory problems are not as intuitive as CPUs. They are often "summarized into more", especially in database services that run for a long period of time. Regular configuration checks, pay attention to system metrics, and optimizes SQL are the fundamental ways.

The above is the detailed content of Troubleshooting MySQL Memory Leaks and Swapping. For more information, please follow other related articles on the PHP Chinese website!