EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL & OpenStack: How to overcome issues as your dataset grows

 | September 29, 2014 |  Posted In: MySQL, OpenStack, Percona XtraBackup

PREVIOUS POST
NEXT POST

MySQL is the database of choice for most OpenStack components (Ceilometer is a notable exception). If you start with a small deployment, it will probably run like a charm. But as soon as the dataset grows, you will suddenly face several challenges. We will write a series of blog posts explaining the issues you may hit and how to overcome them.

Where is MySQL used in OpenStack?

Have a look at the logical diagram of OpenStack below (click the image for a larger view).

 

MySQL & OpenStack: A deployment primer

The diagram is a bit outdated: Neutron appears as Quantum and newer components like Heat are not pictured. But it shows that a database has to be used to store metadata or runtime information. And although many databases are supported, MySQL is the most common choice. Of course MySQL can also be used in instances running inside an OpenStack cloud.

What are the most common issues?

OpenStack_PerconaAs with many applications, when you start small, the database is running well and maintenance operations are fast and easy to perform. But with a dataset that grows, you will find that the following operations are becoming increasingly difficult:

  1. Having good backups: mysqldump is the standard backup tool for small deployments. While backups of instances having 100GB of data is still quite fast, restore is single-threaded and will take hours. You will probably need to use other tools such as Percona XtraBackup, but what are the tradeoffs?
  2. Changing the schema: whenever you have to add an index, change a datatype or add a column, it can trigger a table rebuild which will prevent writes to proceed on the table. While the rebuild is fast when the table has only a few hundreds of MBs of data, ALTER TABLE statements can easily take hours or days for very large tables. Using pt-online-schema-change from Percona Toolkit is a good workaround, but it doesn’t mean that you can blindly run it without any precaution.
  3. Making the database highly available: whenever the database is down, the whole platform is down or runs in a degraded state. So you need to plan for a high availability solution. One option is to use Galera, but that can introduce subtle issues.
  4. Monitoring the health of your database instances: MySQL exposes hundreds of metrics, how do you know which ones to looked at to quickly identify potential issues?

1. and 2. are not likely to be issues for the MySQL instance backing your OpenStack cloud as it will be very small, but they can be big hurdles for guest databases that can grow very large depending on the application.

3. and 4. are highly desirable no matter the size of the database.

Stay tuned for more related posts on MySQL & OpenStack – and feel free to give us your feedback! And remember that if MySQL is showing bad performance in your OpenStack deployment, Percona is here to help. Just give us a call anytime, 24/7. I also invite you and your team to attend the inaugural OpenStack Live 2015 conference, which runs April 13-14, 2015 in Santa Clara, Calif. It runs alongside the Percona Live MySQL Conference and Expo (April 13-16) at the Hyatt Regency Santa Clara and the Santa Clara Convention Center.

PREVIOUS POST
NEXT POST
Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom. In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

2 Comments

  • Re: Changing the schema – This shouldn’t be an issue as of MySQL 5.6. I understand you might say most of the deployments in the wild still using earlier versions such 5.5 or 5.1. Further, I think making database high available and managing large datasets are different issues, what do you think? However, we may also mention few other high availability solutions here such as MySQL Fabric and MySQL with PACEMAKER…

  • aftab,

    5.6 simplifies schema changes a lot, that’s true. But not everyone is using 5.6 even for new deployments unfortunately (run yum install mysql-server on CentOS 6.5 and you’ll end up with MySQL 5.1.73).

    High availability can be desirable even if you have a very small database, agreed. By the way this is the case for the metadata database in OpenStack: it’s small but you don’t want downtime because then your whole cloud will be down. However for large databases, a HA solution becomes even more necessary because in general you can’t afford to repair the master or to rebuild it from a backup if it fails.

    Galera is only one of the options to make MySQL available, however it’s gaining traction in the OpenStack world. That’s why I mentioned it rather than other options.

Leave a Reply