6 ms·

Notes on the Amazon Aurora Paper

139 points by aptxkid 8y ago

deleted 8y ago
[deleted]
danielecook 8y ago
I don’t understand how aurora achieves the speed it does with a log based approach. Can someone please clarify?
- dmlittle 8y ago
  Last time we benchmarked Aurora (~2 years ago) the write speed of Aurora is pretty slow compared to RDS (Postgres RDS was able to achieve 3x write throughput)
  - danielecook 8y ago
    I never did a thorough benchmark but I was working with a poorly indexed DB (actually...no indexes) that had millions of records and despite the lack of indices the database still queried quickly.
    - dmlittle 8y ago
      Read speeds are pretty good but write speeds not so much. For our particular use case 99.99% of queries ran would have been inserts with reports only generated once per month.
  - misframer 8y ago
    What was the concurrency like in those benchmarks?
    - dmlittle 8y ago
      I don't remember as this was 2 years ago. We were only concerned with write speeds as that was the majority of queries we would be performing. Read speeds were pretty good.
  - PetahNZ 8y ago
    Anecdotally after migrating from RDS MySQL to serverless Aurora there was a noticeable slowdown of our dashboard and reporting tools. Our typical workloads (ecommerce transactions) are slightly slower on average, but the peaks seem down.
    - sciurus 8y ago
      Serverless Aurora likely has different performance characteristics than normal Aurora.
- wgjordan 8y ago
  > I don’t understand how aurora achieves the speed it does with a log based approach. Can someone please clarify? Aurora splits out 'database' nodes (the server instances you provision and pay for) from 'storage' nodes (a 'multi-tenant scale-out storage service' that automatically performs massively-parallel disk I/O in the background). Instead of MySQL writing various data to tablespaces, redo log, double-write buffer, and binary log, Aurora sends only the redo-log over the network to the storage service (in parallel to 6 nodes/3 AZs for durability). No need for extra tablespace, double-write buffer, binary-log writes, or extra storage-layer mirroring, since durability is guaranteed as soon as a quorum of storage nodes receives the redo-log. The reduced write amplification results in 7.7x fewer network IOs per transaction at the 'database' layer for Aurora (vs standard MySQL running on EBS networked storage, in the benchmark described in the paper), and 46x fewer disk IOs at the 'storage' layer [1]. [1] https://www.allthingsdistributed.com/files/p1041-verbitski.pdf#page=4 https://www.allthingsdistributed.com/files/p1041-verbitski.p...
  - gigatexal 8y ago
    That’s an impressive savings in io
wgjordan 8y ago
> But here in the paper, it compares Aurora to a MySQL synchronous mirroring setup, which is really unfair, IMO. Why the active primary has to replicate everything over the wire to the active standby? Doesn't MySQL's replication support Statement-Based Replication? The comparison in the paper is against the synchronous-mirroring pattern underlying RDS Multi-AZ [1], which is the existing fully-managed, high-availability MySQL solution Amazon offers, so the direct comparison is appropriate for existing RDS Multi-AZ users considering a migration to Aurora. [1] https://aws.amazon.com/blogs/database/amazon-rds-under-the-hood-multi-az/ https://aws.amazon.com/blogs/database/amazon-rds-under-the-h...
- aptxkid 8y ago
  Thanks! That makes much more sense!
ddorian43 8y ago
Everything is faster when you move your data to ebs \s. Though google has done this for all databases (at least their internal ones) (even bigtable). And some companies say they can do it for nvme & ram. You just need real-good networking and some type of asic and removing kernel from the path.
ddorian43 8y ago
For something open-source like this, look at tikv(transactional key-value (rust)) + tidb(query layer (go)).
matharmin 8y ago
Separating the storage layer from the database and query layer seems to be the future of databases - there's no reason for every database to re-implement the storage and replication layer just to provide a different query language or data model. This is also what makes FoundationDB so attractive for me. Are there any other large projects doing this?
- Artemis2 8y ago
  CockroachDB is made up of several layers to implement SQL and transactions on top of a distributed key-value store: https://www.cockroachlabs.com/docs/stable/architecture/overview.html#layers https://www.cockroachlabs.com/docs/stable/architecture/overv... TiDB has a similar architecture: https://www.pingcap.com/docs/architecture/ https://www.pingcap.com/docs/architecture/
  - gigatexal 8y ago
    The TiDB approach is a lot more like Aurora though as you have much finer grained controls over how many storage and query nodes you have.
- qaq 8y ago
  Depends on the use-case you can look at performance of say ClickHouse vs alternatives which separate storage layer. The performance difference is fairly significant.
- LoSboccacc 8y ago
  CosmoDb has a similar approach but still a long way to go
- deleted 8y ago
  [deleted]
- jandrewrogers 8y ago
  People often greatly underestimate the performance loss incurred by separating the storage layer from the execution layer. The difference is an integer factor. In a high-performance database design, the execution scheduling behavior must be tightly and intricately coupled to the I/O operation scheduling behavior or you lose much low-hanging throughput optimization. That said, this can be solved by changing the level of abstraction. If you tightly coupled the execution and storage engine as a single module, which is not intrinsically dependent on the type of data model, it would allow high-performance multi-use within reasonable constraints. Albeit with a very different API than a storage engine. But that is not a thing that exists in open source AFAIK. The loss from separating storage and execution may not matter for databases where operational efficiency is not paramount (e.g. because the scale is too small for it to matter) but many database engine designers are not going to take that use case limiting performance hit because the CapEx/OpEx implications are large.
eecc 8y ago
Funny how the CQRS circle closes: it is an architectural design that overhauls some concepts of database design (the redo log, views) into the application layer (for some, to the extreme of reducing the database to a simple log.) And now this same separation is appearing in a DBMS design :)
- tirumaraiselvan 8y ago
  As per my understanding, CQRS fits here if you consider the database log as the "event source". But wouldn't it be terrible performance wise to create the "view" from the event source rather than using the storage layer.
  - ricardobeat 8y ago
    I think there’s a parallel with how apps keep a trailing materialied view of the data instead of re-creating from the log, unless necessary.
ralusek 8y ago
Given this architecture, do we know why Aurora caps at 64TB max size?
kakwa_ 8y ago
I'm wondering how aurora would behave with queries mixing read and write. For example, with something like "INSERT INTO my_table(field1, field2, field3, ...) SELECT f1, f2, f3... from root_table WHERE ...", I'm wondering how it will spread the load across nodes.
benmmurphy 8y ago
One of the neat things about aurora is there is a very small slave lag between the primary and read replicas. If you only have a single thread doing replication then you can easily have a situation where a multi-cpu master can get ahead of a slave only using a single thread. Lazily materializing the pages gives aurora 'parallel replication' for free. I think Mysql supports parallel replication now but I think it is limited if transactions conflict [https://mariadb.com/kb/en/library/parallel-replication/ https://mariadb.com/kb/en/library/parallel-replication/]. Another thing I noticed with Aurora is the incremental cost of storage for Aurora is extremely cheap compared to the cost of storage in EBS or the cost of storage if you used instance storage in EC2. The Aurora storage is 0.1/GB but this is replicated 6 times which is more than EBS and EBS costs the same 0.1/GB. This also might be why no-one is going to build a similar system to Aurora. It will be hard to sell something like Aurora to cloud users because the storage costs are going to more than what EC2 charges.
anentropic 8y ago
dumb question which maybe has a well-known answer: has anyone done the same thing but for postgres instead of mysql?
- a-robinson 8y ago
  Amazon - https://aws.amazon.com/rds/aurora/details/postgresql-details/ https://aws.amazon.com/rds/aurora/details/postgresql-details...
  - anentropic 8y ago
    aha cheers! LOL
    - mcheshier 8y ago
      Be careful though - we use Aurora PG and it's great for what it does, but they do not support managed upgrades across major PG versions yet! We're stuck on 9.6.x because the time to dump and restore our large DB is a non-starter with the rest of the business.
      - foxylion 8y ago
        Did you try to upgrade using AWS database migration service?

139 points by aptxkid 8y ago

deleted 8y ago

[deleted]

danielecook 8y ago

I don’t understand how aurora achieves the speed it does with a log based approach. Can someone please clarify?

dmlittle 8y ago
Last time we benchmarked Aurora (~2 years ago) the write speed of Aurora is pretty slow compared to RDS (Postgres RDS was able to achieve 3x write throughput)
- danielecook 8y ago
  I never did a thorough benchmark but I was working with a poorly indexed DB (actually...no indexes) that had millions of records and despite the lack of indices the database still queried quickly.
  - dmlittle 8y ago
    Read speeds are pretty good but write speeds not so much. For our particular use case 99.99% of queries ran would have been inserts with reports only generated once per month.
- misframer 8y ago
  What was the concurrency like in those benchmarks?
  - dmlittle 8y ago
    I don't remember as this was 2 years ago. We were only concerned with write speeds as that was the majority of queries we would be performing. Read speeds were pretty good.
- PetahNZ 8y ago
  Anecdotally after migrating from RDS MySQL to serverless Aurora there was a noticeable slowdown of our dashboard and reporting tools. Our typical workloads (ecommerce transactions) are slightly slower on average, but the peaks seem down.
  - sciurus 8y ago
    Serverless Aurora likely has different performance characteristics than normal Aurora.
wgjordan 8y ago
> I don’t understand how aurora achieves the speed it does with a log based approach. Can someone please clarify? Aurora splits out 'database' nodes (the server instances you provision and pay for) from 'storage' nodes (a 'multi-tenant scale-out storage service' that automatically performs massively-parallel disk I/O in the background). Instead of MySQL writing various data to tablespaces, redo log, double-write buffer, and binary log, Aurora sends only the redo-log over the network to the storage service (in parallel to 6 nodes/3 AZs for durability). No need for extra tablespace, double-write buffer, binary-log writes, or extra storage-layer mirroring, since durability is guaranteed as soon as a quorum of storage nodes receives the redo-log. The reduced write amplification results in 7.7x fewer network IOs per transaction at the 'database' layer for Aurora (vs standard MySQL running on EBS networked storage, in the benchmark described in the paper), and 46x fewer disk IOs at the 'storage' layer [1]. [1] https://www.allthingsdistributed.com/files/p1041-verbitski.pdf#page=4 https://www.allthingsdistributed.com/files/p1041-verbitski.p...
- gigatexal 8y ago
  That’s an impressive savings in io

wgjordan 8y ago

> But here in the paper, it compares Aurora to a MySQL synchronous mirroring setup, which is really unfair, IMO. Why the active primary has to replicate everything over the wire to the active standby? Doesn't MySQL's replication support Statement-Based Replication? The comparison in the paper is against the synchronous-mirroring pattern underlying RDS Multi-AZ [1], which is the existing fully-managed, high-availability MySQL solution Amazon offers, so the direct comparison is appropriate for existing RDS Multi-AZ users considering a migration to Aurora. [1] https://aws.amazon.com/blogs/database/amazon-rds-under-the-hood-multi-az/ https://aws.amazon.com/blogs/database/amazon-rds-under-the-h...

aptxkid 8y ago
Thanks! That makes much more sense!

ddorian43 8y ago

Everything is faster when you move your data to ebs \s. Though google has done this for all databases (at least their internal ones) (even bigtable). And some companies say they can do it for nvme & ram. You just need real-good networking and some type of asic and removing kernel from the path.

ddorian43 8y ago

For something open-source like this, look at tikv(transactional key-value (rust)) + tidb(query layer (go)).

matharmin 8y ago

Separating the storage layer from the database and query layer seems to be the future of databases - there's no reason for every database to re-implement the storage and replication layer just to provide a different query language or data model. This is also what makes FoundationDB so attractive for me. Are there any other large projects doing this?

Artemis2 8y ago
CockroachDB is made up of several layers to implement SQL and transactions on top of a distributed key-value store: https://www.cockroachlabs.com/docs/stable/architecture/overview.html#layers https://www.cockroachlabs.com/docs/stable/architecture/overv... TiDB has a similar architecture: https://www.pingcap.com/docs/architecture/ https://www.pingcap.com/docs/architecture/
- gigatexal 8y ago
  The TiDB approach is a lot more like Aurora though as you have much finer grained controls over how many storage and query nodes you have.
qaq 8y ago
Depends on the use-case you can look at performance of say ClickHouse vs alternatives which separate storage layer. The performance difference is fairly significant.
LoSboccacc 8y ago
CosmoDb has a similar approach but still a long way to go
deleted 8y ago
[deleted]
jandrewrogers 8y ago
People often greatly underestimate the performance loss incurred by separating the storage layer from the execution layer. The difference is an integer factor. In a high-performance database design, the execution scheduling behavior must be tightly and intricately coupled to the I/O operation scheduling behavior or you lose much low-hanging throughput optimization. That said, this can be solved by changing the level of abstraction. If you tightly coupled the execution and storage engine as a single module, which is not intrinsically dependent on the type of data model, it would allow high-performance multi-use within reasonable constraints. Albeit with a very different API than a storage engine. But that is not a thing that exists in open source AFAIK. The loss from separating storage and execution may not matter for databases where operational efficiency is not paramount (e.g. because the scale is too small for it to matter) but many database engine designers are not going to take that use case limiting performance hit because the CapEx/OpEx implications are large.

eecc 8y ago

Funny how the CQRS circle closes: it is an architectural design that overhauls some concepts of database design (the redo log, views) into the application layer (for some, to the extreme of reducing the database to a simple log.) And now this same separation is appearing in a DBMS design :)

tirumaraiselvan 8y ago
As per my understanding, CQRS fits here if you consider the database log as the "event source". But wouldn't it be terrible performance wise to create the "view" from the event source rather than using the storage layer.
- ricardobeat 8y ago
  I think there’s a parallel with how apps keep a trailing materialied view of the data instead of re-creating from the log, unless necessary.

ralusek 8y ago

Given this architecture, do we know why Aurora caps at 64TB max size?

kakwa_ 8y ago

I'm wondering how aurora would behave with queries mixing read and write. For example, with something like "INSERT INTO my_table(field1, field2, field3, ...) SELECT f1, f2, f3... from root_table WHERE ...", I'm wondering how it will spread the load across nodes.

benmmurphy 8y ago

One of the neat things about aurora is there is a very small slave lag between the primary and read replicas. If you only have a single thread doing replication then you can easily have a situation where a multi-cpu master can get ahead of a slave only using a single thread. Lazily materializing the pages gives aurora 'parallel replication' for free. I think Mysql supports parallel replication now but I think it is limited if transactions conflict [https://mariadb.com/kb/en/library/parallel-replication/ https://mariadb.com/kb/en/library/parallel-replication/]. Another thing I noticed with Aurora is the incremental cost of storage for Aurora is extremely cheap compared to the cost of storage in EBS or the cost of storage if you used instance storage in EC2. The Aurora storage is 0.1/GB but this is replicated 6 times which is more than EBS and EBS costs the same 0.1/GB. This also might be why no-one is going to build a similar system to Aurora. It will be hard to sell something like Aurora to cloud users because the storage costs are going to more than what EC2 charges.

anentropic 8y ago

dumb question which maybe has a well-known answer: has anyone done the same thing but for postgres instead of mysql?

a-robinson 8y ago
Amazon - https://aws.amazon.com/rds/aurora/details/postgresql-details/ https://aws.amazon.com/rds/aurora/details/postgresql-details...
- anentropic 8y ago
  aha cheers! LOL
  - mcheshier 8y ago
    Be careful though - we use Aurora PG and it's great for what it does, but they do not support managed upgrades across major PG versions yet! We're stuck on 9.6.x because the time to dump and restore our large DB is a non-starter with the rest of the business.
    - foxylion 8y ago
      Did you try to upgrade using AWS database migration service?