Month: October 2015

Leader election using MySQL

MySQL

Being a stateful, centralized datastore, MySQL can serve in negotiating leadership: a mechanism to elect a single service out of multiple services; moreover, a mechanism to promote a new leader should the existing leader cease to function.

What of Zookeeper?

Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for Zookeeper. It has HA via multiple nodes & quorum,  ephemeral nodes, all you need. To achieve similar benefits with MySQL you’d need to use Galera or NDB Cluster; so why not use Zk? The use case at hand is

orchestrator, a multi-node, mostly stateless service that happens to use MySQL as backend datastore. Ir relies on MySQL to exist in backend. It already expects it to be there. If the MySQL server is down, so is the service, effectively. In such case it doesn’t hurt adding another dependency on MySQL; this does not reduce HA. You need to take care of MySQL HA anyhow so there’s no additional cost. In fact, going to Zookeeper makes the additional cost as you introduce a new component to the system that can be avoided.

Terms of the solution

Our proposed solution offers:

  • Single leader election out of multiple nodes
  • Leader actively reaffirms its leadership periodically
  • Timeout based re-election: decision to re-elect new leader based on the fact current leader has not reaffirmed its leadership over X seconds
  • A way to forcibly assume leadership for a specific node
  • A way to forcibly call for re-elections by demoting existing leader
  • A node/service can easily tell whether it’s the leader or not
  • Anyone can tell who the leader is

SQL solution

The solution is composed of a single table and a set of queries which implement the above offers. We assume a service can uniquely identify itself; this is easy to achieve:

  • If services are running from different hosts (as should be the case, this is service HA), use hostname for ID
    • But what if the service restarts? Are you good with calling this “the same service” or is this now a new service running on the same host?
  • In such case use combination of hostname & OS process ID
    • Or generate a random token upon startup
    • Or use startup timestamp Whichever solution you pick, make sure it is human readable, such that it is easy to tell

which service is the leader. This helps operations. We note this as service_id

Table

The following table will have a single row; the

service_id in that row is the active leader.

CREATE TABLE service_election ( 
  anchor tinyint(3) unsigned NOT NULL, 
  service_id varchar(128) NOT NULL, 
  last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (anchor) 
) ENGINE=InnoDB

Attempt election

Continue reading » “Leader election using MySQL”

New statements I’d like to see in MySQL 5.8

MySQL

Following up on New features I’d like to see in MySQL 5.8, here are statements I would like to see in MySQL 5.8:

  • ENABLE EVENTS;
    When promoting a slave to master, I want to be able to enable all those events that are in SLAVESIDE_DISABLED state. Today I script an iteration over the events an constructing the ALTER EVENT…ENABLE statement one by one. Just activate those!
  • SKIP GTID TRANSACTION;
    I have a transaction on slave that I want to skip, and there’s GTID. The sequence of

    STOP SLAVE;
SET GTID_NEXT="...";
BEGIN;
COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

    is just something I don’t want to do. To compute the GTID_NEXT; to open a transaction; to use session variables; this may seem straightforward to import the above from a shell script, but calculating the next GTID is not entirely trivial; issuing the above from your programming language makes for a weird “all these have to be in the same session AND you’re going to do a transaction meanwhile”. With golang it’s actually a problem.
    Make it simple for me. I’m willing to do the STOP/START SLAVE.

  • BINLOG ENTRY ‘<arbitrary text>’;
    Very selfishly, I want to be able to inject a comment into the binary log, of arbitrary text. I want this comment to appear in SBR format, as if it were a DDL.
    My selfish reason: injection of Pseudo-GTID. But I can see various other use cases, such as application level injection of app-logic checkpointing; chef/glu injection of “code deployed at this time”; application injection of “daily audit done to this point”. This is too cool and too easy to skip.
  • SHOW RELAY LOGS;
    Similar to SHOW BINARY LOGS;
  • PURGE RELAY LOGS TO ‘…’;
    Similar to PURGE BINARY LOGS TO ‘…’;
    It’s time relay logs stopped being 2nd class citizens.
  • SHOW NONBLOCKING [GLOBAL|SESSION] STATUS;
    Issue a SHOW GLOBAL|SESSION STATUS query that only shows those variables for which it does not need to block. i.e. this is a safe, fast “show me everything you’ve got that I won’t need to pay for”.

Yes, yes, statements are also features, I know.

 

New features I’d like to see in MySQL 5.8

MySQL

Following up on Morgan Tocker’s What would you like to see in MySQL 5.8?, having attended and participated at the brainstorming at Percona Live Amsterdam, and publishing this post while failing to comply with any of Morgan’s suggested media, these are the features I would like to see in MySQL 5.8:

  • Dynamicly enable/disable log-bin and log-slave-updates
    Today, when changing chef/puppet role of a server from a simple slave to an intermediate master and vice versa, a MySQL restart is required. This is a very big pain which makes replication automation complex, not to mention warmup times.
  • “nice“.
    I want to be able to execute a query that is nice, i.e has lower priority; will not consume all resources; will stall/throttle so as to allow other queries to complete. Luis asked and I said this could be on a per statement basis, e.g. add a SQL_NICE query hint. But I’m unsure that would be the correct behavior. It also makes sense to do so on a per connection basis (perhaps provide connection attributed to hint niceness?).
  • Online-ier ALTER TABLE. I would in particular want it to apply the nice feature, above. Otherwise throttle by user defined metrics.
  • Online-ier ALTER TABLE in replication stream.  Can the slaves run the ALTER statement in parallel?
  • Re-Group Commit: in MTS, and when intermediate masters involved, copy+paste the group commit as applied on master as working downstream. I suspect this is easily achievable. The result: same parallelism for replicating slaves in all levels, whether they replicate directly from master or from 2nd, 3rd tier intermediate masters. Today parallelism decreases as we go downstream.
  • Global user-defined-variables. I want to be able to define arbitrary (global) variables that I can later query via SELECT @@global.arbitrary. This would be similar to HTML 5‘s “data-*” attributes. I often wish I could tell & ask MySQL my puppet role; or the server status (is it live? Is it offline? Does it belong to a specific pool? etc.). Similar to “loose-*” syntax, this could be a “data-*” or “user-*” name prefix system.

I will follow up on new statements I would like to see in MySQL 5.8.

The brainstorming session at PerconaLive, I should note, was pure joy, and apart from getting two nice furry dolphins I enjoyed the engagement, the diversity of ideas, and the fact Oracle engineers (Mark in particular) were very busy taking notes or otherwise openly discussing the viability of some requested features.