1. Troubleshoot memory usage, you can use top or htop to view the mysqld process, and analyze the buffer pool usage status through SHOW ENGINE INNODB STATUS\G and SHOW GLOBAL STATUS LIKE 'Innodb\_buffer\_pool\_pages\_%'; 2. Check whether the configuration items such as innodb\_buffer\_pool\_size, key\_buffer\_size, query\_cache\_size, etc. are reasonable to avoid blindly increasing the buffer size; 3. Determine whether there is a memory leak, you can observe RSS changes, check error logs, upgrade versions, and disable plug-in troubleshooting; 4. Optimization suggestions include regularly checking slow query logs, setting a reasonable maximum number of connections, and enabling Performance Schema monitors, tracks memory trends using Prometheus or Zabbix and sets alarm thresholds.

MySQL memory usage is too high or suspected memory leaks, which are problems that many operations and maintenance and developers will encounter in actual work. Especially when server resources are tight and database responses are slow, troubleshooting MySQL memory status often becomes a key part of optimization. Below are some practical investigation ideas and tuning suggestions.

Check the current memory usage

The most direct way is to view the memory occupied by the mysqld process through top or htop , but this is just surface information. To go deeper, you can go to the MySQL command line to execute the following statement:

 SHOW ENGINE INNODB STATUS\G

In this part of the output, you can see the usage of InnoDB buffer pool, including "Modified DB pages" and "Pages read" and other indicators, which helps determine whether the cache occupies a large amount of memory.

You can also view the global state variables:

 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';

If the Buffer Pool usage is close to 100% over the long term, you may want to consider adjusting configuration or optimizing queries.

Check whether the key configuration items are reasonable

MySQL memory consumption mainly comes from several core configuration parameters:

• innodb_buffer_pool_size : This is the largest header. It is usually recommended to set it to 50%~80% of physical memory, but it also depends on the specific load.
• key_buffer_size : If there are many MyISAM tables, you need to pay attention, otherwise you can keep the default.
• query_cache_type / query_cache_size : Removed from MySQL 8.0. Please turn off the old version to avoid contention.
• tmp_table_size / max_heap_table_size : The temporary table is too large and may also cause memory to soar.
• thread_stack / sort_buffer_size / join_buffer_size : These parameters assigned by each connection are greatly affected by multiplying by the maximum number of connections.

Suggested practices:

• Use the MySQL Tuner tool to assist in analyzing the matching degree of current configuration and system resources.
• Avoid blindly increasing the buffer size, especially when the number of concurrent connections is high.
• Pay attention to whether there are a large number of connections in Sleep state, which may be that the program does not release the connection correctly.

Determine if there is a memory leak

Memory leaks usually show that memory grows slowly, decreases after restarting, and then continues to rise again. To confirm whether it is a leak, you can:

• Observe the trend of the RSS (resident memory) of the mysqld process over time.
• Check the MySQL error log for exception information.
• Upgrade to the latest stable version to see if the problem is mitigated (some older versions do have memory management bugs).
• If plugins are enabled (such as Performance Schema, Audit Plugin), try to disable troubleshooting one by one.

If you suspect that a specific operation or SQL is causing memory growth, you can track it with the memory event in performance_schema :

 SELECT * FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%memory%sql%';

Optimization suggestions and daily monitoring

The following things can be done in daily maintenance to prevent and detect memory problems:

• Regularly check slow query logs, optimize SQL for large result sets or full table scans.
• Set a reasonable maximum number of connections (max_connections) to avoid excessive connections from exhausting resources.
• Enable and periodically view memory statistics in Performance Schema and Information Schema.
• Use monitoring tools (such as Prometheus Grafana, Zabbix) to track memory trends.
• For instances on the cloud, pay attention to whether the automatic scaling policy is timely and whether the appropriate alarm threshold is set.

Overall, the MySQL memory problem is not particularly complicated, but it is easily overlooked. The key is to establish a basic monitoring mechanism and have a clear understanding of configuration parameters. Basically that's all, just adjust it as needed.

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