7 ms·

MySQL InnoDB Cluster GA

92 points by tpetry 10y ago

tpetry 10y ago
A fully automated HA solution is yet missing for PostgreSQL.
- gshulegaard 10y ago
  https://www.postgresql.org/docs/9.5/static/high-availability.html https://www.postgresql.org/docs/9.5/static/high-availability... https://www.citusdata.com/solutions/infrastructure/high-availability-postgresql https://www.citusdata.com/solutions/infrastructure/high-avai... Edit: And before someone mentions "Citus" being "third-party" or "closed" or some such: https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...
  - Zikes 10y ago
    There's also CockroachDB, which is wire-compatible with Postgres. Of course, there are also some performance trade-offs to consider, and it is currently missing many SQL and data type features available in Postgres and others.
    - the_duke 10y ago
      It's a completely different database, it just happens to support the postgres binary protocol.
      - Zikes 10y ago
        Yes, it is. However when I went looking for a "HA Postgres" solution I found it to be a viable alternative for my use case.
        dianasaur323 10y ago
        CockroachDB employee here - Yes, CockroachDB is a completely different database from PostgreSQL. CockroachDB supports HA from the bottom up with data broken up into ranges that are consistently replicated using Raft. As mentioned earlier in the thread, it uses the PostgreSQL wire protocol and supports a good amount of SQL (but not all since the dialect is quite large if you include extensions). If you are looking for HA with SQL compatibility, it would be worth taking a look at CockroachDB.
      - anarazel 10y ago
        s/binary/wire/
  - threeseed 10y ago
    Citus by definition is third party. And whilst it has been recently open sourced it is always a risk to depend on a project that is controlled by one vendor. Especially when you are looking for enterprise support.
    - JBReefer 10y ago
      I disagree - I think the one vendor open source thing works well, consider Node, Scala, VS Code, etc.
      - threeseed 10y ago
        Completely different situations. Node and Scala for example are open source projects first then a company comes in and hires engineers and provides support. That model tends to work fine because you still have a rich, diverse community. It's different when it's a company that open sources a project. It can sometimes be difficult then for that project to have that same diversity of views.
  - tpetry 10y ago
    CitusDB (not Citus Cloud) is not fully automated: You can have only one master node, if this node fails you have to manually elect a new master node. So it really does not gain anything compared to stock postgresql for HA.
- philips 10y ago
  People are working on things on-top of Kubernetes, such as https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator
  - cookiecaper 10y ago
    I hate this. Kubernetes is designed specifically for stateless applications; everything about running stateful applications on it now is experimental. Running k8s/Docker in prod is hard enough for actually stateless apps, running a database in it is an utterly horrifying idea. Please limit the extent of your lemming-ness to at least not attempting to containerize PostgreSQL or other old-school database systems. They could not be less suited to this type of application.
    - elvinyung 10y ago
      > Kubernetes is designed specifically for stateless applications Is that actually true? The Borg paper[1] mentions that Google uses it to run stateful things like Bigtable and GFS nodes, and as far as I can tell, Kubernetes has the exact same goals as Borg.[2] I would love to see a source for your claim. [1] https://pdos.csail.mit.edu/6.824/papers/borg.pdf https://pdos.csail.mit.edu/6.824/papers/borg.pdf [2] http://queue.acm.org/detail.cfm?id=2898444 http://queue.acm.org/detail.cfm?id=2898444
      - threeseed 10y ago
        "Omega, an offspring of Borg stored the state of the cluster in a centralized Paxos-based transaction-oriented store that was accessed by the different parts of the cluster control plane (such as schedulers), using optimistic concurrency control to handle the occasional conflicts" Seems like Google never stored state within the containers.
        elvinyung 10y ago
        I think that's a description of how the cluster metadata is managed, and not a description of the actual cluster tasks.
- elvinyung 10y ago
  Quorum commits are coming.[1] This provides a similar feature to the "Group Replication" functionality for MySQL outlined in the post. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3901fd70cc7ccacef1b0549a6835bb7d8dcaae43 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
  - morgo 10y ago
    I'm not sure it is quite the same, since it is WAL based (i.e. can you write to any node?) There is semi-sync in MySQL as well, which ensures N replicas have received a write.
    - p4lindromica 10y ago
      That's not what semi-sync does. Semi-sync commits the write to the master and then blocks on returning a result to the client until N replicas have received a write OR a timeout is reached. There is no ability to roll back the write on the master once it is committed.
      - morgo 10y ago
        This was the case for 5.5 and 5.6. MySQL 5.7+ will default to wait for the ack before committing locally: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_rpl_semi_sync_master_wait_point https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
        p4lindromica 10y ago
        Does AFTER_SYNC mean MySQL 5.7+ can have unacknowledged writes appear on the slaves after a master crash and failover?
        morgo 10y ago
        I believe the answer is "yes" but this problem is not unique to semi-sync. Take a single server example: You could ask the database to "COMMIT"; it receives the command and processes it, but does not manage to send you notification that it has managed. There is a further failure state: The master could be behind when it crashed. So you need to look at it's gtid executed set or re-image it before plugging it back in. (Not a distributed systems guy; I work on the non HA+replication parts of the server)
morgo 10y ago
Hi! I'm a Product Manager for the MySQL Server. I don't specifically work on InnoDB Cluster, but am happy to answer questions. Let me start of by describing how the clustering works: - A cluster is 3+ nodes in a group, each with a complete set of data - When you say 'COMMIT' the server you are connected to certifies your write-set with its peers (i.e. has anyone else tried to modify the same data). Once the majority agree, the transaction is considered successful. - The distributing and certification of the transaction is synchronous, but the applying of the transaction is asynchronous. Thus you actually retain pretty good performance if the network latency is reasonable. The performance question comes up a bit, so here's another post for more context: http://mysqlhighavailability.com/an-overview-of-the-group-replication-performance/ http://mysqlhighavailability.com/an-overview-of-the-group-re...
- forgot-my-pw 10y ago
  How does this differ from the Galera Cluster / mysql-wsrep implementation?
  - morgo 10y ago
    Very similar concept, but differences are well described here: http://lefred.be/content/group-replication-vs-galera/ http://lefred.be/content/group-replication-vs-galera/ The Tl;dr I give most people is that the Group Replication technology (innodb cluster) uses Paxos for group communication (with majority) and Galera uses Totem Single-ring Ordering (all nodes).
    - INTPenis 10y ago
      Interesting, I've been using galera clustering for a couple of years and the first months of setting up a new cluster, a new environment, have always been plagued by bugs and issues. Eventually it stabilizes but I still have binary logs disabled because of an open issue over a year old. I'm afraid that MySQL will have the same issues now that it's new.
- spullara 10y ago
  Is there an AWS Cloudformation configuration that will deploy this to AWS in an automated fashion? Have you run Aphyr's Jepsen tests?
forgot-my-pw 10y ago
How does this differ from Galera Cluster / mysql-wsrep? https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-cluster/ https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-clu...
- morgo 10y ago
  (See my other reply)
jthnews 10y ago
Is there any support for sharding?
- morgo 10y ago
  Nope, it's planned though. In the mean time, I recommend using something like ProxySQL to route based on schema to shard between multiple instances of MySQL.
  - v4tk 10y ago
    InnoDB Cluster includes MySQL Router, but you recommend using ProxySQL?
    - morgo 10y ago
      For this use case, yes. ProxySQL also natively supports Group Replication though so you are free to chose either.
stephenr 10y ago
A relevant read for those who are considering this vs a Galera based solution: https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/ https://www.percona.com/blog/2017/02/24/battle-for-synchrono...

92 points by tpetry 10y ago

tpetry 10y ago

A fully automated HA solution is yet missing for PostgreSQL.

gshulegaard 10y ago
https://www.postgresql.org/docs/9.5/static/high-availability.html https://www.postgresql.org/docs/9.5/static/high-availability... https://www.citusdata.com/solutions/infrastructure/high-availability-postgresql https://www.citusdata.com/solutions/infrastructure/high-avai... Edit: And before someone mentions "Citus" being "third-party" or "closed" or some such: https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...
- Zikes 10y ago
  There's also CockroachDB, which is wire-compatible with Postgres. Of course, there are also some performance trade-offs to consider, and it is currently missing many SQL and data type features available in Postgres and others.
  - the_duke 10y ago
    It's a completely different database, it just happens to support the postgres binary protocol.
    - Zikes 10y ago
      Yes, it is. However when I went looking for a "HA Postgres" solution I found it to be a viable alternative for my use case.
      - dianasaur323 10y ago
        CockroachDB employee here - Yes, CockroachDB is a completely different database from PostgreSQL. CockroachDB supports HA from the bottom up with data broken up into ranges that are consistently replicated using Raft. As mentioned earlier in the thread, it uses the PostgreSQL wire protocol and supports a good amount of SQL (but not all since the dialect is quite large if you include extensions). If you are looking for HA with SQL compatibility, it would be worth taking a look at CockroachDB.
    - anarazel 10y ago
      s/binary/wire/
- threeseed 10y ago
  Citus by definition is third party. And whilst it has been recently open sourced it is always a risk to depend on a project that is controlled by one vendor. Especially when you are looking for enterprise support.
  - JBReefer 10y ago
    I disagree - I think the one vendor open source thing works well, consider Node, Scala, VS Code, etc.
    - threeseed 10y ago
      Completely different situations. Node and Scala for example are open source projects first then a company comes in and hires engineers and provides support. That model tends to work fine because you still have a rich, diverse community. It's different when it's a company that open sources a project. It can sometimes be difficult then for that project to have that same diversity of views.
- tpetry 10y ago
  CitusDB (not Citus Cloud) is not fully automated: You can have only one master node, if this node fails you have to manually elect a new master node. So it really does not gain anything compared to stock postgresql for HA.
philips 10y ago
People are working on things on-top of Kubernetes, such as https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator
- cookiecaper 10y ago
  I hate this. Kubernetes is designed specifically for stateless applications; everything about running stateful applications on it now is experimental. Running k8s/Docker in prod is hard enough for actually stateless apps, running a database in it is an utterly horrifying idea. Please limit the extent of your lemming-ness to at least not attempting to containerize PostgreSQL or other old-school database systems. They could not be less suited to this type of application.
  - elvinyung 10y ago
    > Kubernetes is designed specifically for stateless applications Is that actually true? The Borg paper[1] mentions that Google uses it to run stateful things like Bigtable and GFS nodes, and as far as I can tell, Kubernetes has the exact same goals as Borg.[2] I would love to see a source for your claim. [1] https://pdos.csail.mit.edu/6.824/papers/borg.pdf https://pdos.csail.mit.edu/6.824/papers/borg.pdf [2] http://queue.acm.org/detail.cfm?id=2898444 http://queue.acm.org/detail.cfm?id=2898444
    - threeseed 10y ago
      "Omega, an offspring of Borg stored the state of the cluster in a centralized Paxos-based transaction-oriented store that was accessed by the different parts of the cluster control plane (such as schedulers), using optimistic concurrency control to handle the occasional conflicts" Seems like Google never stored state within the containers.
      - elvinyung 10y ago
        I think that's a description of how the cluster metadata is managed, and not a description of the actual cluster tasks.
elvinyung 10y ago
Quorum commits are coming.[1] This provides a similar feature to the "Group Replication" functionality for MySQL outlined in the post. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3901fd70cc7ccacef1b0549a6835bb7d8dcaae43 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- morgo 10y ago
  I'm not sure it is quite the same, since it is WAL based (i.e. can you write to any node?) There is semi-sync in MySQL as well, which ensures N replicas have received a write.
  - p4lindromica 10y ago
    That's not what semi-sync does. Semi-sync commits the write to the master and then blocks on returning a result to the client until N replicas have received a write OR a timeout is reached. There is no ability to roll back the write on the master once it is committed.
    - morgo 10y ago
      This was the case for 5.5 and 5.6. MySQL 5.7+ will default to wait for the ack before committing locally: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_rpl_semi_sync_master_wait_point https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
      - p4lindromica 10y ago
        Does AFTER_SYNC mean MySQL 5.7+ can have unacknowledged writes appear on the slaves after a master crash and failover?
        morgo 10y ago
        I believe the answer is "yes" but this problem is not unique to semi-sync. Take a single server example: You could ask the database to "COMMIT"; it receives the command and processes it, but does not manage to send you notification that it has managed. There is a further failure state: The master could be behind when it crashed. So you need to look at it's gtid executed set or re-image it before plugging it back in. (Not a distributed systems guy; I work on the non HA+replication parts of the server)

morgo 10y ago

Hi! I'm a Product Manager for the MySQL Server. I don't specifically work on InnoDB Cluster, but am happy to answer questions. Let me start of by describing how the clustering works: - A cluster is 3+ nodes in a group, each with a complete set of data - When you say 'COMMIT' the server you are connected to certifies your write-set with its peers (i.e. has anyone else tried to modify the same data). Once the majority agree, the transaction is considered successful. - The distributing and certification of the transaction is synchronous, but the applying of the transaction is asynchronous. Thus you actually retain pretty good performance if the network latency is reasonable. The performance question comes up a bit, so here's another post for more context: http://mysqlhighavailability.com/an-overview-of-the-group-replication-performance/ http://mysqlhighavailability.com/an-overview-of-the-group-re...

forgot-my-pw 10y ago
How does this differ from the Galera Cluster / mysql-wsrep implementation?
- morgo 10y ago
  Very similar concept, but differences are well described here: http://lefred.be/content/group-replication-vs-galera/ http://lefred.be/content/group-replication-vs-galera/ The Tl;dr I give most people is that the Group Replication technology (innodb cluster) uses Paxos for group communication (with majority) and Galera uses Totem Single-ring Ordering (all nodes).
  - INTPenis 10y ago
    Interesting, I've been using galera clustering for a couple of years and the first months of setting up a new cluster, a new environment, have always been plagued by bugs and issues. Eventually it stabilizes but I still have binary logs disabled because of an open issue over a year old. I'm afraid that MySQL will have the same issues now that it's new.
spullara 10y ago
Is there an AWS Cloudformation configuration that will deploy this to AWS in an automated fashion? Have you run Aphyr's Jepsen tests?

forgot-my-pw 10y ago

How does this differ from Galera Cluster / mysql-wsrep? https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-cluster/ https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-clu...

morgo 10y ago
(See my other reply)

jthnews 10y ago

Is there any support for sharding?

morgo 10y ago
Nope, it's planned though. In the mean time, I recommend using something like ProxySQL to route based on schema to shard between multiple instances of MySQL.
- v4tk 10y ago
  InnoDB Cluster includes MySQL Router, but you recommend using ProxySQL?
  - morgo 10y ago
    For this use case, yes. ProxySQL also natively supports Group Replication though so you are free to chose either.

stephenr 10y ago

A relevant read for those who are considering this vs a Galera based solution: https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/ https://www.percona.com/blog/2017/02/24/battle-for-synchrono...