Скачать или смотреть Addressing Lack of Swap Memory in a MariaDB 10.4 Galera Cluster

Discover effective solutions for managing swap memory in your MariaDB 10.4 Galera cluster, including configuration recommendations and performance insights.
---
This video is based on the question https://stackoverflow.com/q/66917847/ asked by the user 'madial9663' ( https://stackoverflow.com/u/14207101/ ) and on the answer https://stackoverflow.com/a/66959319/ provided by the user 'Rick James' ( https://stackoverflow.com/u/1766831/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Lack of swap memory on Mariadb 10.4 Galera cluster

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Swap Memory Issues in MariaDB 10.4 Galera Cluster

When managing a MariaDB Galera cluster, ensuring optimal performance and memory management can be challenging. A common scenario involves running out of swap memory, which can lead to degraded performance or application failures. In this post, we will discuss a specific case involving a 3-node Galera cluster with MariaDB 10.4.

The user reported that their system had 32GB of RAM and 2GB of Swap. Even though the RAM was nearly full, the swap memory size had reached its maximum and showed no signs of decreasing, raising concerns about server performance and stability.

Analyzing the Current Configuration

The user shared their MySQL configuration file (my.cnf), which provided insights into the current settings. Here are some notable options that were active:

InnoDB Settings:

innodb_buffer_pool_size=24000M: This setting determines the memory allocated for InnoDB buffer pool.

innodb_log_file_size=6144M: Size of log files that InnoDB uses.

Query Cache and Buffers:

query_cache_size=512M: Indicates potential for significant overhead as it can lead to slowdowns.

Observations:

The maximum swap usage indicated potential issues with memory allocation and management.

A high query cache size without efficient usage could exacerbate performance problems.

Recommendations for Configuring MariaDB

Given the analysis, here are some targeted recommendations to optimize performance and possibly resolve the high swap usage:

1. Review and Optimize Memory Settings

Lower table_open_cache: Reduce the setting

[[See Video to Reveal this Text or Code Snippet]]

Adjust temporary tables:
Decrease the tmp_table_size and max_heap_table_size from 1024M to about 200M each:

[[See Video to Reveal this Text or Code Snippet]]

Disable Query Cache:
Set query_cache_size to 0 to eliminate slowdowns:

[[See Video to Reveal this Text or Code Snippet]]

Adjust Connection Preferences:
Modify the max_connections and thread_cache_size:

[[See Video to Reveal this Text or Code Snippet]]

2. Reevaluate InnoDB Settings

Buffer Pool Instances:
If you're using a significant amount of RAM, consider reducing innodb_buffer_pool_instances to improve efficiency:

[[See Video to Reveal this Text or Code Snippet]]

Io Capacity Settings:
If employing SSDs, increase innodb_io_capacity significantly, so it better matches I/O capabilities:

[[See Video to Reveal this Text or Code Snippet]]

3. Monitoring and Analysis

Regular performance monitoring is essential. Look for patterns such as:

High frequency of SHOW commands which may indicate an aggressive monitoring tool.

Unusually high numbers of GRANT commands affecting performance.

Monitoring max_used_connections to ensure you’re not reaching limits that can impact other operations.

4. General Recommendations

Uptime Analysis:
Ensure your server has been up long enough to gather reliable metrics. Frequent restarts may skew your metrics.

Evaluate Disk Performance:
Check if your configuration is suited for HDD or if you're using SSDs; adjust configurations accordingly.

Consider Swap Use:
If swap continues to be an issue, consider adjusting settings or even disabling swap usage in certain cases, as it can potentially lead to performance degradation.

Conclusion

By carefully reviewing and adjusting your MariaDB settings, you can significantly improve both memory management and overall performance, minimizing swap usage. It is essential to routinely monitor performance metrics after implementing changes to ensure that you achieve the desired outcome. Implementing these changes should help your MariaDB 10.4 Galera cluster run more efficiently, alleviating the swap memory concerns you have encou