Compose for MySQL - A developer's view

Published

In this interview with Chris Winslett, Compose developer and lead on the Compose for MySQL, we talk about why MySQL is on the Compose platform, what makes it different on Compose and how the Compose for MySQL beta is going.

Q: So why Compose for MySQL?

Chris Winslett: We already had nine databases and we already had a SQL database. The question is though is "Why MySQL?" and the answer is that it's simple yet powerful. You can get the relational database model without the high administration cost you see with other SQL databases. You get all the SQL capabilities, SELECT statements with GROUPing and JOINing and so on. It also can query across databases and concatenate the results together with UNION ALL. With other databases, like Postgres, you have to make choices which tend to increase complexity; MySQL has this feature out of the box.

There's also a large ecosystem of libraries - every programming language can connect to MySQL. PHP, Python, C, all of then have extensive libraries and a lot of these have been around so long that there are two or more versions. PHP had an old model and a new model. Ruby has a MySQL library, and also a later MySQL2 library. They've all gone through these iterations reacting to the needs of the database systems leading to some very mature experiences when running on top of MySQL. That means that it's easy for a new user to spin up a database and get working with it. A large ecosystem of tools is another reason. Some common tools include Wordpress, Drupal, SugarCRM and other open-source CRMs, along with GUIs for creating queries and reports.

The size of the MySQL environment is compelling. It's been the largest database since the late 90s when web databases and the open-source movement began growing. Which leads to the last reason - Customers were asking for it and wanted it. They had other databases on Compose and enjoyed the autoscaling and the automatic backups and they wanted a MySQL which was easy to deploy and highly available like all our databases are.

Q: So what do users get when they deploy Compose for MySQL?

CW: We start with availability on AWS, Softlayer and Google Cloud; you can deploy on all those platforms. Then there's the Compose process for what we expect from databases. High availability, automated disaster recovery backups, failover support and simple routing all delivered from a private VLAN and manageable from the web.

Q: How do you create a MySQL database on Compose?

Same as any other Compose database: sign up - we have free thirty day trials, and get to the Compose web front end then click on the Create Deployments button. You'll see all the databases we do at Compose there. Browse down to the Beta section, and you'll find Compose for MySQL in there. Click it, enter a name for your database, pick where you want it, pick a size - remembering we have auto-scaling - and click Create. Your Compose for MySQL database will be with you shortly; it takes about two or three minutes.

Q: What does the Beta mean?

CW: A Beta database is a database that Compose has just begun offering. We've been offering MySQL since late October and during this beta period we monitor the database. With MySQL, what we are doing is watching the metrics, monitoring the uptime, seeing how we can improve the uptime, how we can improve self-healing tasks and seeing what kind of questions customers have about MySQL. We fully expect this to be a production-grade database and we have high expectations during this beta. However, we want customers to know it's a new database on Compose so it may not best fit some use cases. That's where we gather data in the beta.

Q: So what MySQL are you running?

CW: We're running MySQL 5.7.17 currently with Group Replication. We don't modify MySQL in anyway, so you can use all your standard MySQL drivers and tools with it. The one caveat is that because we use Group Replication to run the MySQL cluster, all the tables in the database require primary keys. A primary key is a unique identifier for a row; it can be an integer, UUID or string. It just has to be unique for the clustering.

Q: Where would you not have unique ids?

CW: One example would be a join table, where you are creating a table which joins users records and group records together. The table created to represent that join would typically not be designed to have a unique id. So what you need to do is alter the table, add an id column and make that id column an auto-incrementing integer.

Q: Why do you need to do this?

CW: The unique id caveat lets us run multiple nodes with replication and high availability. Having a unique id means it's easier for replication to see what's new and what has changed and keep things consistent. That means we can replicate data over three nodes.

Q: Why three nodes?

CW: Three replicated nodes allow us to take a node offline without bringing the database down. That means that we can do zero-downtime maintenance. If you've run databases before, you'll know the number one reason for a database outage is not because a host has gone down, but because you need to do maintenance on that host; update the kernel, update how the system is tuned or reset some parameters. Maintenance is the number one reason for database downtime.

We also get zero-downtime backups. MySQL backups are best if you can shut down the database on a node, so what we do is shut down a data node, do the backups and bring that node back up. That gives us the best, most consistent backups.

Finally, we get failover during a server outage. While the number one reason for an outage is maintenance, the number one reason for an unplanned outage is server failure. Three nodes give us a lot of advantages during these unplanned outages. That's why we were ok with the requirement to have primary keys on tables. The tradeoff for high availability is something we think – and we expect customers will think – is worth it.

Q: So, how do you pick which node to connect to?

CW: We look to make it as simple as possible. Customers applications connect to a haproxy and that haproxy talks to the master data node. We try and take a lot of the magic out of the process of connecting. The haproxy knows which node is currently the master data node.

Q: How do you know what's in your cluster?

CW: Look at the Topology in the Compose console overview. What you can see there is the result of health checks being run on the cluster. You can see the clusters own private infrastructure with the three data nodes on them and you can see the proxy which is routing to the master among the data nodes. You don't need to know that, though, all you need is the to know is the address of the proxy.

Q: Do you have any advice for someone bringing an application to Compose for MySQL and the cloud?

CW: Remember to create your cloud database as close as possible, network-wise, to your application as possible.

Q: You mention how Compose runs beta databases; Any insights from the MySQL Beta so far?

CW: We'll be blogging about the Compose for MySQL beta and doing some deep dives into how group replication works and how we recover from failure. Look out for them appearing soon.


If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

Image by Maxime Daquet
Dj Walker-Morgan
Dj Walker-Morgan is Compose's resident Content Curator, and has been both a developer and writer since Apples came in II flavors and Commodores had Pets. Love this article? Head over to Dj Walker-Morgan’s author page and keep reading.