Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Deep dive on Amazon Aurora

24 March,2022 by Rambler

Watched a good quality talk on Amazon Aurora by Richard Waymire.   Added some notes below - and some snapshots of the video.

There is a link to the full video at bottom of post 

General Summary

Postgres - Babelfish for Aurora PostgreSQL and babelfish for PostgreSQL 

             - PostgreSQL 12,13 Support

             - New Extensions:PostGIS

MySQL - MySQL 8.0 support

 

Architecture 

- Aurora always runs in 3 Availability Zones (AZ)  for reliability & scalability. An AZ is defined as " distinct locations within an AWS Region that are engineered to be isolated from failures in other Availability Zones. They provide inexpensive, low-latency network connectivity to other Availability Zones in the same AWS Region.

- Storage is in 10 GB blocks 

-Write log will trigger 6 aynchronous writes , when 4 are commited acknowledge is sent to app - defined as a quorum

Aurora_storage

- Intelligence is built into the storage tier - whereas on PostgreSQL \ MySQL it's happening at the relational level. 

- Support up to 15 read replicas per cluster . The more read replicas increases the load on the writer node

-What happens if my database crashes?   restart the host   - if host can't restart , look to initiate the read replica in another Availability Zone 

Amazon Aurora global database - designed for DR , It takes a copy of your Aurora storage and place in another Region. It is maintained  via Replication Agents . 

                                       A write insert will flow to the Replicated Aurora Storage  

Aurora_global_database

 

-Works fine in headless configuration , read replicas can also be added to the DR site . It is possible there may be some data loss as the replication is asynchronous.  Failovers are manual to allow assessment of any data loss.

-For Aurora MySQL there is a feature called Global Database Write forwarding.This needs to be enabled. It is not as a scalability feature

 

Aurora Storage Internals

Normal DBMS e.g SQL Server,Oracle  do a regular checkpoint for hardening process

In comparison , aurora - there are no checkpoints - log records are pushed down to the Aurora storage.Continious coalesce

Aurora Storage Node

Aurora_storage_node

 

Fast Clones - Currently an underused feature - but has great potential. An immediate use case is testing , daily restores to lowers systems.

Allows you to spin up another cluster , and get a copy , and then run various reports etc.One of the reasons the clone is so quick to spin up is that the storage copy is not a completely different physical data , but is a series of pointers back to the original data . The clone is giving you a version of the data as it was when you took the copy. The Primary storage continues to operate as normal.

Aurora PostgreSQL 13 new features - Improved performance on partitioned tables , parallelized vacuum for indexes, index de-duplication for B-tree indexes, faster operations

Aurora Serverless - scale instantly , serverless has parity with Aurora capabilities including read replicas , multi-AZ , Global Database

                            - Buffer pool resizing is a complex area of Aurora serverless . A particular challenge is decreasing the memory size. Resizing is scaled according to least recently used

DevOps Guru for RDS - A new machine learning powered capability for Amazon RDS that automatically detects and diagnoses performance issues. Only works on performance insights

Migration - AWS DMS \ SCT

               Postgresql: pg_dump\pg_restore

               MySQL:mysqldump

                Read replica - replication to read only and then promote 

Babelfish - Run SQL Server applications on PostgreSQL

 

Babelfish

 

What is Bablefish all about ?  It is all about Commercial migration . Normally you would look at Schema Conversion Tool (SCT) to figure out how to convert the schema , use DMS to migrate the databases and then start the hard part of changing the code. What Babelfish does is not only add compatibility with T-SQL but also adds the SQL Wire Protocol . The Babelfish project is  not trying to replicate SQL Server but try to view it as an acceleration mechanism to get you onto PostgreSQL

i.e you can use your SQL Server app to talk to Postgres via the TDS protocol . Exploiting the Postgres extension mechanism . It Postgres but Combing t-sql and driver language

You can still do the SCT\DMS combination but that would require reworking upfront

You now have an alternative which supports a partial migration - i.e bring over things as if they were SQL Server, and now you can finish the migration at a more leisurely pace. 

Opensource available on Github . In theory - the community will add t-sql features & AWS will pull back into Aurora 

 

Watch full video


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Deep dive on Amazon Aurora


dba-ninja.com