On SHOW BINLOG/RELAYLOG EVENTS

MySQL

Some notes after working with SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS statements; there are a few gotchas and some interesting facts. My reflections also follow.

I’m calling these commands from orchestrator when working with Pseudo-GTID (which I do alot). I prefer to work with agent-free design, where a single, remote service can do everything: examine replication status, scan binary logs for information, and recover broken topologies via gluing together servers that were not previously directly associated.

Alas, documentation is short on these commands, and some stuff I learned the hard way.

Basically, SHOW BINLOG/RELAYLOG EVENTS commands are a poor man’s replacement to mysqlbinlog, only you can issue them on MySQL protocol, and you do not have to have the binary/relay log files locally on your host.

Fun fact

The binary logs are called so because they are compressed. You are familiar with the binlog position you see on SHOW MASTER STATUS or SHOW SLAVE STATUS. You are familiar with the binlog position as you see it when you execute “mysqlbinlog mybinlog.001234”. The position of a new entry equals to the file size of the binary log at that time. If:

$ ls -l master/data/mysql-bin.015901
-rw-rw---- 1 user user 401408 Jul 18 02:44 master/data/mysql-bin.015901

Then the next entry will be at position 401408, as this is the file size in bytes.

And so when MySQL writes an entry to the binary log, it (of course) knows the entry’s position in the binary log, but then also immediately knows the position of the next entry.

We’ll revisit this fact later.

Output of SHOW BINLOG/RELAYLOG EVENTS

The output of both statement depends on the binlog_format. In Statement Based Replication it may look like: Continue reading » “On SHOW BINLOG/RELAYLOG EVENTS”

Pseudo GTID, ASCENDING

MySQL

Pseudo GTID is a technique where we inject Globally Unique entries into MySQL, gaining GTID abilities without using GTID. It is supported by orchestrator and described in more detail here, here and here.

Quick recap: we can join two slaves to replicate from one another even if they never were in parent-child relationship, based on our uniquely identifiable entries which can be found in the slaves’ binary logs or relay logs. Having Pseudo-GTID injected and controlled by us allows us to optimize failovers into quick operations, especially where a large number of server is involved.

Ascending Pseudo-GTID further speeds up this process for delayed/lagging slaves.

Recap, visualized

(but do look at the presentation):

pseudo-gtid-quick

  1. Find last pseudo GTID in slave’s binary log (or last applied one in relay log)
  2. Search for exact match on new master’s binary logs
  3. Fast forward both through successive identical statements until end of slave’s applied entries is reached
  4. Point slave into cursor position on master

What happens if the slave we wish to reconnect is lagging? Or perhaps it is a delayed replica, set to run 24 hours behind its master?

The naive approach would expand bullet #2 into:

  • Search for exact match on master’s last binary logs
  • Unfound? Move on to previous (older) binary log on master
  • Repeat

The last Pseudo-GTID executed by the slave was issued by the master over 24 hours ago. Suppose the master generates one binary log per hour. This means we would need to full-scan 24 binary logs of the master where the entry will not be found; to only be matched in the 25th binary log (it’s an off-by-one problem, don’t hold the exact number against me).

Ascending Pseudo GTID

Since we control the generation of Pseudo-GTID, and since we control the search for Pseudo-GTID, we are free to choose the form of Pseudo-GTID entries. We recently switched into using Ascending Pseudo-GTID entries, and this works like a charm. Consider these Pseudo-GTID entries: Continue reading » “Pseudo GTID, ASCENDING”

What makes a MySQL server failure/recovery case?

MySQL

Or: How do you reach the conclusion your MySQL master/intermediate-master is dead and must be recovered?

This is an attempt at making a holistic diagnosis of our replication topologies. The aim is to cover obvious and not-so-obvious crash scenarios, and to be able to act accordingly and heal the topology.

At Booking.com we are dealing with very large amounts of MySQL servers. We have many topologies, and many servers in each topology. See past numbers to get a feel for it. At these numbers failures happen frequently. Typically we would see normal slaves failing, but occasionally — and far more frequently than we would like to be paged for — an intermediate master or a master would crash. But our current (and ever in transition) setup also include SANs, DNS records, VIPs, any of which can fail and bring down our topologies.

Tackling issues of monitoring, disaster analysis and recovery processes, I feel safe to claim the following statements:

  • The fact your monitoring tool cannot access your database does not mean your database has failed.
  • The fact your monitoring tool can access your database does not mean your database is available.
  • The fact your database master is unwell does not mean you should fail over.
  • The fact your database master is alive and well does not mean you should not fail over.

Bummer. Let’s review a simplified topology with a few failure scenarios. Some of these scenarios you will find familiar. Some others may be caused by setups you’re not using. I would love to say I’ve seen it all but the more I see the more I know how strange things can become. Continue reading » “What makes a MySQL server failure/recovery case?”

Orchestrator visual cheatsheet

MySQL

Orchestrator is growing. Supporting automatic detection of topologies, simple refactoring of topology trees, complex refactoring via Pseudo-GTID, failure detection and automated discovery, it is becoming larger and larger by the day.

One of the problems with growign projects is hwo to properly document them. Orchestrator enjoys a comprehensive manual, but as these get more and more detailed, it is becoming difficult to get oriented and pointed in the right direction. I’ve done my best to advise the simple use cases throughout the manual.

One thing that is difficult to put into words is topologies. Explaining “failover of an intermediate master S1 that has S2,…,Sn slaves onto a sibling of S1 provided that…” is too verbose. So here’s a quick visual cheatsheet for (current) topology refactoring commands. Refactoring commands are a mere subset of overall orchestrator commands, but they’re great to play with and perfect for visualization.

The “move” and related commands use normal replication commands (STOP SLAVE; CHANGE MASTER TO; START SLAVE UNTIL;”…).

The “match” and related commands utilize Pseudo-GTID and use more elaborate MySQL commands (SHOW BINLOG EVENTS, SHOW RELAYLOG EVENTS).

So without further ado, here’s what each command does (and do run “orchestrator” from the command line to get a man-like explanation of everything, or just go to the manual). Continue reading » “Orchestrator visual cheatsheet”

“awesome-mysql” curated list created, open for pull requests

MySQL

Following up on popular “awesome-*” lists (e.g. awesome-python, awesome-golang etc.), I’ve created the awesome-mysql curated list.

This is a list of technologies (and resources) in and around MySQL, and means to serve as a place to find reliable software and info. I recently happened to notice there are some tools I’m familiar with that are unknown to others; tools unknown to me that are in good use.

The list is naturally and intentionally incomplete. I wish this to be a community based creation; so I put some categories and some tools. I left many out, deliberatey. Please assist by filling in the missing projects, tools, libraries! Additions gladly accepted via pull-requests. Do note the contribution guidelines (somewhat lengthy, I apologize).

I will moderate FUDs, promotional, commercials etc., and otherwise it may take some days for me to merge requests.

The work is licensed under the Creative Commons Attribution-ShareAlike 4.0 International License.

Percona Live 2015: Reflections

MySQL

Some personal reflections on PerconaLive 2015:

Percona acquires Tokutek

Well done! Tokutek develops the TokuDB storage engine for MySQL and TokuMX engine for MongoDB. I will discuss the MySQL aspect only.

TokuDB was released as open source in 2013. It has attained a lot of traction and I have used it myself for some time. I met issues with locking or otherwise operational difficulties which I reported, and otherwise was fascinated by such features as great compression, online schema changes, and more.

Recently another company, InfiniDB, that also released its MySQL-backed codebase as open source, went out of business. I was afraid the same might happen to Tokutek.

I see Percona’s purchase as a very good move for the community. I saw a lot of TokuDB interest in Percona for some time now, and it is clearly interested in the technology. I expect they will add their own hands-on experience into the development of more operations-friendly features; put effort in solving locking issues (it’s been a while since I last checked, of course some of these may have been addressed by now). I am guessing they will work on a Galera/TokuDB integration and offer a “Toku-XtraDB-Cluster”.

TokuDB can compete with InnoDB in many places, while in others each will have its distinct advantage.

I see this is as good news for the community.

Community Awards and Lightning Talks

On a completely different subject, I believe it is commonly accepted that this year’s setup for the community awards & lightning talks was unsuccessful. The noise was astounding, human traffic was interrupting and overall this was a poor experience. We (Giuseppe Maxia, Kortney Runyan & myself) made a quick, informal brainstorming on this and came up with a couple ideas. One of which we hope to try in the upcoming Percona Live Europe – Amsterdam.

We apologize to the speakers for the difficulties.

Percona Live Europe – Amsterdam

Haha! Having recently relocated to the Netherlands I’m of course very happy. But regardless, Percona Live London was fun – and yet running on low fuel. I think it was a great idea to change location (and more locations expected in the future). This is the path taken by such conferences as OSCon, Velocity, Strata and more. Amsterdam in particular, as I’ve recently learned, is especially appreciated by many. I think this conf will do great!

Woz

And now for something completely different. Woz’ talk was that. I’m happy he came; I appreciate that he discussed education; and it was fun.

Percona Live 2015: Reflections; the Apache CCLA offer

MySQL

Facebook, Google, Twitter, LinkedIn, Alibaba, MariaDB, Percona team up and offer Oracle all public changes under the Apache CCLA

Read again please.

My one word summary of this is: Romantic. In the most positive sense.

Disclaimer: I am not a lawyer; this is my understanding of the current status and of the offer.

Summarizing the deal: the teams participating with WebScaleSQL would like to push code upstream. Current legal issues limit their options. Existing patches/contributions from Percona & MariaDB are licensed by GPLv2, which Oracle cannot import as it distributes a commercial, closed source, edition, in addition to its open source MySQL community edition.

So what happens is that there is a lot of free code, great patches, new features out there, that are only available via MariaDB or WebscaleSQL or Percona Server, but not in the Oracle MySQL code base. This, in turn, means Oracle re-implements many features originating from said companies. And, more importantly, said companies need to routinely rebase their code on new Oracle releases, repeating tedious work.

The offer is that Oracle agrees to the Apache CCLA as a license by which it would be able to incorporate contributions. Oracle would then be able to use incorporated code in both open source and commercial edition. Oracle will choose what code to incorporate; hopefully many patches will be accepted upstream, and the community will benefit from a rich featureset, rapid developed MySQL server.

Clearly a lot of work, persuasion, lawyer time, discussions etc. have been invested in this effort. I would like to add my humble +1/like/favorite/whathaveyou. You may add yours by letting Oracle know your opinion on the subject. Media tools are great for this.

 

 

MySQL Community Awards 2015: the Winners

MySQL

The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporates for their contributions to the MySQL ecosystem. It is a from-the-community, by-the-community and for-the-community effort. The committee is composed of an independent group of community members of different orientation and opinion, themselves past winners or known contributors to the community.

The 2015 community awards were presented on April 15rd, 2015, during the community event at the Percona Live conference. The winners are:

MySQL Community Awards: Community Contributor of the year 2015

  • Daniël van Eeden
    Daniël has done great work on MySQL security and has continued to fantastically support MySQL User Group.NL. He has also logged a lot of bugs (and submitted patches), across all sorts of different MySQL products and has done a great deal to help improve the quality of MySQL.Daniël consistently provides extremely good feedback, on a wide range of features and products, from MySQL server security, through InnoDB, partitioning, and even on other products such MySQL Enterprise Backup and MySQL Enterprise Monitor. His bugs are always reported with a high quality, and many times he even includes a contribution to fix those bugs.
  • Justin Swanhart
    Justin has worked tirelessly for the past few years on some amazing projects of his own design, Shard-Query and Flexviews. Cross shard aggregation is an extremely complex thing to get right, and Shard-Query takes an interesting approach at this. Flexviews provides a materialized view framework, which is something that MySQL lacks to many people’s annoyance. Additionally Justin has also built some performance_schema related tools, reported many MySQL bugs, and has been a public speaker about MySQL in “can do” style.
  • Morgan Tocker
    In his day job, Morgan is Community Manager at Oracle. While some of his community interaction has been because of his job, he has gone far and beyond his corporate responsibilities. He is one of the most prolific writers on the MySQL Planet, he has been the most public face of MySQL, and he is always asking for feedback and showing a sincere concern for the Open Source community.For example, Morgan’s community polls on what defaults should be changed in MySQL 5.7 put some of the MySQL product decision making directly into the hands of the community. He is a key player on keeping the community and the MySQL developers at Oracle in touch with each other.

MySQL Community Awards: Application of the year 2015

Continue reading » “MySQL Community Awards 2015: the Winners”

Speaking at Percona Live: Pseudo GTID and Easy Replication Topology Management

MySQL

In two weeks time I will be presenting Pseudo GTID and Easy Replication Topology Management at Percona Live. From the time I submitted the proposal a LOT has been developed, experimented, deployed and used with both Pseudo GTID and with orchestrator. In my talk I will:

  • Suggest that you skip the “to GTID or not to GTID” question and go for the lightweight Pseudo GTID
  • Show how Pseudo GTID is used in production to recover from various replication failures and server crashes
  • Do an outrageous demonstration
  • Tell you about 50,000 successful experiments and tests done in production
  • Show off orchestrator and its support for Pseudo GTID, including automated crash analysis and recovery mechanism.

I will further show how the orchestrator tooling makes for a less restrictive, more performant, less locking, non-intrusive, trusted and lightweight replication topology management solution. Continue reading » “Speaking at Percona Live: Pseudo GTID and Easy Replication Topology Management”

MySQL Community Awards 2015: Call for Nominations!

MySQL

The 2015 MySQL Community Awards event will take place, as usual, in Santa Clara, during the Percona Live MySQL Conference & Expo, April 2015.

The MySQL Community Awards is a community based initiative. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based of past winners or their representatives, as well as known contributors.

It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self criticizing committee.

The Call for Nominations is open. We are seeking the community’s assistance in nominating candidates in the following categories: Continue reading » “MySQL Community Awards 2015: Call for Nominations!”