Or: Surviving the Codership Acquisition Without Losing Your Cluster Why this long post? Recently, the database landscape shifted significantly when MariaDB plc absorbed Codership. If you aren't familiar, Codership is the c ompany that introduced the Galera library and the WSREP API to MySQL, creating the first virtually synchronous replication solution for the MySQL ecosystem. For years, they…
Let’s be honest: migrating a relational database to Kubernetes sounds fantastic in a whiteboard meeting, but the reality of day-two operations is a completely different story. When moving MySQL to Kubernetes, the ultimate goal is simple: identify a safe, performant set of configuration values for your database pods. But where do you start? Usually, you look at your overall node resources say, a…
It is time to talk again about Flow control and group replication. This time with a special eye on the use of Group Replication in the Kubernetes context. In this article we will dig a bit on how it works and what are the various side effects. The problem Recently I was refining the calculation I use in the MySQL calculator for Operator given I was constantly encountering a very serious problem…
Overview When building high-availability MySQL environments, the choice between MySQL Group Replication (GR) and Percona XtraDB Cluster (PXC) often comes down to how they handle the eternal database dilemma: data consistency versus performance. While both provide "synchronous-like" replication, they approach the problem of stale reads —reading data that has been committed on one node but not yet…
First of all, I want to say a huge thank you to Frederic Descamps and the entire team who worked on the MySQL Belgian Days. I was thrilled to see the high number of presentations and the excellent quality across the board. We had two rooms packed with attendees, and the event was simply great. It was also incredibly productive to finally reconnect with so many people in the community face-to-face.…
I have a curse. My curse is curiosity. Here in Percona I found one person that is very “dangerous” for me, and he and I also share the same first name, well almost, Marcos Albe. Marcos is a smart guy with a lot of ideas, and he is not shy to share them. One day we were talking about sysbench, and he mentioned to me: you know it would be nice to have a test for joins in sysbench. I wonder why we…
"What happens when you run a 10 TB MySQL database on Kubernetes?" That's the question many of our customers and users asked and honestly, we were extremely curious ourselves. So, we ditched the weekend plans, rolled up our sleeves, and jumped down the rabbit hole. What we found was far more challenging (and perhaps a bit more "psychedelic") than expected. We spent days rigorously testing the…
This article is focused on describing the latest performance benchmarking executed on the latest releases of Community MySQL, Percona Server for MySQL and MariaDB. In this set of tests I have used the machine described here . Assumptions There are many ways to run tests, and we know that results may vary depending on how you play with many factors, like the environment or the MySQL server…
MySQL Shell is a powerful tool for managing MySQL databases, and one of its most exciting features is the ability to extend its functionality using plugins. If you're a Python developer, you can create custom plugins to automate tasks, add new commands, or integrate MySQL Shell with other tools. However debugging the python code could be cumbersome for mysql-shell given the python code requires…
This article is focused on describing the latest performance benchmarking executed on the latest release of MySQL and Percona. In this set of tests I have used the machine described here . Assumptions There are many ways to run tests, and we know that results may vary depending on how you play with many factors, like the environment or the MySQL server settings. However, if we compare several…
The aim of this long article is to give you the instructions and tools to migrate your production database, from your current environment to a solution based on Percona Everest (MySQL) . Nice, you decided to test Percona Everest, and you found that it is the tool you were looking for to manage your private DBaaS. For sure the easiest part will be to run new environments to get better understanding…
This article is in large part the same of what I have published in the Percona blog . However I am reproposing it here given it is the first some other benchmarking exercise that I am probably going to present here in an extended format, while it may be more concise in other platforms. In any case why this tests. I am curious, and I do not like (at all) what is happening around MySQL and MariaDB,…
In my previous article, Comparisons of Proxies for MySQL , I showed how MySQL Router was the lesser performing Proxy in the comparison. From that time to now, we had several MySQL releases and, of course, also some new MySQL Router ones. Most importantly, we also had MySQL Router going back to being a level 7 proxy capable of redirecting traffic in case of R/W operations ( see this ). All these…
Some fun is coming While I was writing the internal guidelines on how to migrate from MariaDB to Percona Server, I had to export the users accounts in a portable way. This given MariaDB uses some non standard syntax brings me to first test some external tools such as Fred https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants and our PT-SHOW-GRANTS tool. Useless to say this had open…
Historically MySQL is great in horizontal READ scale. The scaling in that case is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication. However those solutions do not offer the same level of scaling for writes operation. Why? Because the solutions still rely on writing in one single node that works as Primary. Also in…
Overview HAProxy, ProxySQL, MySQL Router (AKA MySQL Proxy), in the last few years I had to answer multiple times on what proxy to use and in what scenario. When designing an architecture there are many components that need to be considered before deciding what is the best solution. When deciding what to pick, there are many things to consider like where the proxy needs to be, if it “just” needs to…
How we could fix a nasty out of space issue leveraging the flexibility of Percona MySQL operator (PMO) When planning a database deployment, one of the most challenging factors to consider is the amount of space we need to dedicate for Data on disk. This is even more cumbersome when working on bare metal. Given it is definitely more difficult to add space when using this kind of solution in respect…
What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter here ( https://www.percona.com/blog/using-mysql-8-dual-passwords/ ). However let me do a brief recap here about it. Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of Password management features…
In our time, every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and given so, it is subject to possible sniffing with all the possible related consequences. Given that it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you…
Last quarter of 2021 AWS released Aurora version 3. This new version aligns Aurora with the latest MySQL 8 version porting many of the advantages MySQL 8 has over previous versions. While this brings a lot of new interesting features for Aurora, what we are going to cover here is to see how DDLs behave when using the ONLINE option. With a quick comparison with what happens in MySQL 8 standard and…
Last month I performed a review of the Percona Operator for MySQL Server ( https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html ) which is still Alpha. That operator is based on Percona Server and uses standard asynchronous replication, with the option to activate semi-synchronous replication to gain higher levels of data consistency between nodes. The whole solution is composed…
Last April 2021 I wrote an article about Online DDL and Group Replication. At that time we were dealing with MySQL 8.0.23 and also opened a bug report which did not have the right answer to the case presented. Anyhow, in that article I have shown how an online DDL was de facto locking the whole cluster for a very long time even when using the consistency level set to EVENTUAL. This article is to…
Percona XtraDB Cluster 8.0.25 has introduced a new option to perform online schema modifications: NBO ( Non Blocking Operation ). When using PXC the cluster relies on wsrep_OSU_method parameter to define the Online Schema Upgrade (OSU) method the node uses to replicate DDL statements. Until now we normally have 3 options: Use Total Isolation Order (TOI, the default) Use Rolling Schema Upgrade…
Well, let me say if that happens because there is a logic mistake in your application. But you need to know and understand what happens in MySQL to be able to avoid the problem. In short the WHY of this article is to inform you about possible pitfalls and how to prevent that to cause you damage. Let us start by having a short introduction to what Repeatable reads are about. Given I am extremely…
Why Marco, why did you write this long article? Yes, it is long, and I know most of the people will not read it in full, but my hope is that at least someone will, and I count on them to make the wave of sanity. Why I wrote it is simple. We write articles to share something we discover, or to share new approaches or as in this case to try to demystify and put in the right perspective the “last…
I decided to write this article after a discussion with other colleagues. We were wondering how and if the Percona Distribution for MySQL Operator(PDMO) could help a starting bu siness or a new project inside an enterprise. We were also wondering how it behaves in relation to already well established solutions, like Amazon RDS or Google SQL. In fact, we often see analysis and benchmarking covering…
Preface Percona is well known for its offer of several outstanding fully open source, free to download software packages. And while Percona started as a MySQL focus company, nowadays it covers different technologies such as MySQL, Postgres and Mongodb. In its constant effort to make life easier for our utilizer Percona had moved from providing single software packages, to Percona Distributions for…
When trying to make things better, make our life very complicated. I was working on a Security Threat Tool script, when I had to learn more about the interaction between static and dynamic privileges in MySQL 8 . Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance the FLUSH operation now has…
When organizing things helps to simplify life. In the previous article we start to explore dynamic privileges and the interaction with the static ones. We also saw how to remove SUPER privilege from a DBA account. What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly. Adding privilege one by one,…
Percona live is over, and we finally get some feedback about our presentations. Initially I had 3 presentations to give: Comparing High Available Solutions With Percona XtraDB Cluster and Percona Server With Group Replication. Comparing Hash Join solution, the good, the bad and the worse. The New version 2021. Boosting MySQL NDB cluster & MySQL Innodb Cluster with PrxySQL V2 But when I saw we were…
Well, we are almost there. Percona Live 2021 is just around the corner and this year comes with a very “dense” agenda. From my side I am giving a speech that is a high-level comparison between PXC and PS with group replication as highly available solutions. It is a bit new for me to be at such high level, given I prefer to dig into technical, but I also think it was worth to stop and have the…
AKA Cluster Error Voting What is Cluster Error Voting (CEV)? “Cluster Error Voting is a new feature implemented by Alexey Yurchenko, and it is a protocol for nodes to decide how the cluster will react to problems in replication. When one or several nodes have an issue to apply an incoming transaction(s) (e.g. suspected inconsistency), this new feature helps. In a 5-node cluster, if 2-nodes fail to…
While I was working on my grFailOver POC, I have also done some additional parallel testing. One of them was to see how online DDL are executed inside a Group Replication cluster. The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Checking the Group Replication (GR) official documentation I was trying to identify if any limitation exists, but the…
Where x is >= 22 ;) The Problem There are few things your data does not like. One is water and another is fire. Well, guess what: If you think that everything will be fine after all, take a look: Given my ISP had part of its management infrastructure on OVH, they had been impacted by the incident. As you can see from the highlight, the ticket number in three years changes very little (2k cases)…
This blog provides high availability (HA) guidelines using group replication architecture and deployment recommendations in MySQL, based on our best practices. Every architecture and deployment depends on the customer requirements and application demands for high availability and the estimated level of usage. For example, using high read or high write applications, or both, with a need for 99.999%…
From MySQL to Percona or MariaDB ...and back As we all know MariaDB starts as a fork of MySQL and then slowly diverges until becoming a different product. Still I often catch at conferences that MariaDB is a drop-in replacement for MySQL ( https://en.wikipedia.org/wiki/Drop-in_replacement ). This for me is a bold statement given drop-in-replacement: “It refers to the ability to replace one…
Overview Percona operator for MySQL (POM) :( https://www.percona.com/doc/kubernetes-operator-for-pxc/index.html ) it’s aim is a special type of controller introduced to simplify complex deployments. The Operator extends the Kubernetes API with custom resources . The Percona Operator for MySQL solution is using Percona Xtradb Cluster behind the hood to provide a highly available, resilient and…
Overview Percona Operator for MySQL (POM) comes with two different proxies, HAProxy and ProxySQL. While the initial version was based on ProxySQL, in time Percona opted to set HAProxy as the default Proxy for the operator, this without removing ProxySQL. While one of the main points was to guarantee users to have a 1:1 compatibility with vanilla MySQL in the way the operator allows connections.…
How scheduler and script stand in supporting failover (Percona and Marco example) In part one of this series , I had illustrated how simple scenarios may fail or have problems when using Galera native support inside ProxySQL. In this post, I will repeat the same tests but using the scheduler option and the external script. The Scheduler First a brief explanation about the scheduler. The scheduler…
In recent times I have been designing several solutions focused on High Availability and Disaster Recovery. Some of them using Percona Server for MySQL with group replication, some using Percona XtraDB Cluster (PXC). What many of them had in common was the use of ProxySQL for the connection layer. This is because I consider the use of a layer 7 Proxy preferable, given the possible advantages…
For what reason should I use a real multi-Primary setup? To be clear, not a multi-writer solution where any node can become the active writer in case of needs, as for PXC or PS-Group_replication. No, we are talking about a multi-Primary setup where I can write at the same time on multiple nodes. I want to insist on this “why?”. After having excluded the possible solutions mentioned above, both…
If you have a business no matter how small, you are collecting data, and you need to have your data accessible to make informed decisions about how to make your business better. The more successful you become the more data you are producing and the more you become dependent by it. This is when you start to realize your must have your data in a safe place like a database instead some spreadsheet.…
A small thing that brings huge help. The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved. The task was not so strange, but what I have to do is to develop a queue processor. Now when you need to process a queue you have two types of queue: static…
Why another article on this Marco? Deadlocks is a topic covered many times and with a lot of articles on the web, also from Percona. I suggest you review the reference section for articles on how to identify Deadlocks and from where they are generated. So why another article? The answer is that messages like the following are still very common: User (John): “Marco our MySQL is having problems”…
Back to part 1 Now that we had seen how to setup our environment with PXC8 is time to see how our setup will behave and what can be done in case of issues. We will now analyse the following common situations: - Put a node in maintenance or remove a node and insert it back - node crash - network failure - proxysql node crash The following tests are done using a java application connecting using…
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…
What you may not know about random number generation in sysbench Sysbench is a well known and largely used tool to perform benchmarking. Originally written by Peter Zaitsev in early 2000, it has become a de facto standard when performing testing and benchmarking. Nowadays it is maintained by Alexey Kopytov and can be found in Github at https://github.com/akopytov/sysbench. What I have noticed…
While this symbol was designed by Gerald Holtom in 1958 for the nuclear disarmament, in the years it had become more. It represent the will to resolve conflict with words and understanding, the will to be respectful of human and cultural diversity, the will to put the egoism/egotism/egocentrism aside. Nowadays is so difficult to remember that sometime we just need to say NO to political/social…
Understand dirty reads when using ProxySQL Recently I had been asked to dig a bit about WHY some user where getting dirty reads when using PXC and ProxySQL. While the immediate answer was easy, I had taken that opportunity to dig a bit more and buildup a comparison between different HA solutions. For the ones that cannot wait, the immediate answer is …drum roll, PXC is based on Galera replication,…
This week is almost over, and it pass after two nice event that I had attended. PerconaLive 2019 in Amsterdam Schiphol and ProxySQL Technology Day in Ghent. What are my takeaways on both events? Well, let us start with PerconaLive first. Venue The venue was nice, the hotel comfortable and rooms were nice as well, clean and quiet. Allow to have good rest and same time decent space to work if you…