pgeast 2010 - postgresql at my yearbook

PostgreSQL at myYearbook.com

Gavin M. RoyChief Technology OfficermyYearbook.com

pgEast 2010

About myYearbook.com

2007 - 100M Page views per Month

2010 - 1.5B Page views per Month

Top 5 Social Network in the United States as measured by Hitwise

In the Top 30 most trafficked sites in the United States as measured by ComScore

99% Uptime

Growth is a double-edged sword.

2007: Midnight Calls2008: Sizing hardware2009: Complex2010: Connections

“The best laid schemes o’ mice an’ menGang aft agley”

- Robert Burns, To a Mouse

SAN - Fiber ChannelRamSAN - Fiber ChanneliSCSI? Too slow

Started on DASFusionIO?

Plan for Growth Things we’ve learned about concurrency & data growth

Concurrency:Too many backendsPool with pgBouncer

Too Much Traffic

Too Many BackendsToo Much Lock ContentionSlow PostgreSQL Queries

pgBouncer to the Rescue

Table Partitioning

• Supported in PostgreSQL as of 8.1

• Excellent method for maintaining data

• Allows for removal of aged data without bloat

• Focused SELECTS while allowing ad-hoc SELECT across all partitions

Vertically Partitioning Data

• Isolate application data in different database servers

• Replicate common data needed for joins

Spread table data across multiple database servers

Horizontally Partitioning Data

Ancient Mountaineering Adage:

“Anything that can possibly go wrong, does.” - Jack Sack (1952)

Daily Backups and Offsite (worst)

Warm Standby

PostgreSQL 9.0 Hot+Stream

How to kill PostgreSQL performance.

Inadequate Hardware

Not Enough RamNot Enough Disk

ControllersSlow Communication

CPU Oversaturation

Lock Contention == DeathLong Running ExclusiveHigh Share LocksReduce by partitioningconcurrent reindex, etc

Bloat == Slower DeathMVCCHOT (Tuples)Index Bloat

“Knowledge is Power”

- Sir Francis Bacon

Reacting can be Proactive

• check_postgres.pl by Greg Mullane

• Nagios plugin

• Bloat

• Management activity such as last analyze and vacuum

• wal file count, txid wrap around, sequence exhaustion

• many other items it checks

Be Trendy

• Know your database behavior over time

• Predict future issues and behavior

• Identify issues as they occur

• Review impact of maintenance

• Know if your heap use exceeds your index use

• Look for daily change

Trending and Analysis at myYearbook.com

• Cacti - http://www.cacti.net

• Posuta - http://code.google.com/p/posuta/

• pgFouine - http://pgfouine.projects.postgresql.org/

• Staplr - http://github.com/myYearbook/staplr

http://twitter.com/CradAny Questions?