There are a lot of things I love about Prometheus; its data model is fantastic for monitoring applications and PromQL language is often more expressive than SQL for data retrieval needs you have in the observability space. One thing, though, I hate about Prometheus with a deep passion is the behavior of its rate() and similar functions, deeply rooted in the Prometheus computational model, which I was told by the development team is not likely to change.

So What’s the Problem, and Why is it Such a Big Deal?

First – the problem.  rate() functions give you the rate of change of the time series for the Interval supplied, so rate(mysql_global_status_questions[10s]) will basically give us the average number of MySQL questions over the last 10seconds. Everything is great so far.

But what if the resolution of this time series is lower than 10 seconds, for example, if we take …

In the last blog post of this series, we discussed in detail how Master Key encryption works. In this post, based on what we already know about Master Key encryption, we look into how Master Key rotation works.

The idea behind Master Key rotation is that we want to generate a new Master Key and use this new Master Key to re-encrypt the tablespace key (stored in tablespace’s header).

Let’s remind ourselves what a Master Key encryption header looks like (it is located in tablespace’s header):

From the previous blog post, we know that when a server starts it goes through all encrypted tablespaces’ encryption headers. During that, it remembers the highest KEY ID it read from all the encrypted tablespaces. For instance, if we have three tables with KEY_ID = 3 and one table with KEY ID = 4, it means that …

This talk covers some of the challenges we sought to address by creating a Kubernetes Operator for Percona XtraDB Cluster, as well as a look into the current state of the Operator, a brief demonstration of its capabilities, and a preview of the roadmap for the remainder of the year. Find out how you can deploy a 3-node PXC cluster in under five minutes and handle providing self-service databases on the cloud in a cloud-vendor agnostic way. You’ll have the opportunity to ask the Product Manager questions and provide feedback on what challenges you’d like us to solve in the Kubernetes landscape.

Please join Percona Product Manager Tyler Duzan on Wednesday, February 26, 2020, at 1 pm EST for his webinar “Building a Kubernetes Operator for Percona XtraDB Cluster”.

…

This is the second part of a two-articles series. In the first part, we introduced the Common Table Expression (CTE), a new feature available on MySQL 8.0 as well as Percona Server for MySQL 8.0. In this article, we’ll present the Recursive Common Table Expression. SQL is generally poor at recursive structures, but it is now possible on MySQL to write recursive queries. Before MySQL 8.0, recursion was possible only by creating stored routines.

What is a Recursive Common Table Expression?

A recursive CTE is one having a subquery that refers to its own name. It is particularly useful in the following cases:

• To generate series
• Hierarchical or tree-structured data traversal

This blog is the first part of a two-articles series. In this article, I’m going to introduce the Common Table Expression (CTE), a new feature available on MySQL 8.0, as well as Percona Server for MySQL 8.

What is a Common Table Expression?

We can define a CTE as an alternative to a derived table. In a small way, CTE simplifies complex joins and subqueries, improving the readability of the queries. CTE is part of ANSI SQL 99 and was introduced in MySQL 8.0.1. The same feature is available even on Percona Server for MySQL 8.0.

The main reasons for using CTE are:

• Better readability of the queries
• Can be referenced multiple times in the same query
• Improved performance
• A valid alternative to a VIEW, if your user cannot create VIEWs
• Easier chaining of multiple CTE …

As our Percona Kubernetes Operator for Percona XtraDB Cluster gains in popularity, I am getting questions about its performance and how to measure it properly. Sysbench is the most popular tool for database performance evaluation, so let’s review how we can use it with Percona XtraDB Cluster Operator.

Operator Setup

I will assume that you have an operator running (if not, this is the topic for a different post). We have the documentation on how to get it going, and we will start a three-node cluster using the following cr.yaml file:

apiVersion: pxc.percona.com/v1-3-0
kind: PerconaXtraDBCluster
metadata:
  name: cluster1
  finalizers:
    - delete-pxc-pods-in-order
spec:
  secretsName: my-cluster-secrets
  sslSecretName: …

From the initial release, one of the biggest complaints I had about Group Replication is that it allowed “stale” reads and there was no way to prevent them or to even know that you read “stale” data. That was a huge limitation. Thankfully, Oracle released features to control the consistency levels, and it was exactly a year ago! I don’t know about you, but I personally was confused by naming it group_replication_consistency=’AFTER’ or ‘BEFORE’.

So now I want to try to make sense of it and share my understanding (even if it is one year later).

Setup:

• 3 Node Percona Server for MySQL 8.0.18 running in …

As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune it correctly. This post is a bit long and complex as it goes very deep into some InnoDB internals.

InnoDB internally handles flush operations in the background to remove dirty pages from the buffer pool. A dirty page is a page that is modified in memory but not yet flushed to disk. This is done to lower the write load and the latency of the transactions. Let’s explore the various sources of flushing inside InnoDB.

Idle Flushing

We already discussed the idle flushing in the previous post …

If you’re using MySQL’s Performance Schema you may use “query digests” as IDs to identify specific query patterns in the events_statements_summary_by_digest Performance Schema Table.

You might assume these hashes are stable between different versions, so, for example, when upgrading from MySQL 5.7 to MySQL 8, you can compare the query response time and other execution details for the same hashes as part of your upgrade process and have confidence that queries run the same (or better) after the upgrade.   Unfortunately, you can’t.

For some reason, the hashing algorithm is different between MySQL 5.6,  MySQL 5.7, and MySQL 8 even for the most trivial queries:

MySQL 5.6

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';
+----------------------------------+-------------+
| digest                           | digest_text | …

Percona Server for MySQL 8.0.18 ships all functionality to run Group Replication and InnoDB Cluster setups, so I decided to evaluate how it works and how it compares with Percona XtraDB Cluster in some situations.

For this I planned to use three bare metal nodes, SSD drives, and a 10Gb network available for in-between nodes communication, but later I also added tests on three bare metal nodes with NVMe drives and 2x10Gb network cards.

To simplify deployment, I created simple ansible scripts.

Load Data

The first initial logical step is to load data into an empty cluster, so let’s do this with our sysbench-tpcc …