PostgreSQL 10 High Performance电子书

Title Page

Copyright and Credits

PostgreSQL 10 High Performance

Dedication

Packt Upsell

Why subscribe?

PacktPub.com

Contributors

About the authors

About the reviewer

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

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

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

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

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

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

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

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

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

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

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

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

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

Disabling optimizer features

Working around optimizer bugs

Avoiding plan restructuring with OFFSET

External trouble spots

Parallel queries

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

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

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

pgpool-II load balancing for replication scaling

pgBouncer

Application server pooling

Database caching

memcached

pgmemcache

Summary

Scaling with Replication

Hot Standby

Terminology

Setting up WAL shipping

Streaming replication

Tuning Hot Standby

Replication queue managers

Synchronous replication

Logical replication

Slony

Londiste

Read scaling with replication queue software

Special application requirements

Bucardo

pgpool-II

Other interesting replication projects

Replication solution comparison

Summary

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

PostgreSQL 10 – declarative partitioning – the built-in partitioning

Range partitioning

Partition maintenance

Caveats

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

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

Backup

pg_dump

Continuous archiving and point in time recovery

Common performance issues

Counting rows

Unexplained writes

Slow function and prepared statement execution

PL/pgSQL benchmarking

High foreign key overhead

Trigger memory use

Transition tables for trigger

Heavy statistics collector overhead

Targeted statistics resets

Extended statistics

Materialized views

Foreign data wrapper

The amcheck module

pgAdmin

gprof

OProfile

Visual Studio

DTrace

DTrace on FreeBSD

Linux SystemTap emulation of DTrace

Performance-related features by version

Aggressive PostgreSQL version upgrades

Version 9.0

Replication

Queries and EXPLAIN

Database development

Configuration and monitoring

Tools

Internals

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think