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 …

It’s been a while since I wrote about ClickHouse, there are a lot of new features that are worth mentioning, and for this, I recommend to follow the Altinity Blog but today I want to look at the improved integration of ClickHouse and MySQL.

There are two features:

1. Using MySQL protocol and MySQL client to connect to ClickHouse
2. Use MySQL tables to select and join with ClickHouse tables

Using MySQL Protocol

By enabling MySQL protocol in ClickHouse server, you will allow for the MySQL command line tool or applications that typically connect to MySQL to connect to ClickHouse and execute queries.

With this, keep in mind:

• It does not automatically allow you to use MySQL SQL syntax, you still need to write queries in ClickHouse-dialect.
• The same issue with datatypes. ClickHouse uses Int32/Int64 …

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 …

In this blog post, we will continue to explore Vitess and test an example database provided in its repository. This is Part III of the previously discussed installation of Vitess on minikube environment, so please make sure to follow those steps to bring the cluster up to the following level.  

$ kubectl get pods,jobs
NAME                                                                READY     STATUS    RESTARTS   AGE
po/etcd-global-kbbcqlgvp9                                           1/1       Running   0          43m
po/etcd-zone1-lpc5zmdxxn                                            1/1       Running   0          43m
po/my-release-etcd-operator-etcd-backup-operator-6684dd6d8c-pr4n4   1/1       Running   0          1h
po/my-release-etcd-operator-etcd-operator-86d94989d6-w9lpx          1/1       Running …

In this blog post, I’d like to share some experiences in setting up a Vitess environment for local tests and development on OSX/macOS. As previously, I have presented How To Test and Deploy Kubernetes Operator for MySQL(PXC) in OSX/macOS, this time I will be showing how to Run Vitess on Kubernetes.

Since running Kubernetes on a laptop is only experimental, I had faced several issues going through straight forward installation steps so I had to apply a few workarounds to the environment. This setup will have only minimum customization involved.

For a high-level overview of Vitess, please visit Part I of this series, …

The promise of DBaaS like RDS is to reduce operational overhead (among other things) and one of the stellar cases is upgrades (major and minor). The suggested procedure involves just a couple of steps. For example, using AWS Console, you can enable “Auto minor upgrade” or modify the DB instance and schedule the upgrade to run in the next maintenance window.

But, both these options are risky because the upgrade process will start during the maintenance window but it is NOT guaranteed that the upgrade will be completed within the specified duration. 

The Problem

RDS performs a few extra steps to ensure the data consistency and rollback, making the minor version upgrade a time-consuming process:

• It takes a backup (if automated backups are enabled) prior to starting the upgrade process.
• Performs slow shutdown after setting …

In this blog post series, we will discuss an overview of Vitess technology. Vitess is a database clustering solution for horizontal scaling of data sets currently suitable to 250Gb – 300Gb in sizes*. It’s a proven technology used by several web-scale companies including YouTube. PlanetScale is the company behind developing and supporting this open-sourced project. 

Introduction to Vitess 

Here’s high-level Vitess architecture visually explained. 

Ref: Sugu Sougoumarane presents an overview of Vitess at Highload in Moscow.

Basic Vitess Components

There are two main components of Vitess. One of them is vtgate which works as a query router where vttablet is a proxy to MySQL to serve the data. 

• …

While we’ve had MySQL Group Replication support in ProxySQL since version 1.3 (native as of v1.4), development has continued in subsequent versions. I’d like to describe a scenario of how latency can affect ProxySQL in a MySQL Group Replication environment, and outline a few new features that might help mitigate those issues. Before we dive into the specifics of the discussion, however, let’s take a quick overview of ProxySQL and Group Replication for those who may not be familiar.

MySQL Group Replication

Similar in functionality to Percona XtraDB Cluster or Galera, MySQL Group Replication is the only synchronous native HA solution for MySQL*. With built-in automatic distributed recovery, conflict detection, and group membership, MySQL GR provides a completely native HA solution for MySQL environments.

In a high concurrency world, where more and more users->connections->threads are used, contention is a given. But how do we identify the contention point easily?

Different approaches had been discussed previously, like the one using Perf and Flame graphs to track down the function taking way more time than expected. That method is great but how can we do it with what one normally has, like the MySQL Client? Enter: the SEMAPHORES section from the SHOW ENGINE INNODB STATUS command output.

SEMAPHORES

The SEMAPHORES section displays all the metrics related to InnoDB mechanics on waits. This section is your best friend if you have a high concurrency workload. In short, it contains 2 kinds of data: …