Continuing on the series of blog posts about MySQL High Availability, today we will talk about stale reads and how to overcome this issue.

The Problem

Stale reads is a read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value (source Wiktionary).

A practical scenario is when your application applies INSERT or UPDATE data to your master/writer node, and has to read it immediately after. If this particular read is served from another server in the replication/cluster topology, the data is either not there yet (in case of an INSERT) or it still provides the old value (in case of an UPDATE).

If your application or part of your application …

In the first part of this howto, I illustrated how to setup two MySQL InnoDB Cluster linked by an asynchronous replication.

In that solution, I didn’t use any replication filters to ignore the replication of the InnoDB Cluster’s metadata (mysql_innodb_cluster_metadata), but I used the same metadata tables with two different clusters in it.

The benefit is that this allows to backup everything from any node in any of the data center, it works also in MySQL 5.7, and there is not risk to mess up with the replication filters.

In this blog I will show how to use replication filters to link two different clusters. This doesn’t work on …

Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.

In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.

Needs for partitioning:

• Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
• Easy Archival or Purge by dropping or truncate  of partition
• Lesser fragmentation, hence avoiding frequent table optimization.

Partitions management activity like adding/Dropping partition is made easy and automated by …

The settings of MySQL configuration variables have a fundamental impact on the performance of your database system. Sometimes it can be a little tricky to predict how changing one variable can affect others, and especially when dealing with cases like the one I’ll describe in this post, where the outcome is not very intuitive. So here, we’ll look at what happens when you set innodb_open_files higher than the open_files_limit.

We can set the maximum number of open files in our MySQL configuration file using:

open_files_limit=10000

If this isn’t set, then the default – which is 5,000 in MySQL 5.7 – should be used.

See Sveta’s excellent blog post for an explanation …

We recently helped a social media management platform improve its security and performance.

The social media platform supports social network integrations for Twitter, Facebook, Instagram, LinkedIn and YouTube, and has more than 15 million users. The company needed to ensure maximum security and performance for its authentication processes by reducing the hours and errors associated with manual administration.

These improvements would allow the organization to not only minimize downtime and expand the feature set available to its developers, but would also ensure the company’s compliance with Europe’s new General Data Protection Regulation (GDPR). The challenge was automating its database user management and authentication processes, which required knowledge of MySQL security and DevOps environments.

Pythian’s experts have deep knowledge of MySQL security and DevOps-structured environments, and were able to develop …

This is a quick blog demonstrating a couple backup Restore uses cases within a MySQL InnoDB Cluster 8.0 setup.  The backup used was completed in a previous blog (part 2 in this series) with the  MySQL Enterprise Backup 8.0 utility.  I’ll then use that backup to build an additional member to add to the cluster. This blog… Read More »

Join Percona CEO Peter Zaitsev at AWS re:Invent as he presents MySQL High Availability and Disaster Recovery on Tuesday, November 27, 2018, in the Bellagio Resort, Level 1, Gaugin 2 at 1:45 PM.

In this hour-long session, Peter describes the differences between high availability (HA) and disaster recovery (DR), and then moves through scenarios detailing how each is handled manually and in Amazon RDS.

He’ll review the pros and cons of managing HA and DR in the traditional database environment as well in the cloud. Having full control of these areas is daunting, and Amazon RDS makes meeting these needs easier and more efficient.

Regardless of which path you choose, it is necessary that you monitor your …

In this blog post, I’ll highlight new Backup Support in the MySQL Enterprise Backup 8.0 utility for Group Replication and InnoDB Cluster. This will also include usage of the MySQL login-path utility for accessing credentials for the backup process as highlighted in this other blog post on Devops ideas with MySQL and Scripting for Binary Log… Read More »

Features and capabilities continue to arrive in MySQL 8.0, most recently noted in the GA release of MySQL 8.0.13.  The mysql-shell 8.0.12 release introduced a number of things, an important part was a cross-platform mysql-shell secure password handling facility.  The MySQL Login-paths are a part of that security focus.  Here we will look at where InnoDB Cluster 8.0,… Read More »

Over the last year, I have been pursuing a part time hobby project exploring ways to squeeze as much data as possible in MySQL. As you will see, there are quite a few different ways. Of course things like compression ratio matters a lot but, other items like performance of inserts, selects and updates, along with the total amount of bytes written are also important. When you start combining all the possibilities, you end up with a large set of compression options and, of course, I am surely missing a ton. This project has been a great learning opportunity and I hope you’ll enjoy reading about my results. Given the volume of results, I’ll have to write a series of posts. This post is the first of the series. I also have to mention that some of my work overlaps work done by one of my colleague, Yura Sorokin, in a …