It’s not essential to understand how MySQL® and Percona Server for MySQL build indexes. However, if you have an understanding of the processing, it could help when you want to reserve an appropriate amount of space for data inserts. From MySQL 5.7, developers changed the way they built secondary indexes for InnoDB, applying a bottom-up rather than the top-down approach used in earlier releases. In this post, I’ll walk through an example to show how an InnoDB index is built. At the end, I’ll explain how you can use this understanding to set an appropriate value for innodb_fill_factor.

Index building process

To build an index on a table with existing data, there are the following phases in InnoDB

1. Read phase (read from clustered index and build secondary index entries)
2. Merge sort phase
…

In this blog, I will provide answers to the Q & A for the MySQL 8.0 Architecture and Enhancement webinar.

First, I want to thank everybody for attending my April 9, 2019, webinar. The recording and slides are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What kind of Encryption levels does MySQL 8.0 provide?

The MySQL data-at-rest encryption feature supports the Advanced Encryption Standard (AES) block-based encryption algorithm and aes_256_cbc encryption algorithm for data at rest is hard coded.

Q: At what frequency does the redo log buffer flush the changes to disk? When is the commit variable set to zero?

Here’s an …

What's a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an "obvious" candidate, such as a variety of ISO standards, including: ISO 639 language codesISO 3166 country codesISO 4217 currency codes But even in those cases, there might … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables →

Percona is glad to announce the release of Percona XtraBackup 8.0.5 on March 4, 2019. Downloads are available from our download site and from 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 long periods of downtime. Offered free as an open source solution, it drives down backup costs while …

MySQL User camp is one of the prestigious MySQL meetup happening in India for the past 6 years. Mydbops DBA’s PonSuresh and Vignesh has presented about the “JSON functions and their improvements in MySQL 8.0” at MySQL User Camp Bangalore on 13-02-2019.

JSON has been more improved a lot in MySQL 8.0 and improvements in LOB storage of MySQL boost it performance further. This presentation covers the JSON performance enhancements in MySQL 8.0 with its basic functions.

JSON improvements in MySQL 8.0 from Mydbops

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 …