In this blog post, we are going to talk about the variable InnoDB_flush_log_at_trx_commit. We are going to discuss what each value that we can assign to this variable means, and how it can affect performance and durability.
Innodb_flush_log_at_trx_commit controls the durability in ACID compliance
A – atomicity
C – consistency
I – isolation
D – durability
The possible values for this variable is 0, 1, and 2.
The specific path of the query goes to the innodb buffer pool, then log buffer (redo logs), then OS buffer, then finally to log file.
When innodb flush log at transaction commit is set to 0, your write goes thru memory, thru the buffer pool into the log buffer. That write then flushes from the log buffer to the log file on disk for every 1 second or when the OS flushes.
If this variable is set to 1, which is maximum durability. Your write goes to the log buffer, but the commit of the file ensures that it is written all the way on disk. This value will have a bit of performance hit compared to value 0.
If the value is set to 2, the write goes to the log buffer, but the file will be committed all the way to the OS buffer. And then the OS will flush to disk roughly every 1 second.
Advantages and Disadvantages
0 – when the database crashes, the log buffer within memory will get loss, and there is a possibility of losing those transaction. This setting is for performance, but not for durability.
1 – every write will surely be written to the redo log on disk. You will not lose that write regardless of the crash.
2 – You will lose only about 25% performance as compared to 1. If the DB crashes, the file is still written to disk cache, and then written to disk later. But if the DB server itself crashes, that DB server’s disk buffer may lose its data. This can be prevented though if we have battery backup or SAN.
Undo log is used to keep track of changes performed by active transactions and roll them backup if necessary. It is physically stored in the system table spce and, optionally, in other tablespaces.
Redo log tracks data of the requested data changes and is used to recover tables after a crash. It is physically stored in dedicated files.
Redo and Undo logs are both used during crash recovery.
Here is a SQL script that you can use to identify Blocking and Blocked Queries in MariaDB.
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx AS r
ON r.trx_id = w.requesting_trx_id
First, we will set up the environment. We will be having 1 master and 2 slaves. On top of those, we will have our MariaDB MaxScale server.
To make it easier to setup the environment, you may use the vagrant file below.
# -*- mode: ruby maxscale222
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
#: adding ansible stuff
config.ssh.insert_key = false
#: maxscale box
config.vm.define :maxscale do |maxscale|
maxscale.vm.hostname="maxscale"
maxscale.vm.box = "domflannery/centos7"
maxscale.vm.network "public_network"
end
######: MASTER / SLAVE SERVERS :######
#: master, async && semisync replication
config.vm.define :mariadb1, autostart: true do |mariadb1|
mariadb1.vm.hostname="mariadb1"
mariadb1.vm.box = "domflannery/centos7"
mariadb1.vm.network "public_network"
end
#: slave01, async && semisync replication
config.vm.define :mariadb2, autostart: true do |mariadb2|
mariadb2.vm.hostname="mariadb2"
mariadb2.vm.box = "domflannery/centos7"
mariadb2.vm.network "public_network"
end
#: slave02, async && semisync replication
config.vm.define :mariadb3, autostart: true do |mariadb3|
mariadb3.vm.hostname="mariadb3"
mariadb3.vm.box = "domflannery/centos7"
mariadb3.vm.network "public_network"
end
end
I chose public networks that is set automatically (through DHCP) for demo purposes. The VMs IP addresses will be in the same range as the host’s IP address.
Once all the virtual boxes are up, we will login to each of them to get the assigned public IP address. First, login to each server using local IP (127.0.0.1) and the respective port numbers (refer to the chart below). The login and password will be both vagrant
Hostname
IP address
Port
maxscale
127.0.0.1
2222
mariadb1
127.0.0.1
2200
mariadb2
127.0.0.1
2201
mariadb3
127.0.0.1
2202
login: vagrant password: vagrant
I got the port numbers above by looking at the logs after I issued vagrant up. Below is an example.
Get the assigned IP address using the command below (ifconfig will not work in this version of centos)
ip a
After getting the public IP addresses of each server, configure them in your putty. Give each connection sensible names. (Also, don’t include the /24 of the IP address. )
Install MariaDB in mariadb1, mariadb2, and mariadb3. I will be using MariaDB 10.4 on Centos7. If you want to use a different flavor of Linux, then refer to this link to get the correct repositories info.
Let’s create a repo file.
vim MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Move the file to /etc/yum.repos.d/
sudo mv -vi MariaDB.repo /etc/yum.repos.d/
Once the file is in place, we are ready to install MariaDB (The capitalization of the package names is important).
sudo yum install MariaDB-server MariaDB-client -y
SETUP MARIADB REPLICATION (USING GTID)
Get the master and replicas ready.
Login to mariadb1 as root, and edit the my.cnf
vi /etc/my.cnf
Delete the existing lines; then enter the following lines below (or just copy paste them).
Do the same (edit config file) in mariadb2 and mariadb3, but change the server_id to a different value for each server.
After editing the my.cnf file on all 3 servers, start mariadb service
systemctl start mariadb
STARTING UP REPLICATION
Create a user in the master, and grant “replication slave” to the user. The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
In a real-world production environment, you would create a user that can only connect from the IP address of your slaves, but for the sake of demonstration, we’ll just use the wild card character (%)
Point your slaves to the master by executed the commands below.
change master to
master_host='192.168.1.220',
master_user='repl',
master_password='password',
master_use_gtid=slave_pos;
Run the command below to start the slave
start slave;
Check slave status
show slave status \G
Both Slave_IO_Running and Slave_SQL_Running parameters should be Yes.
Execute on Master (only) to grant privileges to maxuser. This user will be used by maxscale.
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'localhost' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repl'@'%';
grant select on mysql.user to 'maxuser'@'localhost';
grant select on mysql.db to 'maxuser'@'localhost';
grant select on mysql.tables_priv to 'maxuser'@'localhost';
grant select on mysql.roles_mapping to 'maxuser'@'localhost';
grant select on mysql.columns_priv to 'maxuser'@'localhost';
grant select on mysql.proxies_priv to 'maxuser'@'localhost';
flush privileges;"
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repuser'@'%';
grant select on mysql.user to 'maxuser'@'';
grant select on mysql.db to 'maxuser'@'';
grant select on mysql.tables_priv to 'maxuser'@'';
grant select on mysql.roles_mapping to 'maxuser'@'';
grant select on mysql.columns_priv to 'maxuser'@'';
grant select on mysql.proxies_priv to 'maxuser'@'';
flush privileges;"
In your maxscale server, run the command below to check list of servers being monitored.
maxctrl list servers
FAILOVER TEST
Let’s monitor the servers continuously every second. I suggest you open a new session of your maxscale server, and run the command below.
watch -n1 maxctrl list servers
Go to mariadb1, then stop MariaDB service.
systemctl stop mariadb
After a few seconds, you will see that mariadb2 has been promoted to master.
Let’s bring up mariadb1.
systemctl start mariadb
Monitor the servers, and you will see that they are all up and running.
SWITCHOVER
Let’s make mariadb1 server the master again. We will be doing a switchover, but before we proceed, we have to verify the monitor that we are going to use.
maxctrl list monitors
In our case, it is MariaDB-Monitor.
Now, we are ready to do the switchover. Here is the command to perform it.
call command indicates that it is a module command that is to be invoked. mariadbmon indicates the module whose command we want to invoke. switchover is the command we want to invoke. MariaDB-Monitor is the first argument to the command, the name of the monitor as specified in the configuration file. mariadb1 the name of the server we want to make into master mariadb2 current master.
We now have successfully switched over the master role back to mariadb1
LOAD BALANCING TEST
In this demo, we will be using the maxscale server as our client.
In the previous section, we’ve already installed MariaDB client. Verify that is installed successfully.
rpm -qa | grep -i MariaDB
In mariadb1 (master), create an app user.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' WITH GRANT OPTION;
To connect to our servers via maxscale, we use the connection string below. Essentially, we have to indicate the maxscale IP address. My maxscale IP address happens to be 192.168.1.87. We also have to include the Read/Write listener port that is 4006 as indicated in the maxscale config file (/etc/maxscale.cnf)
Execute the command below in your maxscale server.
mysql -u app_user -p -h192.168.1.87 -P4006
To know which server you are connect to, run the following statement.
SELECT @@hostname;
If we keep on executing this statement repeatedly, we will notice that we will be connected to our slaves: mariadb2 and mariadb3. This is load balancing at work. MaxScale knows that we are executing a read statement; hence it is redirecting the traffic to our read-only replicas.
Once we start a transaction, MaxScale now recognizes that we are about to update a record; hence the connection will be redirected to the master node only.
START TRANSACTION;
The connection will remain in master until we commit the transaction.
COMMIT;
After we commit the transaction, the connection will again be redirected to the read-only replicas.
This is also called the Read Write Splitting.
MaxScale analyze statements, and send where appropriate. Write statements to master; and Read statements to some slaves.
Here are the formulas for setting memory in SQL Server.
Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).
Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).
Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).
SELECT
SERVERPROPERTY('InstanceName') AS myinstance,
*
FROM
sys.configurations
WHERE name IN
(
'max server memory (MB)',
'min server memory (MB)'
)
GO
Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment
SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info
Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO
