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
Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.
All I/O operations are done via the OS and the storage layer. For example:
Claiming disk space for creating a new database file
Autogrowing a database file
Restoring a database from backup
Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.
How to Turn-On Instant File Initialization
SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.
Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows.
Expand the Local Policies Folder.
Click on User Rights Assignment.
Go down to the “Perform Volume Maintenance Tasks” option and double click it.
Add your SQL Server Service account and click OK out of the dialog.
After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.
I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.
BEGIN
SET NOCOUNT ON;
DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);
DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);
INSERT INTO @cmds
SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 140' FROM sys.databases
WHERE compatibility_level = 120
SET @rc = @@ROWCOUNT;
RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 10, 1, @rc) WITH NOWAIT;
WHILE @i <= @rc
BEGIN
SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;
PRINT @v_cmd;
EXEC(@v_cmd);
SET @i += 1;
END;
END
A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:
Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
Suspect – occurs when a database cannot be recovered during startup
Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet
Database marked as ‘Recovery Pending’
Here a script to check the current state of all your databases in a SQL instance.
SELECT name, state_desc from sys.databases
GO
There are several reasons why a database is marked as ‘Recovery Pending‘
The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
Insufficient memory or disk space that prevented database recovery from getting started.
Let’s Fix It
ALTER DATABASE TestDB SET EMERGENCY;
GO
ALTER DATABASE TestDB set single_user
GO
DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
Concurrent operation has big implications for data integrity. In order to guarantee integrity, a relational database system must comply with the ACID principle. This is normally implemented through transactions.
In ACID, A stands for Atomicity, and that means when data is modified by some transaction, either all of its data modifications are performed or none of them are performed.
The C in ACID stands for Consistency. When a data modification transaction completes, all data must be in a consistent state. All constraints must be satisfied, and all internal structures must be correct.
I is all about Isolation. Modifications made by one transaction must be isolated from those made by other concurrent transactions. This also implies that if you redo the same operations with the same starting data, the results will always be the same.
The D in ACID stands for Durability. When a transaction is complete the results are stored permanently in the system and persist even if a system failure occurs. Now in order to get you set up, so that you can follow along with all the demos, let me introduce you to the tools I’ll be using.
I was trying to alter the database to MULTI_USER mode, but faced this error.
Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.
Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.
Msg 6107, Level 14, State 1, Line 1 Only user processes can be killed.
Basically, it says that you cannot kill a system process.
So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE
Here are the scripts that I use to merge table partitions.
First, let’s check the partition information of a particular table (Indicate the table name in the
SELECT pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
ds.name AS partition_filegroup ,
OBJECT_NAME(si.object_id) AS object_name ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
WHERE pf.name = 'TableName'
GROUP BY ds.name ,
p.partition_number ,
pf.name ,
pf.type_desc ,
pf.fanout ,
pf.boundary_value_on_right ,
ps.name ,
si.object_id ,
rv.value
ORDER BY
p.partition_number
GO
Below is a screenshot of a partitioned table information in one of our environments.
Next, let’s merge the empty partitions. I will merge the empty partitions on the left side only, because I will be needing the partitions on the right side for future growth of my database
Below is the script to merge empty partitions
ALTER PARTITION FUNCTION functionName()
MERGE RANGE(boundaryPoint)
