MariaDB/MySQL – InnoDB_flush_log_at_trx_commit

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.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

MariaDB/MySQL – Difference Between Undo and Redo

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.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

MariaDB – Identify Blocking/Blocked Queries

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

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

MariaDB – MaxScale Set up, Failover, Switchover, Load Balancing

In this blog post, I will show you how to:

  • Setup MariaDB MaxScale
  • Perform Administration and Monitoring
  • Perform a Failover
  • Perform a Switchover

SETUP ENVIRONMENT

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

HostnameIP addressPort
maxscale127.0.0.12222
mariadb1127.0.0.12200
mariadb2127.0.0.12201
mariadb3127.0.0.12202

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. )

Preview(opens in a new tab)

INSTALL MARIADB

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).

[client-server]
!includedir /etc/my.cnf.d

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid

# Network
port = 3306
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0

# Data directory
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# Connections
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M

# Query Cache
query_cache_limit = 128K
query_cache_size = 64M
query_cache_type = 1
log_warnings = 2

# Slow log
slow_query_log
slow_query_log_file = /var/lib/mysql/mariadb-slow.log
long_query_time = 1
log_slow_verbosity = query_plan,explain

# Replication
server-id=1
gtid_strict_mode=1
log-bin = /var/lib/mysql/bin-mariadb.log
expire-logs-days=8
sync_binlog = 1
slave_compressed_protocol
transaction-isolation = READ-COMMITTED
binlog_format = row


# Error log
log_error = mariadb_error.log

# Deadlocks
innodb_print_all_deadlocks = ON

# Performance Schema
performance_schema=on

# User Statistics
userstat = 1

# Denies naming the connection
skip_name_resolve

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;"

SET UP MAXSCALE

Create maxscale ID

useradd -m -d /home/maxscale -u 6603 -s /bin/bash -c "MaxScale Admin Functional ID" -U maxscale

Set password for maxscale ID

echo 'password' | passwd --stdin maxscale

Execute MariaDB Package Repository Setup

The command below will setup 3 different repositories in a single repository configuration file. The repositories are the following:

  • MariaDB Repository
  • MariaDB MaxScale Repository
  • MariaDB Tools Repository

You may read about the setup and usage of the command from this link.

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

Install MariaDB Client

yum install MariaDB-common MariaDB-compat MariaDB-client -y

Install MaxScale

yum install maxscale -y

Generate Encrypted Password.

You can read about encrypting passwords from this link.

# Generate .secrets file
rm -f /var/lib/maxscale/.secrets
maxkeys /var/lib/maxscale

# Generate encrypted password
ls -altr /var/lib/maxscale/.secrets
chown maxscale:maxscale /var/lib/maxscale/.secrets

maxpasswd  /var/lib/maxscale/ password
42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A

maxpasswd  /var/lib/maxscale/ password2
A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73

Configure maxscale.cnf file

vim /etc/maxscale.cnf.d/maxscale.cnf
[mariadb1]
type=server
address=192.168.1.220
port=3306
protocol=mariadbbackend

[mariadb2]
type=server
address=192.168.1.133
port=3306
protocol=mariadbbackend

[mariadb3]
type=server
address=192.168.1.208
port=3306
protocol=mariadbbackend

[MySQLMonitor]
type=monitor
module=mariadbmon
servers=mariadb1,mariadb2,mariadb3
user=repl
password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
replication_user=repl
replication_password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
monitor_interval=5000
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=1
backend_connect_timeout=10
backend_read_timeout=10
backend_write_timeout=10

[ReadWriteSplitService]
type=service
router=readwritesplit
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=6C620E5D01B63D33023313BCD5DD6EC4F60B619F2B7F4B9D18E034428BEDA178
master_failure_mode=fail_instantly
max_slave_replication_lag=1

[ReadWriteMasterService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=master

[ReadOnlySlaveService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=slave

[ReadWriteSplitListener]
type=listener
service=ReadWriteSplitService
protocol=mariadbclient
address=0.0.0.0
port=4006

[ReadWriteMasterListener]
type=listener
service=ReadWriteMasterService
protocol=mariadbclient
address=0.0.0.0
port=4007

[ReadOnlySlaveListener]
type=listener
service=ReadOnlySlaveService
protocol=mariadbclient
address=0.0.0.0
port=4008

Change permissions and owner of config files

chown maxscale:maxscale /etc/maxscale.cnf.d/maxscale.cnf
chmod 640 /etc/maxscale.cnf.d/maxscale.cnf

You may read about the MariaDB Monitor, ReadConnRoute, and ReadWriteSplit from MariaDB’s website.

Verify that MaxScale service is enabled.

systemctl is-enabled maxscale.service

Start MaxScale service.

systemctl start maxscale.service

You check the maxscale log at the path below.

tail -100 /var/log/maxscale/maxscale.log

MONITORING MAXSCALE

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.

maxctrl call command mariadbmon switchover MariaDB-Monitor mariadb1 mariadb2

Here is the explanation of the command above.

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.

That is all for this blog post.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Memory Settings Formula (Best Practice)

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

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter