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
- 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
-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
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
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |