Indexes are crucial for optimizing query execution times in databases, but having an excessive number of indexes, or redundant ones, can negatively impact performance. While pt-duplicate-key-checker is the go-to tool for identifying duplicate or redundant indexes in MySQL, it may not catch all duplicates.

In this blog post, we’ll put ourselves to the test and see if we can identify duplicate and redundant indexes in MySQL. Toward the end, we will identify what the pt-duplicate-key-checker doesn’t.

The unique quiz

Consider the following MySQL table definition. Let’s put our brains to work and note any of the duplicate or redundant indexes (play fair, don’t cheat):

CREATE TABLE `table_with_lot_of_trouble` (
`id` int NOT NULL,
`col1` varchar(1) DEFAULT NULL,
`col2` varchar(2) DEFAULT NULL,
`col3` varchar(3) …

On MySQL and Percona Server for MySQL, there is a schema called information_schema (I_S) which provides information about database tables, views, indexes, and more.

A lot of useful information can be retrieved from this schema, for example, table metadata and foreign key relations, but trying to query I_S can induce performance degradation if your server is under heavy load, as shown in the following example test.

Disclaimer: This blog post is meant to show a less-known problem but is not meant to be a serious benchmark. The percentage in degradation will vary depending on many factors {hardware, workload, number of tables, configuration, etc.}.

Test

The test compares a baseline of how the server behaves while “under heavy load but no queries against I_S” vs. ” under heavy load + I_S queries” to …

2022 was an exciting year for Percona Monitoring and Management (PMM). We’ve added and improved many features, including Alerting and Backup Management. These updates are designed to keep databases running at peak performance and simplify database operations. But as companies grow and see more demand for their databases, we need to ensure that PMM also remains scalable so you don’t need to worry about its performance while tending to the rest of your environment.

PMM2 uses VictoriaMetrics (VM) as its metrics storage engine. Percona’s co-Founder Peter Zaitsev wrote a detailed post about migration from Prometheus to VictoriaMetrics, One of the most significant differences in …

Let’s look at how you can run Percona databases on Kubernetes, the easy way.

Chances are that if you are using the latest Percona Monitoring and Management (PMM) version, you have seen the availability of the new Percona Database as a Service (DBaaS). If not, go and get a glimpse of the fantastic feature with our docs on DBaaS – Percona Monitoring and Management.

Now, if you like it and wanna give it a try! (Yay!), but you don’t wanna deal with Kubernetes, (nay)o worries; we have a tool for you. Introducing the Percona DBaaS Infrastructure Creator, or Percona My Database as a Service (MyDBaaS).

My Database as a Service

…

When we need to upgrade from MySQL 5.7 to MySQL 8, we could choose to do an in-place upgrade or set up another MySQL 8 server(s) from the existing running MySQL 5.7 replica.

This article will explain how to set up MySQL 8 as a replica from an existing MySQL 5.7 server with Percona XtraBackup.

In my lab, we have two test nodes:

PRIMARY:  mysql57 (192.168.56.111)    with   Percona Server 5.7 and  xtrabackup 2.4 installed
REPLICA:  mysql8    (192.168.56.113)   with  Percona Server 8  and   xtrabackup 8.0 installed

0. Pre-flight check with MySQL upgrade checker utility

Let’s run the MySQL upgrade checker utility to verify whether MySQL 5.7 server instances are ready for an upgrade.

MySQL  localhost  JS > util.checkForServerUpgrade('root@localhost:3306', {"password":"####", …

This post was originally published in October 2018 and was updated in March 2023.

pt-query-digest is one of the most commonly used tools for query auditing in MySQL. By default, pt-query-digest reports the top ten queries consuming the most amount of time inside MySQL. A query that takes more time than the set threshold for completion is considered slow, but it’s not always true that tuning such queries makes them faster. Sometimes, when resources on the server are busy, it will impact every other operation on the server, and so will impact queries too. In such cases, you will see the proportion of slow queries going up. That can also include queries that work fine in general.

This article explains a small trick to identify such spots using pt-query-digest and the slow query log. pt-query-digest …

Hello friends, at Percona Managed Services, we usually do different types of things every day, including routine tasks, monitoring, and, very frequently, answering questions that are not always easy to answer.

A few days ago, a client asked us the following question: “Hey Percona, I have a question and a problem simultaneously: I want to delete rows from a table from a specific date back because the data is not necessary. I tried to run the DELETE command, which gave me this error: Cannot delete or update a parent row: a foreign key constraint fails. Could you please help me?”

At first glance, the error message was obviously clear: the table from which rows were to be deleted had a child table, which prevented the execution of the DELETE directly.

“Don’t worry, we’ll take a look at the issue, and …

Public cloud spending is slowing down. Quarter-over-quarter growth is no longer hitting 30% gains for AWS, Google, and Microsoft. This is businesses’ response to tough and uncertain macroeconomic conditions, where organizations scrutinize their public cloud spending to optimize and adjust.

In this blog post, we will see how running databases on Kubernetes with Percona Operators can reduce your cloud bill when compared to using AWS RDS.

Inputs

These are the following instances that we will start with:

• AWS
• RDS for MySQL in us-east-1
• 10 x db.r5.4xlarge
• 200 GB storage each

The cost of RDS consists mostly of two things – compute and storage. We will not consider data transfer or backup costs in this article.

• db.r5.4xlarge – $1.92/hour or …

Oracle’s MySQL 5.7 has had a good, long run, but the official End of Life is October of 2023. The Era of MySQL 5.x will be over, and only MySQL 8.0 will be officially supported. Yup, six months away. So if you are running MySQL, you need to consider upgrading to version 8.0 N-O-W!!

What does an upgrade provide?

MySQL 8.0 has many really cool features and improvements that are well worth the upgrade. The default character set of UTF8MB4 gives Unicode version 9.0 support. So you get the Umaluts, Cedils, and C-J-K Language support in your data, plus emojis. 8.0 is optimized around this character set. This gives you all the international characters you probably need to support global operations.

The Structured Query Language has been greatly enhanced. If you have trouble writing subqueries, you rejoice in lateral-derived joins and Common Table Expressions (CTEs). There is a new intersect clause to aid with sets. …

Introduction/TLDR:

We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.

The problem:

Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.

However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements …