

Secrets of PostgreSQL Performance (DjangoCon) - mace
http://blip.tv/djangocon/secrets-of-postgresql-performance-5572403

======
emmett
I wish this wasn't a video. When someone gives a talk, rather than just
posting the video, won't someone at least post the slides? Or better yet, a
transcript?

~~~
alnayyir
Your wish is my command sir. I am hard of hearing so I try to transcribe
talks. This one is more abbreviated than my last one.

Secrets of Postgres Performance

Measure Everything Before and After

Do not do Dumb Things

Cache Everything

Watch your query counts

Tuning Parameters

shared_buffers: set to 25% of available ram, move up/down 5% to find sweet
spot

effective_cache_size: planning hint that tells PG how much RAM it can expect
for OS disk cache

work_mem: per process amount of ORDER BY space

Other Parameters

wal_buffers: Set to 16MB and forget it

checkpoint_segments: Increase to at least 10

maintenance_work_mem: 50MB for every GB of RAM

synchronous_commit: turn off with data loss risks

Disks. Lots of disks.

Not all disks are created equal. Faster disks make a huge difference.

Configuration of disks matters a lot. RAID-5 is bad. RAID-1+0 is good.

Separate out the WAL onto its own disk. Can 4x write performance.

Worry about CPU _after_ your disk/RAID/filesystem configurations are more or
less optimal.

Use pgbouncer to pool connections

Tablespaces

Consider putting archive or legacy data onto slower drives/volumes

Separate your data and indices onto different volumes if possible

This applies even on SANs

Best of both worlds with Rackspace and Cloud Connect

EBS volumes and Software RAID is best but scary on AWS.

Choice of filesystem matters. No journaled filesystems for your WAL, it is a
journal.

Use XFS for maximum performance.

USE noatime!

Avoid queries in loops.

Count() is slow

~~~
SkyMarshal
Man you are awesome, thank you. I hate video for these kinds of things, it's
like watching paint dry when I could have speed-read through a transcript in a
tenth the time it takes to watch a video.

I don't know if I'm an edge case here, but if not there might be some kind of
opportunity there.

~~~
alnayyir
That occurred to me earlier. There's a lot of people like us (I think) who
really just want to accumulate as much "80/20" information as possible and
don't want multi-media.

Call it, "The wiki for getting to the fucking point".

~~~
aaronbrethorst
Tragically, gettothefuckingpoint.com is taken by a squatter. What a pity.

------
johnpaulett
Slides available as PDF:
[http://media.revsys.com/talks/djangocon/2011/secrets-of-
post...](http://media.revsys.com/talks/djangocon/2011/secrets-of-postgresql-
performance.pdf)

------
spudlyo
I'm having a really hard time refraining from writing a really snarky "Switch
to MySQL" type response, not because I think that MySQL outperforms
PostgreSQL, but because there is always _somebody_ on every MySQL article
discussion who does this. Ok, that's off my chest, sorry to rant.

I enjoyed this video, even though I'm not a PostgreSQL guy, as it has a lot of
good generic info. The advice about XFS and noatime was right on the money,
although I don't think he strongly stated enough why you don't want to run EBS
volumes over software RAID-0. In my experience this is a really bad idea,
because just one of those EBS volumes getting picking up a laggy await
(happens pretty frequently with volumes I've seen in the wild) will drag down
the performance of the entire array. Also I'm told that the old "RAID-5 is
always bad" notion is considerably more nuanced nowadays, and that many of the
earlier OLTP write performance problems with RAID-5 have been largely
mitigated with modern RAID controllers.

~~~
jpitz

      " Also I'm told that the old "RAID-5 is always bad" notion is considerably more nuanced nowadays, and that many of the earlier OLTP write performance problems with RAID-5 have been largely mitigated with modern RAID controllers."

Not to be snarky, but do you have a source for that? RAID5 has a pretty big
intrinsic write penalty.

------
lflux
This gave me the idea to try <http://pgfouine.projects.postgresql.org> he
mentioned on my logs, and I'm already finding a bunch of stuff to fix after a
quick run.

------
toisanji
Anyone know of an available transcript?

~~~
alnayyir
Yes, see my response to emmett.

