pgeast 2010 - postgresql at my yearbook
TRANSCRIPT
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
