Using local write forwarding in an Amazon Aurora MySQL DB cluster

Local (in-cluster) write forwarding allows your applications to issue read/write transactions directly on an Aurora Replica. These transactions are then forwarded to the writer DB instance to be committed. You can use local write forwarding when your applications require read-after-write consistency, which is the ability to read the latest write in a transaction.

Read replicas receive updates asynchronously from the writer. Without write forwarding, you have to transact any reads that require read-after-write consistency on the writer DB instance. Or you have to develop complex custom application logic to take advantage of multiple read replicas for scalability. Your applications must fully split all read and write traffic, maintaining two sets of database connections to send the traffic to the correct endpoint. This development overhead complicates application design when the queries are part of a single logical session, or transaction, within the application. Moreover, because replication lag can differ among read replicas, it's difficult to achieve global read consistency across all instances in the database.

Write forwarding avoids the need to split those transactions or send them exclusively to the writer, which simplifies application development. This new capability makes it easy to achieve read scale for workloads that need to read the latest write in a transaction and aren't sensitive to write latency.

Local write forwarding is different from global write forwarding, which forwards writes from a secondary DB cluster to the primary DB cluster in an Aurora global database. You can use local write forwarding in a DB cluster that is part of an Aurora global database. For more information, see Using write forwarding in an Amazon Aurora global database.

Local write forwarding requires Aurora MySQL version 3.04 or higher.

Enabling local write forwarding

By default, local write forwarding isn't enabled for Aurora MySQL DB clusters. You enable local write forwarding at the cluster level, not at the instance level.

aws rds create-db-cluster \
  --db-cluster-identifier write-forwarding-test-cluster \
  --enable-local-write-forwarding \
  --engine aurora-mysql \
  --engine-version 8.0.mysql_aurora.3.04.0 \
  --master-username myuser \
  --master-user-password mypassword \
  --backup-retention 1

Enabling write forwarding for database sessions

The aurora_replica_read_consistency parameter is a DB parameter and DB cluster parameter that enables write forwarding. You can specify EVENTUAL, SESSION, or GLOBAL for the read consistency level. To learn more about consistency levels, see Read consistency for write forwarding.

The following rules apply to this parameter:

Checking if a DB cluster has write forwarding enabled

To determine that you can use write forwarding in a DB cluster, confirm that the cluster has the attribute LocalWriteForwardingStatus set to enabled.

In the Amazon Web Services Management Console, on the Configuration tab of the details page for the cluster, you see the status Enabled for Local read replica write forwarding.

To see the status of the write forwarding setting for all of your clusters, run the following Amazon CLI command.

aws rds describe-db-clusters \
--query '*[].{DBClusterIdentifier:DBClusterIdentifier,LocalWriteForwardingStatus:LocalWriteForwardingStatus}'

[
    {
        "LocalWriteForwardingStatus": "enabled",
        "DBClusterIdentifier": "write-forwarding-test-cluster-1"
    },
    {
        "LocalWriteForwardingStatus": "disabled",
        "DBClusterIdentifier": "write-forwarding-test-cluster-2"
    },
    {
        "LocalWriteForwardingStatus": "requested",
        "DBClusterIdentifier": "test-global-cluster-2"
    },
    {
        "LocalWriteForwardingStatus": "null",
        "DBClusterIdentifier": "aurora-mysql-v2-cluster"
    }
]

A DB cluster can have the following values for LocalWriteForwardingStatus:

Application and SQL compatibility with write forwarding

You can use the following kinds of SQL statements with write forwarding:

Certain statements aren't allowed or can produce stale results when you use them in a DB cluster with write forwarding. Thus, the EnableLocalWriteForwarding setting is disabled by default for DB clusters. Before enabling it, check to make sure that your application code isn't affected by any of these restrictions.

The following restrictions apply to the SQL statements you use with write forwarding. In some cases, you can use the statements on DB clusters with write forwarding enabled. This approach works if write forwarding isn't enabled within the session by the aurora_replica_read_consistency configuration parameter. If you try to use a statement when it's not allowed because of write forwarding, then you will see an error message similar to the following:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'operation with write forwarding'.

Isolation levels for write forwarding

In sessions that use write forwarding, you can only use the REPEATABLE READ isolation level. Although you can also use the READ COMMITTED isolation level with Aurora Replicas, that isolation level doesn't work with write forwarding. For information about the REPEATABLE READ and READ COMMITTED isolation levels, see Aurora MySQL isolation levels.

Read consistency for write forwarding

You can control the degree of read consistency on a DB cluster. The read consistency level determines how long the DB cluster waits before each read operation to ensure that some or all changes are replicated from the writer. You can adjust the read consistency level to make sure that all forwarded write operations from your session are visible in the DB cluster before any subsequent queries. You can also use this setting to make sure that queries on the DB cluster always see the most current updates from the writer. This setting also applies to queries submitted by other sessions or other clusters. To specify this type of behavior for your application, choose a value for the aurora_replica_read_consistency DB parameter or DB cluster parameter.

As you increase the consistency level, your application spends more time waiting for changes to be propagated between DB instances. You can choose the balance between fast response time and making sure that changes made in other DB instances are fully available before your queries run.

You can specify the following values for the aurora_replica_read_consistency parameter:

For information about the configuration parameters involved in write forwarding, see Configuration parameters for write forwarding.

mysql> set aurora_replica_read_consistency = 'session';

Examples of using write forwarding

The following examples show the effects of the aurora_replica_read_consistency parameter on running INSERT statements followed by SELECT statements. The results can differ, depending on the value of aurora_replica_read_consistency and the timing of the statements.

To achieve higher consistency, you might wait briefly before issuing the SELECT statement. Or Aurora can automatically wait until the results finish replicating before proceeding with SELECT.

For information on setting DB parameters, see Working with parameter groups.

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> insert into t1 values (6); select count(*) from t1;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

mysql> insert into t1 values (6); select count(*) from t1; select count(*) from t1;
Query OK, 1 row affected (0.08 sec)
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.37 sec)
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> insert into t1 values (6); select sleep(2); select count(*) from t1;
Query OK, 1 row affected (0.07 sec)
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.01 sec)
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.75 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.37 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.66 sec)

Running multipart statements with write forwarding

A DML statement might consist of multiple parts, such as an INSERT ... SELECT statement or a DELETE ... WHERE statement. In this case, the entire statement is forwarded to the writer DB instance and run there.

Transactions with write forwarding

If the transaction access mode is set to read only, write forwarding isn't used. You can specify the access mode for the transaction by using the SET TRANSACTION statement or the START TRANSACTION statement. You can also specify the transaction access mode by changing the value of the transaction_read_only session variable. You can change this session value only while you're connected to a DB cluster that has write forwarding enabled.

If a long-running transaction doesn't issue any statement for a substantial period of time, it might exceed the idle timeout period. This period has a default of one minute. You can set the aurora_fwd_writer_idle_timeout parameter to increase it up to one day. A transaction that exceeds the idle timeout is canceled by the writer instance. The next subsequent statement you submit receives a timeout error. Then Aurora rolls back the transaction.

This type of error can occur in other cases when write forwarding becomes unavailable. For example, Aurora cancels any transactions that use write forwarding if you restart the DB cluster or if you disable write forwarding.

When a writer instance in a cluster using local write forwarding is restarted, any active, forwarded transactions and queries on reader instances using local write forwarding are automatically closed. After the writer instance is available again, you can retry these transactions.

Configuration parameters for write forwarding

The Aurora DB parameter groups include settings for the write forwarding feature. Details about these parameters are summarized in the following table, with usage notes after the table.

To control incoming write requests, use these settings:

For more information about aurora_replica_read_consistency, see Read consistency for write forwarding.

For more information on DB parameter groups, see Working with parameter groups.

Amazon CloudWatch metrics and Aurora MySQL status variables for write forwarding

The following Amazon CloudWatch metrics and Aurora MySQL status variables apply when you use write forwarding on one or more DB clusters. These metrics and status variables are all measured on the writer DB instance.

The following CloudWatch metrics and Aurora MySQL status variables are measured on each reader DB instance in a DB cluster with write forwarding enabled.

Identifying forwarded transactions and queries

You can use the information_schema.aurora_forwarding_processlist table to identify forwarded transactions and queries. For more information on this table, see information_schema.aurora_forwarding_processlist.

The following example shows all forwarded connections on a writer DB instance.

mysql> select * from information_schema.AURORA_FORWARDING_PROCESSLIST where IS_FORWARDED=1 order by REPLICA_SESSION_ID;

+-----+----------+--------------------+----------+---------+------+--------------+--------------------------------------------+--------------+--------------------+---------------------------------+----------------------+----------------+
| ID  | USER     | HOST               | DB       | COMMAND | TIME | STATE        | INFO                                       | IS_FORWARDED | REPLICA_SESSION_ID | REPLICA_INSTANCE_IDENTIFIER     | REPLICA_CLUSTER_NAME | REPLICA_REGION |
+-----+----------+--------------------+----------+---------+------+--------------+--------------------------------------------+--------------+--------------------+---------------------------------+---------------------------------------+
| 648 | myuser   | IP_address:port1   | sysbench | Query   |    0 | async commit | UPDATE sbtest58 SET k=k+1 WHERE id=4802579 |            1 |                637 | my-db-cluster-instance-2        | my-db-cluster        | us-west-2      |
| 650 | myuser   | IP_address:port2   | sysbench | Query   |    0 | async commit | UPDATE sbtest54 SET k=k+1 WHERE id=2503953 |            1 |                639 | my-db-cluster-instance-2        | my-db-cluster        | us-west-2      |
+-----+----------+--------------------+----------+---------+------+--------------+--------------------------------------------+--------------+--------------------+---------------------------------+----------------------+----------------+

On the forwarding reader DB instance, you can see the threads associated with these writer DB connections by running SHOW PROCESSLIST. The REPLICA_SESSION_ID values on the writer, 637 and 639, are the same as the Id values on the reader.

mysql> select @@aurora_server_id;

+---------------------------------+
| @@aurora_server_id              |
+---------------------------------+
| my-db-cluster-instance-2        |
+---------------------------------+
1 row in set (0.00 sec)

mysql> show processlist;

+-----+----------+--------------------+----------+---------+------+--------------+---------------------------------------------+
| Id  | User     | Host               | db       | Command | Time | State        | Info                                        |
+-----+----------+--------------------+----------+---------+------+--------------+---------------------------------------------+
| 637 | myuser   | IP_address:port1   | sysbench | Query   |    0 | async commit | UPDATE sbtest12 SET k=k+1 WHERE id=4802579  |
| 639 | myuser   | IP_address:port2   | sysbench | Query   |    0 | async commit | UPDATE sbtest61 SET k=k+1 WHERE id=2503953  |
+-----+----------+--------------------+----------+---------+------+--------------+---------------------------------------------+
12 rows in set (0.00 sec)