In this blog post, I’ve associated several PostgreSQL commands with what I know in MySQL. Below is a table of some of the frequently used commands.
MySQL
PostgreSQL
Description
mysqldump
pg_dumpall
extract a PostgreSQL database cluster into a script file
mysqldump
pg_dump
extract a PostgreSQL database into a script file or other archive file
N/A
pg_restore
restore a PostgreSQL database from an archive file created by pg_dump
N/A
pg_top
pg_top is a PostgreSQL interactive monitoring utility, similar to the Unix top utility, providing a dynamically updated view of activity within a PostgreSQL database
mysql
psql
PostgreSQL interactive terminal
Exit Mysql {quit|exit| [ctrl-c]}
Exit Postgres\q
quit psql,
show databases;
\l or \list
View all of the defined databases on the server
use [dbname];
\c [dbname]
Connect to a database
show tables;
\dt or \dt+
List all the tables in the current schema
describe [tablename];
\d [tablename]
Shows the columns, types, modifiers, indexes, and tables referenced by keys.
show create table [tablename];
No direct equivalent, use below command from shell: pg_dump -st tablename dbname
This will provide the sql used to create a table.
select * from mysql.user;
select * from pg_user; \du
Lists all database roles
show full processlist;
select * from pg_stat_activity;
Show what queries are currently running in the DB
show variables;
show all;
Displays the current setting of run-time parameters
show engine innodb status\G
SELECT * FROM pg_stat_activity; SELECT * FROM pg_stat_database; SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_locks;
There is no central place in PostgreSQL to get all of the information obtained by running show engine InnoDB status in MySQL. However, there are a number of queries you can run to get roughly equivalent data.
show slave status\G
select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay;
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.
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.
This blog post describes how to deploy a TiDB cluster on GCP Google Kubernetes Engine (GKE). TiDB on Kubernetes is the standard way to deploy TiDB on public clouds.
TiDB Architecture
TiDB is designed to consist of multiple components. These components communicate with each other and form a complete TiDB system. The architecture is as follows:
TiDB server
The TiDB server is a stateless SQL layer that exposes the connection endpoint of the MySQL protocol to the outside. The TiDB server receives SQL requests, performs SQL parsing and optimization, and ultimately generates a distributed execution plan. It is horizontally scalable and provides the unified interface to the outside through the load balancing components such as Linux Virtual Server (LVS), HAProxy, or F5. It does not store data and is only for computing and SQL analyzing, transmitting actual data read request to TiKV nodes (or TiFlash nodes).
Placement Driver (PD) server
The PD server is the metadata managing component of the entire cluster. It stores metadata of real-time data distribution of every single TiKV node and the topology structure of the entire TiDB cluster, provides the TiDB Dashboard management UI, and allocates transaction IDs to distributed transactions. The PD server is “the brain” of the entire TiDB cluster because it not only stores metadata of the cluster, but also sends data scheduling command to specific TiKV nodes according to the data distribution state reported by TiKV nodes in real time. In addition, the PD server consists of three nodes at least and has high availability. It is recommended to deploy an odd number of PD nodes.
Storage servers
Storage servers
TiKV server
The TiKV server is responsible for storing data. TiKV is a distributed transactional key-value storage engine. Region is the basic unit to store data. Each Region stores the data for a particular Key Range which is a left-closed and right-open interval from StartKey to EndKey. Multiple Regions exist in each TiKV node. TiKV APIs provide native support to distributed transactions at the key-value pair level and supports the Snapshot Isolation level isolation by default. This is the core of how TiDB supports distributed transactions at the SQL level. After processing SQL statements, the TiDB server converts the SQL execution plan to an actual call to the TiKV API. Therefore, data is stored in TiKV. All the data in TiKV is automatically maintained in multiple replicas (three replicas by default), so TiKV has native high availability and supports automatic failover.
TiFlash server
The TiFlash Server is a special type of storage server. Unlike ordinary TiKV nodes, TiFlash stores data by column, mainly designed to accelerate analytical processing.
Prerequisites
Before deploying a TiDB cluster on GCP GKE, make sure the following requirements are satisfied
1) Create a project
2) Enable Kubernetes Engine API
3) Activate Cloud Shell
Ensure that you have the available quote for Compute Engine CPU in your cluster’s region.
4) Configure the GCP service
Configure your GCP project and default region.
gcloud config set core/project
gcloud config set compute/region
Example:
gcloud config set core/project erudite-spot-326413
gcloud config set compute/zone us-west1-a
TiDB Operator uses Custom Resource Definition (CRD) to extend Kubernetes. Therefore, to use TiDB Operator, you must first create the TidbCluster CRD, which is a one-time job in your Kubernetes cluster.
kubectl get pods --namespace tidb-admin -l app.kubernetes.io/instance=tidb-admin
kubectl get pods --namespace tidb-admin -o wide
Deploy a TiDB Cluster and the Monitoring Component
This section describes how to deploy a TiDB cluster and its monitoring services.
Create namespace
kubectl create namespace tidb-cluster
Note: A namespace is a virtual cluster backed by the same physical cluster. This document takes tidb-cluster as an example. If you want to use other namespace, modify the corresponding arguments of -n or –namespace.
Download the sample TidbCluster and TidbMonitor configuration files
Wait until all Pods for all services are started. As soon as you see Pods of each type (-pd, -tikv, and -tidb) are in the “Running” state, you can press Ctrl+C to get back to the command line and go on to connect to your TiDB cluster.
View the cluster status
kubectl get pods -n tidb-cluster
Get list of services in the tidb-cluster
kubectl get svc -n tidb-cluster
Access the TiDB database
After you deploy a TiDB cluster, you can access the TiDB database via MySQL client.
Prepare a bastion host
The LoadBalancer created for your TiDB cluster is an intranet LoadBalancer. You can create a bastion host in the cluster VPC to access the database.
Note: You can also create the bastion host in other zones in the same region.
Note: In the regional cluster, the nodes are created in 3 zones. Therefore, after scaling out, the number of nodes is 2 * 3 = 6.
After that, execute kubectl edit tc basic -n tidb-cluster and modify each component’s replicas to the desired number of replicas. The scaling-out process is then completed.
kubectl edit tc basic -n tidb-cluster
Deploy TiFlash and TiCDC
TiFlash is the columnar storage extension of TiKV.
TiCDC is a tool for replicating the incremental data of TiDB by pulling TiKV change logs.
This blog post describes how to deploy a TiDB cluster on AWS Elastic Kubernetes Service (EKS). TiDB on Kubernetes is the standard way to deploy TiDB on public clouds
Configure AWS Command Line using Security Credentials
Go to AWS Management Console –> Services –> IAM
Select the IAM User: <user>
**Important Note:** Use only IAM user to generate **Security Credentials**. Never ever use Root User. (Highly not recommended)
Click on **Security credentials** tab
Click on **Create access key**
Copy Access ID and Secret access key
Go to command line and provide the required details
aws configure
Test if AWS CLI is working after configuring the above:
aws ec2 describe-vpcs
Install kubectl CLI
Kubectl binaries for EKS please prefer to use from Amazon
This will help us to get the exact Kubectl client version based on our EKS Cluster version. You can use the below documentation link to download the binary.
# Download the Package
mkdir kubectlbinary
cd kubectlbinary
curl -o kubectl https://amazon-eks.s3.us-west-2.amazonaws.com/1.16.8/2020-04-16/bin/darwin/amd64/kubectl
# Provide execute permissions
chmod +x ./kubectl
# Set the Path by copying to user Home Directory
mkdir -p $HOME/bin && cp ./kubectl $HOME/bin/kubectl && export PATH=$PATH:$HOME/bin
echo 'export PATH=$PATH:$HOME/bin' >> ~/.bash_profile
# Verify the kubectl version
kubectl version --short --client
Output: Client Version: v1.16.8-eks-e16311
mkdir kubectlbinary
cd kubectlbinary
curl -o kubectl.exe https://amazon-eks.s3.us-west-2.amazonaws.com/1.16.8/2020-04-16/bin/windows/amd64/kubectl.exe
```
- Update the system **Path** environment variable
```
# Verify the kubectl client version
kubectl version --short --client
kubectl version --client
Install eksctl CLI
eksctl on Mac
# Install Homebrew on MacOs
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
# Install the Weaveworks Homebrew tap.
brew tap weaveworks/tap
# Install the Weaveworks Homebrew tap.
brew install weaveworks/tap/eksctl
# Verify eksctl version
eksctl version
eksctl on windows or linux
For windows and linux OS, you can refer below documentation link.
TiDB is designed to consist of multiple components. These components communicate with each other and form a complete TiDB system. The architecture is as follows:
TiDB server
The TiDB server is a stateless SQL layer that exposes the connection endpoint of the MySQL protocol to the outside. The TiDB server receives SQL requests, performs SQL parsing and optimization, and ultimately generates a distributed execution plan. It is horizontally scalable and provides the unified interface to the outside through the load balancing components such as Linux Virtual Server (LVS), HAProxy, or F5. It does not store data and is only for computing and SQL analyzing, transmitting actual data read request to TiKV nodes (or TiFlash nodes).
Placement Driver (PD) server
The PD server is the metadata managing component of the entire cluster. It stores metadata of real-time data distribution of every single TiKV node and the topology structure of the entire TiDB cluster, provides the TiDB Dashboard management UI, and allocates transaction IDs to distributed transactions. The PD server is “the brain” of the entire TiDB cluster because it not only stores metadata of the cluster, but also sends data scheduling command to specific TiKV nodes according to the data distribution state reported by TiKV nodes in real time. In addition, the PD server consists of three nodes at least and has high availability. It is recommended to deploy an odd number of PD nodes.
Storage servers
TiKV server
The TiKV server is responsible for storing data. TiKV is a distributed transactional key-value storage engine. Region is the basic unit to store data. Each Region stores the data for a particular Key Range which is a left-closed and right-open interval from StartKey to EndKey. Multiple Regions exist in each TiKV node. TiKV APIs provide native support to distributed transactions at the key-value pair level and supports the Snapshot Isolation level isolation by default. This is the core of how TiDB supports distributed transactions at the SQL level. After processing SQL statements, the TiDB server converts the SQL execution plan to an actual call to the TiKV API. Therefore, data is stored in TiKV. All the data in TiKV is automatically maintained in multiple replicas (three replicas by default), so TiKV has native high availability and supports automatic failover.
TiFlash server
The TiFlash Server is a special type of storage server. Unlike ordinary TiKV nodes, TiFlash stores data by column, mainly designed to accelerate analytical processing.
Create a EKS cluster and a node pool
It is recommended to create a node pool in each availability zone (at least 3 in total) for each component when creating an EKS.
This section describes how to deploy a TiDB Operator on AWS EKS.
Install Helm (Prerequisite)
MAC – Install Helm
brew install helm
Windows 10 – Install Helm
choco install kubernetes-helm
Create CRD
TiDB Operator uses Custom Resource Definition (CRD) to extend Kubernetes. Therefore, to use TiDB Operator, you must first create the TidbCluster CRD, which is a one-time job in your Kubernetes cluster.
Create a file called crd.yaml. Copy the configuration from the link below.
To confirm that the TiDB Operator components are running, execute the following command:
kubectl get pods --namespace tidb-admin -l app.kubernetes.io/instance=tidb-operator
Deploy a TiDB cluster and the Monitoring Component
This section describes how to deploy a TiDB cluster and its monitoring component in AWS EKS.
Create namespace
kubectl create namespace tidb-cluster
Note: A namespace is a virtual cluster backed by the same physical cluster. This document takes tidb-cluster as an example. If you want to use a different namespace, modify the corresponding arguments of -n or –namespace.
Deploy
Download the sample TidbCluster and TidbMonitor configuration files:
After the yaml file above is applied to the Kubernetes cluster, TiDB Operator creates the desired TiDB cluster and its monitoring component according to the yaml file.
Verify Cluster & Nodes
View cluster status
kubectl get pods -n tidb-cluster
When all the Pods are in the Running or Ready state, the TiDB cluster is successfully started.
List worker nodes
List Nodes in current kubernetes cluster
kubectl get nodes -o wide
Verify Cluster, NodeGroup in EKS Management Console
Go to Services -> Elastic Kubernetes Service -> ${clustername}
Verify Worker Node IAM Role and list of Policies
Go to Services -> EC2 -> Worker Nodes
Verify CloudFormation Stacks
Verify Control Plane Stack & Events
Verify NodeGroup Stack & Events
Below are the associated NodeGroup Events
Access the Database
You can access the TiDB database to test or develop your application after you have deployed a TiDB cluster.
Prepare a bastion host
The LoadBalancer created for your TiDB cluster is an intranet LoadBalancer. You can create a bastion host in the cluster VPC to access the database.
Select the cluster’s VPC and Subnet and verify whether the cluster name is correct in the dropdown box.
You can view the cluster’s VPC and Subnet by running the following command:
eksctl get cluster -n tidbcluster -r ap-northeast-1
Allow the bastion host to access the Internet. Select the correct key pair so that you can log in to the host via SSH.
Install the MySQL client and connect
sudo yum install mysql -y
Connect the client to the TiDB cluster
mysql -h ${tidb-nlb-dnsname} -P 4000 -u root
kubectl get svc basic-tidb -n tidb-cluster
${tidb-nlb-dnsname} is the LoadBalancer domain name of the TiDB service. You can view the domain name in the EXTERNAL-IP field by executing kubectl get svc basic-tidb -n tidb-cluster.
kubectl get svc basic-tidb -n tidb-cluster
Check TiDB version
select tidb_version()\G
Create test table
use test;
create table test_table (id int unsigned not null auto_increment primary key, v varchar(32));
select * from information_schema.tikv_region_status where db_name=database() and table_name='test_table'\G
In the output below, the EXTERNAL-IP column is the LoadBalancer domain name.
You can access the ${grafana-lb}:3000 address using your web browser to view monitoring metrics. Replace ${grafana-lb} with the LoadBalancer domain name.
Upgrade
To upgrade the TiDB cluster, edit the spec.version by executing the command below.
kubectl edit tc basic -n tidb-cluster
Scale out
Before scaling out the cluster, you need to scale out the corresponding node group so that the new instances have enough resources for operation.
This section describes how to scale out the EKS node group and TiDB components.
Scale out EKS node group
When scaling out TiKV, the node groups must be scaled out evenly among the different availability zones. The following example shows how to scale out the tikv-1a, tikv-1c, and tikv-1d groups of the ${clusterName} cluster to 2 nodes.
After scaling out the EKS node group, execute kubectl edit tc basic -n tidb-cluster, and modify each component’s replicas to the desired number of replicas. The scaling-out process is then completed.
Deploy TiFlash/TiCDC
TiFlash is the columnar storage extension of TiKV.
TiCDC is a tool for replicating the incremental data of TiDB by pulling TiKV change logs.
In the configuration file of eksctl (cluster.yaml), add the following two items to add a node group for TiFlash/TiCDC respectively. desiredCapacity is the number of nodes you desire.
Depending on the EKS cluster status, use different commands:
If the cluster is not created, execute eksctl create cluster -f cluster.yaml to create the cluster and node groups.
If the cluster is already created, execute eksctl create nodegroup -f cluster.yaml to create the node groups. The existing node groups are ignored and will not be created again.
Deploy TiFlash/TiCDC
To deploy TiFlash, configure spec.tiflash in tidb-cluster.yaml:
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.
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.
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.
