Configure MariaDB and MySQL Galera Replication performance

Configure MariaDB and MySQL Galera Replication performance

1. File Modification
    - Open the `/etc/mysql/mariadb.cnf` or `my.cnf` file (note that `my.cnf` is a soft link to `mariadb.cnf`).
    - Add the following configuration options:

    ```bash
    # Provider specific configuration options
    wsrep_provider_options="gcache.size=1024M;gmcast.segment=0;gcache.recover=yes"

    # Number of threads to process writesets from other nodes (depends on total CPU of the host)
    wsrep_slave_threads=8
    ```

2. Understanding gcache
    - Galera uses a preallocated file called gcache to store writesets in a circular buffer style.
    - The gcache size defines how many writesets the donor node can serve in Incremental State Transfer (IST).
    - You can estimate the downtime you can afford while still being able to perform an IST based on your write stream and gcache size.

3. Improving Replication Performance
    - Increasing the number of slave threads in Galera can improve throughput.
    - The impact is significant enough to be considered more than just a statistical anomaly.

For more detailed information, you can refer to these articles:
 

- Understanding gcache in Galera https://severalnines.com/blog/understanding-gcache-galera/
-  How to improve replication performance in MySQL or MariaDB Galera Cluster  https://severalnines.com/blog/how-improve-replication-performance-mysql-or-mariadb-galera-cluster/

Please remember to handle database credentials securely when using such tools and ensure you have the necessary permissions to modify configuration files. Always backup your configuration files before making any changes.

Refer this for sysctl.conf tuning

https://gist.github.com/linuxmalaysia/0cc0d6218fefb045f867c17eed3af7f8

 

Idea is translate using Google Translate, rewrite by Google Bard, Wording for blog post by ChatGPT and addon content by https://open-assistant.io/chat

Catatan popular daripada blog ini

Strengthening Linux Server Security: OpenSCAP, Lynis, AIDE, SELinux, Fail2ban, Firewalld, and FIPS Mode

I'll do the things the best I know how; I'll do it the best way I can; I'll do them till the end - The Power of Determination: Achieving Success Through Persistence

Safeguarding Yourself From Scammers