MySQL/MariaDB – Innochecksum

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

Cheers!

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

MySQL/MariaDB – Identifying and Avoiding Deadlocks

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.

Cheers!

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

MySQL/MariaDB – Swapping

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.

vm.swappiness = 0

Cheers!

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

Linux – strace

To trace system calls and signals, we use the strace command.

Install strace in RedHat/CentOS:

yum install strace -y

The command below provides information about all the system calls that the application is using.

strace ls

The command below provides counters including the number of errors that were encountered while the application is operational.

strace -c ls

Cheers!

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

MySQL/MariaDB – optimizer_switch

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
defaultReset all optimizations to their default values.
optimization_name=defaultSet the specified optimization to its default value.
optimization_name=onEnable the specified optimization.
optimization_name=offDisable the specified optimization.
There is no need to list all flags – only those that are specified in the command will be affected.

Cheers!

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