5 ms·

I wonder if they have any "special sauce" in running PostgreSQL on EC2, or if the real value is in the easy provisioning? We're running PG 9.0 on EC2 now via E

I wonder if they have any "special sauce" in running PostgreSQL on EC2, or if the real value is in the easy provisioning? We're running PG 9.0 on EC2 now via EBS, and everything I've read says it's tough to get it performant - even with large volumes and RAID 10, you still get spikes when your virtualization neighbors slam the disk.

notaddicted 15y ago
Considering their "Continuous Protection" feature, could they be streaming a write log out to S3? I think that way they could use the backup as the sole guarantee of durability, and use instance storage for the database. EDIT: also, I'm not sure if this is generally known elsewhere, but might they not be on ec2, they are advertising 1.7 GB cache, which is the same as a small instance, except without any room for the operating system or postgres itself.
- imbriaco 15y ago
  You're exactly right about log streaming to S3. We open sourced the tool that we use to perform that: https://github.com/heroku/wal-e https://github.com/heroku/wal-e
  - bdonlan 15y ago
    S3 doesn't support continual logging - you need to decide on a file size up-front. Are you creating a new object for each log entry, or is there a window in which commits can be lost before the log gets flushed out, or do you have some other workaround...?
    - joevandyk 15y ago
      There's a window of a couple minutes in which data could be lost (in the worst case scenario).
      - sector 15y ago
        So it's postgres log shipping, with S3 as the target?
        deleted 15y ago
        [deleted]
        joevandyk 15y ago
        Yup.
  - jaylevitt 15y ago
    So would I need to (learn and) use PG9's replication with this? Or is one ephemeral-stored database plus shipped logs sufficient, assuming I can live with some downtime if the instance disappears? As an experienced techie but a PG novice, what do I need to go learn about PG recovery procedures to safely use this?
mark_l_watson 15y ago
Someone at Heroku wrote a very useful article a few years ago on getting around the pain points of EBS, using RAID on EBS, etc. (This is probably the article I remembered:http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs/ http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs...)
- moe 15y ago
  I/O performance is still abysmal even on their largest instance type when striping over 20 EBS volumes (I benchmarked that a while back). And we still noticed a high variance in I/O performance during peak hours despite the striping. Any recent dedicated server with 16 spindles will completely annihilate EBS in terms of IOPS. And that's with plain old spinning rust - add some SSDs and EBS ain't even in the same ballpark anymore.
joevandyk 15y ago
I wouldn't use EBS. Instance storage is much more consistent, in terms of latency and performance.
- jaylevitt 15y ago
  I didn't realize that; in fact, I thought I saw something that claimed the opposite. It'd be great to see someone write up a HOWTO for WAL-E and PG9 replication on ephemeral stores in EC2.

notaddicted 15y ago

Considering their "Continuous Protection" feature, could they be streaming a write log out to S3? I think that way they could use the backup as the sole guarantee of durability, and use instance storage for the database. EDIT: also, I'm not sure if this is generally known elsewhere, but might they not be on ec2, they are advertising 1.7 GB cache, which is the same as a small instance, except without any room for the operating system or postgres itself.

imbriaco 15y ago
You're exactly right about log streaming to S3. We open sourced the tool that we use to perform that: https://github.com/heroku/wal-e https://github.com/heroku/wal-e
- bdonlan 15y ago
  S3 doesn't support continual logging - you need to decide on a file size up-front. Are you creating a new object for each log entry, or is there a window in which commits can be lost before the log gets flushed out, or do you have some other workaround...?
  - joevandyk 15y ago
    There's a window of a couple minutes in which data could be lost (in the worst case scenario).
    - sector 15y ago
      So it's postgres log shipping, with S3 as the target?
      - deleted 15y ago
        [deleted]
      - joevandyk 15y ago
        Yup.
- jaylevitt 15y ago
  So would I need to (learn and) use PG9's replication with this? Or is one ephemeral-stored database plus shipped logs sufficient, assuming I can live with some downtime if the instance disappears? As an experienced techie but a PG novice, what do I need to go learn about PG recovery procedures to safely use this?

mark_l_watson 15y ago

Someone at Heroku wrote a very useful article a few years ago on getting around the pain points of EBS, using RAID on EBS, etc. (This is probably the article I remembered:http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs/ http://orion.heroku.com/past/2009/7/29/io_performance_on_ebs...)

moe 15y ago
I/O performance is still abysmal even on their largest instance type when striping over 20 EBS volumes (I benchmarked that a while back). And we still noticed a high variance in I/O performance during peak hours despite the striping. Any recent dedicated server with 16 spindles will completely annihilate EBS in terms of IOPS. And that's with plain old spinning rust - add some SSDs and EBS ain't even in the same ballpark anymore.

joevandyk 15y ago

I wouldn't use EBS. Instance storage is much more consistent, in terms of latency and performance.

jaylevitt 15y ago
I didn't realize that; in fact, I thought I saw something that claimed the opposite. It'd be great to see someone write up a HOWTO for WAL-E and PG9 replication on ephemeral stores in EC2.