Toad World Blog

A first look at Amazon Aurora with PostgreSQL compatibility – Benefits and Drawbacks – Part VII

Aug 2, 2017 12:43:00 PM by Porus Homi Havewala

In the previous parts of this article series, we started to look at Amazon AWS and its Relational Database Service (Amazon RDS); in particular, Amazon’s own Aurora database with PostgreSQL compatibility.

We examined in detail the benchmark results for PgBench and SysBench. We then had a look at the write IO traffic in an Amazon Aurora Database Node and saw for ourselves the improvements in the write flow.

Next, we examined the storage node architecture and found the “magic” behind the benchmark improvements. We also looked at the IO traffic in the read replicas as supported by Amazon Aurora. Since there were almost no writes being done on the replica, more resources were available to support read queries. This means the replicas can do more read work.

We will now look at the database cache and how it stays warm in the case of Aurora. In a traditional database, the database cache normally exists in the memory so long as the operating system database processes exist. When the database is shutdown or aborts, the database processes will cease and the database cache will also be lost.

However, what happens in Aurora? The scenario of a running database, followed by a crash and restart, and what happens to the cache can be seen in the diagram below.

7318.pp1.png-1100x19998

In the case of Aurora, the cache is moved out in the sense that it exists independently and is not reliant on the database processes. The cache will remain warm in the event of a database restart.

As a result, the database can resume fully loaded operations much faster. So the applications using Aurora databases can also restart faster due to the database caches that survive crashes.

 

Performance Insights

We will now examine the new “Performance Insights” recently released (2016) by AWS. This is a new cloud-based tool that is designed to help you to understand database performance at a very detailed level. You will be able to look inside of each query and learn more about how your database handles it.

Since Amazon RDS implies “managed” databases, customers who did not have deep tuning expertise requested an easy tool for performance management of databases, in a single pane of glass.

Other products like Oracle Enterprise Manager had been doing this for many years for Oracle databases, since the Oracle database was very complex and had many rich tuning possibilities. However they were on-premises installations of Enterprise Manager that looked at on-premise Oracle databases, and later at Oracle public cloud databases in a “hybrid” single pane of glass. On the cloud, Oracle decided to develop a new product known as the Oracle Management Cloud, rather than use Enterprise Manager itself.  

So the new Amazon Performance Insights likewise automatically samples performance data and shows the database load in a GUI screen. Many people on LinkedIn have remarked that the screens look quite familiar, reminding them of Oracle Enterprise Manager’s Diagnostic Pack screens for the Oracle database.

2133.pp2.png-1100x19998

The Performance Insights capability is included with RDS at no extra cost, and allows performance data (metric) retention up to 35 days. It will also help in database lock detection, display execution plans for the SQL queries, and allow API access to the metrics.

In the next and final part of this article series, we will look at more aspects of Performance Insights.

(Disclaimer: The views expressed in this article are the author’s own views and do not reflect the views of Amazon Web Services (AWS) or Oracle Corporation. The author has written this article as an independent, unbiased cloud architect/consultant. The information in this article is from AWS public presentations.)

Tags: PostgreSQL

Porus Homi Havewala

Written by Porus Homi Havewala

Porus Homi Havewala is a Double Oracle Certified Master (OCM) in 11g and 10g. He was awarded the prestigious "Oracle ACE Director" title by Oracle USA in 2008. He worked as the Strategic Program Advisor at Oracle Corporation Singapore, and is a regional SME on Oracle Enterprise Manager technology, specifically concentrating on private/hybrid Database cloud capabilities on Oracle systems.

Porus has worked prominently for a number of years at the largest telecommunications company Telstra in Australia, where he personally set up and managed the first production Enterprise Manager Grid Control 10g project in the world in the early 2000s, controlling thousands of Data Center targets with multiple Administrator teams around Australia. He has 26+ years of experience in the IT industry, and 18+ years of experience with Oracle Technology, including as an Oracle Developer and Oracle Senior DBA.

He is the creator and manager of the Enterprise Manager blog with Oracle Press Credentials, the Clouds and Databases blog, and the "Oracle Senior DBA" group on LinkedIn.com with 61,000+ members. Porus is the author of thirteen published technical articles and three white papers on Enterprise Manager in the Oracle Technical Network (OTN), and has authored a number of Enterprise Manager Books. He started his technical writing with a book on Microsoft Foxpro as early as 1994. His recent Enterprise Manager books are as follows: Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos published by PACKT Publishing, and the latest book Oracle Database Cloud Cookbook with Oracle Enterprise Manager 13c Cloud Control published by Oracle Press.

Dedication: I dedicate my pen and all its output to my beloved parents in heaven, they are the ones that have taught me the good values in life.

Disclaimer: My writings in blog entries, articles or books express my views and opinions in my personal capacity and not that of Oracle Corporation.