Reduce read I/O cost of your Amazon Aurora PostgreSQL database with range partitioning

海外精选
海外精选的内容汇集了全球优质的亚马逊云科技相关技术内容。同时,内容中提到的“AWS” 是 “Amazon Web Services” 的缩写,在此网站不作为商标展示。
0
0
{"value":"*This blog post was last updated June 8, 2022.*\n\n[Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/) offers a SQL database with enterprise-grade speed, availability, and scale at a cost comparable to open-source databases. With Aurora, you pay for [storage I/O operations](https://aws.amazon.com/rds/aurora/pricing/) that you consume. This pay-per-use model makes Aurora low-cost compared to traditional enterprise databases. However, it’s important to design your Aurora clusters to use I/O effectively at scale.\n\nIn this post, we demonstrate how to use PostgreSQL native partitioning to reduce I/O costs and increase read and write throughput with in-place partitioning that requires minimal downtime. Our example demonstrates a production-scale system that partitions a time series table database with over a hundred columns and relationships. The procedure migrates an application with 33 TB of data and ingests 46 GB a day into a partitioned-managed database that scales horizontally up to the current Aurora storage limit (128 TB). Our experiment optimizes the I/O costs ([ReadIOPS](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.Monitoring.Metrics.html)) of the writer and readers database instances by up to 40% with a monthly partitioning policy and 60% with a daily partitioning policy. We also validated the solution with a smaller dataset (1 TB) that includes only 10 columns and ingested only 20 GB a day to demonstrate the impact on smaller databases. For the smaller dataset, we observed similar cost reduction.\n\nBefore going further, we recommend reviewing our [previous post](https://aws.amazon.com/blogs/database/avoid-postgresql-lwlockbuffer_content-locks-in-amazon-aurora-tips-and-best-practices/) with tips on achieving high performance processing with Aurora when the database working set exceeds the available cache.\n\n#### **What is partitioning in PostgreSQL and why does it matter?**\n\n[Table partitioning in PostgreSQL](https://www.postgresql.org/docs/14/ddl-partitioning.html) enables high-performance data input and reporting. You can use partitioning for [databases ingesting large amounts of transactional data](https://aws.amazon.com/blogs/database/speed-up-time-series-data-ingestion-by-partitioning-tables-on-amazon-rds-for-postgresql/). Partitioning also provides [faster queries of large tables and helps maintain data without provisioning a large database instance](https://aws.amazon.com/blogs/database/improve-performance-and-manageability-of-large-postgresql-tables-by-migrating-to-partitioned-tables-on-amazon-aurora-and-amazon-rds/) because it requires fewer I/O resources.\n\nIn this post, we partition the time series data of the fictitious ordering system for monthly and daily ranges to compare the trade-offs.\n\nWhen the PostgreSQL optimizer reads a database query, it examines the WHERE clause of the query and tries to direct the database scan to only the relevant partitions. In the fictitious ordering system, the database engine routes the INSERT and UPDATE statements to the correct partitions, which are the child tables of the main table. That’s why it’s important to consider detaching old partitions if your business allows it. Furthermore, PostgreSQL version 14 allows you to detach the partition from the table as an online command. The data churning is simpler if you align your data size with the database instance size to limit compute costs.\n\nFinally, Aurora PostgreSQL supports automatic management of partition creation and runtime maintenance with the Partition Manager ([pg_partman](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_Partitions.html)) extension. `pg_partman` is a PostgreSQL extension that helps you manage your time series table partition sets.\n\n#### **Simulate hyperscale data growth in Aurora PostgreSQL**\n\nWe ran a benchmark with a [load simulator](https://github.com/aws-samples/postgres-lightweight-locks-simulator) that generated 1,000 write (insert and update) and 10,000 read (select) transactions per second, weighing an average of 47 GB per day. We partitioned the database after the total database size was 33 TB of data and observed the I/O usage before and after partitioning.\n\nThe load simulator mimics a fictitious ordering system that allows users to place new orders (steps 1 and 2 in the following diagram) and update the orders throughout the order lifecycle (steps 3 and 4).\n\n![截屏20220828 17.37.18.png](https://dev-media.amazoncloud.cn/479f470f5684436c90599dcf2d637710_%E6%88%AA%E5%B1%8F2022-08-28%2017.37.18.png)\n\nThe load is simulated by a cyclic [positive sine wave](https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/appsimulator.sh#L41) that triggers a thread, which inserts new orders. The orders IDs are stored in an [Amazon Simple Queue Service](https://aws.amazon.com/sqs/) (Amazon SQS) queue for processing by another type of thread that updates the orders. The write load consumes up to 60% of an db.r6g.8xlarge database CPU and processes up to 1,000 insert and update transactions per second.\n\nThe following figure shows more details of our ordering system load simulation.\n\n![image.png](https://dev-media.amazoncloud.cn/200dc44ad3584ca680e11f8ae080b5b7_image.png)\n\n#### **Partitioning the orders table**\n\nOur dataset size was 33 TB before partitioning the orders table, including its indexes. There are a few ways to partition tables. The first method uses [AWS Database Migration Service](https://aws.amazon.com/dms/) (AWS DMS), and the second migrates the data in-place. [The first method](https://aws.amazon.com/blogs/database/improve-performance-and-manageability-of-large-postgresql-tables-by-migrating-to-partitioned-tables-on-amazon-aurora-and-amazon-rds/) uses PostgreSQL logical replication, which provides fine-grained control over replicating and synchronizing parts of your tables. AWS DMS copies data from a non-partitioned table to a partitioned table and enables migration to a partitioned table with minimal downtime.\n\nWe chose the in-place method because it’s the fastest and simplest method, but required a few brief (10 seconds) application interruptions throughout the migration steps.\n\nWe also needed to adapt the application to the new primary key that includes the partition key, so anytime the application used the primary key `public_id`, we added the partition key `created_at`. Unique constraints (and therefore primary keys) on partitioned tables must include all the partition key columns. [This limitation](https://www.postgresql.org/docs/current/ddl-partitioning.html) exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure must guarantee no duplicates in different partitions. For example, in the [insert simulation](https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/insert-into-order.sql#L31), instead of `RETURNING public_id;`, we add `RETURNING public_id,created_at;`. For [updates](https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/app-update-from-sqs.sh#L27), we add to the following code:\n\n```\nUPDATE orders SET text_notnull_1=substr(md5(random()::text), 0, 25) WHERE public_id = \"$public_id\";\n```\n\nThe following is our updated code:\n\n```\nUPDATE orders SET text_notnull_1=substr(md5(random()::text), 0, 25) WHERE public_id = \"$public_id\" AND created_at=”$created_at\";\n```\n\nThe procedure is mostly done while the application is online, but a few applications interruptions are required to avoid exclusive locks to the database when running ALTER TABLE commands, so before steps 2, 4, and 5 in the following instructions, which require ALTER TABLE, we blocked the application access to the database, changed the table for a moment, and then restored application access to the database.\n\n1. Create the partitioned table with the original name and a `_parent` suffix (for example, `orders_parent`).\nWe extract the `orders_parent` table and indexes DDL based on the existing schema with [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html), a tool that extracts a PostgreSQL database into a script file (the output of `pg_dump -Ox --schema-only -t orders -U postgres`) and add the PARTITION BY RANGE clause. See the following code:\n```\nCREATE TABLE orders_parent (\n id \t\tbigint not null,\n created_at \tdate not null,\n …\n col100\n) PARTITION BY RANGE (created_at)\n```\n\n2. Scope the existing orders before partitioning to be the first partition.\nWe need to specify date and time upper and lower boundaries by setting a constraint on the partition key, `created_at`. We do this by determining the target day for transitioning to the partitioned table and generate the ALTER TABLE statement that creates the constraint.In our example, we create the `orders_old_created_at` constraint 20 days in advance:\n```\nDO $$\nDECLARE\n -- the end date of the partition, from now\n days_ahead int := 20;\n\n -- min/max values of the partition \n min_part_key timestamp;\n max_part_key timestamp;\n\n -- store the DDL to create the constraint\n alter_stmt text;\nBEGIN\n -- get the min and max created_at date\n select \n date(min(created_at)), \n date(max(created_at)) + (days_ahead||' days')::interval\n into min_part_key, max_part_key\n from orders;\n\n -- generate the SQL to create the constraint\n select \n format('alter table orders add constraint orders_old_created_at \n check ( created_at >= ''%s'' and created_at < ''%s'') NOT VALID', \n min_part_key,max_part_key)\n into alter_stmt;\n\n -- display the SQL to create the constraint\n RAISE NOTICE 'running the sql: %', alter_stmt;\n\n -- execute the SQL to create the constraint\n EXECUTE alter_stmt;\nEND$$;\n```\n\nWe create the constraint with no validation and run the validation separately in step 4 as an online operation that requires only a brief application interruption for the duration of the ALTER TABLE command.\n\n3. Prepare the partition management with the `pg_partman` extension on [Amazon RDS for PostgreSQL versions 12.5 and higher](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html):\n```\nCREATE SCHEMA partman;\nCREATE EXTENSION pg_partman WITH SCHEMA partman;\nSELECT partman.create_parent( p_parent_table => 'public.orders',\n p_control => 'created_at',\n p_type => 'native',\n p_start_partition => '2022-05-18',\n p_interval=> 'monthly',\n p_premake => 3);\n```\nNote that we configured the `p_start_partition` value based on the constraint value from step 2, `max(created_at)`.\n\n4. Validate the constraint validation from step 2 ended. This step requires a brief application interruption.\n```\nalter table orders validate constraint orders_old_created_at;\n\n```\n\n5. The transition day to the partitioned table. This step requires an application interruption. Rename the existing orders table as the old partition table.\n```\nBEGIN\nalter table orders rename to orders_old;\n--Swap the parent orders to be the new order partitioned table. \nalter table orders_parent rename to orders;\ncommit;\n```\n\n6. Attach the old orders table as a partition:\n```\nDO $$\nDECLARE\n -- the end date of the partition, from now\n days_ahead int := 20;\n\n -- min/max values of the partition\n min_part_key timestamp;\n max_part_key timestamp;\n\n -- store the DDL to attach the partition\n alter_stmt text;\nBEGIN\n -- get the min and max created_at date\n select \n date(min(created_at)), \n date(max(created_at)) + (days_ahead||' days')::interval\n into min_part_key, max_part_key\n from orders;\n\n -- generate the SQL to attach the partition\n select \n format('alter table orders attach partition orders_old for values from (''%s'') to (''%s'')', min_part_key, max_part_key)\n into alter_stmt;\n\n -- display the SQL to attach the partition\n RAISE NOTICE 'running the sql: %s', alter_stmt;\n\n -- execute the SQL to attach the partition\n EXECUTE alter_stmt;\nEND$$;\n```\n\nNow that the orders table is daily partitioned, queries with `created_at` use only the relevant partitions when populating the shared buffer pool. This keeps the working set in-memory optimized.\n\n#### **Performance analysis**\n\nThe IOPS cost optimization derived from the reduction in IOPS needed to fetch a transaction record by [index](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STATIO-ALL-INDEXES-VIEW) and [table](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW), denoted by the `db.IO.blks_read` metric, because the working set fits the database instance memory size and better utilizes the buffer cache, denoted by `db.Cache.blks_hit.avg`.\n\nOur simulation shows 600,000 blocks/second spikes of `db.IO.blks_read` before partitioning with the 33 TB working set, compared with 25,000 blocks/second spikes of `db.IO.blks_read` with daily partitioning (46 GB working set size). We also observed only 600,000 blocks/second of `db.Cache.blks_hit.avg` before partitioning and up to 1.5 million `db.Cache.blks_hit.avg` with daily partitions.\n\nThe following figure shows the CloudWatch metrics of the memory blocks in the writer instance before partitioning (33 TB).\n\n![image.png](https://dev-media.amazoncloud.cn/a76d8bf667dd42ad94dcd0652d339904_image.png)\n\nThe following figure shows the memory blocks in the writer instance with daily partitioning (45 GB).\n\n![image.png](https://dev-media.amazoncloud.cn/06a1d30b17bd470fae0abd94b57fb13c_image.png)\n\nNext, we compare the `ReadIOPS` that derives from the memory blocks reads optimization in the writer and reader instances. Note the same 140,000 per-second writes (`db WriteIOPS`), caused by inserts and updates before and after partitioning. The `ReadIOPS` on the writer (`db ReadIOPS`) instances is 100, 000 per second, compared to 40,000 per second with daily partitioning. We noticed similar optimization on the reader instance (`db2 ReadIOPS`).\n\nThe following figure shows the read/write IOPS in the writer instance before partitioning (33 TB).\n\n![image.png](https://dev-media.amazoncloud.cn/3b8358eaddf74e078fce72cc22968b49_image.png)\n\nThe following figure shows the read/write IOPS in the writer instance with daily partitioning (45 GB).\n\n![image.png](https://dev-media.amazoncloud.cn/283b628258334c7f94852933dcea968e_image.png)\n\n#### **Cost analysis**\n\nWe analyzed the Aurora storage I/O cost and usage with [AWS Cost Usage Reports](https://docs.aws.amazon.com/cur/latest/userguide/what-is-cur.html) and found an average cost savings of up to 40% for the storage I/O for the synthetic workload we simulated. We analyzed weekly, bi-weekly, and monthly ranges of time and assessed the storage I/O cost savings to remove data bias.\n\nThe following table summarizes our weekly findings, as illustrated in the following figures that denotes hourly RDS IOPS charges (Region-RDS:PIOPS-Storage and Region-RDS:PIOPS)\n\n![截屏20220828 18.00.36.png](https://dev-media.amazoncloud.cn/358da00471344fb6847fee135e8430cf_%E6%88%AA%E5%B1%8F2022-08-28%2018.00.36.png)\n\n![image.png](https://dev-media.amazoncloud.cn/f646a968fe0f4907b42b5db627700aad_image.png)\n\n![image.png](https://dev-media.amazoncloud.cn/d6e8d0bfe0454c2eaf942fdb7310ed45_image.png)\n\n#### **Conclusion**\n\nIn this post, we showed you how to optimize Aurora storage I/O costs by up to 60% due to rapid growth challenges in PostgreSQL databases using the native PostgreSQL support in partitioning and with the `pg_partman` extension. We also discussed partitioning strategies and trade-offs with an example workload and provided both performance and cost analysis.\n\nWe’re eager to hear from you about similar challenges you experienced with your PostgreSQL database. Please leave a comment in the comment section or create an issue in the simulator [code sample](https://github.com/aws-samples/postgres-lightweight-locks-simulator).\n\n#### **About the Authors**\n\n![image.png](https://dev-media.amazoncloud.cn/c583c6632ce542dc82baad1ca5a26f9f_image.png)\n\n**Sami Imseih** is a Database Engineer specialized in PostgreSQL at AWS. Sami has been overseeing mission critical database environments for over a decade. Since 2019, Sami has been helping customers achieve success with database migrations to Amazon RDS for PostgreSQL.\n\n![image.png](https://dev-media.amazoncloud.cn/3812c4f7b50746dcbf990cf830c5226c_image.png)\n\n**Yahav Biran** is a Principal Solutions Architect in AWS, focused on game tech at scale. Yahav enjoys contributing to open-source projects and publishes in the AWS blog and academic journals. He currently contributes to the K8s Helm community, AWS databases and compute blogs, and Journal of Systems Engineering. He delivers technical presentations at technology events and works with customers to design their applications in the cloud. He received his PhD (Systems Engineering) from Colorado State University.","render":"<p><em>This blog post was last updated June 8, 2022.</em></p>\n<p><a href=\"https://aws.amazon.com/rds/aurora/postgresql-features/\" target=\"_blank\">Amazon Aurora PostgreSQL-Compatible Edition</a> offers a SQL database with enterprise-grade speed, availability, and scale at a cost comparable to open-source databases. With Aurora, you pay for <a href=\"https://aws.amazon.com/rds/aurora/pricing/\" target=\"_blank\">storage I/O operations</a> that you consume. This pay-per-use model makes Aurora low-cost compared to traditional enterprise databases. However, it’s important to design your Aurora clusters to use I/O effectively at scale.</p>\n<p>In this post, we demonstrate how to use PostgreSQL native partitioning to reduce I/O costs and increase read and write throughput with in-place partitioning that requires minimal downtime. Our example demonstrates a production-scale system that partitions a time series table database with over a hundred columns and relationships. The procedure migrates an application with 33 TB of data and ingests 46 GB a day into a partitioned-managed database that scales horizontally up to the current Aurora storage limit (128 TB). Our experiment optimizes the I/O costs (<a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.Monitoring.Metrics.html\" target=\"_blank\">ReadIOPS</a>) of the writer and readers database instances by up to 40% with a monthly partitioning policy and 60% with a daily partitioning policy. We also validated the solution with a smaller dataset (1 TB) that includes only 10 columns and ingested only 20 GB a day to demonstrate the impact on smaller databases. For the smaller dataset, we observed similar cost reduction.</p>\n<p>Before going further, we recommend reviewing our <a href=\"https://aws.amazon.com/blogs/database/avoid-postgresql-lwlockbuffer_content-locks-in-amazon-aurora-tips-and-best-practices/\" target=\"_blank\">previous post</a> with tips on achieving high performance processing with Aurora when the database working set exceeds the available cache.</p>\n<h4><a id=\"What_is_partitioning_in_PostgreSQL_and_why_does_it_matter_8\"></a><strong>What is partitioning in PostgreSQL and why does it matter?</strong></h4>\n<p><a href=\"https://www.postgresql.org/docs/14/ddl-partitioning.html\" target=\"_blank\">Table partitioning in PostgreSQL</a> enables high-performance data input and reporting. You can use partitioning for <a href=\"https://aws.amazon.com/blogs/database/speed-up-time-series-data-ingestion-by-partitioning-tables-on-amazon-rds-for-postgresql/\" target=\"_blank\">databases ingesting large amounts of transactional data</a>. Partitioning also provides <a href=\"https://aws.amazon.com/blogs/database/improve-performance-and-manageability-of-large-postgresql-tables-by-migrating-to-partitioned-tables-on-amazon-aurora-and-amazon-rds/\" target=\"_blank\">faster queries of large tables and helps maintain data without provisioning a large database instance</a> because it requires fewer I/O resources.</p>\n<p>In this post, we partition the time series data of the fictitious ordering system for monthly and daily ranges to compare the trade-offs.</p>\n<p>When the PostgreSQL optimizer reads a database query, it examines the WHERE clause of the query and tries to direct the database scan to only the relevant partitions. In the fictitious ordering system, the database engine routes the INSERT and UPDATE statements to the correct partitions, which are the child tables of the main table. That’s why it’s important to consider detaching old partitions if your business allows it. Furthermore, PostgreSQL version 14 allows you to detach the partition from the table as an online command. The data churning is simpler if you align your data size with the database instance size to limit compute costs.</p>\n<p>Finally, Aurora PostgreSQL supports automatic management of partition creation and runtime maintenance with the Partition Manager (<a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_Partitions.html\" target=\"_blank\">pg_partman</a>) extension. <code>pg_partman</code> is a PostgreSQL extension that helps you manage your time series table partition sets.</p>\n<h4><a id=\"Simulate_hyperscale_data_growth_in_Aurora_PostgreSQL_18\"></a><strong>Simulate hyperscale data growth in Aurora PostgreSQL</strong></h4>\n<p>We ran a benchmark with a <a href=\"https://github.com/aws-samples/postgres-lightweight-locks-simulator\" target=\"_blank\">load simulator</a> that generated 1,000 write (insert and update) and 10,000 read (select) transactions per second, weighing an average of 47 GB per day. We partitioned the database after the total database size was 33 TB of data and observed the I/O usage before and after partitioning.</p>\n<p>The load simulator mimics a fictitious ordering system that allows users to place new orders (steps 1 and 2 in the following diagram) and update the orders throughout the order lifecycle (steps 3 and 4).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/479f470f5684436c90599dcf2d637710_%E6%88%AA%E5%B1%8F2022-08-28%2017.37.18.png\" alt=\"截屏20220828 17.37.18.png\" /></p>\n<p>The load is simulated by a cyclic <a href=\"https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/appsimulator.sh#L41\" target=\"_blank\">positive sine wave</a> that triggers a thread, which inserts new orders. The orders IDs are stored in an <a href=\"https://aws.amazon.com/sqs/\" target=\"_blank\">Amazon Simple Queue Service</a> (Amazon SQS) queue for processing by another type of thread that updates the orders. The write load consumes up to 60% of an db.r6g.8xlarge database CPU and processes up to 1,000 insert and update transactions per second.</p>\n<p>The following figure shows more details of our ordering system load simulation.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/200dc44ad3584ca680e11f8ae080b5b7_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Partitioning_the_orders_table_32\"></a><strong>Partitioning the orders table</strong></h4>\n<p>Our dataset size was 33 TB before partitioning the orders table, including its indexes. There are a few ways to partition tables. The first method uses <a href=\"https://aws.amazon.com/dms/\" target=\"_blank\">AWS Database Migration Service</a> (AWS DMS), and the second migrates the data in-place. <a href=\"https://aws.amazon.com/blogs/database/improve-performance-and-manageability-of-large-postgresql-tables-by-migrating-to-partitioned-tables-on-amazon-aurora-and-amazon-rds/\" target=\"_blank\">The first method</a> uses PostgreSQL logical replication, which provides fine-grained control over replicating and synchronizing parts of your tables. AWS DMS copies data from a non-partitioned table to a partitioned table and enables migration to a partitioned table with minimal downtime.</p>\n<p>We chose the in-place method because it’s the fastest and simplest method, but required a few brief (10 seconds) application interruptions throughout the migration steps.</p>\n<p>We also needed to adapt the application to the new primary key that includes the partition key, so anytime the application used the primary key <code>public_id</code>, we added the partition key <code>created_at</code>. Unique constraints (and therefore primary keys) on partitioned tables must include all the partition key columns. <a href=\"https://www.postgresql.org/docs/current/ddl-partitioning.html\" target=\"_blank\">This limitation</a> exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure must guarantee no duplicates in different partitions. For example, in the <a href=\"https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/insert-into-order.sql#L31\" target=\"_blank\">insert simulation</a>, instead of <code>RETURNING public_id;</code>, we add <code>RETURNING public_id,created_at;</code>. For <a href=\"https://github.com/aws-samples/postgres-lightweight-locks-simulator/blob/3f0daa7136641351a665a8a12970c7e5fa88d965/k8s-load-simu-setup/app-update-from-sqs.sh#L27\" target=\"_blank\">updates</a>, we add to the following code:</p>\n<pre><code class=\"lang-\">UPDATE orders SET text_notnull_1=substr(md5(random()::text), 0, 25) WHERE public_id = &quot;$public_id&quot;;\n</code></pre>\n<p>The following is our updated code:</p>\n<pre><code class=\"lang-\">UPDATE orders SET text_notnull_1=substr(md5(random()::text), 0, 25) WHERE public_id = &quot;$public_id&quot; AND created_at=”$created_at&quot;;\n</code></pre>\n<p>The procedure is mostly done while the application is online, but a few applications interruptions are required to avoid exclusive locks to the database when running ALTER TABLE commands, so before steps 2, 4, and 5 in the following instructions, which require ALTER TABLE, we blocked the application access to the database, changed the table for a moment, and then restored application access to the database.</p>\n<ol>\n<li>Create the partitioned table with the original name and a <code>_parent</code> suffix (for example, <code>orders_parent</code>).<br />\nWe extract the <code>orders_parent</code> table and indexes DDL based on the existing schema with <a href=\"https://www.postgresql.org/docs/current/app-pgdump.html\" target=\"_blank\">pg_dump</a>, a tool that extracts a PostgreSQL database into a script file (the output of <code>pg_dump -Ox --schema-only -t orders -U postgres</code>) and add the PARTITION BY RANGE clause. See the following code:</li>\n</ol>\n<pre><code class=\"lang-\">CREATE TABLE orders_parent (\n id \t\tbigint not null,\n created_at \tdate not null,\n …\n col100\n) PARTITION BY RANGE (created_at)\n</code></pre>\n<ol start=\"2\">\n<li>Scope the existing orders before partitioning to be the first partition.<br />\nWe need to specify date and time upper and lower boundaries by setting a constraint on the partition key, <code>created_at</code>. We do this by determining the target day for transitioning to the partitioned table and generate the ALTER TABLE statement that creates the constraint.In our example, we create the <code>orders_old_created_at</code> constraint 20 days in advance:</li>\n</ol>\n<pre><code class=\"lang-\">DO $$\nDECLARE\n -- the end date of the partition, from now\n days_ahead int := 20;\n\n -- min/max values of the partition \n min_part_key timestamp;\n max_part_key timestamp;\n\n -- store the DDL to create the constraint\n alter_stmt text;\nBEGIN\n -- get the min and max created_at date\n select \n date(min(created_at)), \n date(max(created_at)) + (days_ahead||' days')::interval\n into min_part_key, max_part_key\n from orders;\n\n -- generate the SQL to create the constraint\n select \n format('alter table orders add constraint orders_old_created_at \n check ( created_at &gt;= ''%s'' and created_at &lt; ''%s'') NOT VALID', \n min_part_key,max_part_key)\n into alter_stmt;\n\n -- display the SQL to create the constraint\n RAISE NOTICE 'running the sql: %', alter_stmt;\n\n -- execute the SQL to create the constraint\n EXECUTE alter_stmt;\nEND$$;\n</code></pre>\n<p>We create the constraint with no validation and run the validation separately in step 4 as an online operation that requires only a brief application interruption for the duration of the ALTER TABLE command.</p>\n<ol start=\"3\">\n<li>Prepare the partition management with the <code>pg_partman</code> extension on <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html\" target=\"_blank\">Amazon RDS for PostgreSQL versions 12.5 and higher</a>:</li>\n</ol>\n<pre><code class=\"lang-\">CREATE SCHEMA partman;\nCREATE EXTENSION pg_partman WITH SCHEMA partman;\nSELECT partman.create_parent( p_parent_table =&gt; 'public.orders',\n p_control =&gt; 'created_at',\n p_type =&gt; 'native',\n p_start_partition =&gt; '2022-05-18',\n p_interval=&gt; 'monthly',\n p_premake =&gt; 3);\n</code></pre>\n<p>Note that we configured the <code>p_start_partition</code> value based on the constraint value from step 2, <code>max(created_at)</code>.</p>\n<ol start=\"4\">\n<li>Validate the constraint validation from step 2 ended. This step requires a brief application interruption.</li>\n</ol>\n<pre><code class=\"lang-\">alter table orders validate constraint orders_old_created_at;\n\n</code></pre>\n<ol start=\"5\">\n<li>The transition day to the partitioned table. This step requires an application interruption. Rename the existing orders table as the old partition table.</li>\n</ol>\n<pre><code class=\"lang-\">BEGIN\nalter table orders rename to orders_old;\n--Swap the parent orders to be the new order partitioned table. \nalter table orders_parent rename to orders;\ncommit;\n</code></pre>\n<ol start=\"6\">\n<li>Attach the old orders table as a partition:</li>\n</ol>\n<pre><code class=\"lang-\">DO $$\nDECLARE\n -- the end date of the partition, from now\n days_ahead int := 20;\n\n -- min/max values of the partition\n min_part_key timestamp;\n max_part_key timestamp;\n\n -- store the DDL to attach the partition\n alter_stmt text;\nBEGIN\n -- get the min and max created_at date\n select \n date(min(created_at)), \n date(max(created_at)) + (days_ahead||' days')::interval\n into min_part_key, max_part_key\n from orders;\n\n -- generate the SQL to attach the partition\n select \n format('alter table orders attach partition orders_old for values from (''%s'') to (''%s'')', min_part_key, max_part_key)\n into alter_stmt;\n\n -- display the SQL to attach the partition\n RAISE NOTICE 'running the sql: %s', alter_stmt;\n\n -- execute the SQL to attach the partition\n EXECUTE alter_stmt;\nEND$$;\n</code></pre>\n<p>Now that the orders table is daily partitioned, queries with <code>created_at</code> use only the relevant partitions when populating the shared buffer pool. This keeps the working set in-memory optimized.</p>\n<h4><a id=\"Performance_analysis_166\"></a><strong>Performance analysis</strong></h4>\n<p>The IOPS cost optimization derived from the reduction in IOPS needed to fetch a transaction record by <a href=\"https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STATIO-ALL-INDEXES-VIEW\" target=\"_blank\">index</a> and <a href=\"https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW\" target=\"_blank\">table</a>, denoted by the <code>db.IO.blks_read</code> metric, because the working set fits the database instance memory size and better utilizes the buffer cache, denoted by <code>db.Cache.blks_hit.avg</code>.</p>\n<p>Our simulation shows 600,000 blocks/second spikes of <code>db.IO.blks_read</code> before partitioning with the 33 TB working set, compared with 25,000 blocks/second spikes of <code>db.IO.blks_read</code> with daily partitioning (46 GB working set size). We also observed only 600,000 blocks/second of <code>db.Cache.blks_hit.avg</code> before partitioning and up to 1.5 million <code>db.Cache.blks_hit.avg</code> with daily partitions.</p>\n<p>The following figure shows the CloudWatch metrics of the memory blocks in the writer instance before partitioning (33 TB).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/a76d8bf667dd42ad94dcd0652d339904_image.png\" alt=\"image.png\" /></p>\n<p>The following figure shows the memory blocks in the writer instance with daily partitioning (45 GB).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/06a1d30b17bd470fae0abd94b57fb13c_image.png\" alt=\"image.png\" /></p>\n<p>Next, we compare the <code>ReadIOPS</code> that derives from the memory blocks reads optimization in the writer and reader instances. Note the same 140,000 per-second writes (<code>db WriteIOPS</code>), caused by inserts and updates before and after partitioning. The <code>ReadIOPS</code> on the writer (<code>db ReadIOPS</code>) instances is 100, 000 per second, compared to 40,000 per second with daily partitioning. We noticed similar optimization on the reader instance (<code>db2 ReadIOPS</code>).</p>\n<p>The following figure shows the read/write IOPS in the writer instance before partitioning (33 TB).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/3b8358eaddf74e078fce72cc22968b49_image.png\" alt=\"image.png\" /></p>\n<p>The following figure shows the read/write IOPS in the writer instance with daily partitioning (45 GB).</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/283b628258334c7f94852933dcea968e_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Cost_analysis_190\"></a><strong>Cost analysis</strong></h4>\n<p>We analyzed the Aurora storage I/O cost and usage with <a href=\"https://docs.aws.amazon.com/cur/latest/userguide/what-is-cur.html\" target=\"_blank\">AWS Cost Usage Reports</a> and found an average cost savings of up to 40% for the storage I/O for the synthetic workload we simulated. We analyzed weekly, bi-weekly, and monthly ranges of time and assessed the storage I/O cost savings to remove data bias.</p>\n<p>The following table summarizes our weekly findings, as illustrated in the following figures that denotes hourly RDS IOPS charges (Region-RDS:PIOPS-Storage and Region-RDS:PIOPS)</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/358da00471344fb6847fee135e8430cf_%E6%88%AA%E5%B1%8F2022-08-28%2018.00.36.png\" alt=\"截屏20220828 18.00.36.png\" /></p>\n<p><img src=\"https://dev-media.amazoncloud.cn/f646a968fe0f4907b42b5db627700aad_image.png\" alt=\"image.png\" /></p>\n<p><img src=\"https://dev-media.amazoncloud.cn/d6e8d0bfe0454c2eaf942fdb7310ed45_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Conclusion_202\"></a><strong>Conclusion</strong></h4>\n<p>In this post, we showed you how to optimize Aurora storage I/O costs by up to 60% due to rapid growth challenges in PostgreSQL databases using the native PostgreSQL support in partitioning and with the <code>pg_partman</code> extension. We also discussed partitioning strategies and trade-offs with an example workload and provided both performance and cost analysis.</p>\n<p>We’re eager to hear from you about similar challenges you experienced with your PostgreSQL database. Please leave a comment in the comment section or create an issue in the simulator <a href=\"https://github.com/aws-samples/postgres-lightweight-locks-simulator\" target=\"_blank\">code sample</a>.</p>\n<h4><a id=\"About_the_Authors_208\"></a><strong>About the Authors</strong></h4>\n<p><img src=\"https://dev-media.amazoncloud.cn/c583c6632ce542dc82baad1ca5a26f9f_image.png\" alt=\"image.png\" /></p>\n<p><strong>Sami Imseih</strong> is a Database Engineer specialized in PostgreSQL at AWS. Sami has been overseeing mission critical database environments for over a decade. Since 2019, Sami has been helping customers achieve success with database migrations to Amazon RDS for PostgreSQL.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/3812c4f7b50746dcbf990cf830c5226c_image.png\" alt=\"image.png\" /></p>\n<p><strong>Yahav Biran</strong> is a Principal Solutions Architect in AWS, focused on game tech at scale. Yahav enjoys contributing to open-source projects and publishes in the AWS blog and academic journals. He currently contributes to the K8s Helm community, AWS databases and compute blogs, and Journal of Systems Engineering. He delivers technical presentations at technology events and works with customers to design their applications in the cloud. He received his PhD (Systems Engineering) from Colorado State University.</p>\n"}
目录
亚马逊云科技解决方案 基于行业客户应用场景及技术领域的解决方案
联系亚马逊云科技专家
亚马逊云科技解决方案
基于行业客户应用场景及技术领域的解决方案
联系专家
0
目录
关闭
contact-us