Percona is a variant of the MySQL project architected for optimal performance and is a drop-in replacement for MySQL databases. Creating and using Percona 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 Percona database
- How to connect to your database
- Things to know when using Percona on Pagoda Box
Creating a Percona Database
Creating a Percona database can be done either in your dashboard or from your Boxfile.
Through the Dashboard
To create a Percona database through your dashboard, click on "New Data Service," then click the Percona icon.
You're given the option to name your database and select which Percona 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 Percona service. More on this below and in the Service Topologies doc.
Click "Create Service" to begin provisioning your Percona database.
Through the Boxfile
To create a Percona database through the Boxfile, include a database service with the
type specified as
percona. You can also specify a
topology, but it isn't required.
Percona Database in the Boxfile YAML
database1: type: percona topology: single
On your next deploy, the database will be created.
Configuring Your Percona Database
All environment configuration is handled in the Boxfile. The MySQL, MariaDB, & Percona Settings in the Boxfile doc walks through all of the configurable Percona options.
Connecting to Your Percona Database
Connecting services in your application to a Percona 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.
You can hard-code your database connection credentials into your codebase, but there are some drawbacks to this approach:
Connection credentials are viewable in your repo - On Pagoda Box, this doesn't compromise the security of your database.
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 Percona database on Pagoda Box, we automatically generate environment variables for each connection credential:
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 Percona Database
You have a few options when it comes to managing the data inside your Percona database. The following docs walk through each:
Making Percona Redundant
Data stored in your database is incredibly important and ideally will always be available. Adding redundancy to your Percona service is the best way to ensure your data is always available. To make your Percona 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 Percona 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 Percona
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.
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 Percona databases.
Auto-Increment is Set to 2 in Redundant Percona Databases
For redundant Percona 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 Percona instances.
Table of Contents
If you have any questions, suggestions, or corrections, let us know.