MySQL offers a wide array of options to configure replication, but with all of those options, how can you be sure you are doing it right?

Replication is the first step to providing a higher level of availability to your MySQL database. A well configured replication architecture can be the difference between your data being highly available, or your MySQL setup becoming a management nightmare. At PlanetScale, we support hundreds of thousands of database clusters, all using replication to provide high availability, so we have a little bit of experience in this arena!

In this article, we’re going to explore some of the best practices when it comes to replication, both locally and across longer distances.

Use an active/passive configuration

When replicating with …

Have you heard about MySQL replication but aren’t sure exactly why you should care?

Having multiple servers for any workload is typically considered best practice. After all, a workload split across multiple servers helps balance out the performance of any application. When it comes to working with your database, though, the benefits may not be as clear.

In this article, you’ll learn about five real-world use cases for implementing MySQL replication.

What is MySQL replication?

Before we get into its use cases, let us briefly describe what MySQL replication is.

MySQL replication is a process that is used to keep multiple MySQL servers in sync. When you first set up a MySQL environment, it is typically with a single server to run your databases. One approach to …

When the performance of your database server starts to decline from general usage, you have several options for optimizing it.

One common method for optimizing your MySQL database is through partitioning. In this article, we’ll cover the basics of MySQL partitioning, how to apply partitioning to your database, and we'll discuss how it’s related to sharding.

The basics of MySQL partitioning

Partitioning is the idea of splitting something large into smaller chunks. In MySQL, the term “partitioning” means splitting up individual tables of a database.

When you partition a table in MySQL, the table is split up into several logical units known as partitions, which are stored separately on …

Deep dive into MySQL’s connection handling mechanisms for optimal connection pooling and improved concurrency.

Scaling a database presents challenges. As you grow, you might begin having trouble handling ever-increasing throughput or data size. You might find that query latency is getting worse. You might be pushing the limits of your hardware. When this happens, a classic option is vertically scaling your database by getting better hardware, but is there a better way? And what happens when you reach the vertical limits?

This is where horizontal sharding comes in. In this article, we'll cover some common indicators that your database may be ready for horizontal sharding. We'll also look at some measures you can implement until then. Let's dig in.

Signs your database is hitting its scaling …

For developers building out an application, a transactional datastore is the obvious and proven choice, but with success comes scale limitations. A monolithic database works well initially, but as an application sees growth, the size of its data will eventually grow beyond what is optimal for a single server.

Implementing read replicas can improve your performance but will likely add lag between your primary and your replicas, leading to performance or correctness issues for your application. These complexities can sometimes require major architectural changes, leading to a suboptimal user experience and difficult compromises, having to choose between application performance or data consistency. Scaling write traffic is even more challenging; for example, even the largest MySQL database will see performance issues at a certain point.

…

Every day PlanetScale processes more than 10 billion of our customers’ queries. We need to collect, store, and serve telemetry data generated by these queries to power Insights, our built-in query performance tool. This post describes how we built a scalable telemetry pipeline using Apache Kafka and a sharded PlanetScale database.

Insights requirements

To show you Insights, we pull from the following datasets:

• Database-level time series data (e.g., queries per second across the entire database).
• Query pattern-level time series data (e.g., p95 for a single query pattern like SELECT email FROM users where id = %)
• Data on specific query executions for slow/expensive …

There are several different ways to store dates and times in MySQL, and knowing which one to use requires understanding what you'll be storing and how MySQL handles each type.

There are five column types that you can use to store temporal data in MySQL. They are:

• DATE
• DATETIME
• TIMESTAMP
• YEAR
• TIME

Each column type stores slightly different data, has different minimum and maximum values, and requires different amounts of storage.

In the table below, you'll see each column type and their various attributes.

| Column    | Data        | Bytes | Min                 | Max                 |
|-----------|-------------|-------|---------------------|---------------------|
| DATE      | Date only   | 3     | 1000-01-01          | 9999-12-31          |
| DATETIME …

One of the hidden gems in the MySQL documentation is this note in section 8.3.6:

As an alternative to a composite index, you can introduce a column that is “hashed” based on information from other columns. If this column is short, reasonably unique, and indexed, it might be faster than a “wide” index on many columns.

We will build on this idea by creating generated hash columns for indexed lookups on large values and enforcing uniqueness across many columns. Instead of creating huge composite indexes, we'll index the compact generated hashes for fast lookups.

Before diving into generated hash columns, let's look at generated columns in general.

Generated columns in MySQL

A …

When working with MySQL (or any database!), it's essential to understand how indexes work and how they can be used to improve the efficiency of queries. An index is a separate data structure that maintains a copy of part of your data, structured to allow quick data retrieval. Usually, this structure is a B+ Tree. We have an entire post on how indexes work if you want to go into greater detail.

Obfuscated indexes

Creating indexes is only part of the battle. You must also know how to write your queries so that you allow MySQL to use your indexes. One common mistake people make when writing queries is that they obfuscate their indexes. Obfuscating an index simply …