Recent Posts by Tyler Poland

Take Out The Papers And The Trash

How to clean and clear large tables in MySQL

From time to time our data team gets requests for advice on how to perform cleanup operations against large database tables. Typically, these originate in a ticket requesting information about how the disk is being used or why a specific table is performing poorly.

Somewhat less often, we are asked us to explain why a cleanup attempt has failed or why it has caused downtime for an application. Managing these types of operations with minimal or no downtime can be a challenge given the way a database like MySQL performs these tasks.

The most common form of table cleanup operation we are asked about is for the sessions table. Even though these are not really recommended practice(https://guides.rubyonrails.org/action_controller_overview.html#session) they are still quite common to see. Depending on your application workload and use cases these tables can grow very quickly in size; often including older records that are never going to be used again. Even though the data payload in each row is relatively small, it's not uncommon to find sessions tables that are 10 or 20 GB in size—often larger than the rest of the database combined.

The "Standard" Solution

While there is no automatic session cleanup built into a rails app it happens to be really easy to write a rake task that handles this cleanup for you. In fact, it's so easy that it's often overlooked until the table has reached the size where it is difficult to manage .

Read More

The Importance of Testing Database Backups

Catching some attention in technology headlines this week, GitLab suffered a major loss of database data and had a long and difficult recovery due to a combination of failing and untested backup and recovery strategies. We commend their openness in responding to this incident and several members of our team benefitted from joining the public livestream of their recovery efforts.

Observing this unfold in realtime encouraged us to take a step back and think about our backup and recovery strategies, and whether we are really doing enough to encourage our customers to test and understand this critical part of their application services. This also was a driving force behind the development of a brand new tool, eyrestore, which is designed to make the restoration of logical backups easier between different Engine Yard environments.

Read More

Faster Database Transfers

Sometimes it becomes necessary to move your database from one environment to another. Common reasons for this include:

  • Updating a Testing or Development environment with Production data
  • Migrating from one stack version to another (e.g. Stable-v4 -> Stable-v5)
  • Upgrading to a newer major version of your database (e.g. MySQL 5.5 -> MySQL 5.6 or Postgres 9.2 -> Postgres 9.4)
  • Upgrading storage to Encrypted EBS
  • Upgrading the filesystem to EXT4

The traditional method involves three database related steps:

  1. Creating an on-demand backup in the source environment.
  2. Downloading the most recent backup in the source environment.
  3. Transferring and Restoring the backup from the source to the target environment.

This results in some time and processing inefficiency since using eybackup uploads the backup to s3 and then it needs to be downloaded as a separate operation. For a small database this works just fine; unfortunately, for large databases this extra step could add hours to your migration plan. A better way to approach this would be to use the database-native backup tools to backup the source database to a file, and then transfer that file to the destination for restore.

Read More

HELP! My Hair Is On Fire And My Database Is Using 90% Memory

One of the more common inquiries we encounter is a question about memory and/or swap utilization for database instances. Often this comes with a request to assist in upgrading to a larger database instance. Many times the upgrade isn’t actually necessary and we take some time to share how databases such as MySQL and Postgres leverage memory. In light of how often we answer this question it seemed like a fitting topic to share more about on our blog.

Read More

Important MySQL Configuration Changes

The data team is introducing configuration changes for MySQL that will allow us to bring new features to our platform. These changes will be mostly transparent, but some could potentially impact applications in a noticeable manner (both positive and negative). In preparation, we are detailing these changes and, where appropriate, how you can override them using Custom Chef recipes.

Read More

Subscribe Here!