As the name indicates, an undo log record contains information about how to undo the recent changes by a transaction. When a transaction writes data, it always makes writes on the tablespace files. InnoDB Undo log stores copy of data that is being modified by any current transaction. So, at this point in time if any other transaction queries for the original data (row) which is being modified,  the undo logs provide the same and serve the purpose. This is what provides a consistent read view ( based on isolation ) during any data modifications.

Here in the above representation, Transaction T1 modifies the data (Data-1). During the time of modification to ensure the reads are consistent, transactions T2  and T3 are given access only to the copy (previous row version) of Data -1 which is stored in the “UNDO” …

Database schema change is becoming more frequent than before, Four out of five application updates(Releases) requires a corresponding database change, For a DBA schema change is a more often a repetitive task, it might be a request from the application team for adding or modifying columns in a table and many more cases.

MySQL supports online DDL from 5.6 and the latest MySQL 8.0 supports instant columns addition.

This blog post will look at the online DDL algorithms inbuilt which can be used to perform schema changes in MySQL.

DDL Algorithms supported by InnoDB is,

• COPY
• INPLACE
• INSTANT ( from 8.0 versions)

INPLACE Algorithm:

INPLACE algorithm performs operations in-place to the original table and avoids the table copy and rebuild, whenever possible.

If the INPLACE algorithm is specified with the …

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …

We will have a look at a new feature in MySQL 8.0 called binlog encryption. This feature is available from the MySQL version 8.0.14 or above.

Our previous blogs discussed about table space encryption in MySQL and Percona servers. In Mydbops, we are giving high importance about achieving security compliances.

The binary log records changes made to the databases so that it can be used to replicate the same to the slaves and also for the point in time recovery (PITR). So, it means that if someone has access to the binary logs, they can reproduce our entire database in many forms. As a DBA, we need to make sure that the binary log files are protected from users who are having access to the file system and also, log files need …

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

1. Clone-Plugin
2. Xtrabackup
3. mysqldump
4. mydumper with myloader
5. mysqlpump

Test …

Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method  . Through this blog I am going to  compare the both methods .

Remember you can use the parallel data loading utility only via MySQL Shell .

Internal Work Flow :

This section describes the …

Hope everyone aware about known about LVM(Logical Volume Manager) an extremely useful tool for handling the storage at various levels. LVM basically functions by layering abstractions on top of physical storage devices as mentioned below in the illustration.

Below is a simple diagrammatic expression of LVM

         sda1  sdb1   (PV:s on partitions or whole disks)
           \    /
            \  /
          Vgmysql      (VG)
           / | \
         /   |   \
      data  log  tmp  (LV:s)
       |     |    |
      xfs  ext4  xfs  (filesystems)

IOPS is an extremely important resource, when it comes to storage it defines the performance of disk. Let’s not forget PIOPS(Provisioned IOPS) one of the major selling points for AWS and other cloud vendors for production machines such …

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example: 

mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …

MySQL 8.0 introduces many new features. We will have a look at the exciting histogram  feature in MySQL 8.0

Histogram:

What is Histogram?

In General, a histogram is an accurate representation of the distribution of numerical data. In MySQL, It will be useful to collect data distribution for a specific column.

What problem it solves?

In general DB Optimizer gives us the best execution plan, But the stats make the execution plan better and better .The data distribution of values in columns can make good impact in optimiser in case of column with less distinct values.

We will see an example of how it helps optimizer in some cases. 

I have used a production case. MySQL version is 8.0.15 installed in ubuntu 18.04 (32GB RAM,8 core) with optimal configuration. Let us try to optimise a …