4 ms·

A Performance Cheat Sheet for PostgreSQL

172 points by okket 8y ago

pella 8y ago
quick & dirty - PG tunning: https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua
- ofrzeta 8y ago
  Is there something like this for MySQL?
ramenmeal 8y ago
Hard to take perf advice from a website that never stops loading.
stared 8y ago
For visualizing EXPLAIN there is a cool tool: http://tatiyants.com/pev/ http://tatiyants.com/pev/
IronWolve 8y ago
Does anyone have a direct link without signing up? Just put your logo in the pdf, brand it, but I'm not gonna sign up just to get spammed with advertising.
- deathtrader666 8y ago
  There's nothing to download. Everything is right there on the page.
chairleader 8y ago
Nice reference! Does anyone have similar references for doing 0-downtime migrations? For example, building your indexes with CONCURRENTLY to prevent table locking.
- nimajalali 8y ago
  Not exhaustive but this has come in handy: https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/ https://www.braintreepayments.com/blog/safe-operations-for-h...
- pjungwir 8y ago
  If you are using Rails, this is a nice guardrail: https://github.com/ankane/strong_migrations https://github.com/ankane/strong_migrations Even if you aren't on Rails or don't want to use the gem, it is still nice a checklist.
mmt 8y ago
> It is not recommended to use RAID5 since the performance of this type of RAID for databases is not good. This strikes me as a generalization that is, at best, questionable. (Also, these days, with spinning disks, I'd more likely do RAID6, but I expect it's comparable enough). I'd tend to agree that, historically, it's been true, and is demonstrably true for certain situations, such as a read-heavy load or recovering from a failed disk. However, is it really still true for modern RAID cards? What about with SSDs? I'd expect that cost is still a significant consideration for anyone specifying hardware, making the difference between RAID5 and RAID10 important.
- nisa 8y ago
  If you have 4 disks in RAID10 you have 4x read iops of a single disk and 2x write iops. RAID5 has at most 1x write iops (still needs a read) and 4x read iops. RAID6 is even worse. Details here: https://blog.storagecraft.com/raid-performance/ https://blog.storagecraft.com/raid-performance/ While the CPU is not a bottleneck anymore the amount of possible read/write iops still matter. Same for SSDs but here you could probably argue that the single SSD write iops for RAID5 are good enough for most usecases. It really depends on your workload and datasize and usage patterns... But you can scale RAID 10 linear with more disks and you'll get more write iops - this does not work with RAID5 / RAIDZ. For ZFS you can find some benchmark data here: https://calomel.org/zfs_raid_speed_capacity.html https://calomel.org/zfs_raid_speed_capacity.html The general direction should be similiar to RAID cards.
  - mmt 8y ago
    > If you have 4 disks in RAID10 you have 4x read iops of a single disk and 2x write iops. RAID5 has at most 1x write iops (still needs a read) and 4x read iops. RAID6 is even worse. Details here: https://blog.storagecraft.com/raid-performance/ https://blog.storagecraft.com/raid-performance/ It's articles like that linked one which make me ask, because they make assumptions that are, themselves, questionable. Specifically, RAID5 requiring a read to do a write is only true in the case of non-full-stripe writes. Is that really likely with a sanely configured stripe width combined with a modern RAID card's cache (4GB)? There's also an implicit assumption that the channel bandwidths (disk to expander, expander to raid card, raid card's PCIe) don't matter, but they do, especially when SSDs can saturate them. Even exclusively using the notion of "iops" is (and always has been) suspect to me, especially lacking an expected data transfer size and some notion of randomness/locality. > The general direction should be similiar to RAID cards. I'd only be convinced of that if I were to see a few comparitive benchmarks of a variety of workloads. Otherwise, ZFS does too much of its work actually on the CPU and in main memory, whereas the RAID cards moves it down the PCIe bus. At least for RAID10, that would eliminate all the write-amplification over PCIe.

172 points by okket 8y ago

pella 8y ago

quick & dirty - PG tunning: https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua

ofrzeta 8y ago
Is there something like this for MySQL?

ramenmeal 8y ago

Hard to take perf advice from a website that never stops loading.

stared 8y ago

For visualizing EXPLAIN there is a cool tool: http://tatiyants.com/pev/ http://tatiyants.com/pev/

IronWolve 8y ago

Does anyone have a direct link without signing up? Just put your logo in the pdf, brand it, but I'm not gonna sign up just to get spammed with advertising.

deathtrader666 8y ago
There's nothing to download. Everything is right there on the page.

chairleader 8y ago

Nice reference! Does anyone have similar references for doing 0-downtime migrations? For example, building your indexes with CONCURRENTLY to prevent table locking.

nimajalali 8y ago
Not exhaustive but this has come in handy: https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/ https://www.braintreepayments.com/blog/safe-operations-for-h...
pjungwir 8y ago
If you are using Rails, this is a nice guardrail: https://github.com/ankane/strong_migrations https://github.com/ankane/strong_migrations Even if you aren't on Rails or don't want to use the gem, it is still nice a checklist.

mmt 8y ago

> It is not recommended to use RAID5 since the performance of this type of RAID for databases is not good. This strikes me as a generalization that is, at best, questionable. (Also, these days, with spinning disks, I'd more likely do RAID6, but I expect it's comparable enough). I'd tend to agree that, historically, it's been true, and is demonstrably true for certain situations, such as a read-heavy load or recovering from a failed disk. However, is it really still true for modern RAID cards? What about with SSDs? I'd expect that cost is still a significant consideration for anyone specifying hardware, making the difference between RAID5 and RAID10 important.

nisa 8y ago
If you have 4 disks in RAID10 you have 4x read iops of a single disk and 2x write iops. RAID5 has at most 1x write iops (still needs a read) and 4x read iops. RAID6 is even worse. Details here: https://blog.storagecraft.com/raid-performance/ https://blog.storagecraft.com/raid-performance/ While the CPU is not a bottleneck anymore the amount of possible read/write iops still matter. Same for SSDs but here you could probably argue that the single SSD write iops for RAID5 are good enough for most usecases. It really depends on your workload and datasize and usage patterns... But you can scale RAID 10 linear with more disks and you'll get more write iops - this does not work with RAID5 / RAIDZ. For ZFS you can find some benchmark data here: https://calomel.org/zfs_raid_speed_capacity.html https://calomel.org/zfs_raid_speed_capacity.html The general direction should be similiar to RAID cards.
- mmt 8y ago
  > If you have 4 disks in RAID10 you have 4x read iops of a single disk and 2x write iops. RAID5 has at most 1x write iops (still needs a read) and 4x read iops. RAID6 is even worse. Details here: https://blog.storagecraft.com/raid-performance/ https://blog.storagecraft.com/raid-performance/ It's articles like that linked one which make me ask, because they make assumptions that are, themselves, questionable. Specifically, RAID5 requiring a read to do a write is only true in the case of non-full-stripe writes. Is that really likely with a sanely configured stripe width combined with a modern RAID card's cache (4GB)? There's also an implicit assumption that the channel bandwidths (disk to expander, expander to raid card, raid card's PCIe) don't matter, but they do, especially when SSDs can saturate them. Even exclusively using the notion of "iops" is (and always has been) suspect to me, especially lacking an expected data transfer size and some notion of randomness/locality. > The general direction should be similiar to RAID cards. I'd only be convinced of that if I were to see a few comparitive benchmarks of a variety of workloads. Otherwise, ZFS does too much of its work actually on the CPU and in main memory, whereas the RAID cards moves it down the PCIe bus. At least for RAID10, that would eliminate all the write-amplification over PCIe.