PostgreSQL 9.6 High Performance by Ahmed, Ibrar -- Read -- Imperial Library of Trantor

Index

Title Page Copyright Credits About the Authors About the Reviewer www.PacktPub.com Customer Feedback Preface

What this book covers What you need for this book Who this book is for Conventions Reader feedback Customer support

Downloading the example code Errata Piracy Questions

PostgreSQL Versions

Performance of historical PostgreSQL releases

Choosing a version to deploy Upgrading to a new major version

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 Using a PostgreSQL's Extensions

pgFoundry Additional PostgreSQL related software

PostgreSQL application scaling life cycle Performance tuning as a practice Summary

Database Hardware

Balancing hardware spending

CPUs Memory Disks

RAID Drive error handling 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

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 I/O per second Sequential access and ZCAV

Short stroking

Commit rate

PostgreSQL test_fsync The INSERT rate Windows commit rate

Disk benchmarking tools

Basic disk benchmarking using HD Tune

Short stroking tests Measuring IOPS using HD Tune Unpredictable performance and Windows

Disk throughput in case of sequential read and write bonnie++

Bonnie++ 2.0 Bonnie++ ZCAV

sysbench 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

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 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

Memory for Database Caching

Memory units in the postgresql.conf file 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

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

Vacuuming and statistics

autovacuum Enabling autovacuum maintainance_work_mem default_statistics_target

Checkpoints

checkpoint_timeout checkpoint_completion_target 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

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 Removing tuple timeout 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 Fixing the index bloat

Dump and restore Vacuuming the database/table CLUSTER Reindexing 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 eqpa pgsi mk-query-digest

Summary

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

Sample pgbench test results

SELECT-only test TPC-B-like test (Read/Write) 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

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

Planning for plan changes

Clustering against an index Explain with buffer counts

Index creation and maintenance

Unique indexes Concurrent index creation Clustering an index

Fillfactor

Reindexing

Index types

B-tree

Text operator classes

Hash GIN GiST SP-GiST BRIN Index only scans

count(*) Visibility map Advanced index use

Multicolumn indexes Indexes for sorting Partial indexes Expression-based indexes Indexing for full-text search

Summary

Query Optimization

Sample datasets

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

Visual explain Verbose output Machine readable explain output Planning analysis tools

Assembling row sets

Tuple ID

Object ID

Sequential scan Index scan Index only scans 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 Copy command

COPY TO COPY FROM COPY FREEZE

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

Collecting 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 datasets 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

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 Table-level Lock Modes Transaction lock waits Table lock waits Logging lock information

Deadlocks Disk usage Monitoring Buffer, background writer, and checkpoint activity

Saving pg_stat_bgwriter snapshots Tuning using background writer statistics

Summary

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

Pooling and Caching

Connection pooling

Pooling connection counts pgpool-II Connection pooling Replication Watch-dog Failover Load balancing pgBouncer

Application server pooling Database caching

What is memcached pgmemcache

Summary

Scaling with Replication

Hot standby

Terminology Setting up WAL shipping Streaming replication Cascading replication Synchronous replication Tuning Hot Standby

Replication queue managers

Slony Londiste Read scaling with replication queue software Special application requirements Bucardo pglogical xDB pgpool-II

Other interesting replication projects Replications solution comparison Summary

Partitioning Data

Table inheritance dellstore2 database Partitioning in PostgreSQL Range partitioning Determine a key field to partition over Sizing the partitions

List partitioning

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

Database Profiling

Profiling using gprof Debugging using Valgrind Visual Studio Profiling using DTrace

DTrace on FreeBSD Linux SystemTap emulation of DTrace

Summary

Avoiding Common Problems

Bulk loading

Loading methods

External loading programs

Tuning for bulk loads Skipping WAL acceleration Recreate indexes and add constraints Parallel restore Post load clean up

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

Summary

Performance Features by Release

Aggressive PostgreSQL version upgrades Performance features in version 8.1 Performance features in version 8.2 Performance features in version 8.3 Performance features in version 8.4 Performance features in version 9.0

Replication Queries and EXPLAIN Database development Configuration and monitoring

Performance features in version 9.1 Performance features in version 9.2 Performance features in version 9.3 Performance features in version 9.4 Performance features in version 9.5 Performance features in version 9.6 Summary

← Prev
Back
Next →

← Prev
Back
Next →