Here is the translation of your text into American English:
—
When MySQLTuner recommends “Reduce your overall MySQL memory footprint for system stability”, it means that MySQL is consuming too much memory, which can affect system stability. To improve performance and avoid stability issues, you can follow the strategies below:
1. Review Current Memory Usage
Before making changes, it’s important to check how much memory MySQL is using and how it’s configured. Run the following commands in MySQL to obtain information:
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
2. Reduce max_connections if Necessary
The max_connections value defines how many simultaneous connections MySQL can handle. If it’s set too high, it can lead to high memory consumption. If the maximum number of connections in use (Max_used_connections) is much lower than the set limit, you can reduce it.
Check the maximum connection usage:
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
If the value of Max_used_connections is, for example, 30, but max_connections is set to 500, then there’s an over-allocation of memory.
✅ Solution: Reduce the max_connections value in my.cnf:
[mysqld]
max_connections = 100
You can also adjust it in real-time without restarting MySQL:
SET GLOBAL max_connections = 100;
3. Adjust innodb_buffer_pool_size
This parameter controls the amount of memory used by InnoDB to store cached data. Generally, it should occupy around 60%-70% of the available RAM on a server dedicated to MySQL.
View the current value:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
If MySQL is consuming too much memory, you can reduce this value in my.cnf:
[mysqld]
innodb_buffer_pool_size = 2G # Adjust based on database size
If you have multiple small databases, you can split the buffer into several instances:
innodb_buffer_pool_instances = 2
4. Adjust tmp_table_size and max_heap_table_size
When MySQL handles large temporary tables, it stores the data in memory until it reaches the limit defined by tmp_table_size and max_heap_table_size. If these variables are too large, they can generate high memory consumption.
View the current value:
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
If they are set too high (for example, 256M or more), reduce them in my.cnf:
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
You can also apply it temporarily:
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;
5. Adjust table_open_cache and table_definition_cache
If there are too many open tables, MySQL may consume more memory than necessary.
View the current configuration:
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'table_definition_cache';
Reduce values if they are too high:
[mysqld]
table_open_cache = 2000
table_definition_cache = 1500
Apply changes in real-time:
SET GLOBAL table_open_cache = 2000;
SET GLOBAL table_definition_cache = 1500;
6. Adjust Connection Buffers (sort_buffer_size, join_buffer_size)
Sort and join buffers can consume significant memory, especially when there are many active connections.
View current values:
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
Reduce if they are too high:
[mysqld]
sort_buffer_size = 2M
join_buffer_size = 2M
Apply changes in real-time:
SET GLOBAL sort_buffer_size = 2M;
SET GLOBAL join_buffer_size = 2M;
7. Review query_cache_size (If Used)
The query_cache_size can affect MySQL performance and generate high memory usage if enabled in environments with many writes.
Check if it’s enabled:
SHOW VARIABLES LIKE 'query_cache_type';
SHOW VARIABLES LIKE 'query_cache_size';
If it’s enabled (query_cache_type = ON) and there are many writes to the database, it is recommended to turn it off:
[mysqld]
query_cache_type = OFF
query_cache_size = 0
Apply it in real-time:
SET GLOBAL query_cache_type = OFF;
SET GLOBAL query_cache_size = 0;
8. Monitor Memory Usage in Real-Time
To check how much memory MySQL is using in real-time, you can use:
SELECT * FROM sys.memory_global_total;
Or on Linux servers:
ps aux | grep mysqld
For a more detailed view, you can use MySQLTuner or MyTop:
mysqltuner
mytop -u root -p
Conclusion
Reducing MySQL’s memory consumption is key to improving system stability and avoiding performance issues. The main adjustments include:
✅ Reducing max_connections if there is an excess of unused connections.
✅ Adjusting innodb_buffer_pool_size to avoid overloading RAM.
✅ Limiting tmp_table_size and max_heap_table_size to prevent MySQL from using too much memory on temporary tables.
✅ Optimizing table_open_cache, table_definition_cache, and query_cache_size.
✅ Adjusting connection buffers sort_buffer_size and join_buffer_size to reduce unnecessary consumption.
By implementing these changes and regularly monitoring memory usage, you can optimize MySQL’s performance and stability. 🚀