Debugging PostgreSQL performance the hard way

Re:Debugging PostgreSQL performance the hard way by dfsegoat on Thursday September 29, 2016 @03:56PM (#12608298)

Fantastic write-up! Put this on our data engineering reference article list.

I am just curious - are you using Provisioned IOPS? If so what value? Noticed in your metrics your Queue Depth was pegged around 15-20 on average. According to AWS queue depth is essentially number of operations waiting because device is busy (somewhat analogous to iostat => await metric i assume).

When we hit that 15-20+ range or higher - it typically means we are maxing out our provisioned IOPS (1k or 2k - nothing crazy), and we see a lot of performance slowdown (in both MySQL and PG RDS).

We have not tried increasing IOPS - but was curious if you had in your previous performance tuning efforts for this or any other issues?

[ Parent | on HN ]

Re:Debugging PostgreSQL performance the hard way by endymi0n on Thursday September 29, 2016 @04:38PM (#12608578)

The dirty secret AWS doesn't want you to know is: Provisioned IOPS is really bad value for the money. Like a lot of others, we're just using a massively overprovisioned General purpose SSD volume, but reaching IOPS limits came back to bite us multiple times. Be sure to monitor it (and no, they don't provide any convenient way to do that, had to import several metrics from Cloudwatch API to Prometheus and combine there).

[ Parent | on HN ]

Re:Debugging PostgreSQL performance the hard way by pjungwir on Thursday September 29, 2016 @06:06PM (#12609172)

Around 2009-2010, EBS volatility was leading a lot of people to run their database on instance storage instead of EBS. (At the time I suspected that even Heroku was doing this.) That seems pretty crazy, since the other name for instance storage is "ephemeral storage". If you have replication you reduce the risk, but it's still a wild thing to do. But I understand it. Would you run Postgres on NFS? If not, then why is EBS okay?

I haven't heard of people doing this for a few years, so I think PIOPS must have really improved things. But I agree, it sure is expensive! So I'm curious if anyone is still taking the instance storage approach to get more consistent disk performance.

[ Parent | on HN ]

Re:Debugging PostgreSQL performance the hard way by nasalgoat on Friday September 30, 2016 @09:31AM (#12612291)

The secret to EBS is to use General SSD, not Provisioned, but use a RAID stripe.

The reason this works is because IOPS are provisioned per EBS drive and by the size of the drive. So a RAID0 stripe of, say, ten General SSD drives will outperform the more expensive PIOPS single drive.

[ Parent | on HN ]

Re:Debugging PostgreSQL performance the hard way by dfsegoat on Thursday September 29, 2016 @05:15PM (#12608808)

Totally with you there. The pricing is nutty and we had similar issues - but that was back on magnetic drive type (we migrated to RDS in 2012) - ever since then we've been too scared to "get off the piops".

So just provision a 1TB volume or so, and garner the standard iops throughput that comes as a result of the volume size right? I will give that a shot. Looks like you are hitting 1k+ on read and write easy. That is pretty impressive and plenty for us.

[ Parent | on HN ]

Re:Debugging PostgreSQL performance the hard way by mbell on Thursday September 29, 2016 @09:04PM (#12609937)

With a gp2 SSD EBS volume you get 3k iops baseline per 1TB of storage up to a max of 10k iops (~3.3TB volume), there is also some burst allowance over that. In my experience so far the baseline numbers have been pretty accurate.

This is a relatively recent change (last year?), it used to cap out at 3k iops rather than 10k. The only real reason to go for provisioned iops now is if you need > 10k iops per volume.

[ Parent | on HN ]