MariaDB

Last Updated October 2014

MariaDB is a variant of the MySQL project and is a drop-in replacement for MySQL databases. Creating and using MariaDB databases on Pagoda Box is extremely simple, but there are some things to take note of. After reading this doc, you will be familiar with:

  • How to create a MariaDB database
  • How to connect to your database
  • Things to know when using MariaDB on Pagoda Box

Creating a MariaDB Database

Creating a MariaDB database can be done either in your dashboard or from your Boxfile.

Through the Dashboard

To create a MariaDB database through your dashboard, click on "New Data Service," then click the MariaDB icon.

You're given the option to name your database and select which MariaDB version you'd like use. If no name is provided, we will create one for you. You're also give a choice of "topology" which defines the architecture of your MariaDB service. More on this below and in the Service Topologies doc.

Click "Create Service" to begin provisioning your MariaDB database.

Through the Boxfile

To create a MariaDB database through the Boxfile, include a database service with the type specified as mariadb. You can also specify a topology, but it isn't required.

MariaDB in the Boxfile YAML

  database1:
    type: mariadb
    topology: single
/Boxfile

On your next deploy, the database will be created.

Configuring MariaDB

All environment configuration is handled in the Boxfile. The MySQL, MariaDB, & Percona Settings in the Boxfile doc walks through all of the configurable MariaDB options.

Connecting to MariaDB

Connecting services in your application to a MariaDB database isn't any different on Pagoda Box than any other host. Each database has specified connection credentials used to connect your codebase to your database. You can get the credentials in the App Dashboard. Simply click on your database service and go to the "Connection" tab.

There are two approaches to including connections credentials in your codebase – hard-coding the credentials or using the auto-generated environment variables.

Hard-Coding Credentials

You can hard-code your database connection credentials into your codebase, but there are some drawbacks to this approach:

  1. Connection credentials are viewable in your repo - On Pagoda Box, this doesn't compromise the security of your database.

  2. You have to create your database before you can get the credentials - To hard-code your database credentials, you first need to know what they are and you can't do that until you create the database. This isn't a critical problem. It's more of a workflow issue.

Using Automated Environment Variables

Whenever you create a MariaDB database on Pagoda Box, we automatically generate environment variables for each connection credential:

DATABASE1_NAME
DATABASE1_USER
DATABASE1_PASS
DATABASE1_HOST
DATABASE1_PORT

Note: The service ID (DATABASE1) changes depending on which database you're connecting to.

Including environment variables in place of connection credentials in your codebase allows you to avoid storing the actual credentials in your repo and sets connection details before the database is even created. If you launch a database from your Boxfile with the credential environment variables in place, on the next deploy, your database will be created and your app will automatically be able to connect to it.

Check out the Environment Variables doc for more information.

Managing Your MariaDB Database

You have a few options when it comes to managing the data inside your MariaDB database. The following docs walk through each:

Through a Pagoda Tunnel
Through an SSH Connection
Installing phpMyAdmin in Your App (Not Recommended)

Making MariaDB Redundant

Data stored in your database is incredibly important and ideally will always be available. Adding redundancy to your MariaDB service is the best way to ensure your data is always available. To make your MariaDB database redundant, use the "redundant" topology when creating your database.

If your database has already been created, the topology can be changed in your dashboard.

With the Redundant topology, a second MariaDB instance is provisioned and master-master replication between the two instances. If the primary instance every fails or becomes unresponsive, the second will act as a hot failover and take over handling requests until the primary instance can be repaired and/or restarted.

Things to Know When Using MariaDB

Avoid Using MyISAM Tables

MyISAM was once the default storage engine for MySQL databases but has since been replaced with InnoDB, mainly because of MyISAM's loss of integrity at higher concurrency. MyISAM is non-transactional, meaning it cannot queue or manage concurrent queries. If a MyISAM table is ever updated by multiple queries at once, it will lock the table and potentially your entire database. We discourage using MyISAM tables unless you really understand how they work and how to mitigate the risk of table locks while using them.

MariaDB also provides the Aria tables as another alternative to MyISAM.

MyISAM Tables Not Allowed in Redundant Databases

Becuase MyISAM is non-transactional, it cannot be used in a redundant architecture. The constant reads and writes performed in the sync operation would cause constant table locks, resulting in an almost non-functional database. Because of this, MyISAM tables cannot be created or used in redundant MariaDB databases.

MariaDB Loves RAM

Whenever you create a new MariaDB database, its RAM usage will typically be higher than expected. This is totally normal. We've found MariaDB will use most of the available RAM until around 128mb become available.

To be as performant as possible, MariaDB stores as much of your data as in can in RAM. Because of this, a general rule of thumb when using MariaDB is to have at least enough RAM to hold your entire uncompressed dataset.

MariaDB Loves to Hold On to Resources

Many times you'll find that long after a spike in resource usage, MariaDB will continue to "run hot" for an extended period of time. Whenever MariaDB is under load and using a lot of resources, it holds onto those resources in the form of buffer and cache, even after the demand is long gone. So even though your database isn't under heavy load, its resource usage will still appear to be high. If demand does pick up again, MariaDB will drop whatever it's holding in the buffer and cache to make resources available for increased resource usage.

Auto-Increment is Set to 2 in Redundant MariaDB Databases

For redundant MariaDB databases on Pagoda Box, the auto-increment is set to 2 and cannot be changed. This is done to prevent any conflicts between the two running MariaDB instances.

If you have any questions, suggestions, or corrections, let us know.