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

Index

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

← Prev
Back
Next →

← Prev
Back
Next →