Anyone with PostgreSQL-on-EC2 recipes that work?

Seeing ridiculously slow PostgreSQL performance on an application.  Can't claim the application is particularly tightly coded for DB usage (too much ORM-ish-ness), so there's room to improve there, but right now we're seeing the same app 10x slower on the EC2 instances than on my (somewhat high-end) laptop... which makes me wonder if there's some well-known-but-not-to-me way to get reasonable performance out of PG on EC2.

Comments

  1. Dana

    Dana on 04/27/2010 9:39 a.m. #

    Maybe you have done this already, but I found that tweaking the kernel parameters described in http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html helped immensely.

  2. Sangraal Aiken

    Sangraal Aiken on 04/27/2010 11:13 a.m. #

    Just curious if you could give us some more information about your setup.

    - What size instance did you use? Different instances have different IO performance. You can run 'iostat -x 2' to get an idea of what your disks are doing.
    - Is your data directory on the local filesystem or on an Elastic Block?
    - Have you tuned PostgreSQL to utilize more RAM for caching, etc?

    I've heard a lot of complaints about Amazon's EC2 performance especially when it comes to IO. That being said, the flexibility of the platform gives you several options for squeezing out more performance, such as setting up a software striped RAID 0 over multiple Elastic Blocks to increase IO. Of course doing this means you can't take snapshots so you'll have to backup your data another way. In my case, I setup a PostgreSQL instance in Warm Standby in addition to nightly dumps to overcome the loss of EBS snapshooting capability.

  3. Mike Fletcher

    Mike Fletcher on 04/28/2010 11:17 a.m. #

    We were running on a small instance, since upgraded to a large. That approximately doubled total application performance (which was expected). We're running directly against the "local" disk, rather than against ECB, as reports seemed to suggest that ECB would be markedly slower than the on-machine disk. Kernel and postgresql parameters tweaked as per the postgresql guides.

    I'm looking for more exotic recipes, such as making a high-memory instance load the entire DB into ram and then replicate logs onto ECB or some other persistent mechanism. At this point (a day later) we've solved "today", I'm now curious what to do for "tomorrow", when we want to scale up performance another 10x or 100x.

    I understand the traditional approaches to DB scaling, I'm thinking in terms of cloud-specific approaches that might take advantage of the Amazon infrastructure to boost DB performance.

Comments are closed.

Pingbacks

Pingbacks are closed.