In the light of support for Percona XtraDB Cluster (PXC), the DBdeployer anniversary, and the newly released Percona XtraDB Cluster 8.0, I’m happy to announce that Giuseppe Maxia has recently updated DBdeployer to support Percona XtraDB Cluster 8.0.
We at Percona, with my colleague Ramesh Sivaraman, have tested and deployed Percona XtraDB Cluster (PXC) 8.0.18 successfully using DBdeployer.
What is DBdeployer and How Do You …
[Read more]A couple of weeks ago, one of our customers reached us asking about the WARNING messages in their MySQL error log. After a while, there were a few more requests from some other customers asking whether to worry about these messages or not. In this post, I am going to write about the condition at which this WARNING message is written into the log and will explain some of the fundamentals behind the scene.
Look at the warningmber message which appears in the MySQL error log. It says it’s difficult to find a free block in the buffer pool and searched through the pool in a loop for 336 times. This is something weird to imagine; why would it have to go in a loop so many times? Let’s try to understand this.
[Warning] InnoDB: Difficult to find free blocks in the buffer pool (336 search iterations)! 0 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version …
[Read more]In this two-part blog series, I wanted to cover some failover scenarios with Group Replication. In part one, I will discuss an interesting behavior and performance degradation I discovered while writing these posts. In part two, I will show several failover scenarios and demonstrate how Group Replication handles each situation.
The test environment is very basic, a three-node Group
Replication (mysql1,mysql2,myslq3) on MySQL 8.0.19 with default
settings. mysql2 is the Primary node.
In this scenario, I was testing a partial network failure when a node gets separated from the primary, but other nodes still can see it.
You would think mysql3 is going to lose quorum and
exit the cluster, but no. Inside the cluster, all the nodes are
in constant communication with each other, not just …
March is not only the month when a pandemic closed all borders and people had to stay home on the quarantine; it’s also the month when daylight saving time change happens. For some regions, this is not only a time change but also a switch to a different timezone. For example, New York uses EST during winter and EDT during summer. If you use the system timezone and do not restart the MySQL server or a PXC node after the switch, you may notice that the change was not implemented.
You may end up with a situation where some of your cluster nodes still use the time zone before the switch (e.g. EST) and others use the timezone after the change (e.g. EDT).
$ date Sun Mar 8 03:03:28 EDT 2020 $ ./bin/mysql -h127.0.0.1 -P3373 -uroot test ... EDT node> show variables like '%zone%'; …[Read more]
This is a three-part blog series that focuses on dealing with an unexpected high traffic event as it is happening. Part one can be found here, and part two can be found here. 13. Configure MySQL Server Properly
Complexity:Medium
Potential Impact: High
A poorly configured MySQL Server can cause severe issues, especially under high load during a traffic spike, yet getting the basics right is not that hard. While MySQL Server has more than 400 variables you can tune, you rarely need to change more than 10-20 of them to get 95% of the possible performance for your workload.
…
[Read more]This is a three-part blog series that focuses on dealing with an unexpected high traffic event as it is happening. Part one can be found here and part three can be found here. 7. Get More Memory
Complexity: Low
Potential Impact: High
If your data does not fit into memory well, your MySQL performance is likely to be severely limited. If your data already fits in well, adding even more memory will not provide any performance improvements.
Even when you’re running on very fast storage, such as Intel Optane or directly Attached NVMe Storage, accessing data in memory is still more than an order …
[Read more]This is a three-part blog series. Part two is located here, and part three can be found here.
There was no reason to plan for it, but the load on your system increased 100%, 300%, 500%, and your MySQL database has to support it. This is a reality many online systems have to deal with these days. This series focuses on dealing with the unexpected high traffic event as it is happening.
There are also a lot of things you can do proactively, which we covered in “Prepare Your Databases for High Traffic on …
[Read more]While there is no magic bullet for MySQL 5.7 database tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement – with no guesswork required.
Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same. We will focus on MySQL 5.7 for the purposes of this article.
Initial MySQL performance tuning can be broken down to the following categories:
MySQL 5.7 Database Tuning Tuning For Your Hardware
Depending on the hardware …
[Read more]More and more people are nowadays thinking of cloud migration. The question of “Which cloud provider performs better for my MySQL workload?” is really common but cannot always be easily answered. However, there are ways to come up with an answer. This question also applies when thinking of moving to any provider, not necessarily a cloud one or DBaaS.
The Problem
The most reliable conclusion can be found if you have a testing environment that is fully identical and can produce the same amount of traffic compared to your production version. In this case, the comparison should be straightforward as what you have to do is point your testing environment against the under-evaluation (cloud) providers, and evaluate the differences in performance. But this is not always easy, as many organizations do not have such environments or it’s not always possible to replay all traffic.
The Idea
In this …
[Read more]Disclaimer: The following blog post does not try to provide a solution for split-brain situations, and the example provided is for demonstrative purposes only. Inconsistencies resulting from a split-brain scenario might possibly be more complex than the one shown, so do not use the following example as a complete guide.
What is Split-Brain?
A split-brain scenario is the result of two data sets (which were originally synced) losing the ability to sync while potentially continuing to receive DMLs over the same rows and ids on both sides. This could have consequences such as data corruption or inconsistencies where each side has data that does not exist on the other side.
For example before split-brain:
After split-brain:
Node1:
Node2:
It can be seen that after the split-brain scenario there are many differences between the nodes:
