New – Amazon RDS Optimized Reads and Optimized Writes

{"value":"Way back in 2009 I wrote [Introducing Amazon RDS – The Amazon Relational Database Service](https://aws.amazon.com/blogs/aws/introducing-rds-the-amazon-relational-database-service/) and told you that:\n\nRDS makes it easier for you to set up, operate, and scale a relational database in the cloud. You get direct database access without worrying about infrastructure provisioning, software maintenance, or common database management tasks.\n\nSince that launch we have continued to do our best to help you to avoid all of those items, while also working to make RDS ever-more cost effective. For example, we recently launched [Graviton2 DB Instances](https://aws.amazon.com/blogs/aws/new-amazon-rds-on-graviton2-processors/) that deliver up to 52% better price/performance and a new [Multi-AZ Deployment Option](https://aws.amazon.com/blogs/aws/amazon-rds-multi-az-db-cluster/) that delivers up to 33% better price/performance along with 2x faster transaction commit latency.\n\nToday I would like to tell you about two new features that will accelerate your [Amazon RDS for MySQL](https://aws.amazon.com/rds/mysql/) workloads:\n\n**Amazon RDS Optimized Reads** achieve faster query processing by placing temporary tables generated by MySQL on NVMe-based SSD block storage that is physically connected to the host server. Queries that use temporary tables, such as those involving sorts, hash aggregations, high-load joins, and Common Table Expressions (CTEs) can execute up to 50% faster with Optimized Reads.\n\n\n**Amazon RDS Optimized Writes** deliver an improvement of up to 2x in write transaction throughput at no extra charge, and with the same level of provisioned IOPS. Optimized Writes are a great fit for write-heavy workloads that generate lots of concurrent transactions. This includes digital payments, financial trading platforms, and online games.\n\n### ++Amazon RDS Optimized Reads++\nAmazon RDS for MySQL without Optimized Reads places temporary tables on [Amazon Elastic Block Store (Amazon EBS)](https://aws.amazon.com/ebs/) volumes. Optimized Reads offload the operations on temporary objects from EBS to the instance store attached to **r5d, m5d, r6gd** and **m6gd** instances. As a result EBS volumes can be more efficiently utilized for reads and writes on persistent data, as well as background operations such as flushes, insert buffer merges, and so forth. This increased efficiency is (of course) always nice to have, but it is particularly beneficial for certain use cases:\n\n- **Analytical Queries** that include Complex Table Expressions, derived tables, and grouping operations.\n- **Read Replicas** that handle the unoptimized queries for an application.\n- **On-Demand or Dynamic Reporting Queries** with complex operations such as ```GROUP BY```and ```ORDER``` BY that can’t always use appropriate indexes.\n- **Other Workloads** that use internal temporary tables.\n\nYou can monitor the MySQL status variable```created_tmp_files```\n to observe the rate of creation for temporary tables.\n\nThe amount of instance storage available on the instance varies by instance family and size. Here’s a guide:\n\n\n\n### Using Optimized Reads\nTo take advantage of this new feature, choose MySQL engine version 8.0.28 or newer and launch Amazon RDS for MySQL on one of the instance types listed above:\n\n\n\nYou can monitor the use of instance storage by watching new CloudWatch metrics including **FreeLocalStorage, ReadIOPSLocalStorage, WriteIOPSLocalStorage**, and so forth (see the [User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-metrics.html) for a complete list of new and existing metrics).\n\n### ++Amazon RDS Optimized Writes++\nBy default, MySQL uses an on-disk doublewrite buffer that serves as an intermediate stop between memory and the final on-disk storage. Each page of the buffer is 16 KiB but is written to the final on-disk storage in 4 KiB chunks. This extra step maintains data integrity, but also consumes additional I/O bandwidth. When running those write-heavy workloads that I described earlier, this might require provisioning of additional IOPS to meet your performance and throughput requirements.\n\nOptimized Writes uses uniform 16 KiB database pages, file system blocks, and operating system pages, and writes them to storage atomically (all or nothing), resulting in the performance improvement of up to 2x that I mentioned earlier.\n\n### ++Using Optimized Writes++\nYou must create a new DB Instance from scratch on a **db.r5b** or **db.r6i** instance with the latest version of MySQL 8.0 in order to make use of Optimized Writes:\n\n\n\n\nThis setting affects the format of DB snapshots, with two important consequences:\n\n\n1. You cannot restore an existing non-optimized snapshot to a new, optimized one in order to enable Optimized Writes.\n2. Restoring a snapshot that was made with optimization enabled will enable Optimized Writes in the new instance.\n\n\nIf you scale to an instance type that does not support Optimized Writes, Amazon RDS will enable MySQL’s doublewrite mode on the instance as a fallback. If you scale into an instance that supports Optimized Writes from one that does not, Amazon RDS will launch MySQL in doublewrite mode, wait for the recovery and log replay to complete, and then relaunch MySQL with doublewrite disabled.\n\nOptimized Writes are now available in the US East (Ohio, N. Virginia), US West (Oregon), Asia Pacific (Singapore, Tokyo), and Europe (Frankfurt, Ireland, Paris) Regions and you can start to benefit from them today!\n\n— [Jeff](https://twitter.com/jeffbarr);\n\n\n\n### **[Jeff Barr](https://aws.amazon.com/blogs/aws/author/jbarr/)**\nJeff Barr is Chief Evangelist for Amazon Web Services. He started this blog in 2004 and has been writing posts just about non-stop ever since.\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n","render":"<p>Way back in 2009 I wrote <a href=\"https://aws.amazon.com/blogs/aws/introducing-rds-the-amazon-relational-database-service/\" target=\"_blank\">Introducing Amazon RDS – The Amazon Relational Database Service</a> and told you that:</p>\n<p>RDS makes it easier for you to set up, operate, and scale a relational database in the cloud. You get direct database access without worrying about infrastructure provisioning, software maintenance, or common database management tasks.</p>\n<p>Since that launch we have continued to do our best to help you to avoid all of those items, while also working to make RDS ever-more cost effective. For example, we recently launched <a href=\"https://aws.amazon.com/blogs/aws/new-amazon-rds-on-graviton2-processors/\" target=\"_blank\">Graviton2 DB Instances</a> that deliver up to 52% better price/performance and a new <a href=\"https://aws.amazon.com/blogs/aws/amazon-rds-multi-az-db-cluster/\" target=\"_blank\">Multi-AZ Deployment Option</a> that delivers up to 33% better price/performance along with 2x faster transaction commit latency.</p>\n<p>Today I would like to tell you about two new features that will accelerate your <a href=\"https://aws.amazon.com/rds/mysql/\" target=\"_blank\">Amazon RDS for MySQL</a> workloads:</p>\n<p><strong>Amazon RDS Optimized Reads</strong> achieve faster query processing by placing temporary tables generated by MySQL on NVMe-based SSD block storage that is physically connected to the host server. Queries that use temporary tables, such as those involving sorts, hash aggregations, high-load joins, and Common Table Expressions (CTEs) can execute up to 50% faster with Optimized Reads.</p>\n<p><strong>Amazon RDS Optimized Writes</strong> deliver an improvement of up to 2x in write transaction throughput at no extra charge, and with the same level of provisioned IOPS. Optimized Writes are a great fit for write-heavy workloads that generate lots of concurrent transactions. This includes digital payments, financial trading platforms, and online games.</p>\n<h3><a id=\"Amazon_RDS_Optimized_Reads_13\"></a><ins>Amazon RDS Optimized Reads</ins></h3>\n<p>Amazon RDS for MySQL without Optimized Reads places temporary tables on <a href=\"https://aws.amazon.com/ebs/\" target=\"_blank\">Amazon Elastic Block Store (Amazon EBS)</a> volumes. Optimized Reads offload the operations on temporary objects from EBS to the instance store attached to <strong>r5d, m5d, r6gd</strong> and <strong>m6gd</strong> instances. As a result EBS volumes can be more efficiently utilized for reads and writes on persistent data, as well as background operations such as flushes, insert buffer merges, and so forth. This increased efficiency is (of course) always nice to have, but it is particularly beneficial for certain use cases:</p>\n<ul>\n<li><strong>Analytical Queries</strong> that include Complex Table Expressions, derived tables, and grouping operations.</li>\n<li><strong>Read Replicas</strong> that handle the unoptimized queries for an application.</li>\n<li><strong>On-Demand or Dynamic Reporting Queries</strong> with complex operations such as <code>GROUP BY</code>and <code>ORDER</code> BY that can’t always use appropriate indexes.</li>\n<li><strong>Other Workloads</strong> that use internal temporary tables.</li>\n</ul>\n<p>You can monitor the MySQL status variable<code>created_tmp_files</code><br />\nto observe the rate of creation for temporary tables.</p>\n<p>The amount of instance storage available on the instance varies by instance family and size. Here’s a guide:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/846b6676db6b4eb8af662e43bcedc1c2_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Using_Optimized_Reads_28\"></a>Using Optimized Reads</h3>\n<p>To take advantage of this new feature, choose MySQL engine version 8.0.28 or newer and launch Amazon RDS for MySQL on one of the instance types listed above:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/1e701bbb1f424257beb783c45c658212_image.png\" alt=\"image.png\" /></p>\n<p>You can monitor the use of instance storage by watching new CloudWatch metrics including <strong>FreeLocalStorage, ReadIOPSLocalStorage, WriteIOPSLocalStorage</strong>, and so forth (see the <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-metrics.html\" target=\"_blank\">User Guide</a> for a complete list of new and existing metrics).</p>\n<h3><a id=\"Amazon_RDS_Optimized_Writes_35\"></a><ins>Amazon RDS Optimized Writes</ins></h3>\n<p>By default, MySQL uses an on-disk doublewrite buffer that serves as an intermediate stop between memory and the final on-disk storage. Each page of the buffer is 16 KiB but is written to the final on-disk storage in 4 KiB chunks. This extra step maintains data integrity, but also consumes additional I/O bandwidth. When running those write-heavy workloads that I described earlier, this might require provisioning of additional IOPS to meet your performance and throughput requirements.</p>\n<p>Optimized Writes uses uniform 16 KiB database pages, file system blocks, and operating system pages, and writes them to storage atomically (all or nothing), resulting in the performance improvement of up to 2x that I mentioned earlier.</p>\n<h3><a id=\"Using_Optimized_Writes_40\"></a><ins>Using Optimized Writes</ins></h3>\n<p>You must create a new DB Instance from scratch on a <strong>db.r5b</strong> or <strong>db.r6i</strong> instance with the latest version of MySQL 8.0 in order to make use of Optimized Writes:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/35fb99d3f40b418495a56988c6bb834f_image.png\" alt=\"image.png\" /></p>\n<p>This setting affects the format of DB snapshots, with two important consequences:</p>\n<ol>\n<li>You cannot restore an existing non-optimized snapshot to a new, optimized one in order to enable Optimized Writes.</li>\n<li>Restoring a snapshot that was made with optimization enabled will enable Optimized Writes in the new instance.</li>\n</ol>\n<p>If you scale to an instance type that does not support Optimized Writes, Amazon RDS will enable MySQL’s doublewrite mode on the instance as a fallback. If you scale into an instance that supports Optimized Writes from one that does not, Amazon RDS will launch MySQL in doublewrite mode, wait for the recovery and log replay to complete, and then relaunch MySQL with doublewrite disabled.</p>\n<p>Optimized Writes are now available in the US East (Ohio, N. Virginia), US West (Oregon), Asia Pacific (Singapore, Tokyo), and Europe (Frankfurt, Ireland, Paris) Regions and you can start to benefit from them today!</p>\n<p>— <a href=\"https://twitter.com/jeffbarr\" target=\"_blank\">Jeff</a>;</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/2d6262765ef94846a4d5749f785acade_image.png\" alt=\"image.png\" /></p>\n<h3><a id=\"Jeff_Barrhttpsawsamazoncomblogsawsauthorjbarr_61\"></a><strong><a href=\"https://aws.amazon.com/blogs/aws/author/jbarr/\" target=\"_blank\">Jeff Barr</a></strong></h3>\n<p>Jeff Barr is Chief Evangelist for Amazon Web Services. He started this blog in 2004 and has been writing posts just about non-stop ever since.</p>\n"}