System Configuration¶
Length: 964 words; Writer: Staff; Published: October 20, 2014; Topic: General; Level: Beginner
After you finish installing Galera Cluster on your server, you’re ready to configure the database itself to serve as a node in a cluster. To do this, you’ll need to edit the MySQL configuration file.
Using a text editor, edit the /etc/my.cnf file. You’ll need to include entries like the ones shown in this sample excerpt:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="example_cluster"
wsrep_cluster_address="gcomm://IP.node1,IP.node2,IP.node3"
wsrep_sst_method=rsync
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Depending on your system and the location of your installation of MySQL or MariaDB, you will need to adjust the valuables for variables (e.g., the path to the data directory).
Configuring the Database Server
In addition to settings for the system, there are other basic configurations that you will need to set in the /etc/my.cnf file. Make these changes before starting the database server.
First, make sure that mysqld is not bound to 127.0.0.1. This is the IP address for localhost. If the bind-address variable is in the file, comment it out by adding a hash sign (i.e., #) at the start of the line:
# bind-address = 127.0.0.1
Next, ensure the configuration file includes the conf.d/ by adding a line with !includedir at the start, followed by the file path:
!includedir /etc/mysql/conf.d/
Now, set the binary log format to use row-level replication, as opposed to statement-level replication. You’d do this by adding the following line:
binlog_format=ROW
Don’t change this value later as it affects performance and consistency. The binary log can only use row-level replication for Galera Cluster.
Galera Cluster will not work with MyISAM or other non-transactional storage engines. So, make sure the default storage engine is InnoDB using the default_storage_engine variable like so:
default_storage_engine=InnoDB
Next, ensure the InnoDB locking mode for generating auto-increment values is set to interleaved lock mode. This is designated by a value of 2 for the appropriate variable:
innodb_autoinc_lock_mode=2
Don’t change this value afterwards. Other modes may cause INSERT statements to fail on tables with AUTO_INCREMENT columns.
Warning
When innodb_autoinc_lock_mode is set to traditional lock mode (i.e., a value of 0) or to consecutive lock mode (i.e., a value of 1) it can cause unresolved deadlocks and make the system unresponsive in Galera Cluster.
After all of that, make sure the InnoDB log buffer is written to file once per second, rather than on each commit, to improve performance. To do this, set the innodb_flush_log_at_trx_commit variable to 0 like so;
innodb_flush_log_at_trx_commit=0
Warning
Although setting innodb_flush_log_at_trx_commit to a value of 0 or 2 improves performance, it also introduces potential problems. Operating system crashes or power outages can erase the last second of transaction. Although normally you can recover this data from another node, it can still be lost entirely in the event that the cluster goes down at the same time.
After you make all of these changes and additions to the configuration file, you’re ready to configure the database privileges.
Configuring the InnoDB Buffer Pool¶
The InnoDB storage engine uses its own memory buffer to cache data and for indexes of tables. You can configure this memory buffer through the innodb_buffer_pool_size parameter. The default value is 128 MB. To compensate for the increased memory usage of Galera Cluster over a standalone MySQL database server, you should scale your usual value back by five percent.
innodb_buffer_pool_size=122M
Configuring Swap Space
Memory requirements for Galera Cluster are difficult to predict with any precision. The particular amount of memory it uses can vary significantly, depending upon the load the given node receives. In the event that Galera Cluster attempts to use more memory than the node has available, the mysqld instance will crash.
The way to protect a node from such crashes is to ensure that there is sufficient swap space available on the server. This can be either in the form of a swap partition or swap files. To check the available swap space, execute the following from the command-line:
# swapon --summary
Filename Type Size Used Priority
/dev/sda2 partition 3369980 0 -1
/swap/swap1 file 524284 0 -2
/swap/swap2 file 524284 0 -3
If swap is not configured, nothing will be returned from this command. If your system doesn’t have swap space available or if the allotted space is insufficient, you can fix this by creating swap files.
First, create an empty file on your disk, set the file size to whatever size you require. You can do this with the fallocate tool like so:
fallocate -l 512M /swapfile
Alternatively, you can manage the same using dd utility like this:
dd if=/dev/zero of=/swapfile bs=1M count=512
Be sure to secure the swap file by changing the permissions on the filesystem with chmod like this:
chmod 600 /swapfile
$ ls -a / | grep swapfile
-rw------- 1 root root 536870912 Feb 12 23:55 swapfile
This sets the file permissions so that only the root user can read and write to the file. No other user or group member can access it. Using the ls command command above shows the results.
Now you’re read to format the swap file. You can do this with the mkswap utility. You’ll then need to active the swap file.
mkswap /swapfile
swapon /swapfile
Using a text editor, update the /etc/fstab file to include the swap file by adding the following line to the bottom:
/swapfile none swap defaults 0 0
After you save the /etc/fstab file, you run swapon again to see the results:
swapon --summary
Filename Type Size Used Priority
/swapfile file 524284 0 -1
