Skip to content

## MySQL master discovery methods, part 3: app & service discovery

MySQL High availability orchestrator Replication May 8, 2018May 22, 2018

This is the third in a series of posts reviewing methods for MySQL master discovery: the means by which an application connects to the master of a replication tree. Moreover, the means by which, upon master failover, it identifies and connects to the newly promoted master.

These posts are not concerned with the manner by which the replication failure detection and recovery take place. I will share `orchestrator` specific configuration/advice, and point out where cross DC `orchestrator/raft` setup plays part in discovery itself, but for the most part any recovery tool such as `MHA`, `replication-manager`, `severalnines` or other, is applicable.

We discuss asynchronous (or semi-synchronous) replication, a classic single-master-multiple-replicas setup. A later post will briefly discuss synchronous replication (Galera/XtraDB Cluster/InnoDB Cluster).

### App & service discovery

Part 1 and part 2 presented solutions where the app remained ingorant of master’s identity. This part takes a complete opposite direction and gives the app ownership on master access.

We introduce a service discovery component. Commonly known are Consul, ZooKeeper, etcd, highly available stores offering key/value (K/V) access, leader election or full blown service discovery & health.

We satisfy ourselves with K/V functionality. A key would be `mysql/master/cluster1` and a value would be the master’s hostname/port.

It is the app’s responsibility at all times to fetch the identity of the master of a given cluster by querying the service discovery component, thereby opening connections to the indicated master.

The service discovery component is expected to be up at all times and to contain the identity of the master for any given cluster.

Continue reading » “MySQL master discovery methods, part 3: app & service discovery”

## MySQL master discovery methods, part 2: VIP & DNS

MySQL High availability orchestrator Replication May 7, 2018May 22, 2018

This is the second in a series of posts reviewing methods for MySQL master discovery: the means by which an application connects to the master of a replication tree. Moreover, the means by which, upon master failover, it identifies and connects to the newly promoted master.

These posts are not concerned with the manner by which the replication failure detection and recovery take place. I will share `orchestrator` specific configuration/advice, and point out where cross DC `orchestrator/raft` setup plays part in discovery itself, but for the most part any recovery tool such as `MHA`, `replication-manager`, `severalnines` or other, is applicable.

We discuss asynchronous (or semi-synchronous) replication, a classic single-master-multiple-replicas setup. A later post will briefly discuss synchronous replication (Galera/XtraDB Cluster/InnoDB Cluster).

### Master discovery via VIP

In part 1 we saw that one the main drawbacks of DNS discovery is the time it takes for the apps to connect to the promoted master. This is the result of both DNS deployment time as well as client’s `TTL`.

A quicker method is offered: use of VIPs (Virtual IPs). As before, apps would connect to `cluster1-writer.example.net`, `cluster2-writer.example.net`, etc. However, these would resolve to specific VIPs.

Say `cluster1-writer.example.net` resolves to `10.10.0.1`. We let this address float between servers. Each server has its own IP (say `10.20.0.XXX`) but could also potentially claim the VIP `10.10.0.1`.

VIPs can be assigned by switches and I will not dwell into the internals, because I’m not a network expert. However, the following holds:

- Acquiring a VIP is a very quick operation.
- Acquiring a VIP must take place on the acquiring host.
- A host may be unable to acquire a VIP should another host holds the same VIP.
- A VIP can only be assigned within a bounded space: hosts connected to the same switch; hosts in the same Data Center or availability zone.

Continue reading » “MySQL master discovery methods, part 2: VIP & DNS”

## MySQL master discovery methods, part 1: DNS

MySQL High availability orchestrator Replication May 3, 2018May 22, 2018

This is the first in a series of posts reviewing methods for MySQL master discovery: the means by which an application connects to the master of a replication tree. Moreover, the means by which, upon master failover, it identifies and connects to the newly promoted master.

These posts are not concerned with the manner by which the replication failure detection and recovery take place. I will share `orchestrator` specific configuration/advice, and point out where cross DC `orchestrator/raft` setup plays part in discovery itself, but for the most part any recovery tool such as `MHA`, `replication-manager`, `severalnines` or other, is applicable.

We discuss asynchronous (or semi-synchronous) replication, a classic single-master-multiple-replicas setup. A later post will briefly discuss synchronous replication (Galera/XtraDB Cluster/InnoDB Cluster).

### Master discovery via DNS

In DNS master discovery applications connect to the master via a name that gets resolved to the master’s box. By way of example, apps would target the masters of different clusters by connecting to `cluster1-writer.example.net`, `cluster2-writer.example.net`, etc. It is up for the DNS to resolve those names to IPs.

Continue reading » “MySQL master discovery methods, part 1: DNS”

## Using dbdeployer in CI tests

MySQL dbdeployer gh-ost Open Source Replication testing February 20, 2018May 11, 2020

I was very pleased when Giuseppe Maxia (aka datacharmer) unveiled dbdeployer in his talk at pre-FOSDEM MySQL day. The announcement came just at the right time. I wish to briefly describe how we use `dbdeployer` (work in progress).

### The case for gh-ost

A user opened an issue on `gh-ost`, and the user was using MySQL `5.5`. `gh-ost` is being tested on `5.7` where the problem does not reproduce. A discussion with Gillian Gunson raised the concern of not testing on all versions. Can we run `gh-ost` tests for all MySQL/Percona/MariaDB versions? Should we? How easy would it be?

### gh-ost tests

`gh-ost` has three different test types:

- Unit tests: these are plain `golang` logic tests which are very easy and quick to run.
- Integration tests: the topic of this post, see following. Today these do not run as part of an automated CI testing.
- System tests: putting our production tables to the test, continuously migrating our production data on dedicated replicas, verifying checksums are identical and data is intact, read more.

Unit tests are already running as part of automated CI (every PR is subjected to those tests). Systems tests are clearly tied to our production servers. What’s the deal with the integration tests? Continue reading » “Using dbdeployer in CI tests”

## orchestrator 3.0.6: faster crash detection & recoveries, auto Pseudo-GTID, semi-sync and more

MySQL High availability Open Source orchestrator Replication January 29, 2018January 29, 2018

`orchestrator` 3.0.6 is released and includes some exciting improvements and features. It quickly follows up on 3.0.5 released recently, and this post gives a breakdown of some notable changes:

### Faster failure detection

Recall that `orchestrator` uses a holistic approach for failure detection: it reads state not only from the failed server (e.g. master) but also from its replicas. `orchestrator` now detects failure faster than before:

- A detection cycle has been eliminated, leading to quicker resolution of a failure. On our setup, where we poll servers every `5sec`, failure detection time dropped from `7-10sec` to `3-5sec`, keeping reliability. The reduction in time does not lead to increased false positives.
  Side note: you may see increased not-quite-failure analysis such as “I can’t see the master” (`UnreachableMaster`).
- Better handling of network scenarios where packets are dropped. Instead of hanging till TCP timeout, `orchestrator` now observes server discovery asynchronously. We have specialized failover tests that simulate dropped packets. The change reduces detection time by some `5sec`.

### Faster master recoveries

Promoting a new master is a complex task which attempts to promote the best replica out of the pool of replicas. It’s not always the most up-to-date replica. The choice varies depending on replica configuration, version, and state.

With recent changes, `orchestrator` is able to to recognize, early on, that the replica it would like to promote as master is ideal. Assuming that is the case, `orchestrator` is able to immediate promote it (i.e. run hooks, set `read_only=0` etc.), and run the rest of the failover logic, i.e. the rewiring of replicas under the newly promoted master, asynchronously.

This allows the promoted server to take writes sooner, even while its replicas are not yet connected. It also means external hooks are executed sooner.

Between faster detection and faster recoveries, we’re looking at some `10sec` reduction in overall recovery time: from moment of crash to moment where a new master accepts writes. We stand now at `< 20sec` in almost all cases, and `< 15s` in optimal cases. Those times are measured on our failover tests.

We are working on reducing failover time unrelated to `orchestrator` and hope to update soon.

### Automated Pseudo-GTID

As reminder, Pseudo-GTID is an alternative to GTID, without the kind of commitment you make with GTID. It provides similar “point your replica under any other server” behavior GTID allows. Continue reading » “orchestrator 3.0.6: faster crash detection & recoveries, auto Pseudo-GTID, semi-sync and more”

## Implementing non re-entrant functions in Golang

golang golang January 4, 2018January 4, 2018

A non re-entrant function is a function that could only be executing once at any point in time, regardless of how many times it is being invoked and by how many goroutines.

This post illustrates blocking non re-entrant functions and yielding non re-entrant functions implementations in `golang`.

### A use case

A service is polling for some conditions, monitoring some statuses once per second. We want each status to be checked independently of others without blocking. An implementation might look like:

    func main() {
        tick := time.Tick(time.Second)
        go func() {
            for range tick {
                go CheckSomeStatus()
                go CheckAnotherStatus()
            }
        }()
    }

We choose to run each status check in its own goroutine so that `CheckAnotherStatus()` doesn’t wait upon `CheckSomeStatus()` to complete.

Each of these checks typically take a very short amount of time, and much less than a second. What happens, though, if `CheckAnotherStatus()` itself takes more than one second to run? Perhaps there’s an unexpected network or disk latency affecting the execution time of the check.

Does it make sense for the function to be executed twice at the same time? If not, we want it to be non re-entrant. Continue reading » “Implementing non re-entrant functions in Golang”

## orchestrator 3.0.3: auto provisioning raft nodes, native Consul support and more

MySQL November 16, 2017November 16, 2017

orchestrator 3.0.3 is released! There’s been a lot going on since 3.0.2:

### orchestrator/raft: auto-provisioning nodes via lightweight snaphsots

In an orchestrator/raft setup, we have n hosts forming a `raft` cluster. In a 3-node setup, for example, one node can go down, and still the remaining two will form a consensus, keeping the service operational. What happens when the failed node returns?

With `3.0.3` the failed node can go down for as long as it wants. Once it comes back, it attempts to join the raft cluster. A node keeps its own snapshots and its raft log outside the relational backend DB. If it has recent-enough data, it just needs to catch up with raft replication log, which is acquires from one of the active nodes.

If its data is very stale, it will request a snapshot from an active node, which it will import, and will just resume from that point.

If its data is gone, that’s not a problem. It gets a snapshot from an active node, improts it, and keeps running from that point.

If it’s a newly provisioned box, that’s not a problem. It gets a snapshot from an active node, … etc.

- SQLite backed setups can just bootstrap new nodes. No need to dump+load or import any data.
  - Side effect: you may actually use `:memory:`, where SQLite does not persist any data to disk. Remember that the raft snapshots and replication log will cover you. The cheat is that the raft replication log itself is managed and persisted by an independent SQLite database.
- MySQL backed setups will still need to make sure `orchestrator` has the privileges to deploy itself.

More info in the docs.

This plays very nicely into the hands of `kubernetes`, which is on `orchestrator`‘s roadmap.

### Key Value, native Consul support (Zk TODO)

`orchestrator` now supports Key-Value stores built-in, and Consul in particular.

At this time the purpose of orchestrator KV is to support master discovery. `orchestrator` will write the identity of the master of each cluster to KV store. The user will use that information to apply changes to their infrastructure.

For example, the user will rely on Consul KV entries, written by `orchestrator`, to generate proxy config files via `consul-template`, such that traffic is directed via the proxy onto the correct master.

`orchestrator` supports:

- Manually writing identity of cluster’s master to KV store
  - e.g. ``orchestrator-client -c submit-masters-to-kv-stores -alias mycluster``
- Automatically updating master’s identify upon failover

Key-value pairs are in the form of ``–``. For example:

- Key is `main_cluster`
- Value is `my-db-0123.my.company.com:3306`

### Web UI improvements

Using the web UI, you can now: Continue reading » “orchestrator 3.0.3: auto provisioning raft nodes, native Consul support and more”

## gh-ost 1.0.42 released: JSON support, optimizations

MySQL gh-ost Open Source September 14, 2017September 15, 2017

gh-ost 1.0.42 is released and available for download.

### JSON

MySQL 5.7’s `JSON` data type is now supported.

There is a soft-limitation, that your `JSON` may not be part of your `PRIMARY KEY`. Currently this isn’t even supported by MySQL anyhow.

### Performance

Two noteworthy changes are:

- Client side prepared statements reduce network traffic and round trips to the server.
- Range query iteration avoids creating temporary tables and filesorting.

We’re not running benchmarks at this time to observe performance gains.

### 5.7

More tests validating `5.7` compatibility (at this time GitHub runs MySQL `5.7` in production).

### Ongoing

Many other changes included.

We are grateful for all community feedback in form of open Issues, Pull Requests and questions!

`gh-ost` is authored by GitHub. It is free and open source and is available under the MIT license.

### Speaking

In two weeks time, Jonah Berquist will present gh-ost: Triggerless, Painless, Trusted Online Schema Migrations at Percona Live, Dublin.

Tom Krouper and myself will present MySQL Infrastructure Testing Automation at GitHub, where, among other things, we describe how we test `gh-ost` in production.

## Speaking at Percona Live Dublin: keynote, orchestrator tutorial, MySQL testing automation

MySQL Open Source PerconaLive secondary Speaking September 13, 2017September 13, 2017

I’m looking forward to a busy Percona Live Dublin conference, delivering three talks. Chronologically, these are:

- Practical orchestrator tutorial
  Attend this 3 hour tutorial for a thorough overview on orchestrator: what, why, how to configure, best advice, deployments, failovers, security, high availability, common operations, …
  We will of course discuss the new orchestrator/raft setup and share our experience running it in production.
  The tutorial will allow for general questions from the audience and open discussions.
- Why Open Sourcing Our Database Tooling was the Smart Decision
  What it says. A 10 minute journey advocating for open sourcing infrastructure.
- MySQL Infrastructure Testing Automation at GitHub
  Co-presenting with Tom Krouper, we share how & why we run infrastructure tests in and near production that gives us trust in many of our ongoing, ever changing operations. Essentially this is “why you should feel OK trusting us with your data”.

See you there!

## orchestrator 3.0.2 GA released: raft consensus, SQLite

MySQL Open Source orchestrator September 12, 2017September 12, 2017

orchestrator 3.0.2 GA is released and available for download (see also packagecloud repository).

`3.0.2` is the first stable release in the `3.0*` series, introducing (recap from 3.0 pre-release announcement):

### orchestrator/raft

Raft is a consensus protocol, supporting leader election and consensus across a distributed system. In an `orchestrator/raft` setup `orchestrator` nodes talk to each other via raft protocol, form consensus and elect a leader. Each `orchestrator` node has its own dedicated backend database. The backend databases do not speak to each other; only the `orchestrator` nodes speak to each other.

No MySQL replication setup needed; the backend DBs act as standalone servers. In fact, the backend server doesn’t have to be MySQL, and `SQLite`is supported. `orchestrator` now ships with `SQLite` embedded, no external dependency needed.

For details, please refer to the documentation:

- orchestrator/raft: overview
- orchestrator/raft vs. shared backend DB setup, comparison

### SQLite

Suggested and requested by many, is to remove `orchestrator`‘s own dependency on a MySQL backend. `orchestrator` now supports a SQLite backend.

`SQLite` is a transactional, relational, embedded database, and as of `3.0` it is embedded within `orchestrator`, no external dependency required.

### orchestrator-client

orchestrator-client is a client shell script which mimics the command line interface, while running `curl | jq` requests against the HTTP API. It stands to simplify your deployments: interacting with the orchestrator service via `orchestrator-client` is easier and only requires you to place a shell script (this is as opposed to installing the `orchestrator` binary + configuration file).

`orchestrator-client` is the way to interact with your `orchestrator/raft` cluster. `orchestrator-client` now has its own `RPM/deb` release package.

You may still use the web interface, web API ; and a special `--ignore-raft-setup` keeps power at your hand (use at your own risk).

### State of orchestrator/raft

`orchestrator/raft` is a big change: Continue reading » “orchestrator 3.0.2 GA released: raft consensus, SQLite”