MySQL Group Replication, the perfect HA database backend for web hosting

Many web hosting provider are looking for HA solution for the database backend they deliver to their customers.

Galera never became the perfect choice for these environment due to 2 factors:

  1. no DBA really manage the databases
  2. Galera runs database changes in Total Order Isolation

What does that really mean ? In fact, when you are a website hosting provider, you host the website (apache, nginx) on vhosts and you share a database server in which every customer has access to their own schema for their website.

Most of the time, those websites are CMS like Drupal, WordPress or Joomla (and certainly many others sharing the same expectations).

Using these tools allows you to create and manage websites quickly and easily. However on a shared environment, you can’t expect that all users will use the same version of the CMS at the same time, neither the same plugins. Some may have customized the core or plugins of their favorite solution.

This means that the application itself takes care of database design and operations. So if one of the users decides to upgrade his WordPress (or add/remove a plugin that will create/modify some table scheme), on a Galera Cluster, he will lock all writes on ALL databases served by the cluster. All writes will be stalled for the total execution time of the DDLs that are part of wp-admin/includes/upgrade.php.

This upgrade of that particular website will then affect all other sites that are on the same system.

MySQL Group Replication doesn’t suffer from the same behavior and makes it the ideal solution to achieve High Availability for you database on a shared system.

The only problem you could encounter with MySQL Group Replication is if you use a cluster in Multi-Primary mode and preform concurrent DDLs as I explained on this videocast.

To avoid any problem, when using MySQL Group Replication in Multi-Primary node, it’s recommended to route all DDLs to the same node (this is not needed  if you use the default Single-Primary mode). You could filter out such statements using ProxySQL between your web servers and your database server.

 

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.