Transcription
Check Please!What your Postgres database wishes you would monitor/ PresentationFriday, May 21, 2010
Who am I? Lead Database Operations at OmniTI Database Consulting / ManagementPostgres? TB OLAP/DSSmultiple 1000 tps OLTPcustom built, private labellong time user (6.5-9.x) Friday, May 21, 2010community membermajor contributor
Check Yourself! Basic Tuning Is Job #1 Resources Friday, May 21, 2010shared buffers, effective cache size, checkpointshttp://wiki.postgresql.org/wiki/Tuning Your PostgreSQL al-postgresqlconf-presentation
Before You Wreck Yourself!Friday, May 21, 2010
Before You Wreck Yourself! Monitoring Friday, May 21, 2010If a server crashes in the woodsPain is a great motivator
Before You Wreck Yourself!Friday, May 21, 2010
Before You Wreck Yourself! Friday, May 21, 2010Trending Knowing what things look like when they’re good helps determinewhen things are bad You can often tell where you’re going by looking at where you camefrom
Tools? Tools cannot replace experience and discipline Popular tools Friday, May 21, 2010But they can help you maintain that disciplinenagios / munincacti / mrtgcirconus.com / reconnoiter{ check postgres }
Connections Hard limit on allowed connections Game Over Large numbers of concurrentusers Internet facing systems- Paul RJ Muller, beached whaleFriday, May 21, 2010
Disk Space Data, clog, xlogs, log files Game Over Everybody(planning)- Squiggle, Overloaded?Friday, May 21, 2010
WAL Files pg xlog directory, transaction logs excessive disk space heavy write transactionsmaintain database consistencyexcessive recovery timepg start/stop backup (buggy systems)- Jazzmasterson, Workspace 3.0 - Noguchi FileFriday, May 21, 2010
Size Matters more data more disk space large tables, i/o issues fast paced developmentonly grow if you shouldunbounded growth?everyone else (eventually)- elmada, Size MattersFriday, May 21, 2010
Bloating mvcc leaves dead rows i/o issues heavy updates, data churnunused space in tables, indexesdisk space (eventually)untuned systems- Joe Alterio, burpalurpaFriday, May 21, 2010
Transactions every statement is in a transaction underlying effects OLTP Systemsselect/insert/update/deletesload spikesLogging/Internet Facing Systems- laurieofindy , Walmart on Black Friday 2009Friday, May 21, 2010
All Stats pg stat tables underlying effects OLTP Systemstables, indexes. scans, tuplesload spikesLogging/Internet Facing Systems- Inju, Statistics for the Utterly ConfusedFriday, May 21, 2010
Free Space Map Tracks unused space Table / Index Bloat Medium to Large SystemsKeeps vacuum effectiveHigh Update / Data Churn DB- SkyTruth, Deepwater Horizon Oil Spill - RADARSAT-2, May 8, 2010Friday, May 21, 2010
Autovacuum Max Freeze Age Ensures all tables get vacuumed Heavy I/O High TPS / OLTPPrevents XID wrap-aroundLocking Issuespg dump- Joe Marinaro, Good Morning!Friday, May 21, 2010
Long Running Queries postgres can’t freeze query plans Can cause issues for vacuum active data collectionpg stat activityUh, response time obligationsdevelopers write queries ;-)- Tawcan, Spiral Out.keep goingFriday, May 21, 2010
Idle Transactions BEGIN; zzz. Can cause issues for vacuum pretty much everyonepg stat activityConnections holding memory- psd, Canadian Cashpoints, Bah!Friday, May 21, 2010
Sequence limits sequences limited to 2 billion Can break inserts Heavy insert (update?) systemsnon-transactional- gavinzac, Rise and Fall in DonegalFriday, May 21, 2010
Wrap-around Postgres must vacuum every tablewithin 2 Billion transactions Catastrophic data loss pretty much everyone- Jurvetson, Wrapped Around the AxleFriday, May 21, 2010
Settings postgresql.conf doesn’t alwaysreflect reality temporary changes can leadto long term trouble pretty much everyone- denovich, P9220453.jpgFriday, May 21, 2010
Thanks! PGConPGCommunityOmniTIWant more? Friday, May 21, 2010xzilla@users.sourceforge.net, http://www.xzilla.net@robtreat2robert@omniti.com, http://www.omniti.com/is/hiring
nagios / munin cacti / mrtg . Internet facing systems Connections - Paul RJ Muller, beached whale Friday, May 21, 2010 Data, clog, xlogs, log files . Free Space Map - SkyTruth, Deepwater Horizon Oil Spill - RADARSAT-2, May 8, 2010 Friday, May 21, 2010
