MariaDB | SQL Conjuror

MySQL/MariaDB Memory

By Enrique 30/03/2022 31/03/2022 MariaDB, Linux, MySQL, Database Administration, Troubleshooting

In this blog post, I’m gonna talk about MySQL Memory Issues. What causes High memory usage and what is the impact on MySQL database instances.

But first, we can do a quick review on the following topics:

How MySQL uses memory? (Log files, buffers, InnoDB buffer pool size)
How MySQL allocates memory?
Swap memory
Troubleshooting
Best practices.

Globally shared memory is allocated upon the creation of an instance and is shared by all connections
Example: thread cache, query cache, buffer pool, and log buffer.

Private memory is used to allocate cache upon connection to MySQL server. It is allocated for each thread. For example; sort buffer, join buffer, and temporary table.

InnoDB maintains one or more buffer pools that cache frequently used data and indexes in the main memory.

So, when a read query is executed from the client program, InnoDB checks to see if the required data pages are in the buffer pool. If it is not in the buffer pool, InnoDB requests the data from the tablespace. Then it will put the data pages in the buffer pool. And then, MySQL will return the results to the client.

When transactions are performed, data changes are written to the log buffer in memory. then every time a transaction is committed, the transaction logs get flushed to the disk, but this behavior can be changed (InnoDB_flush_log_at_trx_commit). If a crash occurs (while the tables are being modified), the redo log files are used for recovery.

On the other hand, Dirty pages in the buffer pool get written to the disk at checkpoints. This is to ensure that the cache has enough free space.

innodb_buffer_pool_size is the most important tuning parameter. It caches table and index data. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing.

Setting it too low can degrade the performance. Setting it too high can increase the memory consumption causing the DB to crash.

MySQL allocation is not only from innodb_buffer_pool_size, but also from other buffers in the database such sort_buffer_size, read_buffer_size, read_rnd_buffer, join_buffer_size, and tmp_table_size and this will require additional 5-10% extra memory

Below is a formula to calculate the approximate memory usage for your MySQL:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Swapping can happen when a system requires more memory than is allocated .A large buffer may lead to swapping in the operating system and make the performance slow.

To avoid your MySQL/MariaDB data being SWAP instead of RAM, you have to play with a kernel parameter called swappiness.

A swappiness value is used to change the balance between swapping out runtimememory and dropping pages from the system page cache. The higher the value, the more the system will swap. The lower the value, the less the system will swap. The maximum value is 100, the minimum is 0, and 60 is the default.

I performed a test in AWS RDS MySQL. I ran a stored procedure that consumed a lot of memory until the server crashed. We will notice that when memory gets low, Swap usage will increase. We will also see a spike in disk IO usage.

When MySQL crashed, we will lose connection to the database. In AWS RDS, we can see the logs and EVENTs to identify the cause of the crash. The log indicates that the database process was killed by the OS due to excessive memory consumption.

I also did the same test in Azure MySQL. The difference between Azure and AWS is that when the DB crash, in Azure, it will failover to the Standby replica, but in AWS, a mysqld process crash would not trigger a failover.

In Azure, under Private DNS Zone, if we see that the IP address changed, then it means that it failed over to the standby replica successfully.

Cheers!

MySQL/MariaDB – FLUSH TABLES WITH READ LOCK

By Enrique 29/03/2022 30/03/2022 MariaDB, MySQL, Database Administration

To block all write access and to mark all tables as ‘properly closed’ on disk, we execute the FLUSH TABLES WITH READ LOCK. Note that the tables can still be used for read operations. Open tables will be closed and all tables will be locked for all databases with a global lock.

FLUSH TABLES and RELOAD privilege are required to execute this operation.

To release the lock, you may use UNLOCK TABLES, which implicitly commits any active transaction only if any tables currently have been locked with the LOCK TABLES. If the UNLOCK TABLES is executed after FLUSH TABLES WITH READ LOCK, then the commit will not occur because FLUSH TABLES WITH READ LOCK does not acquire table locks.

Cheers!

Flush Tables With Read Lock

By Enrique 18/08/2021 18/08/2021 MariaDB, MySQL, Database Administration

This command closes all open tables and locks all tables for all databases with a global read lock

FLUSH TABLES or RELOAD privilege is required for this operation.

To release the lock, use UNLOCK TABLES. This command implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES.

Inserting rows into the log tables is not prevented with FLUSH TABLES WITH READ LOCK.

Cheers!

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!

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!

Facebook

Google

Twitter

Category: MariaDB

MySQL/MariaDB Memory

MySQL/MariaDB – FLUSH TABLES WITH READ LOCK

Flush Tables With Read Lock

MySQL/MariaDB – ulimit open files & MySQL open_files_limit

MySQL/MariaDB – Innochecksum

MySQL/MariaDB – Identifying and Avoiding Deadlocks

MySQL/MariaDB – Swapping

MySQL/MariaDB – optimizer_switch

MariaDB/MySQL – Seconds Behind Master Deep Dive

MariaDB/MySQL – Table Open Cache & Table Definition Cache

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud