First I want to thank everyone who attended my May 21, 2020 webinar “How Safe is Asynchronous Master-Master Setup in MySQL?“. Recording and slides are available on the webinar page.

Here are answers to the questions from participants which I was not able to provide during the webinar.

Q: What do you generally think of hosting Relational Databases on VM’s as opposed to Bare metals?

A: With modern hardware and modern virtual machines this is absolutely possible. I know about many successful high loaded applications that run MySQL on VMs.

Just note that running a few VMs on a single physical machine may lead to resource loss rather than saving. For …

Percona engineers often need to analyze and review complex SQL database queries. Good formatting can make these SQL queries much easier to understand and work with. Without clear formatting, queries can become confusing and hard to debug.

Online query formatting services provide one set of solutions. Examples are Code Beautify, FreeFormatter, and sqlformat.org. However, many users are not comfortable sharing their queries with third-party services, especially if your SQL code contains confidential information.

This article examines alternatives to online tools for SQL query formatting tools that have been successfully used by Percona engineers. These solutions come in different types:

1. Plug-ins to your code editor or …

Here in Percona’s Training department, we like to think that we instruct our learners on the best practices for all things MySQL-related. In addition to performance tuning, query optimization, and replication configurations, another important topic is backups. Let’s dive in and discuss some of the basics and best practices around backups for MySQL.

Logical MySQL Backups

In MySQL, it is possible to take backups in two different forms. The first form, logical, is the most universal. Essentially, you create all of the necessary INSERT statements to repopulate your table data. The two most popular tools in the area are mysqldump and mydumper.

mysqldump

This tool has been around since the beginning and supports a slew of different options, too numerous to discuss in this post.

Here is a simple example for taking a logical backup of the …

Database backups are a fundamental requirement in almost every implementation, no matter the size of the company or the nature of the application. Taking a backup should be a simple task that can be automated to ensure it’s done consistently and on schedule. Percona has an enterprise-grade backup tool, Percona XtraBackup, that can be used to accomplish these tasks. Percona also has a Percona Kubernetes Operator for Percona XtraDB Cluster (PXC Operator), which has Percona XtraBackup built into it. Percona XtraBackup has the ability for both automated and on-demand backups. Today we will explore taking backups and restoring these backups using the PXC Operator deployed …

Percona has enforced stronger security in Percona XtraDB Cluster (PXC) 8, but this requires some attention during the rollout of the new server version, so let see the why and what.

In PXC there are two different kinds of traffic: client-server exchange (ie: application traffic), and replication traffic. The latter refers to any SST/IST, write-set, and other service messages the nodes exchange.

In PXC 5.7 it is possible to activate SSL encryption by enabling the variable pxc-encrypt-cluster-traffic by following the instructions.

In PXC 8, we choose to enable encryption by default on all replication traffic, to have the highest out-of-box security enforcement.

While this is an obvious …

During this webinar, Tyler Duzan, Product Manager for MySQL and Cloud at Percona, will discuss Percona’s decision to delay Percona XtraDB Cluster 8.0 to ship it with Galera 4 instead of Galera 3, and why that matters. In addition, he’ll talk about the work done to integrate MySQL 8.0 features with Galera as well as how Percona is treating Kubernetes as one of its primary target platforms with this release. This webinar will provide an overall view of the current state of best-in-breed MySQL clustering as well as the Percona roadmap for the future.

Please join Tyler Duzan of Percona, on Tuesday, May 26 at 11 am EDT for his webinar “What’s New in Percona XtraDB Cluster (PXC) 8.0?”.

…

Percona XtraDB Cluster 8.0 comes with an upgraded Galera 4.0 library, which provides a new feature – streaming replication. Let’s review what it is and when it might be helpful.

Previous versions of Percona XtraDB Cluster with Galera 3.x had a limitation in how big transactions are handled.

Let’s review the performance under sysbench-tpcc workload when in parallel we update a big update on a table that is even non-related to the tables in the primary workload.

Without Streaming Replication

Let’s run two workloads.

1. sysbench-tpcc workload with 1 sec resolution
2. In parallel run UPDATE oltp.sbtest SET k=k+1 LIMIT 1000000

Running update:

mysql> update sbtest1 set k=k+1 limit 1000000;
Query OK, 1000000 rows affected (34.48 sec)
Rows matched: 1000000  Changed: …

I was testing the latest Percona XtraDB Cluster 8.0 (PXC) release which has the Galera 4 plugin, and I would like to share my experiences and thoughts on the Streaming Replication feature so far.

What Is Streaming Replication, in One Sentence?

In Galera 4, the large transaction could split into smaller fragments, and even before it got committed these fragments have been replicated to the other nodes and have already started the certification and apply process.

The manual describes all the pros and cons, but let’s see how it works. I have created a table with 10M rows and I am going to run some large updates on that.

First I have run the updates without Streaming Replication, and because it is disabled by …

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. It executes INSERT statements to import the data, that runs in a single connection to fill the new table. In this repository, there is a script called myloader_pt-osc.sh that uses myloader to execute parallel INSERTs, instead of using pt-online-schema-change internal tooling, but it also uses a patched version of pt-online-schema-change.

Patch pt-online-schema-change

There are 2 changes that we needed to perform on pt-online-schema-change:

1- Be able to stop the execution after the copy of the table is created
2- Tell pt-online-schema-change that no data needs to be imported

We are …

As a Solutions Engineer at Percona, one of my responsibilities is to support our customers as they investigate new and emerging technologies. This affords me the opportunity to speak to many current and new customers who partner with Percona.

The topic of Kubernetes is becoming more popular as companies are investigating and adopting this technology. The issue most companies are encountering is having a stateful database that doesn’t fall victim to an environment tuned for ephemeral workloads. This obviously introduces a level of complexity as to how to run a stateful database in an inherently stateless world, as databases are not natively designed for that.

To make your life easier, as a part of the Percona Cloud-Native Autonomous Database Initiative, our engineering teams have built a …