Why do we sometimes want to keep duplicate indexes?

I’ve done dutiful DBA work in the past to identify and remove what are commonly called duplicate indexes. That is, those indexes that look like (a) and (a,b). The thought is that a query will utilize an index as easily on (a) as on (a,b), and removing (a) will save storage cost and write performance. I’ve had the experience, though, of removing (a) and seeing performance tank.

(As an aside, these are really redundant indexes. A duplicate index would be (a,b) and (a,b) by two different names – this can commonly be done by object relational mapping (ORM) or other automated schema creation tools. I’ll call (a) and (a,b) redundant indexes below.)

This test is on Percona Server 5.7.14 with the sys schema installed and performance schema enabled.

Given two tables with the same number of rows and …

Great news: Group Replication is now officially released with MySQL 5.7.17, congratulations to everybody that worked so hard for it!

In this post I plan to share more details about the performance of Group Replication while addressing one of our frequently asked questions, which is how it performs compared to Galera.…

I’ve been lately compiling and generating .deb packages for several MySQL and MariaDB recent versions, and I wanted to try them more in depth -specially MySQL 8.0 and MariaDB 10.2, both of which are still in development.

Several people have already given their first impressions (or will do soon), and testing early is the best way to catch bugs and regressions, and get them fixed before the official release. In fact, as I will comment later, I ran into breaking bugs on both MySQL 8.0 and MariaDB 10.2, which I …

Very often MySQL is much less stable than we realize. In this video I explain how to detect and dianose these MySQL stalls that last for 5-10 seconds or more.

The reason nobody knows about these issues is that they’re:
a) rare
b) intermittent
c) monitoring software can’t catch them

You can use pt-stalk to detect and diagnose such issues. All of the tools I have mentioned in this video are part of Percona Toolkit (no wonder they all start with PT), you can find the toolkit here:

Percona Toolkit

Here’s the main commands I have discussed in this video:

Starting pt-stalk in foreground

# pt-stalk

Starting pt-stalk in background with email notification:

# pt-stalk …

This post introduces PyMyTools v0.1: my first version of a simple diagnostic toolkit for automating boring DBA tasks. Read on for tool descriptions and demonstrations!

Introduction No lengthy introductions this time:

• I wrote these tools to make my own everyday tasks less time consuming. I hope you too will find them useful.
• Tools can be downloaded from GitHub and are provided under the MIT License.
• Your feedback is very welcome (use GitHub Issues).

Tools: general description The tools are designed to automate some of the simple DBA tasks:

• Monitor the values of server status variables.
• Report on basic status variables (buffer/cache usage, uptime, query traffic).
• Dump …

The details on this issue are here:
https://github.com/facebook/mysql-5.6/issues/369

This test is very simple. I loaded the SSB (star schema benchmark) data for scale factor 20 (12GB raw data), added indexes, and tried to count the rows in the table.

After loading data and creating indexes, the .rocksdb data directory is 17GB in size.

A full table scan "count(*)" query takes less than four minutes, sometimes reading over 1M rows per second, but when scanning the index to accomplish the same count, the database can only scan around 2000 rows per second. The four minute query would take an estimated 1000 minutes, a 250x difference.

I have eliminated the type of CRC32 function (SSE vs non-SSE) by forcing the hardware SSE function by patching the code.

There seem to be problems with any queries …

The details on this issue are here:
https://github.com/facebook/mysql-5.6/issues/369

This test is very simple. I loaded the SSB (star schema benchmark) data for scale factor 20 (12GB raw data), added indexes, and tried to count the rows in the table.

After loading data and creating indexes, the .rocksdb data directory is 17GB in size.

A full table scan "count(*)" query takes less than four minutes, sometimes reading over 1M rows per second, but when scanning the index to accomplish the same count, the database can only scan around 2000 rows per second. The four minute query would take an estimated 1000 minutes, a 250x difference.

I have eliminated the type of CRC32 function (SSE vs non-SSE) by forcing the hardware SSE function by patching the code.

There seem to be problems with any queries …

When your car doesn’t start, you don’t just blindly change the battery, starter, fuel pump, spark plugs or all of the above. Instead, you go to your mechanic and ask him to check what is wrong (or you check it yourself if you are the mechanic) and then fix whatever is broken.

Yet very often I see DBAs doing exactly the opposite with their MySQL servers. Rather than assessing what is the server so busy with, they keep changing configuration options until the problem “goes away”. Alternatively, they add more RAM, more CPUs or faster disks, depending on which resources seems to be the most busy at a time. Or they switch to a new server altogether.

MySQL (with a help of some tools) has a really convenient way to analyse the workload and see clearly what exactly is MySQL so busy doing. And even how much improvement you can expect by, say, fixing a specific MySQL query.

…

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is …

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is …