innochecksum prints checksums for InnoDB files. This tool reads an InnoDB tablespace file, calculates the checksum for each page, compares the calculated checksum to the stored checksum, and reports mismatches, which indicate damaged pages.
It was originally developed to speed up verifying the integrity of tablespace files after power outages but can also be used after file copies. Because checksum mismatches cause InnoDB to deliberately shut down a running server, it may be preferable to use this tool rather than waiting for an in-production server to encounter the damaged pages.
Below is a script you can use to get corrupted tables. Replace the directory path where your data files are located. (Stop MySQL/MariaDB service first, before running it)
INNOCKSM_LOG=/mysql/backup/innochecksum_`date +%Y%m%d_%H%M%S`.log
for DB in `ls -1vd /mysql/data/*/ | grep -wv '/mysql/data/mysql/\|/mysql/data/performance_schema/\|/mysql/data/lost+found/'`
do
for IBD in `ls -1v $DB | grep .ibd`
do
innochecksum ${DB}${IBD}
if [ $? -ne 0 ]; then
echo ${DB}${IBD} >> $INNOCKSM_LOG
innochecksum ${DB}${IBD} >> $INNOCKSM_LOG 2>&1
fi
done
done
A deadlock is a special blocking scenario when two or more competing transactions are waiting for each other to free locks. Each process, while holding its own resources, attempts to access a resource that is locked by the other process..
Simulating a Deadlock Scenario
Transaction 1
START TRANSACTION;
SELECT * FROM departments WHERE dept_no = 'd008' LOCK IN SHARE MODE;
Transaction 2 (wait)
START TRANSACTION;
UPDATE departments
SET dept_name = 'Research & Development'
WHERE dept_no = 'd008';
Transaction 1 (deadlock)
UPDATE departments
SET dept_name = 'R&D'
WHERE dept_no = 'd008';
Identify and Analyze Deadlocks
Execute the command below in MySQL/MariaDB.
SHOW ENGINE INNODB STATUS \G
mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-06-02 00:40:29 0x7f99d005e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 4498 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 9, rounds 9, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 1.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-06-02 00:40:08 0x7f99b74f8700
*** (1) TRANSACTION:
TRANSACTION 51038, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 140298596771584, query id 180 localhost instadm updating
UPDATE departments
SET dept_name = 'Research & Development'
WHERE dept_no = 'd008'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `employees`.`departments` trx id 51038 lock_mode X locks rec but not gap waiting
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 64303038; asc d008;;
1: len 6; hex 00000000c75a; asc Z;;
2: len 7; hex 02000000fc0151; asc Q;;
3: len 22; hex 5265736561726368202620446576656c6f706d656e74; asc Research & Development;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `employees`.`departments` trx id 51038 lock_mode X locks rec but not gap waiting
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 64303038; asc d008;;
1: len 6; hex 00000000c75a; asc Z;;
2: len 7; hex 02000000fc0151; asc Q;;
3: len 22; hex 5265736561726368202620446576656c6f706d656e74; asc Research & Development;;
*** (2) TRANSACTION:
TRANSACTION 51039, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 9, OS thread handle 140298597062400, query id 181 localhost instadm updating
UPDATE departments
SET dept_name = 'R&D'
WHERE dept_no = 'd008'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `employees`.`departments` trx id 51039 lock mode S locks rec but not gap
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 64303038; asc d008;;
1: len 6; hex 00000000c75a; asc Z;;
2: len 7; hex 02000000fc0151; asc Q;;
3: len 22; hex 5265736561726368202620446576656c6f706d656e74; asc Research & Development;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `employees`.`departments` trx id 51039 lock_mode X locks rec but not gap waiting
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 64303038; asc d008;;
1: len 6; hex 00000000c75a; asc Z;;
2: len 7; hex 02000000fc0151; asc Q;;
3: len 22; hex 5265736561726368202620446576656c6f706d656e74; asc Research & Development;;
*** WE ROLL BACK TRANSACTION (1)
The output will show many info about the latest deadlock, and why it occurred. Take a close look at the portion where it indicates WAITING FOR THIS LOCK TO BE GRANTED (shows which lock the transaction is waiting for) and HOLD THE LOCK(S) (shows the locks that are holding up this transaction).
Preventing Deadlocks
Keep transactions small and quick to avoid clashing.
Commit transactions right after making a set of related changes to make them less prone to clashes.
Accessing resources in the same physical sequence.
For example, two transactions need to access two resources. If each transaction accesses the resources in the same physical sequence, then the first transaction will successfully obtain locks on the resources without being blocked by the second transaction. The second transaction will be blocked by the first while trying to obtain a lock on the first resource. The outcome will just be a typical blocking scenario instead of a deadlock.
When you assign more memory to buffers than your server has physical RAM, swapping can happen. swapping degrades performance significantly.
SWAP is slower than RAM, because it is used on a physical disk (magnetic or SSD). In other words, it is an emulated memory on disk.
We have to tweak a kernel parameter called swappines, to avoid MySQL/MariaDB data being SWAP instead of RAM.
The balance between swapping out runtime memory and dropping pages from the system page cache can be done using swapping value. The bigger the value, the more system will swap. The smaller the value, the less the system will swap.
The maximum is 100, the minimum is 0, and the default is 60.
Add the following line in your sysctl.conf file in /etc/sysctl.conf to change this parameter in the persistence mode.
To control optimizer behaviour, we can enable/disable specific optimization via optimizer_switch system variable. The optimizer_switch variable can be changed at runtime, and has global and session values.
Execute the command below to see the current set of optimizer flags.`
SELECT @@optimizer_switch \G
To change the value of optimizer_switch, use the following syntax.
SET [GLOBAL|SESSION] optimizer_switch='cmd[,cmd]...';
Syntax
Description
default
Reset all optimizations to their default values.
optimization_name=default
Set the specified optimization to its default value.
optimization_name=on
Enable the specified optimization.
optimization_name=off
Disable the specified optimization.
There is no need to list all flags – only those that are specified in the command will be affected.
seconds_behind_master takes the difference between the timestamp on the Replica minus the timestamp of the event that the SQL_THREAD is processing that (timestamp) that was on the master.
DEMO
Execute the script below in Master Server.
create database if not exists sbm_db;
USE `sbm_db`;
DROP procedure IF EXISTS `gendata`;
DELIMITER $$
CREATE PROCEDURE gendata (in loopLimit int)
BEGIN
declare c int;
set c = 0;
label: LOOP
insert into tbl (fld)
values (FLOOR(1 + (RAND() * 60000)));
set c = c + 1;
if c > loopLimit then
leave label;
end if;
end LOOP label;
END$$
DELIMITER ;
drop table if exists tbl;
create table tbl (
tblId int not null primary key auto_increment,
fld varchar(255),
updatedAt timestamp not null default current_timestamp
);
You might be wondering, why seconds behind master is 93 when there were only 5 seconds of worth of data?
How seconds master works is it takes the difference between the timestamp on the replica minus the timestamp of the event that the sql thread is processing (The timestamp that is on the master).
mysqlbinlog --base64-output="decode-rows" --verbose mysqldb2-relay-bin.000006 | less
# at 2512613
#210522 1:07:50 server id 1 end_log_pos 2512485 GTID last_committed=9012 sequence_number=9013 rbr_only=yes original_committed_timestamp=1621660070 immediate_commit_timestamp=1621660070915181 transaction_length=279
mysql> select 1621660163 - 1621660070;
+-------------------------+
| 1621660163 - 1621660070 |
+-------------------------+
| 93 |
+-------------------------+
To improve performance, each concurrent session in MariaDB/MySQL independently access the same table. In other words, many clients could be executing queries for a given table simultaneously. This is also because MySQL/MariaDB is multithreaded. This, however, uses additional memory, but normally increases performance.
table_open_cache indicates the maximum number of tables the server can keep open in any one table cache instance.
View the current setting of table_open_cache by running the command below, or by checking the value in my.cnf
select @@table_open_cache;
There is a variable called “Open_tables”, which indicates the number of table-opening operations since the server started. This is the variable that you can check to determine whether your table cache is too small.
SHOW GLOBAL STATUS LIKE 'Opened_tables';
If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table_open_cache value at server startup.
FLUSH TABLES is to force all tables to be closed to ensure that if someone adds a new table outside of MySQL, all threads will start using the new table.
Table Definition Cache
This parameter shows the number of table definitions (SHOW CREATE TABLE \G) that can be stored. This is to speed up opening of tables and only one entry per table. You should consider increasing this parameter if you have large number of tables (>400) in your DB instance.
Values between 400 and 2000 are good for table_definition_cache. Do take note that a bigger table definition cache also means that your database uses more RAM.
To check table definition cache value, run the command below.
select @@table_definition_cache;
Add this parameter in your configuration file as follows:
This parameter enables compression on master/slave protocol. If both master and slave supports it, this parameter will instruct MariaDB/MysQL to use compression.
This parameter is not enabled by default.
It can be enabled in your my.cnf file (ensure to restart the DB service after enabling it to take effect)
Replication was single threaded in MariaDB 5.5 and MySQL 5.5. The previous transaction must commit on the slave before the next transaction can start.
A single I/O thread works to replicate events from the Master’s binlog to the relay log in the Slave.
On the Slave, the SQL Thread will apply those events, one after the other.
With that said, as you can imagine, replication can sometimes lag. Being single threaded, it is challenging to keep the Master and Slave in sync.
Enable Parallel Slave
To enable, indicate slave-parallel-threads=# in your my.cnf.
Configure the number (#) of worker threads to apply events in parallel for all your slaves.
If you indicate a value of zero, then that means no worker threads are created.
The value should be at least twice the number of multi-source master connections utilized.
You do not have to restart mysqld when you configure slave-parallel-threads=#, because it is a dynamic variable. However, all slaves connections must be stopped when modifying the value.
Here is a link that shows the performance improvement when using parallel replication.
Waiting for prior transaction to start commit before starting next transaction
The previous batch of transactions that committed together on the master has to complete first
Waiting for prior transaction to commit
Transaction has been executed by the worker thread
Parallel Slave Queue Size
SQL thread will read ahead in the relay logs when parallel replication is used. This will queue events in memory while looking for opportunities for executing events in parallel. The system variable that sets a limit for how much memory it will use for this is slave_parallel_max_queued
In order for the SQL thread to read far enough ahead in the binary log to exploit all possible paralellism, the slave_parallel_threads system variable should be set large enough.
To prevent limit throughput, the slave_parallel_max_queued system variable could be set relatively high. It should just be set low enough that total allocation of the parallel slave queue will not cause the server to run out of memory.
Slave Parallel Mode
There are 2 options for Slave Parallel Mode: In-Order and Out-of-Order
In-order runs transactions in parallel, but instructs the commit steps of the transactions in the precise same sequence as on the master.
Out-of-order will have the ability to execute and commit transactions in a different order on the slave than primarily on the master. The application must be tolerant to viewing updates occur in random order.
