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

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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.

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Leave a Reply

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

Last Tweets Last Tweets