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: Open the Amazon RDS console. In the navigation pane, choose Parameter groups. 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. For Parameter group actions, choose Edit. Set the value of the apg_ccm_enabled cluster parameter to 1. The following screenshot shows the apg10outbound DB cluster Parameter group edit screen. 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: Open the Amazon RDS console. In the navigation pane, choose Databases. Choose ccminstance. This is the writer DB instance of the Aurora PostgreSQL DB cluster. The following screenshot shows the list of available databases. Choose Modify. The Modify DB Instance page opens. See the following screenshot for a preview. In the Failover section, for Priority, choose tier-0. See the following screenshot for a preview. Choose Continue. Check the summary of modifications. Choose Apply immediately. 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 -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 Querying aurora_ccm_status() on the cluster with CCM disabled. ./psql -h noccmclust.cluster-XXXXXX.us-west-2.rds.amazonaws.com -p 5432 -U -d noccmdb noccmdb=> x Expanded display is on. noccmdb=> select * from aurora_ccm_status(); ERROR: Cluster Cache Manager is disabled noccmdb=> For more information, see Monitoring the Buffer Cache. 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 One writer instance (R4.16xl instance class) in us-west-2a (ccminstance) 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. 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 One writer instance (R4.16xl instance class) in us-west-2a AZ (nocminst). 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. 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= 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 -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= 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 -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 -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. On the RDS console, select your cluster. Choose the writer instance. 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= 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= 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 -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= 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 -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 -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. On the RDS console, select your cluster. Choose the writer instance. 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= 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.      

https://probdm.com/site/NzIxNw