What is the best solution for synchronous sync and load balancing an InnoDB database

Question

I'm trying to find the best setup to horizontally scale an ecommerce which is currently on a single dedicated server, but even reading some of the replies here on DBA I'm still confused on what is the best solution for my use case. The sync should be synchronous (I think but I may be wrong here) and it will be integrated with an HAproxy for load balancing (HAProxy will manage also WWW loadbalancing that's why I mention it, but If I have to use MySQL Router o SQLProxy that's ok too). I also need the same autoincrement progression between all the MySQL instances (more below, in the M\M part). Lastly it should be easy to recover if any node goes down.

These are the options I found by googling:

1. MySQL Replication M/M
2. MySQL Replication/Group Replication
3. Percona XtraDB Cluster / Galera
4. MySQL NDB

MySQL Replication M/M

I used this in the past, but I had the problem of using the auto_increment_increment/offset setting, resulting in non-continuos order/cart/etc IDs. AFAIK it cannot be configured for synchronous replication. Also is not the most professional approach to the problem, with many cons.

MySQL Replication/Group Replication

I don't exactly understand the difference between the two, except that the latter seems to have a nicer way to manage/control the cluster. But this seems to do the job I need. If I understood correctly, using it in semisynchronous mode with rpl_semi_sync_source_wait_for_replica_count equal to the amount of MySQL instances is like running in synchronous mode. Is that correct? Also I'm a bit confused about Single o Multi Primary Mode. I think every instance must be RW, so MultiPrimary, if I want to balance also the write queries. Or is better to have a Single Primary RW node and redirect only the reading queries on the secondary nodes (if that's possible with HA, i don't know)?

Percona XtraDB Cluster / Galera

This looks like a Group Replication with additional features and management tools to make it easier, possibly performing even better. If I understood correctly synchronous mode is enforced here, for more consistency. I'm not really sure what else differs from the MySQL one, that's part of my confusion.

MySQL NDB

I read this is the best solution for a synchronous replication. However I don't get if it can be safely used with PHP application wrote with InnoDB in mind. I know I can just re-import the whole InnoDB database with the NDB engine, but it is compatible with any MySQL query I can do on InnoDB? This is the biggest doubt I have. I don't want to move eveything on NDB to find out later that I can't do something or even worse with errors in my app queries.

So, here I am, full of doubt to choose the best option for my use case, since this is a choice I cannot undo. I hope I didn't forget anything useful to know. Can any DBA expert help me in doing the right choice? Thanks.

Answer 1

NDB is the only solution you mention that supports horizontal scalability. That is, part of the data resides on each node, so the more nodes you have, the greater the write capacity.

The others are all flavors of replication, so every node has its own copy of the full dataset. You could get some amount of read scalability by delegating read queries to the replicas, but only if the read queries are tolerant of replication lag.

There is no fully synchronous replication solution. There is always delay between each step: you update a row, then the transaction commits and writes to the binary log, then the binary log is downloaded by the replica, then the replica replays the change against its own copy. If a database could force every step to be synchronous, then the application client would have to wait for everything to complete before the update of a row returns. This would be a deal-breaker for most apps.

There's semi-synchronous which you know about, but this does not ensure that writes have been completed on replicas, only that the log has been received by a replica. There's no guarantee of how long it will take the log to be applied on the replica, and lag could even grow to hours under those conditions. I also would not recommend semi-sync over long networks (i.e. inter-regional), because networks can become degraded or interrupted, and that would cause transaction commits on the master node to be blocked.

Galera solutions have an option to force read queries to wait for replication lag to catch up. This is not the same a synchronous writes, and it can result in unexpected delays on read queries.

InnoDB Cluster monitors replication lag, and if the lag is over 1 second, further writes to the cluster are impeded until the replicas catch up.

Another solution that you didn't list is PlanetScale's Vitess and related products. That solution does have horizontal scaling, and manages a lot of the pain points you mention.

All horizontally scaled, or "sharded" database (NDB, Vitess) have a necessary downside: because the data is split, there's a big difference between a query that can be resolved by reaching one node versus "fan-out" queries that need to reach many nodes. It's necessary to design your data splitting strategy carefully, so that the queries you need to be most efficient are bound to get their result from one node at a time. This can be quite complex in a relational database, and in some cases there is no clean solution (e.g. many-to-many tables).

This means that it's almost never successful to take your incumbent database that was designed with the assumption that all data is co-located, and simply upload it to a sharded database infrastructure and expect it to do well.

The only guideline is TANSTAAFL. Every solution has its advantages and significant disadvantages. It's up to you to pick a solution whose advantages are important for your project, and whose disadvantages are not a deal-breaker given your needs.

You haven't described your workload, like the rate of transactions, ratio of reads to writes, database size, requirements for query latency. Nor have you described how much scalability you need: rate of growth of data and growth of traffic, geographical distribution, number of clients, etc. These are all important factors in choosing a solution.

I recommend you either need to try out each of the solutions with a test database to learn how well they match your needs, or you need to hire a database performance consultant to do that work.

Answer 2

Either InnoDB Cluster (with MySQL 8.0) or Galera (preferrably with a recent MariaDB version)

M/M (Primary-Primary) is a partial solution to HA and scaling; not worth considering, given the above choice.

NDB is for niche apps. There are orders of magnitude more people who can help with non-NDB solutions.

For HA, plan on having at least one server in each of 3 different geographical locations.

Then you need to pick among various routing mechanisms.

Also, use the slowlog to find out what queries can be sped up. This helps whether or not you do the physical scaling.

Simple replication can scale "infinitely", but for scaling reads, not writes.

Sharding

Some, not all, applications readily scale by having some users on each server. Think WordPress, Flickr, etc. One shard (a single server, or cluster for HA) handles some subset of the users, and mostly independent of all the other shards.

With this solution, you leave your existing users on the existing server(s). Spin up a similar configuration for the next hundred customers. When that gets "full" spin up another. Arbitrary read and write scaling.