PostgreSQL 9.0 High Performance Table of Contents

Table of Contents

Preface
Chapter 1: PostgreSQL Versions
Chapter 2: Database Hardware
Chapter 3: Database Hardware Benchmarking
Chapter 4: Disk Setup
Chapter 5: Memory for Database Caching
Chapter 6: Server Configuration Tuning
Chapter 7: Routine Maintenance
Chapter 8: Database Benchmarking
Chapter 9: Database Indexing
Chapter 10: Query Optimization
Chapter 11: Database Activity and Statistics
Chapter 12: Monitoring and Trending
Chapter 13: Pooling and Caching
Chapter 14: Scaling with Replication
Chapter 15: Partitioning Data
Chapter 16: Avoiding Common Problems
Index

• Preface

• Chapter 1: PostgreSQL Versions
  • Performance of historical PostgreSQL releases
    • Choosing a version to deploy
    • Upgrading to a newer major version
      • Upgrades to PostgreSQL 8.3+ from earlier ones
      • Minor version upgrades
  • PostgreSQL or another database?
  • PostgreSQL tools
    • PostgreSQL contrib
      • Finding contrib modules on your system
      • Installing a contrib module from source
      • Using a contrib module
    • pgFoundry
    • Additional PostgreSQL-related software
  • PostgreSQL application scaling lifecycle
  • Performance tuning as a practice
  • Summary

• Chapter 2: Database Hardware
  • Balancing hardware spending
    • CPUs
    • Memory
    • Disks
      • RAID
      • Drive error handling
      • Hard drive reliability studies
      • Drive firmware and RAID
      • SSDs
    • Disk controllers
      • Hardware and Software RAID
      • Recommended disk controllers
      • Attached storage—SAN and NAS
  • Reliable controller and disk setup
    • Write-back caches
      • Sources of write-back caching
      • Disk controller monitoring
      • Disabling drive write caches
    • Performance impact of write-through caching
  • Summary

• Chapter 3: Database Hardware Benchmarking
  • CPU and memory benchmarking
    • memtest86+
    • STREAM memory testing
      • STREAM and Intel vs. AMD
    • CPU benchmarking
    • Sources of slow memory and processors
  • Physical disk performance
    • Random access and I/Os Per Second
    • Sequential access and ZCAV
      • Short stroking
    • Commit rate
      • PostgreSQL test_fsync
      • INSERT rate
      • Windows commit rate
  • Disk benchmarking tools
    • hdtune
      • Short stroking tests
      • IOPS
      • Unpredictable performance and Windows
    • dd
    • bonnie++
      • bonnie++ 2.0
      • bonnie++ ZCAV
    • sysbench
      • Seek rate
      • fsync commit rate
    • Complicated disk benchmarks
  • Sample disk results
    • Disk performance expectations
      • Sources of slow disk and array performance
  • Summary

• Chapter 4: Disk Setup
  • Maximum filesystem sizes
  • Filesystem crash recovery
    • Journaling filesystems
  • Linux filesystems
    • ext2
    • ext3
    • ext4
    • XFS
    • Other Linux filesystems
    • Write barriers
      • Drive support for barriers
      • Filesystem support for barriers
    • General Linux filesystem tuning
      • Read-ahead
      • File access times
      • Read caching and swapping
      • Write cache sizing
      • I/O scheduler elevator
  • Solaris and FreeBSD filesystems
    • Solaris UFS
    • FreeBSD UFS2
    • ZFS
  • Windows filesystems
    • FAT32
    • NTFS
      • Adjusting mounting behaviour
  • Disk layout for PostgreSQL
    • Symbolic links
    • Tablespaces
    • Database directory tree
      • Temporary files
    • Disk arrays, RAID, and disk layout
      • Disk layout guidelines
  • Summary

• Chapter 5: Memory for Database Caching
  • Memory units in the postgresql.conf
  • Increasing UNIX shared memory parameters for larger buffer sizes
    • Kernel semaphores
    • Estimating shared memory allocation

• Inspecting the database cache
  • Installing pg_buffercache into a database
  • Database disk layout
  • Creating a new block in a database
  • Writing dirty blocks to disk

• Crash recovery and the buffer cache
  • Checkpoint processing basics
  • Write-ahead log and recovery processing
  • Checkpoint timing
    • Checkpoint spikes
    • Spread checkpoints
  • Database block lifecycle
    • Dirty block write paths

• Database buffer cache versus operating system cache
  • Doubly cached data
    • Inspecting the OS cache
  • Checkpoint overhead
  • Starting size guidelines
    • Platform, version, and workload limitations

• Analyzing buffer cache contents
  • Inspection of the buffer cache queries
    • Top relations in the cache
    • Summary by usage count
    • Buffer contents summary, with percentages
    • Buffer usage count distribution
  • Using buffer cache inspection for sizing feedback

• Summary

• Chapter 6: Server Configuration Tuning
  • Interacting with the live configuration
    • Defaults and reset values
    • Allowed change context
    • Reloading the configuration file
      • Commented out settings
  • Server-wide settings
    • Database connections
      • listen_addresses
      • max_connections
    • Shared memory
      • shared_buffers
      • Free space map (FSM) settings
    • Logging
      • log_line_prefix
      • log_statement
      • log_min_duration_statement
    • Vacuuming and statistics
      • autovacuum
      • Enabling autovacuum on older versions
      • maintainance_work_mem
      • default_statistics_target
    • Checkpoints
      • checkpoint_segments
      • checkpoint_timeout
      • checkpoint_completion_target
    • WAL settings
      • wal_buffers
      • wal_sync_method
    • PITR and WAL Replication
  • Per-client settings
    • effective_cache_size
    • synchronous_commit
    • work_mem
    • random_page_cost
    • constraint_exclusion
  • Tunables to avoid
    • fsync
    • full_page_writes
    • commit_delay and commit_siblings
    • max_prepared_transactions
    • Query enable parameters

• New server tuning
• Dedicated server guidelines
• Shared server guidelines
• pgtune
• Summary

• Chapter 7: Routine Maintenance
  • Transaction visibility with multiversion concurrency control
    • Visibility computation internals
    • Updates
    • Row lock conflicts
      • Serialization
    • Deletions
    • Advantages of MVCC
    • Disadvantages of MVCC
    • Transaction ID wraparound
  • Vacuum
    • Vacuum Implementation
      • Regular vacuum
      • Returning free disk space
      • Full vacuum
      • HOT
    • Cost-based vacuuming
    • autovacuum
      • autovacuum logging
      • autovacuum monitoring
      • autovacuum triggering
      • Per-table adjustments
    • Common vacuum and autovacuum problems
      • autovacuum is running even though it was turned off
      • autovacuum is constantly running
      • Out of memory errors
      • Not keeping up on a busy server
      • autovacuum is too disruptive
      • Long running transactions
      • Free Space Map exhaustion
      • Recovering from major problems
  • Autoanalyze
  • Index bloat
    • Measuring index bloat
  • Detailed data and index page monitoring
  • Monitoring query logs
    • Basic PostgreSQL log setup
      • Log collection
      • log_line_prefix
      • Multi-line queries
      • Using syslog for log messages
      • CSV logging
    • Logging difficult queries
      • auto_explain
    • Log file analysis
      • Normalized query fingerprints
      • pg_stat_statements
      • pgFouine
      • PQA
      • EPQA
      • pgsi
      • mk-query-digest
  • Summary

• Chapter 8: Database Benchmarking
  • pgbench default tests
    • Table definition
    • Scale detection
    • Query script definition
    • Configuring the database server for pgbench
      • Sample server configuration
  • Running pgbench manually
  • Graphing results with pgbench-tools
    • Configuring pgbench-tools
      • Customizing for 8.3
  • Sample pgbench test results
    • SELECT-only test
    • TPC-B-like test
    • Latency analysis
  • Sources for bad results and variation
    • Developer PostgreSQL builds
    • Worker threads and pgbench program limitations

• pgbench custom tests
  • Insert speed test

• Transaction Processing Performance Council benchmarks
• Summary

• Chapter 9: Database Indexing
  • Indexing example walkthrough
    • Measuring query disk and index block statistics
    • Running the example
    • Sample data setup
    • Simple index lookups
    • Full table scans
    • Index creation
    • Lookup with an inefficient index
    • Combining indexes
    • Switching from indexed to sequential scans
      • Planning for plan changes
    • Clustering against an index
    • Explain with buffer counts
  • Index creation and maintenance
    • Unique indexes
    • Concurrent index creation
    • Clustering an index
      • Fill factor
    • Reindexing
  • Index types
    • B-tree
      • Text operator classes
    • Hash
    • GIN
    • GiST
  • Advanced index use
    • Multicolumn indexes
    • Indexes for sorting
    • Partial indexes
    • Expression-based indexes
    • Indexing for full-text search
  • Summary

• Chapter 10: Query Optimization
  • Sample data sets
    • Pagila
    • Dell Store 2
  • EXPLAIN basics
    • Timing overhead
    • Hot and cold cache behavior
      • Clearing the cache
  • Query plan node structure
    • Basic cost computation
      • Estimated costs and real world costs
  • Explain analysis tools
    • Visual explain
    • Verbose output
    • Machine readable explain output
    • Plan analysis tools
  • Assembling row sets
    • Tuple id
      • Object id
    • Sequential scan
    • Index scan
    • Bitmap heap and index scans
  • Processing nodes
    • Sort
    • Limit
      • Offsets
    • Aggregate
    • HashAggregate
    • Unique
      • WindowAgg
    • Result
    • Append
    • Group
    • Subquery Scan and Subplan
      • Subquery conversion and IN lists
    • Set operations
    • Materialize
    • CTE Scan
  • Joins
    • Nested loop
      • Nested loop with inner Index Scan
    • Merge Join
      • Nested loop and Merge Join materialization
    • Hash Joins
      • Hash semi and anti joins
      • Join ordering
      • Forcing join order
      • Join removal
      • Genetic query optimizer
  • Statistics
    • Viewing and estimating with statistics
    • Statistics targets
      • Adjusting a column target
      • Distinct values
    • Difficult areas to estimate
  • Other query planning parameters
    • effective_cache_size
    • work_mem
    • constraint_exclusion
    • cursor_tuple_fraction
  • Executing other statement types
  • Improving queries
    • Optimizing for fully cached data sets
    • Testing for query equivalence
    • Disabling optimizer features
    • Working around optimizer bugs
    • Avoiding plan restructuring with OFFSET
    • External trouble spots
  • SQL Limitations
    • Numbering rows in SQL
    • Using Window functions for numbering
    • Using Window functions for cumulatives
  • Summary

• Chapter 11: Database Activity and Statistics
  • Statistics views
  • Cumulative and live views
  • Table statistics
    • Table I/O
  • Index statistics
    • Index I/O
  • Database wide totals
  • Connections and activity
  • Locks
    • Virtual transactions
    • Decoding lock information
    • Transaction lock waits
    • Table lock waits
    • Logging lock information
      • Deadlocks
  • Disk usage
  • Buffer, background writer, and checkpoint activity
    • Saving pg_stat_bgwriter snapshots
    • Tuning using background writer statistics
  • Summary

• Chapter 12: Monitoring and Trending
  • UNIX monitoring tools
    • Sample setup
    • vmstat
    • iostat
      • iotop for Linux
      • Examples of good performance
      • Overloaded system samples
    • top
      • Solaris top replacements
      • htop for Linux
    • sysstat and sar
      • Enabling sysstat and its optional features
      • Graphing with kSar
  • Windows monitoring tools
    • Task Manager
      • Sysinternals tools
    • Windows System Monitor
      • Saving Windows System Monitor data
  • Trending software
    • Types of monitoring and trending software
      • Storing historical trend data
    • Nagios
      • Nagios and PostgreSQL
      • Nagios and Windows
    • Cacti
      • Cacti and PostgreSQL
      • Cacti and Windows
    • Munin
    • Other trending packages
      • pgstatspack
      • Zenoss
      • Hyperic HQ
      • Reconnoiter
      • Staplr
      • SNMP tools
  • Summary

• Chapter 13: Pooling and Caching
  • Connection pooling
    • Pooling connection counts
    • pgpool-II
      • pgpool-II load balancing for replication scaling
    • pgBouncer
      • Application server pooling
  • Database caching
    • memcached
    • pgmemcache
  • Summary

• Chapter 14: Scaling with Replication
  • Hot Standby
    • Terminology
    • Setting up WAL shipping
    • Streaming Replication
    • Tuning Hot Standby
  • Replication queue managers
    • Slony
    • Londiste
    • Read scaling with replication queue software
  • Special application requirements
    • Bucardo
    • pgpool-II
  • Other interesting replication projects
  • Summary

• Chapter 15: Partitioning Data
  • Table range partitioning
    • Determining a key field to partition over
    • Sizing the partitions
      • List partitioning
    • Creating the partitions
    • Redirecting INSERT statements to the partitions
      • Dynamic trigger functions
      • Partition rules
    • Empty partition query plans
    • Date change update trigger
    • Live migration of a partitioned table
    • Partitioned queries
    • Creating new partitions
      • Scheduled creation
      • Dynamic creation
    • Partitioning advantages
    • Common partitioning mistakes
  • Horizontal partitioning with PL/Proxy
    • Hash generation
    • Scaling with PL/Proxy
      • Sharding
    • Scaling with GridSQL
  • Summary

• Chapter 16: Avoiding Common Problems
  • Bulk loading
    • Loading methods
      • External loading programs
    • Tuning for bulk loads
    • Skipping WAL acceleration
    • Recreating indexes and adding constraints
    • Parallel restore
    • Post load cleanup
  • Common performance issues
    • Counting rows
    • Unexplained writes
    • Slow function and prepared statement execution
    • PL/pgSQL benchmarking
    • High foreign key overhead
    • Trigger memory use
    • Heavy statistics collector overhead
      • Targeted statistics resets
    • Materialized views
  • Profiling the database
    • gprof
    • OProfile
    • Visual Studio
    • DTrace
      • DTrace on FreeBSD
      • Linux SystemTap emulation of DTrace
  • Performance related features by version
    • Aggressive PostgreSQL version upgrades
    • 8.1
    • 8.2
    • 8.3
    • 8.4
    • 9.0
      • Replication
      • Queries and EXPLAIN
      • Database development
      • Configuration and monitoring
      • Tools
      • Internals
  • Summary

• Index