Knowledge Base/Engine Yard Cloud Documentation/Manage your Database

Set Up Database Replication

Engine Yard
posted this on February 16, 2012 10:02 AM

Updated: March 12th, 2013

This document describes how to:

Important! If the master database fails, contact Engine Yard Support to have your replica instance promoted to master instance.

The best environment type for your database

Engine Yard offers four environment configurations: Single Instance, Staging Configuration, Production Configuration, and Custom Configuration.

For database replication, you need a Production Configuration or a Custom Configuration. 

  • Single Instance. For a small testing environment, a single server that contains both the application and the database is ideal.
  • Staging Configuration. The staging configuration does not include a database replica (slave).
    In non-production environments or environments with small databases, this configuration is a good choice. However, because the database is not replicated, make sure that you backup frequently.
    You can add a database replica (slave) to an existing staging configuration (see below).
  • Production Configuration and Custom Configuration. For typical applications in production environments where you want a database replica (slave), use a production or custom configuration environment containing the following:
    • One application master
    • One or two application slaves
    • One master database
    • A database replica (slave)

Note PostgreSQL only: If you expect your database server requirements to grow so that you will need a larger database instance, consider starting with a custom environment using 64-bit instances. PostgreSQL instances cannot be upgraded easily from 32-bit to 64-bit instances.

Set up a custom environment with database replication

This procedure describes how to set up a new environment that includes a database replica (slave) so that your database is replicated. This is best practice for a production environment.

(If you already have an environment and want to add a database replica (slave) to it, see Add a database replica (slave) to an existing environment below.)

To set up a custom environment with database replication

  1. Create the environment as described in Create an environment.

  2. Select Custom.

  3. Set the size of the master database instance (Server size) and the replica, and enter the size for the /db volume.
    By default, the /db volume for the replica (slave) will be the same size as the master.


    db_server_n_slave_v2.png


    For information about choosing replica (slave) instance sizes, see About Database replica (slave) instance sizes.

  4. Click Boot This Configuration.

Add a database replica (slave) to an existing environment

If you have a basic cluster and want to add database replication, follow this procedure.

To add a database replica (slave) to an existing environment

  1. On the Environment page, click Add.

  2. Click Add Database Slave Instance.

  3. Set the instance size and the volume size.
    Note: The volume size must be the same or larger than the master database.
    Note: You cannot enlarge a PostgreSQL database to a 64-bit instance using a 32-bit snapshot.
    For information about choosing instance sizes, see About Database replica (slave) instance sizes.

  4. MySQL only: If you have an existing snapshot of the database, you can use it instead of creating a new snapshot. To do this, clear the check box to create a new snapshot for the slave and select to use a recent snapshot.
    Note: The check box appears only if a recent snapshot is available. See About using a new or existing snapshot for the database replica (slave) for information.

  5. Click Add to Cluster.

  6. After the database slave boots, click Apply.
    This generates new database.yml files for the database replica (slave) on the application master.

  7. If your environment hasn’t been upgraded since December 2nd, 2011 (i.e. if you see “Enable creation of db slaves with a larger volume than the db master” in the change log), then the /db volume size is not automatically increased and you need to do one of the following:
    • Upgrade your environment, or
    • Submit a ticket with Engine Yard Support and ask to have /db volume size reset (to the size entered in Step 3 above).

About database replica (slave) instance sizes

You can create database replicas (slaves) with smaller or larger instance sizes.

Database replica (slaves) are generated from either existing or new master database snapshots; this snapshot can impact your choice of instance size. For example, PostgreSQL 32-bit snapshots cannot be used to upsize to 64-bit instances.

Smaller database replicas (slaves)

You can save money by using a less expensive instance for your database replica (slave) than for your master database. In general, the minimum recommendation for the database replica (slave) is one size smaller than for the master database. For example, if the master database is running on a standard extra large instance, you might use a standard large instance for your database replica (slave).

Remember that in the case of master database failure, the database replica has to act as the master at least until a new instance as big as the original master can be created. 

Because replication is single-threaded, CPU is not a key factor in sizing. The key factor is the memory on the instance that can be allocated to buffers (the buffer pool innodb for MySQL and shared_buffers for PostgreSQL). For example, if the master database is on a High CPU XL instance, the database replica (slave) could be a High Memory XL instance, which costs less.

If you have concerns about the correct instance size when provisioning a database replica (slave), submit a ticket with Engine Yard Support.

Larger database replicas (slaves)

The sky and your budget are the limits when it comes to provisioning larger database replicas (slaves).
If you are running your database master on a High CPU Medium and need better database performance, we recommend upgrading to the High CPU XL. (Engine Yard does not recommend upgrading from High CPU Medium to a Standard Large because the Standard Large is often less performant than a High CPU Medium.)

MySQL only: About using a new or existing snapshot for the database replica (slave)

If you have an existing snapshot you can use it instead of taking a new snapshot immediately before creating a new database replica (slave).

Choosing between creating a new snapshot or using an older snapshot is a trade-off:

  • Creating a new snapshot means that the new database replica (slave) has less data from the master binary logs that it has to replay to make the replication. However, creating a snapshot can be very IO intensive on the master and, thus, might be a bad choice for high traffic databases during peak traffic hours. Also, for large databases, the shorter time required for replication to catch up after the database replica (slave) is up might be off-set by the time needed to create the new snapshot.

  • Using an existing snapshot skips creating a new snapshot and instead uses an existing one. This option is only available if the master database has binary log data going back to when the snapshot was created. The new database replica (slave) then has to read and replay more binary log data than if using a new snapshot. The amount of binary log data is entirely dependent on your database write traffic volume, but this method is usually much less IO intensive to the master database than creating a new snapshot.

More information

For more information about...See...
Creating an environment                                          Create an environment.
Increasing the size of a database instance or /db volume                    Monitor database usage and increase the database volume or instance size.               
Make a high availability environment Make a high availability environment.
Upgrade an environment Upgrade an environment.
SSHing into an instance Connect to your instance via SSH.
32-bit and 64-bit instance sizes About instance sizes.

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.

 

Comments

User photo
Thibaut Barrère
wisecash

It seems that this page is out of date, if I'm right. When creating an environment today, I see the following choices:

  • single instance
  • staging configuration
  • production configuration
  • custom configuration
instead of what is said on the page here:
  • single server
  • basic cluster
  • custom
February 28, 2013 09:19 AM
User photo
Tyler Bird
Engine Yard Inc.

Thibaut,

Thank you for the observation, we'll get this updated.

Tyler

February 28, 2013 09:55 AM