Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.

It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …

Kubernetes has been a big trend for a while now, particularly well-suited for microservices. Running your main databases on Kubernetes is probably NOT what you are looking for. However, there’s a niche market for them. My colleague Stephen Thorn did a great job explaining this in The Criticality of a Kubernetes Operator for Databases. If you are considering running your database on Kubernetes, have a look at it first. And, if after reading it you start wondering how the Operator works, Stephen also wrote an Introduction to Percona Kubernetes Operator for Percona XtraDB Cluster (PXC), which presents the Kubernetes architecture and how the Percona Operator simplifies the deployment of a …

Here at Pythian we get a lot of exposure to new technologies and implementation strategies via the work we do internally and for our clients. The most noteworthy technology stack that I’ve seen get a lot of traction in the MySQL community recently is the high availability stack including Orchestrator, Consul and ProxySQL. 

I won’t dive too deeply into the details of this implementation as there are several blog posts that our team has submitted on this topic, but the key thing I want you to keep in mind for this particular topic is the usage of Consul as a “source of truth” for the state of your MySQL replication clusters. If Orchestrator or its adjacent scripts are running as expected, Consul should always have the latest information pertaining to the state of your cluster. This is incredibly valuable. In fact, …

Here at Pythian we like to pay attention to what brings people to our site, and one of the main queries we’ve been seeing is “How can I allow a remote MySQL database connection?” Since our mission is to help you love your data, we asked Matthias Crauwels, Lead Database Consultant on one of Pythian’s MySQL teams to address this topic. Over to Matthias!

I’m hoping this may be the start of a series of posts where I can share some common MySQL knowledge in a (hopefully) clear and understandable manner. So let’s get started.

Technical requirements

Historically MySQL allows a client to connect in two different ways. You can either use a local socket solution (on a Linux/Unix system this is called a Unix socket, whereas on Windows systems it’s a named pipe) or via a TCP connection to a listening port (by default 3306).

Since the introduction of MySQL 8.0, a third way is also available using a newer …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on May 23, 2018.

In recent weeks I’ve been focusing on Docker in order to get a much better understanding of the containerized world that is materializing in front of us. Containers aren’t just for stateless applications anymore and we’re seeing more cases where MySQL and other databases are being launched in a containerized fashion, so it’s important to know how to configure your MySQL container!

In docker hub, you will see an option for this by doing a volume mount from the Docker host to the container on /etc/mysql/conf.d. But the problem is that the container image you’re using may …

Schema changes are the big challenges in Galera replication. So, it is recommended to understand the schema changes operation for everyone who uses the Percona XtraDB Cluster (PXB)/Galera clusters. In this blog, I am going to explain the operation and impact of the various schema changes methods used in the PXB/Galera cluster.

• Schema changes with “wsrep_OSU_method = TOI”
• Schema changes with “wsrep_OSU_method = RSU”
• Schema changes with “ONLINE ALGORITHMS”
• Schema changes with “pt-osc”
• Schema changes with “gh-ost”

For testing:

• I have configured the 3-node Percona Xtradb Cluster (8.0.19).
• Executing read/write load using the sysbench.

mysql> select @@wsrep_cluster_address\G
*************************** 1. row …

After spending four years working with ProxySQL in production, I’ve learned a few interesting lessons about interpreting and processing query rules. I hope to save you some time (and avoid wrong turns) with this summary of ProxySQL query rules.

ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for traffic mirroring, among other use cases.

When the rule set is short and simple, you can easily understand what the outcome for a certain query would be. However, for complex sets or combined scenarios, you need a deeper understanding of the engine logic.

Note 1: Rules are processed in rule_id order

It might seem like a trivial start, but during testing or implementation, …

We sometimes receive challenging requests… this is a story about one of those times.

The customer has implemented a sharding solution and would like us to review alternatives or improvements. We analyzed the possibility of using ProxySQL as it looked to be a simple implementation. However, as we had 200 shards we had to implement 200 rules — the first shard didn’t have much overload, but the latest one had to go through 200 rules and took longer.

My first idea was to use FLAGIN and FLAGOUT creating a B-Tree, but the performance was the same. Reviewing the code, I realized that the rules were implemented as a list, which means that, in the end, all the rules were going to be processed until hit with the right one and FLAGIN is used just to filter out.

At that point, I asked, what could I do? Is it possible to implement it differently? What is the performance impact?

One Problem, Two Solutions

I …

PMM (Percona Monitoring and Management) is a great community tool for monitoring your OSDB (MySQL, PostgreSQL, MongoDB) fleet. It’s feature rich, and it’s built and distributed as open-source based on several de-facto industry standard tools such as Grafana and Prometheus. In the second half of last year, major version 2 was released and whereas the major components remained mostly the same, some significant architectural changes were made, but this is out-of-scope for this post.

Upgrading to PMM2 is basically the same as starting from scratch with a fresh install; it requires re-adding all your servers, which can be a painful process if you have a large fleet of instances. For servers where you have OS level access, you can just install the PMM2 agent, but for RDS there is no access to the OS so another approach is required. In this blog, I will focus on getting a large number of RDS instances added.

Adding RDS instances …

In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.

A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.

Some of the problems we face when we demonise the process or use screen for running processes.

• The daemon process gets killed when the server reboot happens. …