When I speak about MySQL performance troubleshooting (or frankly any other database), I tend to speak about four primary resources which typically end up being a bottleneck and limiting system performance: CPU, Memory, Disk, and Network.

It would be great if when seeing what resource is a bottleneck, we could also easily see what queries contribute the most to its usage and optimize or eliminate them. Unfortunately, it is not as easy as it may seem.

First, MySQL does not really provide very good instrumentation in those terms, and it is not easy to get information on how much CPU usage, Disk IO, or Memory a given query caused.  Second, direct attribution is not even possible in a lot of cases. For example, disk writes from flushing data from the InnoDB buffer pool in the …

You got yourself a Kubernetes cluster and are now testing our Percona Kubernetes Operator for Percona XtraDB Cluster. Everything is working great and you decided that you want to increase the number of Percona XtraDB Cluster (PXC) pods from the default 3, to let’s say, 5 pods.

It’s just a matter of running the following command:

kubectl patch pxc cluster1 --type='json' -p='[{"op": "replace", "path": "/spec/pxc/size", "value": 5 }]'

Good, you run the command without issues and now you will have 5 pxc pods! Right? Let’s check out how the pods are being replicated:

kubectl get pods | grep pxc
cluster1-pxc-0                                     1/1     Running   0          25m
cluster1-pxc-1                                     1/1     Running   0          23m
cluster1-pxc-2                                     1/1     Running …

The Percona Kubernetes Operator for Percona XtraDB Cluster(PXC) comes with ProxySQL as part of the deal. And to be honest, the behavior of ProxySQL is pretty much the same as in a regular non-k8s deployment of it. So why bother to write a blog about it? Because what happens around ProxySQL in the context of the operator is actually interesting.

ProxySQL is deployed on its own POD (that can be scaled as well as the PXC Pods can). Each ProxySQL Pod has its own ProxySQL Container and a sidecar container. If you are curious, you can find out which node holds the pod by running

kubectl describe pod cluster1-proxysql-0 | grep Node:
Node: ip-192-168-37-111.ec2.internal/192.168.37.111

Login into and ask for the running containers. You will see something like this:

[root@ip-192-168-37-111 ~]# docker ps | grep -i proxysql …

In real life, there are frequent cases where getting a running application to work correctly is strongly dependent on consistent write/read operations. This is no issue when using a single data node as a provider, but it becomes more concerning and challenging when adding additional nodes for high availability and/or read scaling. 

In the MySQL dimension, I have already described it here in my blog Dirty Reads in High Availability Solution.

We go from the most loosely-coupled database clusters with primary-replica async replication, to the fully tightly-coupled database clusters with NDB Cluster (MySQL/Oracle).

Adding components like ProxySQL to the architecture can, from one side, help in improving high availability, and from the other, it can amplify and randomize the negative effect of …

Last year, I made the first post in a small series, which aimed to highlight unique features of Percona Server for MySQL, by discussing binlog_space_limit option.

Today, I am going to discuss another important type of log available in MySQL that is enhanced in Percona Server for MySQL – the slow query log. The reason why I am doing this is that although this extension has existed since the very early times of versions 5.1 (over 10 years ago!), many people are still unaware of it, which I see from time to time when working with Support customers.

Default Slow Log Inadequacy

How many times have you been wondering why, whilst reviewing slow query logs, the very same query occasionally runs way slower than usual? There may be many reasons for that, but the standard slow …

A few days ago, a customer got in touch asking how they could use Percona Monitoring and Management (PMM) to monitor the roles played by each node in an InnoDB cluster. More specifically, they wanted to check when one of the nodes changed its role from Primary to Secondary, or vice-versa. PMM allows for a high level of flexibility and customization through its support for custom queries, and we just have to be creative on how we approach the problem. In this post, we present the steps we did to test our solution, including the deployment of a 3-node InnoDB Cluster hosted in the same server (for testing) and a PMM 2 server, and connecting them together. Even though this has already been covered in other blog …

The RED Method (Rate, Errors, Duration) is one of the more popular performance monitoring approaches.  It is often applied to Monitoring Microservices though there is nothing that prevents it from being applied to databases like MySQL.

In Percona Monitoring and Management (PMM) v2 we have all the required information stored in the ClickHouse database, and with the built-in ClickHouse datasource it is a matter of creating a dashboard to visualize the data.

While I was editing the dashboard, I added a few other panels, beyond what RED Method requires, in order to show some of the cool things you can do with Grafana + ClickHouse data source and …

In this webinar, Sveta Smirnova, MySQL Engineer at Percona, will uncover nuances of Percona XtraDB Cluster (PXC) schema upgrades and point out details you need to give extra attention to.

Percona XtraDB Cluster (PXC) is a 100% synchronized cluster in regards to DML operations. It is ensured by the optimistic locking model and ability to rollback transaction which cannot be applied on all nodes. However, DDL operations are not transactional in MySQL. This adds complexity when you need to change the schema of the database. Changes made by DDL may affect the results of the queries. Therefore all modifications must replicate on all nodes prior to next data access. For operations that run momentarily, it can be easily achieved, but schema changes may take hours to apply. Therefore in addition to the safest synchronous blocking schema upgrade method: TOI, – PXC supports more relaxed, though not safe, method RSU. RSU: Rolling Schema Upgrade is …

Even though Percona Monitoring and Management 2 (PMM) comes with a lot of dashboards and metrics out of the box, sometimes we need to extend the default metrics by running custom MySQL queries.

For example, suppose you want to have information about cached indexes from Innodb tables from innodb_cached_indexes table. That metric is not being captured by any default dashboard, but it is possible to extend PMM and make it capture the result of custom queries.

Getting Started With Custom Queries

Custom queries can be added to mysqld_exporter by adding them to the appropriate config file in /usr/local/percona/pmm2/collectors/custom-queries/mysql. There are three subdirectories inside it: high-resolution, low-resolution, and medium-resolution. PMM allows …

Our latest Solution Brief uses our relationship with Patreon to detail the ways that Percona services work together to provide customers with a complete database infrastructure solution.

Last year Patreon enlisted Percona’s help with their MySQL databases. Utilizing the full range of Percona Services (Managed Services, Support, Consulting, and Training) has allowed Patreon to make the most of their database infrastructure.

Percona worked with …