Cover

Credits

Copyright

About the Authors

About the Reviewer

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: 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

Chapter 2: 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

[Hard drive reliability studies]

Hard drive reliability studies

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

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

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

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

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

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

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

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

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

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

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

Chapter 10: 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

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

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

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

Connection pooling

Replication

Watch-dog

Failover

Load balancing

pgBouncer

Application server pooling

Database caching

What is memcached

pgmemcache

Summary

Chapter 14: 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

Chapter 15: 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

Chapter 16 : Database Profiling

Profiling using gprof

Debugging using Valgrind

Visual Studio

Profiling using DTrace

DTrace on FreeBSD

Linux SystemTap emulation of DTrace

Summary

Chapter 17: 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

Chapter 18: 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

Index