I have a MariaDB Galera 3-Node Cluster as the DB system but since I built, it is working with the default parameters. I only changed the parameter as max_connections=3000. It has been about 6 months that the system is live with a r/w connection traffic about 100-500 connections per seccond, but the problem is one of the nodes goes down and doesn't serve until I restart it's MariaDB service. It happens 1-10 times for a week, so as you understand I am like a baby sitter for the database.
Since I am not an database expert(I am an Java developer) but it has been assigned to me to build an database system, I could not build an reliable and high performance system with default parameters. After some research, I found to update and add some parameters to the my.cnf configuration file for a better performance but I am not sure about the values. Before I update that, I want to ask there to database experts. Please share your knowledge with me.
The Database Server's properties are:
- Ram: 8gb (Can not be higher)
- HDD: 500GB (Can not be higher or updated as SDD)
- CPU: 8 Core (only 1 cpu)
- OS: Ubuntu Server 20.04
- MariaDB 10.4.21
- Cluster size: 3 Node, MASTER-MASTER Replication, so there are 3 masters
There is no a load balancer like HAProxy, it was not allowed to use a load balancer as an extra server. I only configured Keepalived on Node1 and Node2 and it routes all connections to the Node1 when it is available, otherwise if Node1 is not available or reachable it routes to the Node2. So Node1 is primary, Node2 is secondary servant. Node3 is not used by the users, it is pasif and I start the cluster from this node with galera_new_cluster.
All my.cnf file parameters are as the defaults. I only updated and added the parameters which mentioned below. I want help about these parameters and their values if they are good enough for my system or not:
I updated these parameters: (parameter_name = defaultValue -> updatedValueByMe)
max_connections = 100 -> 3000 innodb_buffer_pool_size = 256M -> 5G innodb_log_buffer_size = 8m -> 128M innodb_io_capacity = 400 -> 4000
I also added these new parameters:
[mysqld] innodb_read_io_threads = 8 innodb_write_io_threads = 6 innodb_log_file_size = 512M thread_handling = pool-of-threads thread_stack = 128K thread_pool_size = 8 thread_pool_oversubscribe = 3 [galera] wsrep_slave_threads = 6 wsrep_provider_options = "gcache.size=1G;gcache.page_size=1G;"
I also want to share my galera configurations on Node1. It may help to understand the system better:
[galera] binlog_format=ROW default-storage-engine=InnoDB innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="XCluster" wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3" wsrep_sst_method=mariabackup wsrep_sst_auth=sstuser:sstuser wsrep_node_address="10.0.0.1" wsrep_node_name="Node1"
Are all values right or is there any unusual values you noticed?
show global status, if it is available. FYI 10.4.22 with a number of Galera fixes (not necessary yours) is out any day now.
show global statusafter a non-trivial amount of uptime.
global statusvariables related to