aurora mysql or regular mysql for AWS RDS startup postgres usage

• AWS offers two managed database services: Amazon RDS and Amazon Aurora.
  • Aurora is a fully managed, MySQL and PostgreSQL-compatible relational database engine designed to deliver up to five times the performance of MySQL and three times the performance of PostgreSQL.
    • Key features of Aurora include:
      • automatic storage scaling
      • high availability with up to 15 read replicas
      • continuous backup to Amazon S3
      • fast database cloning
      • backtrack for rewinding the database to a previous point in time
      • serverless mode for on-demand capacity
      • global database for cross-region replication
  • RDS is a managed relational database service that supports six database engines: MySQL, PostgreSQL, MariaDB, Oracle, Microsoft SQL Server, and Amazon Aurora.
    • Key features of RDS include:
      • automatic backups
      • Multi-AZ deployments for high availability
      • read replicas for read-heavy workloads
      • database snapshots
      • automatic host replacement
      • monitoring and performance insights
  • Aurora pricing is based on the instance class, storage, I/O, and backup storage. There are two pricing models: On-Demand and Reserved Instances.
  • RDS pricing depends on the database engine, instance class, storage, I/O, and backup storage. Similar to Aurora, RDS offers On-Demand and Reserved Instances pricing models.
  • Aurora is an AWS-native service, which means it’s tightly integrated with other AWS services like Lambda, CloudWatch, and CloudFormation.
    • The community around Aurora is growing, with numerous blog posts, tutorials, and case studies available.
  • RDS supports multiple database engines, which means it benefits from the larger community and ecosystem around each engine.
    • RDS also integrates well with other AWS services.
  • Both Aurora and RDS are fully managed services, which means AWS handles the underlying infrastructure, patching, backups, and monitoring.
    • AWS provides support through various channels, including documentation, forums, and paid support plans.
  • Aurora is designed for high scalability, with automatic storage scaling up to 128 TB and support for up to 15 read replicas.
    • Aurora also offers a serverless mode, which automatically adjusts capacity based on workload.
  • RDS provides vertical scaling by changing the instance class and horizontal scaling with read replicas.
    • However, RDS doesn’t offer automatic storage scaling or serverless mode like Aurora.
  • Aurora supports up to 15 read replicas, with sub-10ms replica lag.
    • Aurora Global Database enables cross-region replication for disaster recovery and low-latency global reads.
  • RDS supports read replicas for MySQL, PostgreSQL, and MariaDB, with a maximum of five replicas.
    • Cross-region replication is available for MySQL and PostgreSQL.
  • Aurora provides automatic failover with Multi-AZ deployments and read replicas.
    • Aurora also offers backtrack, which allows you to rewind the database to a previous point in time without restoring from a backup.
  • RDS supports automatic failover with Multi-AZ deployments.
    • However, RDS does not offer a backtrack feature like Aurora.

• Amazon Aurora is a relational, proprietary, closed-source database engine, while RDS MySQL is 5.5, 5.6, and 5.7 compatible and offers the option to select among minor releases.
  • In most cases, no significant application changes are required for either product
    • However, certain MySQL features like the MyISAM storage engine are not available with Amazon Aurora
• Aurora instances will cost you ~20% more than RDS MySQL.
  • Storage pricing may be a bit tricky. For Aurora, IOPs are only limited by the instance type
• If you are looking for a native HA solution then you should use Aurora
  • For a read-intensive workload within an HA environment, Aurora is a perfect match. Combined with ProxySQL for RDS you can get high flexibility
    • Aurora performance is great but is not as much as expected for write-intensive workloads when secondary indexes exist.
  • RDS MySQL is not an HA solution, so you have to mark the master as Multi-AZ and attach the endpoints.
• Both products can be monitored with a variety of monitoring tools.
  • Percona Monitoring and Management (PMM) can also be used to gather metrics.
• Aurora allows you to provision up to fifteen replicas compared to just five in RDS MySQL. Failover is automatic with no data loss on Amazon Aurora.
• For both products, you should carefully examine the known issues and limitations listed on the respective documentation pages.
  • For Aurora, Performance Schema should not be enabled on T2 instances, as it can cause the instance to run out of memory.
• None

• Aurora and RDS are both Amazon Web Services (AWS) databases.
  • Aurora is recommended for bursty workloads whereas RDS is for sustained throughput.
• For workloads that are unpredictable or intermittent, Aurora Serverless may be a more cost-effective option.
  • Aurora Serverless is designed to scale to meet the workload’s needs without human intervention.
  • In comparison, “regular” Aurora gives you managed updates, scaling, separate data and compute plane, etc.
• Athena serverless is for PSQL batch processing with data on S3, bursty workloads (OLAP), whereas Redshift is for OLAP and OLTP on production PSQL with tons of data in the PSQL.
• For steady-state workloads, serverless rather quickly becomes prohibitively expensive.
• Aurora Serverless is documented to have a recovery time of “undefined” in the event of an AZ failure.

• Relational databases in AWS:
  • Amazon RDS
  • Amazon Aurora
• Both are managed services that let you spin up databases easily, but Aurora is a cloud-first architecture.
• RDS aims to reduce the management overhead of running a database in EC2.
• Aurora creates multiple compute and storage nodes, enabling it to scale faster with more replicas, greater durability, and faster recovery from failures.
• Aurora Serverless enables users to run an Aurora database without having to guess the number of compute nodes required.
• Database engines supported: RDS supports five but Aurora only two (MySQL and PostgreSQL).
  • Aurora only uses the InnoDB storage engine; other storage engines cannot be used.
• Aurora is cheaper and faster for some workloads, but for others, it might be slower.
• If you require sustained workloads, choose regular Aurora or RDS instead.
• Aurora pricing is more difficult to predict because in addition to hourly instance cost, it charges per million I/O operations.
• When to choose RDS over Aurora:
  • You use a database engine that Aurora doesn’t support.
  • You want to use AWS Free Tier.
  • You want predictable database costs.
  • Your workloads are not a good fit for Aurora.
• Aurora can be a big win over RDS, but it’s not a slam dunk.

• Relevant info from the webpage:

  • A user was trying to upgrade from Aurora MySQL 5.7 to Aurora MySQL 8.0 but couldn’t find that option in the drop-down DB engine version menu when going into the modify instances screen.

  • The upgrade from Aurora MySQL 5.6 to Aurora MySQL 5.7 was available in the drop down DB engine version menu when going into the modify instances screen.

  • According to the docs, Amazon RDS supports the in-place upgrades for major versions of the MySQL database engine: MySQL 5.5 to MySQL 5.6, MySQL 5.6 to MySQL 5.7, MySQL 5.7 to MySQL 8.0 for regular (non-Aurora) RDS MySQL.

  • Aurora MySQL 8.0 does not currently exist, 5.7 is the latest version for Aurora MySQL.

  • The doc for major version upgrades only applies to regular RDS MySQL, not Aurora MySQL.

💭  Looking into

What is the most recent benchmark test comparing Aurora MySQL and RDS MySQL?

💭  Looking into

What other factors should be taken into account when deciding between Aurora MySQL and RDS MySQL?

💭  Looking into

What are the features of Aurora MySQL 5.7 that are not available with RDS MySQL 8?

💭  Looking into

What is the cost difference between RDS MySQL 8 and Aurora MySQL 5.7?

💭  Looking into

What are the specific advantages of using RDS MySQL 8 over Aurora MySQL 5.7?

• Relevant webpage: https://www.reddit.com/r/aws/comments/l53rgs/aws_aurora_vs_rds_when_to_use_which_one/

  • Question: “aws aurora vs rds when to use which one?” - 2 years ago - r/aws - 2 points

    • User has load spikes at certain times but almost never active in weeks.

    • Is aurora better or is it going to be more expensive even on idle time?

    • If user picks rds but worried about spikes (100-500 users in 1-5 hours), what would be the best rds instance then?

    • Is it possible to increase iops in rds without provision iops?

  • Comments:

    • Which DB engine (Postgres or mysql)?

      • Are you referring to the aurora equivalents of those, or aurora serverless?

    • User wanted to use Postgres standard in aurora, or they could keep using rds.

      • Trying to see if being idle most of the time save postgres cost in rds

      • Smallest Aurora instance is a t3.medium, while normal RDS can go lower.

      • If your DB is going to be idle most of the time, and you are able to use Postgres 10, Aurora Serverless might be the cheapest and best candidate.

        • It can scale down when not being actively used, and then automatically scale back up when demand increases.

      • Serverless is cheaper than rds and the non-serverless aurora.

      • Can use AWS pricing calculator to estimate the cost. It can give you a feel on how much each option will cost you.

      • For RDS, smallest instance seems to be db.t3.micro (2 vCPU, 1GB). With 30 GB storage, it comes to 12.87 per month.

      • Both Aurora standard and serverless will be way more than that (~50$), but you get more power.

      • You will need to check your requirements and constraints and see what options offer the best value.

    • Instead of sizing your DB to bursts, then pretzel bending yourself to your existing use case, look at decoupling white papers to absorb load.

None

• Reddit discussion on “Aurora MySQL vs Aurora Postgres”
  • Postgres is superior to MySQL in every aspect except replication/failover.
  • MySQL is good for online transactions, while PostgreSQL is good for append-only, analytical processes such as data warehousing.
  • Aurora makes replication/failover a non-issue.
  • For highest volume OLTP workloads, Postgres is preferred.
  • MySQL serverless doesn’t have common features like CTE and Window functions.
  • Postgres scales better at a very large scale, while MySQL has built-in clustering for medium-scale workloads through Galera.
  • Resource naming limits might be a disadvantage for Postgres.
  • The workload for the Aurora instance is expected to be around 240 million updates a day.

• For a startup set-up with Postgres usage on AWS, the choice is between RDS MySQL 8 or Aurora with an older version of MySQL 5.7.
  • MySQL 8 has advantages like Atomic DDL migrations and the new INSTANT algorithm, plus various other optimizations.
  • Aurora is best for high transaction rates if there are a lot of queries happening at the same time. Reader nodes on Aurora have near zero replication lag.
  • Aurora promises automatic failover with almost zero data loss.
• Multi-AZ standard RDS has very low data loss chances too, with physical synchronous replicated storage.
  • In multi AZ regular RDS, the backup instance is hidden and not available to anyone, but with Aurora, it’s a cluster with a writer and readers, and one of the readers is promoted to writer in case of failover, so readers can be used too, making it a little cheaper. Application layer integration needed to use reader endpoints.
• There is no MySQL 8 for Aurora - Aurora only supports 5.7.
• If an analytics tool or something with a heavy read requirement is utilized, Aurora is recommended, otherwise MySQL 8.
• AWS’ re:Invent may announce Aurora MySQL 8.0 in a few months.

• Amazon RDS Aurora vs RDS MySQL vs MySQL on EC2?
  • Benchmark Aurora before considering it
    • High rates of write traffic caused Aurora to fail spectacularly, 2 orders of magnitude slower than RDS
    • Conclusion: not suitable if you have high write traffic and secondary indexes
    • It might be good for read-only traffic though
  • Recommended RDS if you don’t have dedicated DBA staff, as it provides automation for DB operations like upgrades and backups
    • Sacrifice a bit of flexibility on tuning options, but it shouldn’t be a problem
    • Can’t have a MySQL user with SUPER privilege, but RDS offers stored procs for most common tasks
  • Compared a multi-AZ RDS instance vs a replica set of EC2 instances, managed by Orchestrator
    • Orchestrator requires three nodes for quorum, making RDS the clear winner on cost, as well as ease of setup and operations
  • Testing was done in Q1 of 2017
    • As with most AWS services, Aurora is expected to improve over time
    • New AWS products are worth testing but probably not production-ready for a few years after introduction
• None

• Aurora and regular RDS are two options for running a database on AWS RDS
  • Aurora provides scalability from 0 to 11, meaning it can scale down to 0 and cost nothing to use, but it can take a bit of time to start up again; although it is optimized for a specific workload, not many people actually need Aurora
  • Regular Aurora is a custom SQL engine that is wire-compatible with MySQL and Postgres, but with some advantages such as being faster on the same CPUs, horizontal disk storage scaling, faster failover and scaling, and more
  • Aurora provides some features not found in RDS, such as automatic storage expansion, global tables, multi-master option, serverless option, backtrack (in-place rewind of the database state), higher availability due to a single node being replicated across 3 AZs, 18 read replicas, multi-region replication, auto-failover, trigger to lambda, and consistent millisecond lag on readers
  • One user’s experience showed that using Aurora to replace an RDS database increased their monthly bill from $30 to $300, likely due to the expensive IOPS pricing in Aurora.

💭  Looking into

What is the best option for AWS RDS startup postgres usage: Aurora MySQL or regular MySQL?