May
21
2024
--

High Availability: Choosing the Right Option for Your Percona Monitoring and Management

high availability percona monitoring and managementEnsuring your monitoring system stays up and running is vital. High availability (HA) minimizes downtime for Percona Monitoring and Management (PMM) during hardware failures, in times of disaster recovery, or increased usage of the tool. It’s not just about extra storage, RAM, or CPU but rather having redundant systems ready to take over seamlessly, like […]

May
08
2024
--

Mastering Database Monitoring: Running PMM in High Availability Mode

Percona Monitoring and Management (PMM) has become a valuable tool for database professionals, providing comprehensive insights into database health and performance. A recent update (version 2.41.0) introduced a significant enhancement: the ability to run PMM in high availability (HA) mode. This feature, currently in technical preview, offers exciting possibilities for ensuring the reliability and robustness […]

Jan
11
2024
--

Is MySQL Router 8.2 Any Better?

Is MySQL Router 8.2 Any BetterIn 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 […]

Dec
21
2023
--

Percona Monitoring and Management High Availability – A Proof of Concept

Percona Monitoring and Management (PMM) is a state-of-the-art piece of software that exists in part thanks to great open source projects like VictoriaMetrics, PostgreSQL, and ClickHouse. The integration of those projects, plus the years of Percona expertise in the database space, makes PMM one of the best database monitoring solutions on the market.

Being software composed of different, multiple technologies can add complexity to a well-known concept: High Availability (HA). Achieving HA for PMM, as a whole, has proved to be a challenge but not an impossible task.

The easy part

Setting up the PMM cluster is the easy part. All it needs is a reverse proxy in front of a couple or more PMM instances. The go-to proxy is HAProxy configured for active/passive topology, that is, without load distribution.

For the purpose of the PoC, a single HAProxy instance is used (running as a docker container). The configuration file looks like this:

global
stats socket /var/run/api.sock user haproxy group haproxy mode 660 level admin expose-fd listeners
log stdout format raw local0 info

defaults
mode http
timeout client 10s
timeout connect 5s
timeout server 10s
timeout http-request 10s
log global

frontend stats
bind *:8404
stats enable
stats uri /
stats refresh 10s

frontend pmmfrontend
bind :80
default_backend pmmservers

backend pmmservers
option tcp-check
server pmm1 172.31.12.174:80 check port 80
server pmm2 172.31.11.132:80 check port 80 backup

The Docker container is run with this command:

docker run -d --name haproxy -v $(pwd):/usr/local/etc/haproxy:ro -p 80:80 -p 443:443 -p 8404:8404 haproxytech/haproxy-alpine:2.

The -v for the volume guarantees that the local copy of the haproxy.cfg file is the one used inside the container. Whenever you make a change in the cfg file, for the haproxy container to use it, just execute:

docker kill -s HUP haproxy

And to follow the haproxy logs:

docker logs -f haproxy

We have two frontends: One for the HAProxy stats and another for the PMM itself. The backend is a single one where the “passive” PMM instance (the one that is a pure “read replica”) is marked as “backup” so that traffic is only routed there in case the primary fails the health check.

For simplicity, the PMM instances are configured to listen to the 80 port (http) on the private IPs. This is made to avoid SSL certificates since everything goes through the same VPC (everything runs on ec2 instances on AWS). The health check, then, can be a simple “tcp-check” against port 80.

Percona Monitoring and Management high availability

As you can see, stats are available via the port 8404. With this, the easy part is done.

For this example, the PMM SERVER endpoint will be where the HAProxy frontend is listening, and that’s the one used when registering a new PMM CLIENT.

PMM CLIENT

And you can access PMM always using the same endpoint.

The not-so-easy part (made easy)

The proxy is configured to be aware of two different PMM instances — pmm1 and pmm2  — (using the private IPs 172.31.12.174 and 172.31.11.132 in this case), but we haven’t mentioned anything about those PMM instances.

And here is the not-so-easy part: One has to deploy at least two PMM instances on at least two different servers AND set up replicas. How to do it? This is the actual Proof of Concept: Enter the PMM HA script: https://github.com/nethalo/pmmha

The script will take care of installing PMM (if you already have it, you can skip this step), preparing the Primary, and setting up the Secondary. Simple as that. PMM will remain to be a black box.

The requirements are:

– Docker 23.0.3 and higher
– Docker installation will fail if on Amazon Linux or RHEL9/EL9 (unless you are on a s390x architecture machine) since the Percona Easy-Install script relies on the “Get Docker” (https://get.docker.com/) script for the docker install. You will need to install docker on your own in those cases
– SSH Access to the host servers
– sudo capabilities
– Ports 443 and 9000 accessible from outside the Primary host machine

Steps to run the script:

git clone https://github.com/nethalo/pmmha.git
cd pmmha
bash pmm.sh

Failover

The failover will be handled by the HAProxy automatically when it detects that the current primary is no longer available. Traffic will be routed to the backup server from the backend, which, if properly set as a replica, will already have the historical data for metrics and QAN, and also the inventory will be ready to continue the data scrapping from the exporters.

Your feedback is key

We want to hear from you! Suggestions, feature requests, and comments in general are welcome, either in the comment section or via the GitHub repository.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Aug
22
2023
--

High Availability vs. Fault Tolerance: Is FT’s 00.001% Edge in Uptime Worth the Headache?

high availability

Estimates vary, but most reports put the average cost of unplanned database downtime at approximately $300,000 to $500,000 per hour, or $5,000 to $8,000 per minute. With so much at stake, database high availability and fault tolerance have become must-have items, but many companies just aren’t certain which one they must have.

This blog article will examine shared attributes of high availability (HA) and fault tolerance (FT). We’ll also look at the differences, as it’s important to know what architecture(s) will help you best meet your unique requirements for maximizing data assets and achieving continuous uptime.

We’ll wrap it up by suggesting high availability open source solutions, and we’ll introduce you to support options for ensuring continuous high performance from your systems.

What does high availability mean?

High availability refers to the continuous operation of a database system with little to no interruption to end users in the event of system failures, power outages, or other disruptions. A basic high availability database system provides failover (preferably automatic) from a primary database node to redundant nodes within a cluster.

High availability does not guarantee 100% uptime, but an HA system enables you to minimize downtime to the point you’re almost there. Within IT, the gold standard for high availability is 99.999%, or “five-nines” of availability, but the level of HA needed really depends on how much system downtime you can bear. Streaming services, for example, run mission-critical systems in which excessive downtime could result in significant financial and reputational losses for the business. But many organizations can tolerate a few minutes of downtime without negatively affecting their end users.

The following table shows the amount of downtime for each level of availability.

high availability

Implementing high availability systems: How does it work?

High availability works through a combination of key elements. Some of the most important elements include:

  • No single point of failure (SPOF): You must eliminate any SPOF in the database environment, including any potential for an SPOF in physical or virtual hardware.
  • Redundancy: Critical components of a database are duplicated so that if one component fails, the functionality continues by using a redundant component. For example, in a server cluster, multiple servers are used to host the same application so that if one server fails, the application can continue to run on the other servers.
  • Load balancing: Traffic is distributed across multiple servers to prevent any one component from becoming overloaded. Load balancers can detect when a component is not responding and put traffic redirection in motion.
  • Failure detection: Monitoring mechanisms detect failures or issues that could lead to failures. Alerts report failures or issues so that they are addressed immediately.
  • Failover: This involves automatically switching to a redundant component when the primary component fails. If a primary server fails, a backup server can take over.

High availability architecture

Two previously mentioned absolutes — no SPOF and foolproof failover — must apply across the following areas if an HA architecture is to be achieved:

  • Infrastructure — This is the hardware that database systems rely on. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
  • Topology management — This is the software management related specifically to the database and managing its ability to stay consistent in the event of a failure.
  • Connection management — This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
  • Backup and continuous archiving — This is of extreme importance if any replication delay happens and the replica node isn’t able to work at the primary’s pace. The backed-up and archived files can also be used for point-in-time recovery.

What is fault tolerance?

Fault tolerance refers to the ability of a database system to continue functioning in full, with no downtime, amid hardware or software failures. When a failure event occurs — such as a server failure, power outage, or network disruption — a fault-tolerant system will ensure that data integrity is preserved and that the system remains operational.

Implementing fault-tolerant systems: How does it work?

Here are some of the key components and characteristics of a fault-tolerant database environment:

  • Replication: Data is replicated across multiple nodes or servers, so if one node fails, the data remains accessible from replicas. Replication can be synchronous (ensuring immediate consistency) or asynchronous (allowing some delay).
  • Redundancy: The system stores multiple copies of data on separate devices. Redundancy provides backups and safeguards against data loss in case of hardware failures.
  • Error detection and correction: Techniques such as checksums, parity bits, and error-correcting codes are used to detect and correct errors that might occur during data transmission or storage.
  • Data integrity checks: Various mechanisms, such as checksums or hashing algorithms, are used to verify the integrity of stored data. This ensures that data remains consistent and accurate even in the presence of errors.
  • Specialized hardware: Specialized hardware is used to detect a hardware fault and initiate an immediate switch to a redundant hardware component.

Architecture for fault-tolerant systems

Fault-tolerant information systems are designed to offer 100% availability. Some of the key elements of such designs include:

  • Backup hardware systems — The hardware system is backed up by systems that are the same or perform the same functions but are at separate data centers and are not dependent on any of the same physical sources of power and functionality.
  • Backup software systems — As with hardware, there is no loss of software functionality because there are mirrored copies performing the same functions but residing elsewhere and using a different source of power.
  • Containerization platforms — Today, companies are increasingly using containerization platforms such as Kubernetes to run multiple instances of the same software. By doing so, if an error or other problem forces the software to go offline, traffic can be routed to other instances, and application functionality continues. 
  • Alternate power sources — To withstand power outages, businesses have alternate sources on standby. Some businesses, for example, have powerful generators ready to roll if electricity is lost.
  • Alternate environments — Fault-tolerant information systems can include separate cloud and on-premises databases that perform the same functions. For companies without their own physical servers, replicated cloud databases are maintained in different regions to provide contingencies for power outages.

High availability vs. Fault tolerance: Understanding the differences

There’s obviously a lot in common in terms of setup, functionality, and purpose. So with all the similarities (replication, load balancing, redundant components, and more), what are the differences? 

In general terms, the purpose of a high availability solution is to minimize downtime and provide continuous access to the database, while the purpose of fault tolerance is to maintain system functionality and data integrity at all times, including during failure events or faults. 

Still, generally speaking, but in financial terms, achieving fault tolerance is more costly, and the payoff often does not justify the expense. For example, it takes a lot of time, money, and expertise to completely mirror a system so that if one fails, the other takes over without any downtime. It can be considerably cheaper to establish a high availability database system in which there’s not total redundancy, but there is load balancing that results in minimal downtime (mere minutes a year).   

Basically, it comes down to a company’s needs, what’s at stake, and what fits its budget. Here are key questions to consider when deciding between high availability and fault tolerance:

How much downtime can your company endure? 

With high availability, you can achieve the gold standard previously mentioned — and your database system will be available 99.999% of the time. 

With a fault-tolerant system, you can spend a lot more and perhaps do better than the 5.26 minutes of downtime (in an entire year) that come with the “five nines” described immediately above. But is it mission-critical to do better than 99.999% availability?

How much complexity and how many redundant components are you willing to take on? 

High availability systems typically have redundant servers or clusters to ensure that if one component fails, another can take over seamlessly. 

Fault tolerance incorporates multiple versions of hardware and software, and it also includes power supply backups. The hardware and software can detect failures and instantly switch to redundant components, but they also constitute more complexity, parts, and cost.

Which option fits your budget?

A high availability database system requires redundancy, load balancing, and failover. It also must ensure that there is no single point of failure. But depending on your needs, there are varying levels of high availability, and an architecture can be fairly simple. 

Fault-tolerant systems are designed with more complex architectures, requiring sophisticated hardware and software components, along with specialized expertise to design, configure, and maintain. The additional complexity adds to the cost of the system.

Percona HA expertise and architectures

At Percona, we advise that, in most cases, the attributes and cost-effectiveness of high availability are the way to go. It’s plenty available and plenty scalable. We’re also big advocates of using open source software that’s free of vendor lock-in and is backed by the innovation and expertise of the global open source community to achieve high availability. 

At the same time, we’re aware that achieving high availability using open source software takes extra effort. HA doesn’t come with the complexity and price tag of a fault-tolerant system, but it still requires considerable time and expertise. 

So instead of you having to select, configure, and test architecture for building an HA database environment, why not use ours? You can use Percona architectures on your own, call on us as needed, or have us do it all for you.

Check out these helpful whitepapers, which include ready-to-use architectures:

Percona Distribution for MySQL: High Availability With Group Replication

Percona Distribution for PostgreSQL: High Availability With Streaming Replication

High availability support you can count on

Percona designs, configures, and deploys high availability databases so that applications always have access to essential data. We’ll help you ensure that applications stay up, databases stay bug-free, and your entire system runs at optimal performance levels.

 

Percona High Availability Database Support

FAQs

What is the difference between DR vs. HA and FT?

Disaster recovery focuses on recovering from major disruptive events and restoring operations. Fault tolerance and high availability focus on building systems that can withstand failures and continue operating with minimal interruption (HA) and without interruption (FT).

What are the goals of high availability and fault tolerance?

The goal of employing fault tolerance is to maintain system functionality and data integrity at all times, including during failures or faults. The goal of employing a high availability solution is to minimize downtime and provide continuous access to the database. 

What is the difference between HA and DR?

High availability is used to prevent downtime due to individual component failures within a live environment, while disaster recovery focuses on recovering from major events that make the database inaccessible or non-functional.

Jul
07
2023
--

Setting Up and Deploying PostgreSQL for High Availability

Deploying PostgreSQL for High Availability

With the average cost of unplanned downtime running from $300,000 to $500,000 per hour, businesses are increasingly using high availability (HA) technologies to maximize application uptime. Unfortunately, using certain open source database software as part of an HA architecture can present significant challenges. Despite all its upside, PostgreSQL software presents such challenges. 

PostgreSQL provides the foundation to start high availability, including features and configurations for physical and logical replication and consistent physical backups with point-in-time recovery (PITR) capabilities. But PostgreSQL does not offer a complete HA solution. Therefore, DBAs must take a piecemeal approach to achieve HA with PostgreSQL, bringing together open source extensions and tools from multiple sources. 

This mish-mash of technologies creates the potential for a single point of failure (SPOF) and an interruption of services to end users — the very thing high availability is supposed to prevent. Because most SPOFs are a consequence of scale, the risk of downtime only gets worse as the environment grows. Where a high availability design once worked well, it can no longer keep up with more complex requirements.  

This blog highlights considerations for keeping your own PostgreSQL databases highly available and healthy. We’ll examine the intricacies of establishing high availability with PostgreSQL software and provide links to Percona HA PostgreSQL reference architectures for businesses of every size (startup/small, medium, large, and enterprise).

Defining PostgreSQL high availability

High availability is essential for any business that relies on digital interactions — and today, that means just about everyone. The tolerable downtime depends on business size, operations, and objectives, but that downtime must be minimal to minuscule (see the “Measuring high availability” chart below). HA in PostgreSQL databases delivers virtually continuous availability, fault tolerance, and disaster recovery. It enables businesses and organizations to meet uptime and service level objectives, protect business-critical data, and maintain performant databases.

In general terms, to achieve HA in PostgreSQL, there must be:

  • Redundancy: To ensure data redundancy and provide continuous performance when a primary server fails, multiple copies of the database reside in replica servers. The same changes made in the primary database are made in the replicas. This ensures continuous business operations. PostgreSQL software supports synchronous streaming replication, asynchronous streaming replication, and logical replication.
  • No single point of failure (SPOF): If the failure of a database infrastructure component could cause downtime, that component is considered an SPOF. The existence of even one SPOF means high availability has not been achieved. The majority of SPOFs are a consequence of scale, where designs that were once adequate (excellent, even) no longer meet evolving, more complex requirements. Downtime due to SPOFs can also be attributed to bottlenecks from architectures designed for applications instead of databases.
  • Failover: This functionality is all about switching — in both unplanned and planned situations — from a failed primary server to a standby server. When the primary server fails, a standby server takes over as the new primary. Failover is essential to minimizing downtime.

Also, in general terms, a high availability PostgreSQL solution must cover four key areas:

  • Infrastructure: This is the physical or virtual hardware database systems rely on to run. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
  • Topology management: This is the software management related specifically to the database and its ability to stay consistent in the event of a failure.
  • Connection management: This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
  • Backup and continuous archiving: PostgreSQL asynchronous replication relies on the write-ahead log (WAL) stream, and it’s vital to have centralized storage that all nodes can access. This is the location where the primary node will archive the WAL segments, and the replicas will restore any segment that isn’t present on the primary anymore. It is of extreme importance to keep the replication working if any replication delay happens and the replica node isn’t able to work at the primary’s pace. Those files can also be used for point-in-time recovery if any disaster occurs.

Key considerations for determining high availability needs

Before going into more detail, it’s important to determine whether your workloads and services require an HA architecture and, if so, to what extent your HA should be. Answers to these questions will help you evaluate the need for a high availability PostgreSQL solution based on your business requirements, potential risks, and operational limitations:

  • How much downtime can your business tolerate before taking a financial hit, losing customers, or both?
  • Does your business’s industry have high availability requirements? If so, what are the legal liabilities?
  • How long does it take to recover from a database failure in your current setup? Can you afford that recovery time?
  • Can you afford the necessary hardware, software, and operational costs of maintaining a PostgreSQL HA solution?
  • Do you have the expertise and resources to design, implement, and manage a high availability solution for PostgreSQL?

Measuring high availability

Depending on the answers to the previous questions, achieving high availability can mean different things for different businesses. It depends on how much downtime an organization can withstand. With streaming services, for example, excessive downtime could result in significant financial and reputational losses for the business. Other organizations can tolerate a few minutes of downtime without negatively affecting end users.

The following table shows the amount of downtime for each level of availability, from “two nines” to “five nines” (the gold standard of HA). You’ll see that high availability doesn’t deliver 100% uptime, but it’s close.

Measuring high availability

Popularity of PostgreSQL

Before delving further into the inner workings of PostgreSQL high availability, let’s briefly examine the burgeoning popularity of this open source relational database software.

PostgreSQL has rapidly gained favor among professional developers in recent years. StackOverflow statistics show that 26% of developers preferred it in 2017, 34% in 2019, and 40% in 2021. Most recently, in StackOverflow’s 2022 Stack Developer Survey, PostgreSQL took a slight lead over MySQL (46.48% to 45.68%) as the most popular database platform among professional developers.

PostgreSQL is favored among relational database options for its complex data analysis, data science, graphing, and AI-related capabilities. PostgreSQL is known for powerful and advanced features, including synchronous and asynchronous replication, full-text searches of the database, and native support for JSON-style storage, key-value storage, and XML. 

PostgreSQL is highly extensible, enabling users to add custom functionality through plug-ins and extensions. It also features tools such as repmgr and Patroni for automatic failover and cluster management.

Being a more advanced database management system, PostgreSQL is well-suited for performing complex queries in a large environment quickly. Because it readily supports failover and full redundancy, it’s often preferred by financial institutions and manufacturers. It’s also preferred for use with geographic information systems (GIS) and geospatial data. PostgreSQL ranks as the fourth most popular database management system (DB-Engines, March 2023).

And it’s open source

Because PostgreSQL software is open source, it’s free of proprietary restrictions that can come with vendor lock-in. Developers can customize the source code and try new applications without a big budget hit. Companies can more easily scale infrastructure — up or down — to meet economic conditions and changing business objectives. With open source software, a business is not trapped into using one provider’s software, support, or services. Instead, the business may design and redesign systems as customer expectations change and business objectives evolve.

Also, with open source, there’s a global community of dedicated volunteers driving the development of PostgreSQL database technology. Open source standards and community support enable developers and DBAs to focus on accelerating PostgreSQL feature creation and enhancing availability, performance, scalability, and security. 

How does PostgreSQL high availability work?

Now, let’s go into more detail about high availability with PostgreSQL. Several methods of the aforementioned replication form the backbone of PostgreSQL high availability. They allow for data redundancy, fault tolerance, failover amid disasters, power outages, human incursions — just about any scenario. Those methods include:

Streaming replication

With streaming replication, the entire database cluster is replicated from one server, known as the primary, to one or more standby servers. The primary server continuously streams the write-ahead logs (WAL) to the standby servers, which apply the changes to their own database copies. Streaming replication comes in two modes:

  • Synchronous streaming replication: In this mode, the primary server waits for confirmation from at least one standby server before committing a transaction. That confirmation ensures the data is replicated to a certain level of redundancy with minimal data loss.
  • Asynchronous streaming replication: This is the default way of replicating data in PostgreSQL setups. In this mode, the primary server does not wait for confirmation before committing to transactions. The upside is enhanced performance, but there can be data loss in the event of a failure.

Logical replication

This data replication method occurs at the logical level of the database objects, such as tables, rather than at the physical level of behind-the-scenes storage. Unlike streaming replication, which replicates the entire cluster, logical replication allows for more control over what data is replicated or not replicated. 

The two main components of logical replication are publishers and subscribers. A publisher shares data and a subscriber receives and applies the replicated data. This approach provides more flexibility for replication configuration.

Beyond — and connected to — the replication methods, these components also are essential in high availability: 

Failover

As stated, this is when a standby server becomes the new primary server due to various reasons, planned or sudden and unexpected. In PostgreSQL, failover can be triggered manually, or it can be automated — with monitoring systems initiating the action.

Failover includes these steps:

  1. The monitoring system sends an alert that the primary server is unavailable, triggering the failover process.
  2. Another server is chosen as the new primary server.
  3. Applications are redirected to the new primary server, and business-as-usual continues using the new primary server.

Automatic switchover

This method is used to minimize downtown and replace the need for manual intervention. The database system triggers automatic failover the instant that predetermined conditions occur.

With automatic switchover, cluster management tools or outside integrated tools monitor the primary server 24/7, checking that it’s connected and doing 100% of its job. The automatic switchover process kicks in if any failure of the primary server is detected, and a standby server becomes the new primary server.

Cluster management tools

Several cluster management tools, such as Patroni and Replication Manager, simplify the configuration, monitoring, and management of PostgreSQL high availability setups. As introduced above, those tools automate tasks like failover, monitoring, and maintenance, making it easier to maintain a highly available PostgreSQL environment.

Load balancing

High availability often involves distributing the database workload across multiple servers to ensure optimal performance and avoid bottlenecks. PostgreSQL supports various load balancing techniques — including connection pooling and connection routing — to distribute incoming queries and transactions among multiple database servers.

Connection pooling includes the availability of established database connections that can be made by multiple applications. Pgbouncer and pgpool-II are connection managers that accept client connections, manage the connection pool, and direct requests to the appropriate backend server.

Proxy load balancers like pgpool-II, pgbouncer, and HAProxy have the capacity to route connections to different database servers. Such a proxy acts as the traffic cop between the applications and the database servers. Steered by load balancing configurations, the proxy forwards requests from applications to the appropriate backend server. Pgpool-II has the extra capacity of a seamless split of reads and writes, while the other two have to do it using a separate port, and the read/write split has to be managed by the application.

DNS load balancing distributes application connections across multiple PostgreSQL database servers using the Domain Name System (DNS). With DNS load balancing, DNS records with the same hostname but different IP addresses are configured. When applications connect to the PostgreSQL database, the DNS server connects the app to available IP addresses in some kind of prioritized or revolving manner. This method is fairly easy to set up, but it’s not so intuitively or easily adjusted to meet changing server setup configurations. 

Monitoring and alerting

To maintain high availability in PostgreSQL databases, it is crucial to have monitoring and alerting mechanisms in place. There are multiple tools available for monitoring the health and performance of PostgreSQL instances. Those tools ensure that notifications are sent as issues and potential issues are detected. 

Bringing it all together in PostgreSQL architectures

When implemented successfully, the components we’ve examined can make for successful high availability in PostgreSQL databases. But, as stated, the PostgreSQL community version does not come with ready-to-go HA. It takes work, the right architecture, and often some outside help.  

Let’s start with the architectures. 

There’s an unfortunate misconception that high availability solutions are too expensive for startups and small businesses. But when using open source tools, coupled with an architecture such as the one offered by Percona, high availability on PostgreSQL can be achieved without a big price tag or the need for an overly complex environment. It can be done by building the HA infrastructure within a single data center. 

As your business grows, so should your high availability architecture. For medium and large businesses, the consequences of downtime, both in terms of lost revenue and erosion of customer sentiment, can be significant. High availability requires more fault-tolerant, redundant systems and probably larger investments in IT staff. Still, when using open source tools, high availability can be achieved cost-effectively and without the threat of vendor lock-in that can come from paid enterprise SQLs. For medium and large businesses, Percona provides an architecture that spreads availability across data centers to add more layers of availability to the cluster. 

For enterprises, the challenges and potential consequences increase exponentially. An architecture must address a lot more. The Percona architecture, for example, features two disaster recovery sites and adds more layers to the infrastructure in order to stay highly available and keep the applications up and running. This architecture, based on tightly coupled database clusters spread across data centers and availability zones, can offer an HA level up to 99.999% when using synchronous streaming replication, the same hardware configuration in all nodes, and fast internode connection.

You can get more details — and view actual architectures — at the Percona Highly Available PostgreSQL web page or by downloading our white paper, Percona Distribution for PostgreSQL: High Availability With Streaming Replication. But for starters, here are elements of a minimalist high availability architecture for PostgreSQL. Such architecture will include at least these four components in the design:

  • Database nodes: In PostgreSQL high availability architectures — such as those depicting streaming replication, logical replication, or shared-disk clustering — multiple data nodes distribute the workload and maintain data consistency. Typically, each data node contains a copy or subset of the data.
  • HA controller: This mechanism identifies when a failure of the primary node occurs and makes sure that there’s not a network partition/split brain. This same component needs a mechanism to perform the failover in case of failure on the primary node. It will elect one of the replicas to be promoted as the new primary node.
  • Connection router: This mechanism changes the query routing so that application requests reach the new primary node.
  • Backup component: This software or mechanism provides fast, consistent, and reliable backup and restores for PostgreSQL and also guarantees that all WAL logs are shipped to a universally accessible storage area.

Again, the descriptions immediately above are for a minimalist HA architecture. It can, and usually does, get a lot more complicated. Percona provides proven architectures to get you going, and we offer cost-effective options for help. 

Steps to deploying and maintaining PostgreSQL high availability

Once the method of replication is determined and the architecture is designed, it’s time to deploy it. As with the architecture itself, deployment can be easier and more cost-effective when enlisting high availability support for PostgreSQL from outside experts. It depends on what expertise you have on staff. 

Every database environment is different, so deployment procedures can vary, but here are some general steps:

  1. Configure the primary server. This server, the primary read-write node, will orchestrate data replication to standby servers.
  2. Create a primary server backup on the standby server or servers. 
  3. Configure the standby server to run in hot standby mode. The hot standby allows the standby server to be used for reads. It prevents complications and losses in the event of a failure or scheduled maintenance of the primary server.
  4. Configure load balancing. Using pgpool-II, HAProxy, or other PostgreSQL load balancing tools, your database will be ready for high traffic and to distribute read queries across multiple servers.
  5. Implement backup and disaster recovery. Since HA alone does not guarantee data protection, you should implement mechanisms for data durability and disaster recovery.
  6.  Regularly schedule backups and test the restore process to ensure data integrity.
  7. Test the setup. Here are a couple options. Ideally, both could be used. 
  • End-to-end (E2E) testing, though time-consuming, will show whether all components and applications of your HA setup work as intended.
  • Chaos engineering is another option. This is when engineers introduce problems throughout the database infrastructure, so they can identify failure points and create solutions — avoiding costly downtime and potential loss of frustrated customers.

Security considerations for high availability PostgreSQL

Keeping databases secure demands attention, of course. Yet again, it should be emphasized that every environment is unique, and specific security requirements will vary accordingly. It’s important to check out PostgreSQL documentation. In some cases, if there is no on-staff expertise to maintain adequate security levels of your high availability cluster, it’s wise to consider support.

But whether you can keep your environment secure on your own or need that outside help, there are some general best practices for securing a cluster in a high availability environment:

Configure SSL/TLS encryption of communication with the database cluster. You can disable unnecessary procedures and make it so connections occur only across trusted networks.

Control access. Implementing role-based access control (RBAC) for PostgreSQL can be a helpful way of managing permissions and restricting access to sensitive data.

Use strong authentication. That means strong user passwords that are regularly changed. In more sensitive situations, it’s advisable to implement more secure methods like certificate-based authentication or LDAP integration. PostgreSQL supports a variety of external authentication methods, including GSSAPI, LDAP, and RADIUS. 

Audit, log, and regularly review. You can configure PostgreSQL to log queries, failed logins,  administrative activity, and any other events. Then, you can examine the logs at any time to spot potential security breaches or suspicious activity.

Conduct regular updates. Apply the latest PostgreSQL security patches and bug fixes ASAP when available. Stay active, or at least observant, as a participant in the global open source community. Stay informed about the latest PostgreSQL security information, including news about vulnerabilities and recommended patches.

Percona high availability architectures and support

There’s no getting around it; achieving high availability with PostgreSQL takes some work — whether you do it or have someone else do it. But if you’d like to avoid or ditch the piecemeal approach to high availability described earlier, Percona HA PostgreSQL architectures pull together open source tools and components to enhance and harden interoperability, ensuring your applications always have access to the data they need. You can implement an architecture yourself, call on Percona as needed, or have us manage your PostgreSQL HA database completely. 

Regardless of the level of support you choose (and you can change your mind at any time), you’ll have a proven architecture, and you’ll avoid vendor lock-in, potentially exorbitant costs, and other pitfalls of going with proprietary arrangements. 

A good place to start is our Highly Available PostgreSQL web page. There, you’ll find ready-to-use PostgreSQL high availability architectures designed specifically and separately to address the needs of small businesses and startups, medium and large businesses, and enterprise and globally distributed businesses.

Also, check out our white paper — Percona Distribution for PostgreSQL: High Availability With Streaming Replication. It will show you how to pull together open source tools and components for achieving the highest levels of HA on PostgreSQL, even as you scale. The white paper also provides the ready-to-use architectures.

 

High Availability PostgreSQL from Percona

 

Download white paper: Percona Distribution for PostgreSQL: High Availability With Streaming Replication

FAQs

The following are commonly asked questions and short answers about high availability in PostgreSQL databases. More detailed answers are presented in the sections above.

What is the difference between high availability and disaster recovery?

High availability provides continuous access to a PostgreSQL database through replication, failover mechanisms, and the absence of single points of failure. Disaster recovery is about restoring data and services after a catastrophic event by using backups, replication, and recovery procedures. 

Can high availability architecture eliminate all downtime?

High availability doesn’t deliver 100% uptime, but it’s close. The gold standard of HA is “five nines,” which means a database is available 99.999% of the time.

What are common challenges when implementing high availability architecture?

The PostgreSQL community version doesn’t come with high availability features and functionality out of the box. That often requires a piecemeal approach to achieving HA. DBAs must bring together a few open source extensions and tools from multiple sources.

What technologies can be used for high availability architecture?

A multitude of technologies must be used together to achieve high availability in a PostgreSQL database. Essential technologies include replication, failover, load balancing, and cluster management.

How often should high availability architecture be tested and validated?

The frequency of testing and validation depends on several factors, including what’s at stake for the business and the complexity of the HA architecture. Certainly, a quarterly in-depth check is advisable, and for more critical situations, monthly might might be advisable..

Jun
22
2023
--

The Ultimate Guide to Database High Availability

Database High Availability

To make data count and to ensure cloud computing is unabated, companies and organizations must have highly available databases. That doesn’t mean downtime is nonexistent, but it must be limited so that significant/irreparable damage (as defined by the business or industry) does not occur.

This guide provides an overview of what high availability means, the components involved, how to measure high availability, and how to achieve it.

Defining high availability

In general terms, high availability refers to the continuous operation of a system with little to no interruption to end users in the event of hardware or software failures, power outages, or other disruptions. A basic high availability database system provides failover (preferably automatic) from a primary database node to redundant nodes within a cluster.

HA is sometimes confused with “fault tolerance.” Although the two are related, the key difference is that an HA system provides quick recovery of all system components to minimize downtime. Some disruption might occur, but it will be minimal. Fault tolerance aims for zero downtime and data loss. As such, fault tolerance is more expensive to implement because it requires dedicated infrastructure that completely mirrors the primary system.

In general terms, again, when it comes to database reliability, the stakes are high. Database downtime can hurt or doom any company with anything to do with the internet. More specifically, a 2022 ITIC survey found that downtime costs more than $300,000 per hour for 91% of small-, mid-size, and large enterprises. Among just the mid-size and large respondents, 44% percent said a single hour of downtime could potentially cost them more than $1 million.

Introducing key parts of high availability

  • Data backup and recovery: A database backup is created and stored in another location to help keep the system operational after a natural disaster, a cyber attack, or other problem.
  • Data replication: Data is continually copied from one database to another to ensure that the system remains operational even if one database fails.
  • Clustering: Multiple nodes work together (and separately if necessary) to provide access to data, ensuring that the system remains operational even if one node fails. (More in the following sub-section.)
  • Load balancing: Requests are evenly distributed across multiple database servers, ensuring the system remains operational even if one server fails.
  • Automated failover: To keep the database operational and minimize downtime, it automatically switches to a backup server if the primary server fails.

More about high availability clusters

HA clusters — also known as failover clusters — are groups of interconnected servers that work together to ensure that an application or service remains available to users. Redundancy and failover protection ensure that if one server fails or goes offline, another server will automatically take over its workload. Typically, the servers are configured in a primary/replica configuration, with one server designated as the primary server that handles all incoming requests and the others designated as replica servers that monitor the primary and take over its workload if it fails.

Additionally, most database setups these days include a read-only mode or some capabilities that help distribute the load. With PostgreSQL, when using physical replication, all the replicas will be in read-only mode. Logical replication has the capability to allow the writes, but it’s not recommended to have writes in both nodes at the same time without a strong conflict-resolution solution. You can, alternatively, set specific tables or a database to read-only mode using the pg_hba.conf file, or you can set read-only access permissions. With MySQL, you can set the whole instance to a read-only mode using a specific parameter in the software’s configuration. You can also restrict write operations by restricting users to read-only privileges pertaining to specific databases or tables. With MongoDB, you can set read-only privileges in which users can query data but cannot perform write operations.

HA clusters are used in web hosting, email hosting, database hosting, and other services that require high availability.

How does high availability work?

High availability works through a combination of the following:

  • No single point of failure (SPOF): You must eliminate any single point of failure in the database environment, including physical or virtual hardware the database system relies on that would cause it to fail.
  • Redundancy: Critical components of a database are duplicated so that if one component fails, the functionality continues by using a redundant component. For example, in a server cluster, multiple servers are used to host the same application so that if one server fails, the application can continue to run on the other servers.
  • Load balancing: Traffic is distributed across multiple servers to prevent any one component from becoming overloaded. Load balancers can detect when a component is not responding and put traffic redirection in motion.
  • Failure detection: Monitoring mechanisms detect failures or issues that could lead to failures. Alert mechanisms report those failures or issues so that they are addressed immediately.
  • Failover: This involves automatically switching to a redundant component when the primary component fails. If a primary server fails, a backup server can take over and continue to serve requests.

Components of high availability infrastructure

Multiple copies of data

Data redundancy helps prevent data loss due to hardware or software failures. By storing multiple copies of data, database administrators can recover lost or corrupted data and maintain data integrity. Redundancy is also critical for disaster recovery. In the event of a natural disaster, cyber attack, or other catastrophic event, redundant systems can help ensure that the database can be quickly restored to a functioning state.

Multiple servers or databases

By copying and maintaining data on multiple servers or databases, database administrators can ensure that there is always a backup copy available. They can use that replicated data to quickly restore the database to a functioning state in case of a system failure, attack, or other potentially damaging event.

Load balancers

Load balancers help distribute incoming traffic across multiple database servers, ensuring that no single server is overloaded with requests. This helps prevent the possibility of a single point of failure, which could cause downtime or slow performance. It also supports the flexibility and scalability of the database infrastructure.

Failover mechanisms

The primary purpose of failover — the automatic process of switching from a failed or offline database server to a standby or backup server — is to ensure user access and functionality of databases without interruption, even if there is a hardware or software failure. By implementing failover mechanisms, organizations can ensure that their critical applications and services remain available even in the event of a failure, minimizing the impact on their business operations.

How to achieve high availability

Not all companies are the same, nor are their requirements for HA. When planning your database HA architecture, the size of your company is a great place to start to assess your needs. For example, if you’re a small business, paying for a disaster recovery site outside your local data center is probably unnecessary and may cause you to spend more money than the data loss is worth. All companies, regardless of size, should consider how to strike a balance between availability goals and cost.

  • Startups and small businesses: Most startups and small businesses can achieve an effective HA infrastructure within a single data center on a local node. This base architecture keeps the database available for your applications in case the primary node goes down, whether that involves automatic failover in case of a disaster or planned switchover during a maintenance window.
  • Medium to large businesses: If you have enough additional budget, consider adding a disaster recovery site outside your local data center. This architecture spans data centers to add more layers of availability to the database cluster. It keeps your infrastructure available and your data safe and consistent even if a problem occurs in the primary data center. In addition to the disaster recovery site, this design includes an external layer of nodes. If communication between the sites is lost, the external node layer acts as a “source of truth” and decides which replica to promote as a primary. In doing so, it keeps the cluster healthy by preventing a split-brain scenario and keeps the infrastructure highly available.
  • Enterprises: An enterprise HA architecture adds another layer of insurance for companies that offer globally distributed services and could face devastating revenue and reputational losses in the event of substantial downtime. This architecture features two disaster recovery sites, adding more layers for the infrastructure to stay highly available and keep applications up and running. Based on tightly coupled database clusters spread across data centers and geographic availability zones, this architecture can offer 99.999% uptime when used with synchronous streaming replication, the same hardware configuration in all nodes, and fast internode connections.

How to measure high availability

High availability does not guarantee 100% uptime, but it allows you to get pretty close. Within IT, the gold standard for high availability is 99.999%, or “five-nines” of availability, but the level of HA needed really depends on how much downtime you can bear. Streaming services, for example, run mission-critical systems in which excessive downtime could result in significant financial and reputational losses for the business. But many organizations can tolerate a few minutes of downtime without negatively affecting their end users.

The following table shows the amount of downtime for each level of availability, from “two nines” to “five nines.”

high availability

Here are additional metrics used to determine the reliability of a database, make adjustments that minimize downtime, and set benchmarks for meeting business continuity requirements. The desired value or range for each of these depends on the specific needs and risk tolerance of the company or organization that uses it:

  • Mean time between failures (MTBF) — a measure of the average time between two failures of a database or component. The MTBF is calculated by dividing the total operating time of the system by the number of failures that have occurred during that time.
  • Mean downtime (MDT) — the average amount of time that a database system is unavailable due to planned or unplanned downtime. The MDT is calculated by adding up the total amount of time that the system was down over a given period and dividing it by the number of downtimes.
  • Recovery time objective (RTO) — the maximum time that a system can be down before it starts to have a significant effect on business operations. Organizations use the RTO to determine how quickly they must recover their systems to meet business continuity requirements.
  • Recovery point objective (RPO) — the point in time by which an organization must recover its data in case of a disaster or system failure. For example, an RPO of 1 hour means that an organization can tolerate a maximum data loss of 1 hour’s worth of data. To ensure that the RPO is met, organizations typically implement backup and recovery procedures that enable them to restore data to a specific point in time.

High availability architecture for open source databases

No matter where a database infrastructure is located (on-premises, public or private cloud environment, or hybrid environment) or what technology is used — be it MySQL, PostgreSQL, or MongoDB — it’s vital to eliminate single points of failure. To be prepared for disaster recovery and attain high availability, server redundancy must be built into the database architecture.

And for that redundancy to be beneficial, the design of the database infrastructure must incorporate failover mechanisms that duplicate essential database functions and components.

The considerations described above (and in other sections) must be addressed across the following areas if a high availability architecture is to be achieved:

  • Infrastructure: This is the physical or virtual hardware that database systems rely on to run. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
  • Topology management: This is the software management related specifically to the database and managing its ability to stay consistent in the event of a failure.
  • Connection management: This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
  • Backup and continuous archiving: This is of extreme importance if any replication delay happens and the replica node isn’t able to work at the primary’s pace. The backed-up and archived files can also be used for point-in-time recovery if any disaster occurs.

Which database is best for high availability?

When choosing a database for high availability, some proprietary options offer attractive specialized features, including built-in replication, automatic failover, and advanced clustering. Those ready-to-go capabilities can simplify the implementation of high availability. Dedicated vendor support can also be a lure. Proprietary vendors can provide expertise, guidance, and timely resolutions that ensure the stability and availability of a database.

But with the right expertise — whether it’s from within your organization or through outside assistance — open source databases can provide the same benefits, as well as other advantages, including:

  • Cost-effectiveness: Open source software is typically free to use, which can significantly reduce the overall cost of establishing high availability. Conversely, proprietary databases often require expensive licenses and ongoing maintenance fees.
  • Flexibility: With open source databases, you have access to the source code, allowing you to modify and optimize the database to meet your specific high availability requirements. This flexibility can be crucial in designing a scalable architecture.
  • Community support: Large and active communities of users, developers, and DBAs can be invaluable when it comes to troubleshooting issues, finding solutions, and staying up-to-date with the latest developments in high availability techniques. The transparency of open code enhances security as potential vulnerabilities can be identified and patched more quickly by the community.
  • Freedom from vendor lock-in: With open source software, you are not tied to a specific vendor or locked into a particular technology stack. You have the freedom to choose and integrate various tools and technologies that best suit your high availability strategy.

HA is an important goal, but switching to a proprietary database solely for HA limits the other benefits of open source database software. In addition to enabling a strong database HA architecture, open source avoids costly licensing fees, offers data portability, gives you the freedom to deploy anywhere, anytime you want, and delivers great software designed by a community of contributors who prioritize innovation and quality.

Getting started with open source database high availability

There are numerous open source options available, but covering all of them here would not be practical. (For a more detailed examination of open source options, read The Ultimate Guide to Open Source Databases.)

Here, for this examination of high availability, we are focusing on three of the most popular open source options:

  • MySQL: This relational (table-based) database software supports asynchronous replication, which provides redundancy and allows for failover in case the primary database becomes unavailable. MySQL supports the creation of read replicas, which can distribute the read workload, improve performance, and provide high availability for read-intensive applications. Another feature (among many) of note is MySQL Cluster, a distributed, shared-nothing database clustering solution. It combines synchronous replication, automatic data partitioning, and node-level failover to provide high availability and scalability.
  • PostgreSQL: This relational database software supports numerous high availability options, including asynchronous streaming replication, logical replication, and tools such as repmgr and Patroni for automatic failover and cluster management. PostgreSQL also supports synchronous replication in which the primary server waits for confirmation from at least one standby server before acknowledging a transaction. PostgreSQL point-in-time recovery uses continuous archiving to create a backup that allows you to recover the database to a specific state.
  • MongoDB: While not open source (see our blog Is MongoDB Open Source? Is Planet Earth Flat? for more), we’ll categorize it here for easy reading. This non-relational (document-based) database uses replica sets to provide high availability. MongoDB’s replica sets maintain multiple copies of data across different nodes. The primary node receives write operations and replicates them to the secondary nodes asynchronously or synchronously, depending on the chosen replication mode. As a result, data is redundantly stored, reducing the risk of data loss.

Some open source databases include great options for HA but generally don’t include a built-in HA solution. That means you’ll need to review the various extensions and tools available carefully. Those extensions and tools can be excellent for enriching their respective databases, but as your environment scales, the database might not keep pace with evolving, more complex requirements.

Greater complexity means that you and your team will need certain skills and knowledge. For example, it might be necessary to write application connectors, integrate multiple systems, and align business requirements with your HA solution. You should also understand open source databases, applications, and infrastructure. Consider the possibility of needing outside expertise to help you manage your HA architecture.

Comparing high availability to similar systems

High Availability vs. Disaster Recovery

High availability is focused on preventing downtime and ensuring that the database remains available, while disaster recovery is focused on recovering from a catastrophic event and minimizing negative effects on the business. High availability typically involves redundant hardware, software, application, and network components that can quickly take over if the primary component fails, while disaster recovery typically involves regular backups, replication to a secondary site, and a well-defined recovery plan that outlines the steps to be taken in the event of a disaster. Companies certainly can and should incorporate high availability and disaster recovery into database architecture.

High Availability vs. Fault Tolerance

High availability ensures that the database remains up and running almost all the time, while fault tolerance is focused on preventing data loss or corruption in the event of a fault or error. Redundancy and failover mechanisms figure prominently in high availability architecture, a design that gives users continued access even if there is a hardware or software failure. A fault-tolerant system is designed to correct and detect errors as they occur such that normal operation is not interrupted. This can mean that instead of failing completely, a system continues to operate, but possibly at a reduced level of efficiency.

High Availability vs. Redundancy

Redundancy is a key element of high availability, so it’s not an either-or situation. As stated, high availability refers to a database that’s functional almost all the time (i.e., “five nines” or some other standard depending on company size and requirements). In other words, there’s little or no downtime. Redundancy, a specific part of high availability, refers to the duplication of critical components or data to provide a backup in case the primary component or data is lost or corrupted. To establish redundancy, the same data must be accessible from at least two places. That means having a primary system and a secondary system. The secondary system includes failover monitoring, so when a failure occurs, data traffic is redirected to that secondary system. In addition to redundancy, high availability includes load balancing, failure detection, and failover, as well as ensuring there is no single point of failure (SPOF).

Best practices for achieving high availability

For medium, large, and enterprise-size companies (as well as startups and small companies, depending on resources), the following general practices can help when planning, implementing, maintaining, and testing high availability architectures. Please note that these are presented as general best practices. (For more detailed information, check out the Percona architecture and solution links in subsequent sections.) Best practices include:

Conduct research and identify objectives

Early on, determine what you need and what you can afford. Here are the early steps:

  • Perform analysis: A good place to start might be to conduct a Failure Mode Analysis (FMA). An FMA or similar analysis can identify possible failure points in the database infrastructure. This will help in determining the redundancy requirements of the different components.
  • Determine requirements: The level of HA that is needed depends on how much downtime a company can bear. For example, some companies that offer streaming services will suffer significant financial and reputational losses in just a couple of minutes of downtime. Other organizations can tolerate a few minutes of downtime without negatively affecting their end users.
  • Consider costs: Multiple factors will, of course, affect how much you should budget. Depending on your setup, costs can include:
    • Hardware devices (servers, storage devices, network switches, etc.)
    • Networking equipment (switches, routers, etc.)
    • Software for clustering, replication, load balancing, failover, etc.; with proprietary licensing, costs can increase significantly as you scale
    • Cloud egress fees that might be charged when moving data out of a vendor’s network
    • Staffing and training (an HA system requires specialized expertise)
    • Testing to ensure the system will function during likely and unlikely conditions
    • Scalability to allow for maximum growth

Focus on redundancy and failover

Whether a company’s database infrastructure is on-premises, in a public or private cloud environment, or is run in a hybrid environment, it is mission-critical that there are no single points of failure. To avoid them, achieve high availability, and be poised for disaster recovery, there must be server redundancy. And for redundancy to matter, the database infrastructure design must include failover. In other words, there must be mechanisms for putting the duplication of essential database functions and components into action.

To establish redundancy, the same data must be accessible from at least two places. That means having a primary system and a secondary system. The secondary system includes failover monitoring, so when a failure occurs, data traffic is redirected to that secondary system.

Use shared-nothing (or shared-none) clusters

High availability can be achieved using the concepts of “shared-all” and “shared-nothing” (also called “shared-none”) clusters. Open source technologies often prefer to use shared-nothing clusters because they are not only simpler to implement and deploy but also to scale. By incorporating that setup, the developer or DBA will ensure that each node within a cluster stores, manages, and processes its data. That will minimize problems that can occur when nodes share access to and the ability to modify the same memory or storage.

The most common shared-nothing architecture has a primary node responsible for receiving all data updates and propagating the updates to the replica nodes with little to no interference or conflict with other nodes. (Each node has its own cache buffer.) If one instance fails, another instance in another node can fill the role entirely, and high availability remains.

In addition to preventing single points of failure and system-wide shutdowns, a big advantage of a shared-nothing architecture is scalability. You can easily scale a shared-nothing system by adding nodes. Since those nodes don’t share data, there won’t be dueling processes that could slow or halt operations.

Apply load balancing

In addition to incursions from the outside and errors generated in-house, unusually high customer use can overwhelm a company’s database operations. That’s why it’s essential to include load balancing in your design. In times of high use, load balancing redirects workloads to components of the infrastructure best suited to pick up the extra work.

To keep services — and the databases that deliver them — running smoothly, a tiered architecture of server clusters helps. In the event that a server fails, a replica server in a different cluster can assume the role of the failed server. The load balancer is the key; it serves as a traffic cop of sorts, directing and redirecting flow from end users/applications to servers where data will flow as intended. If a server fails, the load balancer redirects traffic to a replicated server in another cluster.

Use a container-orchestration platform

Containers can provide multi-cloud compatibility and cost-efficiency, and they are a great tool for isolating applications. But containers are not faster than anything else with the same resources. They can, in fact, be a lot more complex to troubleshoot than the processes of virtual machines (VMs) and bare metal environments. Still, companies find themselves adding more and more containers across multiple hosts.

To get real benefits from those containers, it’s advisable to use a system of orchestrating and managing containerized applications. Kubernetes is a platform that can provide that system. It enables developers and DBAs to manage sizable containers across multiple hosts. Using Kubernetes can yield many benefits, including greater automation than that of traditional VMs, enhanced fault tolerance, and increased flexibility and scalability.

Deploy in multiple regions

Sometimes there’s a misperception that “cloud” means safe. Just because you’ve entrusted your database with a cloud provider instead of hosting on-site doesn’t mean you won’t lose service. Cloud provider data centers go down, and so do entire regions of data centers. So whether you host your own servers, you use a cloud provider, or you use a combination of both, it’s wise to replicate databases across regions, not in the same region.

With replication in different geographic regions, you can assure instantaneous failover in the event of an entire region going down.

Maintain deployment consistency

Using proven and secure code and processes is important in maintaining data integrity and reliability. Deployment consistency is essential in preventing the introduction of bugs and other intrusions that could lead to data corruption or data loss and could slow or interrupt applications and even entire systems. By maintaining database deployment consistency, organizations can ensure that their data is always up-to-date and accurate and that their applications perform reliably. Data consistency is especially important in financial and healthcare systems, where incorrect or outdated data can have serious and even fatal consequences.

Conduct end-to-end (E2E) testing

This practice tests the entire system to ensure that all database components and software applications (and any subsystems) work as intended — individually and in unison. E2E testing verifies that components meet performance, security, and usability requirements. It is often used to identify issues that arise from the interaction between different components of the system, such as when data is passed from one component to another. This type of testing requires a solid understanding of user behaviors and goals, which can be varied. E2E testing, therefore, can be time-consuming and potentially more expensive than some limited budgets allow.

Incorporate chaos engineering

Chaos engineering is testing that involves intentionally introducing failures into a software system to identify potential issues and weaknesses. By intentionally introducing problems throughout the database infrastructure, engineers can identify failure points and create solutions — avoiding costly downtime and potential loss of frustrated customers.

In well-orchestrated chaos engineering, there’s an experimental version of the system and a control version. A barrage of problems and outright attacks are injected into the system. The technical team then reviews the logging data to determine how the incursions affected the system.

Chaos engineering is designed to uncover unexpected, even shocking, surprises. Unlike targeted testing of traditional DevOps procedures, chaos engineering is widespread throughout the entire system. Since it’s performed on live systems, there is a chance that users can be briefly affected, but chaos engineering is a great motivator for following resilient design and maintenance practices. Most of all, chaos engineering is a way to fend off disaster.

Consider outside help

Consider the possibility that you might need outside expertise to help you plan, create, and manage an HA architecture. If an open source solution is right for you, but you need help implementing it, Percona recommends that you research to ensure the outside company is committed to the global open source community. Also, make sure you’ll have the freedom to innovate and to scale — up or down — as your company sees fit. Avoid unnecessary vendor lock-in. If you don’t use Percona, find another company that gives you those freedoms while helping you build the high availability infrastructure you need.

Percona architectures for highly available MySQL and PostgreSQL

Percona uses open source software to design architectures for high availability databases. Businesses of all sizes — small, medium, large, and enterprise — can use our designs to create their own architectures and ensure that applications and data stay available and in sync. If you need help, call on us for on-demand support as you implement an architecture. Or we can do it for you completely.

Here are resources (with viewable architectures) for help getting started:

High availability MySQL architectures

Check out Percona architecture and deployment recommendations, along with a technical overview, for a MySQL solution that provides a high level of high availability and assumes the usage of high read/write applications.

Percona Distribution for MySQL: High Availability with Group Replication

 

If you need even more high availability in your MySQL database, check out Percona XtraDB Cluster (PXC).

High availability PostgreSQL architectures

Access a PostgreSQL architecture description and deployment recommendations, along with a technical overview, of a solution that provides high availability for mixed-workload applications.

 

Percona Distribution for PostgreSQL: High Availability with Streaming Replication

 

High availability support you can count on

Percona designs, configures, and deploys high availability databases so that applications always have access to essential data. Then, we apply the same level of expertise and commitment to ensuring your databases continue to run smoothly and continuously. We’ll help you ensure that applications stay up, databases stay bug-free, and your entire system runs at optimal performance levels.

 

Percona High Availability Database Support

 

FAQ

The following are commonly asked questions and short answers about high availability in databases. More detailed answers are presented in the sections above.

What does high availability mean?

High availability refers to a database’s ability to remain operational and accessible to users without significant downtime or interruptions. To achieve high availability, a database system must be designed with redundancy and failover mechanisms and have no single points of failure. High availability is particularly important in the e-commerce, financial, and healthcare industries.

What is an example of high availability?

Database replication is an example of high availability. Multiple copies of the same database are maintained on different servers so that if one server fails, the database can still be accessed from another server. Different techniques are used to achieve database high availability, including primary-replica replication or multi-primary replication.

How does high availability work?

High availability of a database infrastructure works through a combination of redundancy, load balancing, failure detection, failover, and having no single point of failure (SPOF). Those elements combine to ensure that data is always accessible and that there is minimal downtime or interruption in service, even in the face of hardware or software failures.

What does 99.99% availability mean?

99.99% availability (or “four nines”) means that a database is down no more than 52.60 minutes per year, 4.38 minutes per month, 1.01 minutes per week, and 8.64 seconds per day.

What does 99.999% availability mean?

99.999% availability (or “five nines”) means that a database is down no more than 5.26 minutes per year, 26.30 seconds per month, 6.05 seconds per week, and 864.00 milliseconds per day.

Jun
12
2023
--

Ready-to-Use High Availability Architectures for MySQL and PostgreSQL

high availability architectures for MySQL and PostgreSQL

When it comes to access to their applications, users demand instant, reliable, and secure interactions — and that means databases must be highly available.

With database high availability (HA), services are largely uninterrupted, and end users are largely satisfied. Without high availability, there’s more-than-negligible downtime, and end users can become non-users (as in, former customers). A business can also incur reputational damage and face penalties for not meeting Service Level Agreements (SLAs).

Open source databases provide great foundations for high availability — without the pitfalls of vendor lock-in that can come with proprietary software. However, open source software doesn’t typically include built-in HA solutions. Sure, you can get there with the right extensions and tools, but it can be a long, burdensome, and potentially expensive process. So why not use a proven architecture instead of starting from scratch on your own?

This blog provides links to such architectures — for MySQL and PostgreSQL software. They’re proven and ready-to-go. You can use these Percona architectures to build highly available PostgreSQL or MySQL environments or have our experts do the heavy lifting for you. Either way, the architectures provide outlines for building databases that keep operations running optimally, even during peak usage or amid technical challenges caused by anything from brief outages to disasters.

First, let’s quickly examine what’s at stake and discuss standards for protecting those assets.

Importance of high availability architecture

As indicated, an HA architecture provides the blueprint for building a database that assures the continuity of critical business operations, even amid crashes, incursions, outages, and other threats. Conversely, choosing a piecemeal approach — one in which you attempt to build a database through the trial and error of various tools and extensions  — can leave your system vulnerable.

That vulnerability can be costly: A 2022 ITIC survey found that the cost of downtime is greater than $300,000 per hour for 91% of small-, mid-size, and large enterprises. Among just the mid-size and large respondents, 44% percent said a single hour of downtime could potentially cost them more than $1 million.

The ultimate goal of HA architecture

So what’s the ultimate goal of using an HA architecture? The obvious answer is this: To achieve high availability. That can mean different things for different businesses, but within IT, 99.999% (“five-nines”) of availability is the gold standard of database availability.

It really depends on how much downtime you can bear. With streaming services, for example, excessive downtime could result in significant financial and reputational losses for the business. Elsewhere, millions can be at stake for financial institutions, and lives can be at stake in the healthcare industry. Other organizations can tolerate a few minutes of downtime without negatively affecting or irking their end users. (Now, there’s a golden rule to go with the golden standard: Don’t irk the end user!)

The following table shows the amount of downtime for each level of availability, from “two nines” to “five nines.” You’ll see that doesn’t deliver 100% uptime, but it’s close.high availability

The immediate (working) goal and requirements of HA architecture

The more immediate (and “working” goal) of an HA architecture is to bring together a combination of extensions, tools, hardware, software, etc., and package them in a design (blueprint) of a database infrastructure that’s fit to perform optimally — amid demanding conditions. That design will depict an infrastructure of high availability nodes/clusters that work together (or separately, if necessary) so that if one goes down, another one takes over.

Proven architectures — including those we share in this blog — have met several high availability requirements. When those requirements are met, databases will include:

  • Redundancy: Critical components of a database are duplicated so that if one component fails, the functionality continues by using a redundant component. For example, in a server cluster, multiple servers are used to host the same application so that if one server fails, the application can continue to run on the other servers.
  • Load balancing: Traffic is distributed across multiple servers to prevent any one component from becoming overloaded. Load balancers can detect when a component is not responding and put traffic redirection in motion.
  • No single-point-of-failure (SPOF): This is both an exclusion and an inclusion for the architecture. There cannot be any single-point-of-failure in the database environment, including physical or virtual hardware the database system relies on that would cause it to fail. So there must be multiple components whose function, in part, is ensuring there’s no SPOF.
  • Failure detection: Monitoring mechanisms detect failures or issues that could lead to failures. Alert mechanisms report those failures or issues so that they are addressed immediately.
  • Failover: This involves automatically switching to a redundant component when the primary component fails. If a primary server fails, a backup server can take over and continue to serve requests.
  • Cluster and connection management: This includes software for automated provisioning, configuration, and scaling of database nodes. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
  • Automated backup, continuous archiving, and recovery: This is of extreme importance if any replication delay happens and the replica node isn’t able to work at the primary’s pace. The backed-up and archived files can also be used for point-in-time recovery if any disaster occurs.
  • Scalability: HA architecture should support scalability that enables automated management of increased workloads and data volume. This can be achieved through techniques like sharding, where the data is partitioned and distributed across multiple nodes, or by adding more nodes to the cluster as needed.

Get going (or get some help) using proven Percona architectures

Designing and implementing a highly available database environment requires considerable time and expertise. So instead of you having to select, configure, and test those configurations to build a highly available database environment, why not use ours? You can use Percona architectures on your own, call on us as needed, or have us do it all for you.

High availability MySQL architectures

Check out Percona architecture and deployment recommendations, along with a technical overview, for a MySQL solution that provides a high level of high availability and assumes the usage of high read/write applications.

Percona Distribution for MySQL: High Availability with Group Replication

 

If you need even more high availability in your MySQL database, check out Percona XtraDB Cluster (PXC).

High availability PostgreSQL architectures

Access a PostgreSQL architecture description and deployment recommendations, along with a technical overview, of a solution that provides high availability for mixed-workload applications.

 

Percona Distribution for PostgreSQL: High Availability with Streaming Replication

 

View a disaster recovery architecture for PostgreSQL, with deployment recommendations based on Percona best practices.

PostgreSQL: Disaster Recovery

 

Here are additional links to Percona architectures for high availability PostgreSQL databases:

Highly Available PostgreSQL From Percona

Achieving High Availability on PostgreSQL With Open Source Tools

Highly Availability in PostgreSQL with Patroni

High Availability MongoDB From Percona

Percona offers support for MongoDB clusters in any environment. Our experienced support team is available 24x7x365 to ensure continual high performance from your MongoDB systems.

 

Percona Operator for MongoDB Design Overview

Dec
14
2021
--

High Availability and Disaster Recovery Recipes for PostgreSQL on Kubernetes

High Availability and Disaster Recovery PostgreSQL on Kubernetes

Percona Distribution for PostgreSQL Operator allows you to deploy and manage highly available and production-grade PostgreSQL clusters on Kubernetes with minimal manual effort. In this blog post, we are going to look deeper into High Availability, Disaster Recovery, and Scaling of PostgreSQL clusters.

High Availability

Our default custom resource manifest deploys a highly available (HA) PostgreSQL cluster. Key components of HA setup are:

  • Kubernetes Services that point to pgBouncer and replica nodes
  • pgBouncer – a lightweight connection pooler for PostgreSQL
  • Patroni – HA orchestrator for PostgreSQL
  • PostgreSQL nodes – we have one primary and 2 replica nodes in hot standby by default

high availability postgresql

Kubernetes Service is the way to expose your PostgreSQL cluster to applications or users. We have two services:

  • clusterName-pgbouncer

    – Exposing your PostgreSQL cluster through pgBouncer connection pooler. Both reads and writes are sent to the Primary node. 

  • clusterName-replica

    – Exposes replica nodes directly. It should be used for reads only. Also, keep in mind that connections to this service are not pooled. We are working on a better solution, where the user would be able to leverage both connection pooling and read-scaling through a single service.

By default we use ClusterIP service type, but you can change it in

pgBouncer.expose.serviceType

or

pgReplicas.hotStandby.expose.serviceType,

respectively.

Every PostgreSQL container has Patroni running. Patroni monitors the state of the cluster and in case of Primary node failure switches the role of the Primary to one of the Replica nodes. PgBouncer always knows where Primary is.

As you see we distribute PostgreSQL cluster components across different Kubernetes nodes. This is done with Affinity rules and they are applied by default to ensure that single node failure does not cause database downtime.

Multi-Datacenter with Multi-AZ

Good architecture design is to run your Kubernetes cluster across multiple datacenters. Public clouds have a concept of availability zones (AZ) which are data centers within one region with a low-latency network connection between them. Usually, these data centers are at least 100 kilometers away from each other to minimize the probability of regional outage. You can leverage multi-AZ Kubernetes deployment to run cluster components in different data centers for better availability.

Multi-Datacenter with Multi-AZ

To ensure that PostgreSQL components are distributed across availability zones, you need to tweak affinity rules. Now it is only possible through editing Deployment resources directly:

$ kubectl edit deploy cluster1-repl2
…
-            topologyKey: kubernetes.io/hostname
+            topologyKey: topology.kubernetes.io/zone

Scaling

Scaling PostgreSQL to meet the demand at peak hours is crucial for high availability. Our Operator provides you with tools to scale PostgreSQL components both horizontally and vertically.

Vertical Scaling

Scaling vertically is all about adding more power to a PostgreSQL node. The recommended way is to change resources in the Custom Resource (instead of changing them in Deployment objects directly). For example, change the following in the

cr.yaml

to get 256 MBytes of RAM for all PostgreSQL Replica nodes:

  pgReplicas:
    hotStandby:
      resources:
        requests:
-         memory: "128Mi"
+         memory: "256Mi"

Apply

cr.yaml

:

$ kubectl apply -f cr.yaml

Use the same approach to tune other components in their corresponding sections.

You can also leverage Vertical Pod Autoscaler (VPA) to react to load spikes automatically. We create a Deployment resource for Primary and each Replica node. VPA objects should target these deployments. The following example will track one of the replicas Deployment resources of cluster1 and scale automatically:

apiVersion: autoscaling.k8s.io/v1
kind: VerticalPodAutoscaler
metadata:
  name: pxc-vpa
spec:
  targetRef:
    apiVersion: "apps/v1"
    kind:       Deployment
    name:     cluster1-repl1  
    namespace:  pgo
  updatePolicy:
    updateMode: "Auto"

Please read more about VPA and its capabilities in its documentation.

Horizontal Scaling

Adding more replica nodes or pgBouncers can be done by changing size parameters in the Custom Resource. Do the following change in the default

cr.yaml

:

  pgReplicas:
    hotStandby:
-      size: 2
+      size: 3

Apply the change to get one more PostgreSQL Replica node:

$ kubectl apply -f cr.yaml

Starting from release 1.1.0 it is also possible to scale our cluster using kubectl scale command. Execute the following to have two PostgreSQL replica nodes in cluster1:

$ kubectl scale --replicas=2 perconapgcluster/cluster1
perconapgcluster.pg.percona.com/cluster1 scaled

In the latest release, it is not possible to use Horizontal Pod Autoscaler (HPA) yet and we will have it supported in the next one. Stay tuned.

Disaster Recovery

It is important to understand that Disaster Recovery (DR) is not High Availability. DR’s goal is to ensure business continuity in the case of a massive disaster, such as a full region outage. Recovery in such cases can be of course automated, but not necessarily – it strictly depends on the business requirements.

Disaster Recovery postgresql

Backup and Restore

I think it is the most common Disaster Recover protocol – take the backup, store it in some 3rd party premises, restore to another datacenter if needed.

This approach is simple, but comes with a long recovery time, especially if the database is big. Use this method only if it passes your Recovery Time Objectives (RTO).

Recovery Time Objectives

Our Operator handles backup and restore for PostgreSQL clusters. The disaster recovery is built around pgBackrest and looks like the following:

  1. Configure pgBackrest to upload backups to S3 or GCS (see our documentation for details).
  2. Create the backup manually (through pgTask) or ensure that a scheduled backup was created. 
  3. Once the Main cluster fails, create the new cluster in the Disaster Recovery data center. The cluster must be running in standby mode and pgBackrest must be pointing to the same repository as the main cluster:
spec:
  standby: true
  backup:
  # same config as on original cluster

Once data is recovered, the user can turn off standby mode and switch the application to DR cluster.

Continuous Restoration

This approach is quite similar to the above: pgBackrest instances continuously synchronize data between two clusters through object storage. This approach minimizes RTO and allows you to switch the application traffic to the DR site almost immediately. 

Continuous Restoration postgresql

Configuration here is similar to the previous case, but we always run a second PostgreSQL cluster in the Disaster Recovery data center. In case of main site failure just turn off the standby mode:

spec:
  standby: false

You can use a similar setup to migrate the data to and from Kubernetes. Read more about it in the Migrating PostgreSQL to Kubernetes blog post.

Conclusion

Kubernetes Operators provide ready-to-use service, and in the case of Percona Distribution for PostgreSQL Operator, the user gets a production-grade, highly available database cluster. In addition, the Operator provides day-2 operation capabilities and automates day-to-day routine.

We encourage you to try out our operator. See our GitHub repository and check out the documentation.

Found a bug or have a feature idea? Feel free to submit it in JIRA.

For general questions please raise the topic in the community forum

Are you a developer and looking to contribute? Please read our CONTRIBUTING.md and send the Pull.

Dec
09
2021
--

A Look Into Percona XtraDB Cluster Non-Blocking Operation for Online Schema Upgrade

Percona XtraDB Cluster Non-Blocking Operation

Percona XtraDB Cluster Non-Blocking OperationPercona XtraDB Cluster 8.0.25 (PXC) has introduced a new option to perform online schema modifications: NBO (Non-Blocking Operation).

When using PXC, the cluster relies on the wsrep_OSU_method parameter to define the Online Schema Upgrade (OSU) method the node uses to replicate DDL statements. 

Until now, we normally have three options:

  • Use Total Isolation Order (TOI, the default)
  • Use Rolling Schema Upgrade (RSU)
  • Use Percona’s online schema change tool (TOI + PTOSC)

Each method has some positive and negative aspects. TOI will lock the whole cluster from being able to accept data modifications for the entire time it takes to perform the DDL operation. RSU will misalign the schema definition between the nodes, and in any case, the node performing the DDL operation is still locked. Finally, TOI+PTOSC will rely on creating triggers and copying data, so in some cases, this can be very impactful. 

The new Non-Blocking Operation (NBO) method is to help to reduce the impact on the cluster and make it easier to perform some DDL operations.

At the moment we only support a limited set of operations with NBO like:

  • ALTER INDEX
  • CREATE INDEX
  • DROP INDEX

Any other command will result in an error message ER_NOT_SUPPORTED_YET.

But let us see how it works and what the impact is while we will also compare it with the default method TOI.

What we will do is work with four connections:

1 – to perform ddl
2 – to perform insert data in the table being altered
3 – to perform insert data on a different table
4-5 – checking the other two nodes operations

PXC must be at least Version 8.0.25-15.1.

The table we will modify is :

DC1-1(root@localhost) [windmills_s]>show create table windmills_test\G
*************************** 1. row ***************************
       Table: windmills_test
Create Table: CREATE TABLE `windmills_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `millid` smallint NOT NULL,
  `kwatts_s` int NOT NULL,
  `date` date NOT NULL,
  `location` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `active` tinyint NOT NULL DEFAULT '1',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `strrecordtype` char(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_millid` (`millid`,`active`),
  KEY `IDX_active` (`id`,`active`),
  KEY `kuuid_x` (`uuid`),
  KEY `millid_x` (`millid`),
  KEY `active_x` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=8199260 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

And contains ~five million rows.

DC1-1(root@localhost) [windmills_s]>select count(*) from windmills_test;
+----------+
| count(*) |
+----------+
|  5002909 |
+----------+
1 row in set (0.44 sec)

The Commands

Connection 1:

ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE;
  ALTER TABLE windmills_test drop INDEX idx_1, ALGORITHM=INPLACE;

 

Connection 2:

while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "insert into windmills_test  select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills7 limit 1;" -e "select count(*) from windmills_s.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done

 

Connection 3:

while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "insert into windmills8  select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills7 limit 1;" -e "select count(*) from windmills_s.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done

 

Connections 4-5:

while [ 1 = 1 ];do echo "$(date +'%T.%3N')";/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "show full processlist;"|egrep -i -e "(windmills_test|windmills_s)"|grep -i -v localhost;sleep 1;done

Operations

  • Start inserts from connections
  • Start commands in connections 4 – 5 on the other nodes
  • Execute: 
    • For TOI 
      • DC1-1(root@localhost) [windmills_s]>SET SESSION wsrep_OSU_method=TOI;
    • For NBO
      • DC1-1(root@localhost) [windmills_s]>SET SESSION wsrep_OSU_method=NBO;
    • For both
      • DC1-1(root@localhost) [windmills_s]>ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=shared;

Let’s Run It

Altering a Table with TOI

DC1-1(root@localhost) [windmills_s]>ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE;
Query OK, 0 rows affected (1 min 4.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

Inserts in the altering table (connection 2):

.450
.492
64.993 <--- Alter blocks all inserts on the table we are altering
.788
.609

 

Inserts on the other table (connection 3):

.455
.461
64.161 <--- Alter blocks all inserts on all the other tables as well
.641
.483

 

On the other nodes at the same time of the ALTER we can see:

Id  User             db         Command Time  State             Info                                                                            Time_ms Rows_sent Rows_examined 
15	system user		windmills_s	Query	102	  altering table	ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE	102238	0	       0    <--- time from start

In short, we have the whole cluster locked for ~64 seconds. During this period of time, all the operations to modify data or structure were on hold. 

Let’s Now Try With NBO

Inserts in the altering table:

.437
.487
120.758 <---- Execution time increase
.617
.510

 

Inserts on the other table:

.468
.485
25.061 <---- still a metalock, but not locking the other tables for the whole duration 
.494
.471

 

On the other nodes at the same time of the ALTER we can see:

Id      User         db             Command Time  State             Info                                                                            Time_ms Rows_sent Rows_examined 
110068	system user	 windmills_s	Connect	86	  altering table	ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE	 120420	 0	          0

 

In this case, what is also interesting to note is that:

  1. We have a moment of metalock:
    1. 110174 pmm 127.0.0.1:42728 NULL Query 2 Waiting for table metadata lock  SELECT x FROM information_schema.tables  WHERE TABLE_SCHEMA = 'windmills_s' 1486 10    0
    2. 110068  system user connecting host windmills_s Connect 111 closing tables ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE 111000 0 0
  2. The execution time is longer

Summarizing:

TOI            NBO
Time on hold for insert for altering table   	~64 sec    	~120 sec
Time on hold for insert for another table   	~64 sec      ~25 sec 
metalock                       			whole time  	 only at the end

What is Happening? What are the Differences and Why Does it Take Longer with NBO?

Let’s see at a very high level how the two work:

  • TOI: when you issue a DDL like ADD INDEX a metadata lock is taken on the table and it will be released only at the end of the operation. During this time, you cannot:
    • Perform DMLs on any cluster node
    • Alter another table in the cluster
  • NBO: the metadata lock is taken at the start and at the end for a very brief period of time. The ADD INDEX operation will then work on each node independently. The lock taken at the end is to have all the nodes agree on the operation and commit or rollback (using cluster error voting). This final phase costs a bit more in time and is what adds a few seconds to the operation execution. But during the operation:
    • You can alter another table (using NBO)
    • You can continue to insert data, except in the table(s) you are altering.
    • On node crash, the operation will continue on the other nodes, and if successful it will persist.  

In short, the cluster server behavior changes significantly when using NBO, offering significant flexibility compared to TOI. The cost in time should not linearly increase with the dimension of the table, but more in relation to the single node efficiency in performing the ALTER operation.       

Conclusion

NBO can be significantly helpful to reduce the impact of DDL on the cluster, for now, limited to the widely used creation/modification/drop of an index. But in the future … we may expand it. 

The feature is still a technology preview, so do not trust it in production, but test it and let us know what you think. 

Final comment: another distribution has introduced NBO, but only if you buy the enterprise version.

Percona, which is truly open source with facts not just words, has implemented NBO in standard PXC, and the code is fully open source. This is not the first one, but just another of the many features Percona is offering for free while others ask you to buy the enterprise version.

Enjoy the product and let us have your feedback! Great MySQL to all! 

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com