Introduction to Aurora PostgreSQL cluster cache management

Amazon Aurora is a relational database service that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. The PostgreSQL-compatible edition of Aurora delivers up to three times the throughput of standard PostgreSQL running on the same hardware. This enables existing PostgreSQL applications and tools to run without requiring modification. The combination of PostgreSQL compatibility with Aurora enterprise database capabilities provides an ideal target for commercial database migrations.

Caching in relational databases

Caching is one of the main features all relational databases implement to reduce disk I/O by caching the most frequently used data in the memory area called the buffer cache. Accessing data from the buffer cache is faster than accessing the data from the disk, thereby increasing scalability and application performance.

PostgreSQL caches frequently access data blocks (table and index blocks) and are configured using the configuration parameter (shared_buffers) which sets the amount of memory the database server uses for shared memory buffers. For more information, see Memory in the PostgreSQL documentation website.

Caching and failovers

Aurora PostgreSQL provides a fast failover (approximately 35 seconds) by automatically promoting the read replica with the highest failover priority to become the new master.

Read replicas do not run exactly the same workload as the primary. Therefore, the content of the buffer cache of the read replica may not the representative of the application read-write workload or it could be entirely different with the content of the failed primary.

Depending on the content of the buffer cache at the time of failover, it will take time for the newly promoted writer instance to have to warm up its cache (as in getting to a similar cache state as the failed primary). The time it takes to warm up the buffer cache is the duration until the application will get similar response times to what it experienced before the failover.

Cluster cache management

The cluster cache management (CCM) feature improves the performance of the new primary/writer instance after failover occurs. The replica preemptively reads frequently accessed buffers cached from the  primary/writer instance. With CCM, you can designate a specific Aurora PostgreSQL replica as the failover target. CCM ensures that data in the designated replica’s cache is synchronized with the data in the primary DB instance’s cache.

The following diagram shows the CCM feature. The read-only (RO) node sends the set of buffer addresses that are currently cached in the buffer cache of the RO node to the read/write (RW) node as a bloom filter. This is to ensure that the RO node doesn’t send the same buffer addresses to the RW node repeatedly. When the RW node receives the bloom filter from the RO node, it compares the blocks in the buffer cache and sends frequently used buffers (defaults to usage count greater than three) to the RO node.

If a failover occurs, the designated reader uses values in its warm cache immediately when it is promoted to the new writer DB instance. This approach provides your application better recovery performance. When a failover occurs, the new primary (replica’s) cache is already warm and the application gets the same consistent and predictable post-failover performance.

For more information, see Fast Recovery After Failover with Cluster Cache Management for Aurora PostgreSQL.

Configuring and using CCM

CCM is supported for Aurora PostgreSQL DB cluster versions 9.6.11 and above, and versions 10.5 and above.

The following steps show how to configure and enable the use of CCM on your Aurora PostgreSQL cluster for automatic capture and using managed plans with QPM.

Modifying the Aurora DB cluster parameters

To modify the DB cluster parameters, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Parameter groups.
3. Choose the parameter group for your Aurora PostgreSQL DB cluster.
   The DB cluster must use a parameter group other than the default, because you can’t change values in a default parameter group. For more information, see Creating a DB Cluster Parameter Group. The below screenshot shows the DB cluster parameter group (apg10outbound) used in this blog post.
   
4. For Parameter group actions, choose Edit.
5. Set the value of the apg_ccm_enabled cluster parameter to 1.
   The following screenshot shows the apg10outbound DB cluster Parameter group edit screen.
   
6. Choose Save changes.

For more information, see Modifying Parameters in a DB Cluster Parameter Group.

Setting the promotion tier priority for the writer DB instance

To set the promotion tier priority, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Choose ccminstance.
   This is the writer DB instance of the Aurora PostgreSQL DB cluster. The following screenshot shows the list of available databases.
   
4. Choose Modify.
   The Modify DB Instance page opens. See the following screenshot for a preview.
5. In the Failover section, for Priority, choose tier-0. See the following screenshot for a preview.
   
6. Choose Continue.
7. Check the summary of modifications.
8. Choose Apply immediately.
9. Choose Modify DB Instance.

For more information, see Modify a DB Instance in a DB Cluster and Fault Tolerance for an Aurora DB Cluster.

Setting the promotion tier priority for the reader DB instance

Repeat the steps from the previous section for the reader instance (ccminstance-ro) to act as a failover target. To designate a specific replica for CCM, set the promotion tier priority to 0 for that Aurora replica. The promotion tier priority is a value that specifies the order in which an Aurora replica is promoted to the primary DB instance after a failure. Valid values are 0–15, where 0 is the highest and 15 the lowest priority.

Verifying the CCM is enabled

To verify if the CCM is enabled, query the function aurora_ccm_status() with the following code using psql.
Please refer the documentation on how to connect to PostgreSQL using psql. Please replace the endpoint, port, username and the database name with your specific setup:

./psql -h ccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U <username> -d ccmdb

ccmdb=> \x
Expanded display is on.
ccmdb=> select * from aurora_ccm_status();
- [ RECORD 1 ]--------------+---------
buffers_sent_last_minute   		| 2242000
buffers_found_last_minute  	    | 2242003
buffers_sent_last_scan     		| 17920442
buffers_found_last_scan    		| 17923410
buffers_sent_current_scan  		| 14098000
buffers_found_current_scan	    | 14100964
current_scan_progress      		| 15877443

./psql -h noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U <username> -d noccmdb

noccmdb=> \x
Expanded display is on.
noccmdb=> select * from aurora_ccm_status();
ERROR:  Cluster Cache Manager is disabled
noccmdb=>

Testing for fast recovery after a failover with CCM

You can test the fast recovery after a failover with CCM with pgbench, a PostgreSQL benchmarking tool. Use it to generate a synthetic workload and run benchmarking on two separate Aurora PostgreSQL clusters (one with CCM enabled and other with CCM disabled).

This post manually injects the failover in the middle of the benchmark (after 10 minutes) and resumes the benchmark after the failover. You can measure the time it takes to reach pgbench the average baseline transaction per second (TPS) after the failover on both the clusters.

Environment with CCM enabled

1. One writer instance (R4.16xl instance class) in us-west-2a (ccminstance)
2. One reader instance (R4.16xl instance class) in us-west-2a (ccminstance-ro)
   Below screenshot from the RDS console, shows the “ccmcluster” with the writer and the reader node.
   
3. One EC2 instance (R4.16xl instance class) in us-west-2a AZ running Amazon Linux AMI release 2018.03 for running pgbench workload.
   Below screenshot show the EC2 instance details for the instance used as a pgbench client for ccmcluster (cluster with ccm enabled).
   

Environment with CCM disabled

1. One writer instance (R4.16xl instance class) in us-west-2a AZ (nocminst).
2. One reader instance (R4.16xl instance class) in us-west-2a AZ (noccminst-ro).
   Below screenshot from the RDS console shows the “noccmclust” with the writer and the reader node.
   
3. One EC2 instance (R4.16xl instance class) in us-west-2a AZ running Amazon Linux AMI release 2018.03 for running pgbench workload.
   Below screenshot show the EC2 instance details for the instance used as a pgbench client for noccmclust (cluster with ccm disabled).
   

Benchmarking on a CCM-enabled cluster

Initializing and loading data

Generate sample data on the CCM-enabled Aurora PostgreSQL cluster (ccmcluster) and use ccmdb. This post uses pgbench to generate a synthetic workload. See the following code (please replace the endpoint, port, username and the database name with your specific setup):

./pgbench -i --fillfactor=100 --scale=10000 --host=ccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com --username=<username> ccmdb

Connect to the database and verify that the database size after the pgbench data load is complete, using the following SQL query:

./psql -h ccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username>-d ccmdb

ccmdb=> SELECT pg_size_pretty( pg_database_size('ccmdb') );
 pg_size_pretty 
----------------
 171 GB
row) 

Running the benchmarking workload

To specify the probability of running read-only and read/write workloads, use the pgbench benchmarking option tpcb-like and @. This post runs a tpcb-like workload with 20 times the read-only workload and one read/write workload for 600 seconds. See the following code:

./pgbench  --progress-timestamp -M prepared -n -T 600 -P 1  -c 500 -j 500  --host=ccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com -b tpcb-like@1 -b select-only@20 --username=<username> ccmdb

Examining the buffer cache

You can examine the buffer cache contents with the pg_buffercache extension. This post examines the cache with both CCM enabled and disabled, and compares the content of the buffer cache of the writer node with the read-only node while the workload is running.  With CCM enabled, the content of the buffer cache of the writer node and the read-only node is similar because in the CCM enabled cluster, the writer node periodically sends buffer addresses of the frequently used buffers (defaults to a usage count greater than three) to the read-only node.

To use pg_buffercache on the writer node, connect to the writer node using the clusterendpoint of your cluster and create the pg_buffer_cache extension. See the following code:

./psql -h ccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username> -d ccmdb


ccmdb=> CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
ccmdb=> \dx pg_buffercache
                    List of installed extensions
      Name      | Version | Schema |           Description           
----------------+---------+--------+---------------------------------
 pg_buffercache | 1.3     | public | examine the shared buffer cache
(1 row)

-- Verify if we are connected to the Writer node.

ccmdb=> show transaction_read_only;
-[ RECORD 1 ]---------+----
transaction_read_only | off

SELECT c.relname, count(*) AS buffers
 FROM pg_buffercache b INNER JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
 b.reldatabase IN (0, (SELECT oid FROM pg_database
 WHERE datname = current_database()))
 GROUP BY c.relname
 ORDER BY 2 DESC
 LIMIT 10;

           relname        		| buffers  
-----------------------+----------+----------+----------
 pgbench_accounts      			| 18182376
 pgbench_accounts_pkey 			|  2741898
 pgbench_history       			|    58807
 pgbench_tellers       			|     7286
 pgbench_branches      			|     5137
 pgbench_tellers_pkey  			|     2197
 pgbench_branches_pkey 			|     1130
 pg_attribute          			|       30
 pg_statistic          			|       24
 pg_proc               			|       18

To use pg_buffer_cache on the read-only node, use the value for the readerEndpoint to connect to the read-only node. See the following code:

./psql -h ccmcluster-ro.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username> -d ccmdb

ccmdb=> \dx pg_buffercache
                    List of installed extensions
      Name      | Version | Schema |           Description           
----------------+---------+--------+---------------------------------
 pg_buffercache | 1.3     | public | examine the shared buffer cache
(1 row)

-- Verify if we are connected to the Read-only node.

ccmdb=> show transaction_read_only;
-[ RECORD 1 ]---------+---
transaction_read_only | on

SELECT c.relname, count(*) AS buffers
 FROM pg_buffercache b INNER JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
 b.reldatabase IN (0, (SELECT oid FROM pg_database
 WHERE datname = current_database()))
 GROUP BY c.relname
 ORDER BY 2 DESC
 LIMIT 10;
          relname        		| buffers  
-----------------------+----------+----------+----------
 pgbench_accounts      			| 18162144
 pgbench_accounts_pkey 			|  2741869
 pgbench_history       			|    58804
 pgbench_tellers       			|     7144
 pgbench_branches      			|     5028
 pgbench_tellers_pkey  			|     2156
 pgbench_branches_pkey 			|     1109
 pg_attribute          			|       26
 pg_statistic          			|       24
 pg_operator           			|       15

Initiate the failover from the console after 600 seconds.

1. On the RDS console, select your cluster.
2. Choose the writer instance.
3. From the Actions menu, choose Failover.
   The below screenshot shows how to initiate the failover manually.
   Once the failover is completed, resume the same workload on the new writer instance. See the following code:

   ./pgbench  --progress-timestamp -M prepared -n -T 600 -P 1  -c 500 -j 500  --host=ccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -b tpcb-like@1 -b select-only@20 --username=<username> ccmdb

Benchmarking on a CCM-disabled cluster

The following steps are similar to those on a CCM-enabled cluster. Note where the cluster names are different.

Initializing and loading data

As with the CCM-enabled cluster, generate sample data on the CCM-disabled Aurora PostgreSQL cluster (noccmclust) and use noccmdb. Use pgbench to generate a synthetic workload with the following code:

./pgbench -i --fillfactor=100 --scale=10000 --host=noccmcluster.cluster-XXXXXX.us-west-2.rds.amazonaws.com --username=<username> noccmdb

Connect to the database and verify that the database size after the pgbench data load is complete, using the following SQL query:

./psql -h noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username> -d noccmdb

noccmdb=> SELECT pg_size_pretty( pg_database_size('noccmdb') );
 pg_size_pretty 
----------------
 171 GB
row)

Running the benchmarking workload

As with the CCM-enabled cluster, specify the probability of running read-only and read/write workloads with the pgbench benchmarking option tpcb-like and @. This post runs a tpcb-like workload with 20 times the read-only workload and standard read/write workload for 600 seconds. See the following code:

./pgbench  --progress-timestamp -M prepared -n -T 600 -P 1  -c 500 -j 500  --host=noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -b tpcb-like@1 -b select-only@20 --username=<username> noccmdb

Examining the buffer cache

To use pg_buffercache on the writer node, connect to the writer node using the clusterendpoint of your cluster and create the pg_buffer_cache extension. See the following code:

./psql -h noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username> -d noccmdb

noccmdb=> CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

noccmdb=> \dx pg_buffercache
                    List of installed extensions
      Name      | Version | Schema |           Description           
----------------+---------+--------+---------------------------------
 pg_buffercache | 1.3     | public | examine the shared buffer cache
(1 row)

-- Verify if we are connected to the Writer node.

ccmdb=> show transaction_read_only;
-[ RECORD 1 ]---------+----
transaction_read_only | off

SELECT c.relname, count(*) AS buffers
 FROM pg_buffercache b INNER JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
 b.reldatabase IN (0, (SELECT oid FROM pg_database
 WHERE datname = current_database()))
 GROUP BY c.relname
 ORDER BY 2 DESC
 LIMIT 10;

        relname        			       | buffers  
-----------------------+----------+----------+----------
 pgbench_accounts      		           | 15401635
 pgbench_accounts_pkey 			       |  2741889
 pgbench_history       			       |    17024
 pgbench_tellers       		           |     4992
 pgbench_branches      			       |     3647
 pgbench_tellers_pkey  		           |     2200
 pgbench_branches_pkey 			       |      930
 pg_attribute          			       |       31
 pg_statistic          			       |       20
 pg_proc               			       |       20
(10 rows)

To use pg_buffer_cache on the read-only node, use the value for the readerEndpoint to connect to the read-only node. See the following code:

./psql -h noccmclust-ro.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U  <username> -d noccmdb

noccmdb=> \dx pg_buffercache
                    List of installed extensions
      Name      | Version | Schema |           Description           
----------------+---------+--------+---------------------------------
 pg_buffercache | 1.3     | public | examine the shared buffer cache
(1 row)


-- Verify if we are connected to the Read-only node.

ccmdb=> show transaction_read_only;
-[ RECORD 1 ]---------+---
transaction_read_only | on

SELECT c.relname, count(*) AS buffers
 FROM pg_buffercache b INNER JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
 b.reldatabase IN (0, (SELECT oid FROM pg_database
 WHERE datname = current_database()))
 GROUP BY c.relname
 ORDER BY 2 DESC
 LIMIT 10;

             relname             			| buffers 
---------------------------------+---------+----------+----------
 pgbench_history                 			|   11905
 pg_attribute                    			|      26
 pg_class                        			|      11
 pg_proc                         			|      10
 pg_proc_oid_index               	        |       8
 pg_attribute_relid_attnum_index 	        |       7
 pg_proc_proname_args_nsp_index  	        |       5
 pg_index                        			|       4
 pg_amproc                       			|       4
 pg_class_relname_nsp_index      	        |       4
(10 rows)

Initiate the failover from the console after 600 seconds.

1. On the RDS console, select your cluster.
2. Choose the writer instance.
3. From the Actions menu, choose Failover.
   After the failover is completed, resume the same workload on the new writer. See the following code:

   ./pgbench  --progress-timestamp -M prepared -n -T 600 -P 1  -c 500 -j 500  --host=noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -b tpcb-like@1 -b select-only@20 --username=<username> noccmdb

Comparing benchmarking results

The following graph shows the results from the benchmarking on the CCM-enabled and CCM-disabled Aurora PostgreSQL clusters. The CCM-enabled cluster scaled up to the average 90th percentile of the transaction per second (TPS), whereas the CCM-disabled cluster took approximately 357 seconds (990-633) to scale up to the 90th percentile of the TPS.

Summary

This post demonstrated how to provide more consistent application performance in the case of failover scenarios by using the CCM feature in Aurora PostgreSQL. Aurora PostgreSQL supports up to 15 read replicas on a single Aurora cluster within one AWS Region, and CCM eliminates the application brownout on the failover and provides consistent application failover behavior. You can enable CCM by designating a dedicated read replica to serve as a failover target only, and use another read replica for read-only workloads.

About the Authors

Sameer Malik is a Principal Database Solutions Architect with Amazon Web Services.

Andrei Illyashenko is a Senior Software Development Engineer with Amazon Web Services.