Three wishes for a new year

September 28, 2016

(Almost) another new year by Jewish calendar. What do I wish for the following year?

  1. World peace
  2. Good health to all
  3. Relaxed GTID constraints

I'm still not using GTID, and still see operational issues with working with GTID. As a latest example, our new schema migration solution, gh-ost, allows us to test migrations in production, on replicas. The GTID catch? gh-ost has to write something to the binary log. Thus, it "corrupts" the replica with a bogus GTID entry that will never be met in another server, thus making said replica unsafe to promote. We can work around this, but...

I understand the idea and need for the Executed GTID Set. It will certainly come in handy with multi-writer InnoDB Cluster. However for most use cases GTID poses a burden. The reason is that our topologies are imperfect, and we as humans are imperfect, and operations are most certainly imperfect. We may wish to operate on a replica: test something, by intention or mistake. We may wish to use a subchain as the seed for a new cluster split. We may wish to be able to write to downstream replicas. We may use a 3rd party tool that issues a flush tables with read lock without disabling sql_log_bin. Things just happen.

For that, I would like to suggest GTID control levels, such as:

  1. Strict: same as Oracle's existing implementation. Executed sets, purged sets, whatnot.
  2. Last executed: a mode where the only thing that counts is the last executed GTID value. If I repoint replica, all it needs to check is "hey this is my last executed GTID entry, give me the coordinates of yours. And, no, I don't care about comparing executed and purged sets, I will trust you and keep running from that point on"
  3. Declarative: GTIDs are generated, are visible in each and every binary log entry, but are completely ignored.

I realize Oracle MySQL GTID is out for some over 3 years now, but I'm sorry - I still have reservations and see use cases where I fear it will not serve me right.

How about my previous years wishes? World peace and good health never came through, however:

  • My 2015 wish for "decent, operations friendly built in online table refactoring" was unmet, however gh-ost is a thing now and exceeds my expectations. No, really. Please come see Tom & myself present gh-ost and how it changed our migration paradigm.
  • My 2012 wish for "decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL" was met by MariaDB's window functions.
    Not strictly Window Functions, but Oracle MySQL 8.0 will support CTE (hierarchial/recursive), worth a mention.

See you in Amsterdam!

  • Simon Mudd

    Hi Shlomi,

    I agree with your sentiments about GTID after fixing some "hostname resolve issues" (which I need to write a blog post about) and basically populated all my GTID boxes with "extra GTID" entries because I had run FLUSH HOSTS.

    Hosts are in the P_S.host_cache and are a local cache on the server, so why write this to the binlog? Worse I hadn't realised that if you do this on the master it pushes the action down to all slaves. Now fixed with FLUSH NO_WRITE_TO_BINLOG HOSTS, but even so some things like this make little sense to me. Required a clean up on all downstream slaves which wasn't fun.

    We need to invite these engineers over to our offices to sit and watch how MySQL is used in real life.

    I don't know how you use MySQL now, though you do know how I manage my boxes and we surely do things differently, both of us having our reasons for doing this or that. A few years of experience installing, fixing, poking and throwing away MySQL servers also gives us a different attitude to the engineers who only build stuff in a "clean room". Somehow we need to share that knowledge back to them so when they build and design stuff it does not require too many iterations to work the way we want.

    That said I have 5 8.0.0 production slaves (different replication chains) running now from 5.7 masters churning away at the moment at the updates that come through in replication. So far they seem to be chugging away quite nicely. That's certainly an improvement from the .0 releases we've seen before. That's a good start for 8.0...

  • Yes would be nice to have a list of transactions which are not critical like flush privileges etc. and thus should not break replication.

    Note that because of it is possible to get sudden gaps in the set of gtid's after a restart if using mysqldump.

    Another issue is with filtering: With replicate-do-db etc. the replica still 'thinks' it has the full gtid set.

    However I do like GTID..

Powered by Wordpress and MySQL. Theme by