Mydbops database meetup was held on 26-01-2019 and 48 members from open source database community has attended this event. We had talks on MySQL and MongoDB databases.

Open source database enthusiastic from companies like OLA Cabs, Searce Inc, PayTm, Quikr, Zensar, Grab, SAP labs, Travel Yarri, Meddiff and a few has travelled from other cities of India like Hyderabad and Chennai.

The Welcome talk was given my Selva venkatesh of Mydbops. He spoke about the open source database community and welcomed all the participants and speakers.

Followed by it Karthik P R , CEO/ Founder, Mydbops made his presentation on “InnoDB Performance Optimisation” . InnoDB is the prime engine in MySQL.This talk focuses on the InnoDB Architecture in MySQL 8.0 and its fine tuning. He also answered the various questions raised …

In one of my previous posts, I started a series on data compression options with MySQL. The first post focused on the more traditional compression options like InnoDB Barracuda page compression and MyISAM packing. With this second part, I’ll discuss a newer compression option, InnoDB transparent page compression with punch holes available since 5.7. First, I’ll describe the transparent page compression method and how it works. Then I’ll present similar results as in the first post.

InnoDB transparent page compression

Before we can discuss transparent page compression, we must understand how InnoDB accesses its data pages. To access an InnoDB page, you need to know the tablespace (the file) and the offset of the page within the …

Tweet

When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.

Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good way to …

Introduction –

Recently i worked on a production issue for one of our client under support .They have a architecture of a three node Galera cluster with one asynchronous slave .

• Node1 – 172.10.2.11
• Node2 – 172.10.2.12
• Node3 – 172.10.2.13
• Replica – 172.10.2.14

Architecture –

The slave(replica) was configured with node3 as replica master. Unfortunately the node 3 was crashed with an OOM killer ,also server has a low gcache size, so when i am trying to start the node 3 , it went to SST . Here the data size was around 2.6 TB , in general for completion of whole SST and joining the node back to cluster will take around  approximately 12 hours.

As i told earlier, the replication slave was under …

Introduction –

                     At Mydbops we are exploring the latest MySQL technologies . Recently i was worked for one of our client the project. The requirement is setting up a three node InnoDB cluster with 3 node ProxySQL cluster . During my ProxySQL configuration, i discovered that the ProxySQL group replication host groups are not loading from ProxySQL config file . Then i reached the ProxySQL official website and found that they are already have the bug for this issue and provided the fix as well ( BUG #1050 ). The fixed version is ProxySQL 1.4.9 .

                   I just had the interest to write the blog about this behaviour, as …

Partitioning is a process in which a single larger table is split into several smaller tables (physically) and still considered as a single table.It is generally a good idea for the tables whose size is in a few 100 GB’s.

While performing select,update,delete operations in a partitioned tables, we can notice a better performance in queries .This simple process of optimization is called partition pruning in which we can avoid scanning the certain partitions which does not have any matching values based on partition key.

The following example will explain you in detail about the partition pruning

Here I have used an Amazon linux 2 machine

Hardware Info

1 core CPU
1 GB RAM 
20 GB Disk (SSD) 
maximum of 3000 IOPS

MySQL 5.7 is installed in this machine and two tables (one is not partitioned and other is partitioned) with 1.7 million records of  same data is loaded in using …

During our recent consulting with one of our client, We came across an interesting issue on RDS. The baseline is that “Low IO size on your RDS instance can affect your DB performance”.  Yes, It’s IO size, Not IOPS.

We had our production systems running on RDS MySQL with a single master, 3 replicas. All instances are of same type db.m4.4xlarge with same parameter group configuration and the disk size is 1.5 TB. According to the AWS user guide, each of these instances can support up to 4500 (sustained IOPS) guaranteed IOPS.

Find below the Write IOPS graph for all the instances.

It’s understood that Write IOPS / pattern on Master can vary when compared with Slave, due to a lot of factors like binlog row format, log writing etc. But it has to be almost similar for all the slaves given that it …

Percona is glad to announce the release of Percona XtraBackup 8.0.4 on December 10, 2018. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate …

… or what I should keep in mind in case of disaster

To retrieve and maintain in SQL format the definition of all tables in a database, is a best practice that we all should adopt. To have that under version control is also another best practice to keep in mind.

While doing that may seem redundant, it can become a life saver in several situations. From the need to review what has historically changed in a table, to knowing who changed what and why… to when you need to recover your data and have your beloved MySQL instance not start…

But let’s be honest, only a few do the right thing, and even fewer keep that information up to date. Given that’s the case, what can we do when we have the need to discover/recover the table structure?

From the beginning, MySQL has used some external files to describe its internal structure.

For instance, if I have a schema named windmills and a table …

Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.

In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.

Needs for partitioning:

• Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
• Easy Archival or Purge by dropping or truncate  of partition
• Lesser fragmentation, hence avoiding frequent table optimization.

Partitions management activity like adding/Dropping partition is made easy and automated by …