Enrique | SQL Conjuror

MySQL/MariaDB – ulimit open files & MySQL open_files_limit

By Enrique 02/08/2021 02/08/2021 Linux, MariaDB, MySQL, Database Administration

The amount of resources that can be used can be controlled from the operating system perspective. Each user has limits that are set, but for that particular user, the limits are applied individually to each of its processes.

Limits can either be hard or soft. Only the root user can set the Hardlimits. Other users can set Soft limits, but it cannot be more than the hard limit.

The default value of ulimit open files limit is 1024. This is very low for a typical web server environment that hosts many have database-driven sites.

MySQL/MariaDB also uses this setting. The open_files_limit is set by MySQL/MariaDB to the system’s ulimit. Default is 1024.

NOTE: MySQL/MariaDB can only set its open_files_limit lower than what is specified under ulimit ‘open files’. It cannot be set higher than that.

Examine Current Limits

To inspect current limits


ulimit -a

# -a will show all current limits including hard, soft, open files, etc.

To inspect the current hard and soft limits.


# Hard Limits
ulimit -Ha

# Soft Limits
ulimit -Sa

# H for hard limits, or S for soft limits.

To check current open file limits.


ulimit -n

# –n for number of open files limits

Set ‘open files’ Limit Persistently

Open /etc/security/limits.conf using the text editor of your choice, and add the following lines, then save it.


* soft nofile 102400
* hard nofile 102400
* soft nproc 10240
* hard nproc 10240

Edit the file /etc/security/limits.d/90-nproc.conf using the text editor of your choice, and add the following lines, then save it.


* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240
root soft nproc unlimited

Set open_files_limit in my.cnf (MySQL)

Open and edit /etc/my.cnf

Insert the lines below under [mysql], then save it.


[mysqld]
open_files_limit = 102400

Run the command below to see if there are any .conf files being used by MySQL that overrides the values for open limits.


systemctl status mysqld

Below is something you will see after running the command above.


/etc/systemd/system/mariadb.service.d
└─limits.conf

This means that there is a /etc/systemd/system/mariadb.service.d/limts.conf file, whicv is loaded with MySQL. Now, edit that file as well.


[Service]
LimitNOFILE=102400

Execute the command below for the changes to take effect.

systemctl daemon-reload && /scripts/restartsrv_mysql

Perform Server Reboot

Run the command below in MySQL to see the value of open_files_limit.


SHOW VARIABLES LIKE 'open_files_limit';

Output:


+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 102400 |
+------------------+--------+
1 row in set (0.00 sec)

Cheers!

MySQL/MariaDB – Innochecksum

By Enrique 02/06/2021 09/07/2021 Linux, MariaDB, MySQL, Troubleshooting

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!

MySQL/MariaDB – Identifying and Avoiding Deadlocks

By Enrique 02/06/2021 03/06/2021 MariaDB, MySQL, Performance Optimization, High Availability / Disaster Recovery

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!

MySQL/MariaDB – Swapping

By Enrique 02/06/2021 02/06/2021 MariaDB, MySQL

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!

Linux – strace

By Enrique 01/06/2021 01/06/2021 Linux

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!

MySQL/MariaDB – optimizer_switch

By Enrique 01/06/2021 01/06/2021 MariaDB, MySQL, Database Administration, Performance Optimization

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.

Cheers!

MariaDB/MySQL – Seconds Behind Master Deep Dive

By Enrique 22/05/2021 29/05/2021 MariaDB, MySQL, Database Administration

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

Execute in Replica

STOP SLAVE SQL_THREAD;

Execute in Master

call `gendata`(3000);

mysql> SELECT COUNT(*) FROM tbl;
+----------+
| COUNT(*) |
+----------+
|     3000 |
+----------+

In the Replica, since the SQL_THREAD is not running, these records don’t exists.

mysql> SELECT COUNT(*) FROM tbl;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Now, run these commands in one shot in the Replica to start the SQL_THREAD, get the unix_timestamp(), then stop the SQL_THREAD again.

START SLAVE SQL_THREAD;

SHOW SLAVE STATUS \G

SELECT unix_timestamp();

STOP SLAVE SQL_THREAD;

SELECT COUNT(*) FROM tbl;

Below are my output.

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.17
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db1-bin.000018
          Read_Master_Log_Pos: 3349116
               Relay_Log_File: mysqldb2-relay-bin.000006
                Relay_Log_Pos: 2512613
        Relay_Master_Log_File: db1-bin.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2512410
              Relay_Log_Space: 3349604
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 93
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:


mysql> SELECT unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1621660163 |
+------------------+
1 row in set (0.00 sec)


mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT COUNT(*) FROM tbl;
+----------+
| COUNT(*) |
+----------+
|      552 |
+----------+
1 row in set (0.00 sec)

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 |
+-------------------------+

Cheers!

MariaDB/MySQL – Table Open Cache & Table Definition Cache

By Enrique 27/02/2021 02/06/2021 MariaDB, MySQL, Performance Optimization

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:

[mysqld]
table_definition_cache = 16384

Cheers!

MySQL/MariaDB – slave_compressed_protocol

By Enrique 21/02/2021 24/02/2021 MariaDB, MySQL, Database Administration

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)

Linux – How To Install Netstat

By Enrique 10/02/2021 24/04/2021 Linux

On CentOS/RHEL

yum install net-tools

On Debian/Ubuntu

apt install net-tools

On OpenSuse

zypper install net-tools

On Arch Linux

pacman -S netstat-nat

Run the command below to check the version of netstat installed.

netstat -v

Facebook

Google

Twitter

Author: Enrique

MySQL/MariaDB – ulimit open files & MySQL open_files_limit

MySQL/MariaDB – Innochecksum

MySQL/MariaDB – Identifying and Avoiding Deadlocks

MySQL/MariaDB – Swapping

Linux – strace

MySQL/MariaDB – optimizer_switch

MariaDB/MySQL – Seconds Behind Master Deep Dive

MariaDB/MySQL – Table Open Cache & Table Definition Cache

MySQL/MariaDB – slave_compressed_protocol

Linux – How To Install Netstat

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud