PostgreSQL 9.0 High Performance电子书

PostgreSQL 9.0 High Performance

PostgreSQL 9.0 High Performance

Credits

About the Author

About the Reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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