PostgreSQL 10 high performance expert techniques for query optimization, high availability, and efficient database maintenance
Leverage the power of PostgreSQL 10 to design, administer and maintain a high-performance database solution About This Book Obtain optimal PostgreSQL 10 database performance, ranging from initial design to routine maintenance Fine tune the performance of your queries and avoid the common pitfalls th...
| Main Authors: | , , |
|---|---|
| Format: | eBook |
| Language: | English |
| Published: |
Birmingham, UK
Packt Publishing
2018
|
| Subjects: | |
| Online Access: | |
| Collection: | O'Reilly - Collection details see MPG.ReNa |
Table of Contents:
- 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 content 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 settings
- Logging
- log_line_prefix
- log_statement
- log_min_duration_statement
- News on PostgreSQL 10
- Vacuuming and statistics
- autovacuum
- Enabling autovacuum on older versions
- maintainance_work_mem
- default_statistics_target
- Checkpoints
- checkpoint_segments - max_wal_size
- 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
- Querying 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
- Disk throughput in case of sequential read and write
- Bonnie++
- Bonnie++ 2.0
- Bonnie++ ZCAV
- sysbench
- pgbench
- Seek rate
- Removing test files
- 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
- Benchmarks
- 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 behavior
- 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 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 life cycle
- Dirty block write paths
- Database buffer cache versus operating system cache
- Doubly cached data
- Inspecting the OS cache
- Checkpoint overhead
- Starting size guidelines
- 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 with a busy server
- autovacuum is too disruptive
- Long running transactions
- FSM 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
- Multiline queries
- Using syslog for log messages
- CSV logging
- Logging difficult queries
- auto_explain
- Log file analysis
- Normalized query fingerprints
- pg_stat_statements
- pgBadger
- 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 of 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
- B-tree GIN versus bitmap indexes
- GiST
- Advanced index use
- Multicolumn indexes
- Indexes for sorting
- Partial indexes
- Expression-based indexes
- Indexing for full-text search
- Indexing like or like queries with pg_trgm contrib
- Indexing JSON datatype
- Summary
- Chapter 10: Query Optimization
- Sample data sets
- Pagila
- dellstore2
- 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
- Cover
- Title Page
- Copyright and Credits
- Dedication
- Packt Upsell
- Contributors
- Table of Contents
- 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
- Migrating from PostgreSQL 9.x to 10.x
- a new way to work
- PostgreSQL or another database?
- PostgreSQL 10.x and NoSQL
- PostgreSQL as HUB
- PostgreSQL tools
- PostgreSQL contrib
- Finding contrib modules on your system
- Installing a contrib module from source
- Using a contrib module
- pgFoundry
- PGXN
- Additional PostgreSQL-related software
- PostgreSQL application scaling life cycle
- 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 versus AMD
- CPU benchmarking
- Sources of slow memory and processors
- Physical disk performance
- Random access and input/outputs per second
- Sequential access and ZCAV
- Short stroking
- Commit rate
- PostgreSQL test_fsync
- INSERT rate
- Windows commit rate
- Disk benchmarking tools
- HD Tune
- Short stroking tests
- IOPS
- Unpredictable performance and Windows