Book Image

PostgreSQL 9.0 High Performance

Book Image

PostgreSQL 9.0 High Performance

Overview of this book

PostgreSQL database servers have a common set of problems they encounter as their usage gets heavier and requirements more demanding. You could spend years discovering solutions to them all, step by step as you encounter them. Or you can just look in here.All successful database applications are destined to eventually run into issues scaling up their performance. Peek into the future of your PostgreSQL database's problems today. Know the warning signs to look for, and how to avoid the most common issues before they even happen.Surprisingly, most PostgreSQL database applications evolve in the same way: Choose the right hardware. Tune the operating system and server memory use. Optimize queries against the database, with the right indexes. Monitor every layer, from hardware to queries, using some tools that are inside PostgreSQL and others that are external. Using monitoring insight, continuously rework the design and configuration. On reaching the limits of a single server, break things up; connection pooling, caching, partitioning, and replication can all help handle increasing database workloads. The path to a high performance database system isn't always easy. But it doesn't have to be mysterious with the right guide.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL 9.0 High Performance
Oct, 2010 | 468 pages
No titles found
Table of Contents (120 chapters)
Preface
Preface
Free Chapter
1
What this book covers
What this book covers
2
What you need for this book
What you need for this book
3
Who this book is for
Who this book is for
4
Conventions
Conventions
Note
Note
5
Reader feedback
Reader feedback
6
Customer support
Customer support
Tip
Errata
Piracy
Questions
7
Chapter 1. PostgreSQL Versions
Chapter 1. PostgreSQL Versions
8
Performance of historical PostgreSQL releases
Performance of historical PostgreSQL releases
Choosing a version to deploy
Upgrading to a newer major version
9
PostgreSQL or another database?
PostgreSQL or another database?
10
PostgreSQL tools
PostgreSQL tools
PostgreSQL contrib
pgFoundry
Additional PostgreSQL-related software
11
PostgreSQL application scaling lifecycle
PostgreSQL application scaling lifecycle
12
Performance tuning as a practice
Performance tuning as a practice
13
Summary
Summary
14
Chapter 2. Database Hardware
Chapter 2. Database Hardware
15
Balancing hardware spending
Balancing hardware spending
CPUs
Memory
Disks
Disk controllers
16
Reliable controller and disk setup
Reliable controller and disk setup
Write-back caches
Performance impact of write-through caching
17
Summary
Summary
18
Chapter 3. Database Hardware Benchmarking
Chapter 3. Database Hardware Benchmarking
19
CPU and memory benchmarking
CPU and memory benchmarking
memtest86+
STREAM memory testing
CPU benchmarking
Sources of slow memory and processors
20
Physical disk performance
Physical disk performance
Random access and I/Os Per Second
Sequential access and ZCAV
Commit rate
21
Disk benchmarking tools
Disk benchmarking tools
hdtune
dd
bonnie++
sysbench
Complicated disk benchmarks
22
Sample disk results
Sample disk results
Disk performance expectations
23
Summary
Summary
24
Chapter 4. Disk Setup
Chapter 4. Disk Setup
25
Maximum filesystem sizes
Maximum filesystem sizes
26
Filesystem crash recovery
Filesystem crash recovery
Journaling filesystems
27
Linux filesystems
Linux filesystems
ext2
ext3
ext4
XFS
Other Linux filesystems
Write barriers
General Linux filesystem tuning
28
Solaris and FreeBSD filesystems
Solaris and FreeBSD filesystems
Solaris UFS
FreeBSD UFS2
ZFS
29
Windows filesystems
Windows filesystems
FAT32
NTFS
30
Disk layout for PostgreSQL
Disk layout for PostgreSQL
Symbolic links
Tablespaces
Database directory tree
Disk arrays, RAID, and disk layout
31
Summary
Summary
32
Chapter 5. Memory for Database Caching
Chapter 5. Memory for Database Caching
Memory units in the postgresql.conf
Increasing UNIX shared memory parameters for larger buffer sizes
33
Inspecting the database cache
Inspecting the database cache
Note
Installing pg_buffercache into a database
Database disk layout
Creating a new block in a database
Writing dirty blocks to disk
34
Crash recovery and the buffer cache
Crash recovery and the buffer cache
Checkpoint processing basics
Write-ahead log and recovery processing
Checkpoint timing
Database block lifecycle
35
Database buffer cache versus operating system cache
Database buffer cache versus operating system cache
Doubly cached data
Checkpoint overhead
Starting size guidelines
36
Analyzing buffer cache contents
Analyzing buffer cache contents
Inspection of the buffer cache queries
Using buffer cache inspection for sizing feedback
37
Summary
Summary
38
Chapter 6. Server Configuration Tuning
Chapter 6. Server Configuration Tuning
39
Interacting with the live configuration
Interacting with the live configuration
Defaults and reset values
Allowed change context
Reloading the configuration file
40
Server-wide settings
Server-wide settings
Database connections
Shared memory
Logging
Vacuuming and statistics
Checkpoints
WAL settings
PITR and WAL Replication
41
Per-client settings
Per-client settings
effective_cache_size
synchronous_commit
work_mem
random_page_cost
constraint_exclusion
Tunables to avoid
42
New server tuning
New server tuning
43
Dedicated server guidelines
Dedicated server guidelines
44
Shared server guidelines
Shared server guidelines
45
pgtune
pgtune
46
Summary
Summary
47
Chapter 7. Routine Maintenance
Chapter 7. Routine Maintenance
48
Transaction visibility with multiversion concurrency control
Transaction visibility with multiversion concurrency control
Visibility computation internals
Updates
Row lock conflicts
Deletions
Advantages of MVCC
Disadvantages of MVCC
Transaction ID wraparound
49
Vacuum
Vacuum
Vacuum Implementation
Cost-based vacuuming
autovacuum
Common vacuum and autovacuum problems
50
Autoanalyze
Autoanalyze
51
Index bloat
Index bloat
Measuring index bloat
52
Detailed data and index page monitoring
Detailed data and index page monitoring
53
Monitoring query logs
Monitoring query logs
Basic PostgreSQL log setup
Logging difficult queries
Log file analysis
54
Summary
Summary
55
Chapter 8. Database Benchmarking
Chapter 8. Database Benchmarking
56
pgbench default tests
pgbench default tests
Table definition
Scale detection
Query script definition
Configuring the database server for pgbench
57
Running pgbench manually
Running pgbench manually
58
Graphing results with pgbench-tools
Graphing results with pgbench-tools
Configuring pgbench-tools
59
Sample pgbench test results
Sample pgbench test results
SELECT-only test
TPC-B-like test
Latency analysis
60
Sources for bad results and variation
Sources for bad results and variation
Developer PostgreSQL builds
Worker threads and pgbench program limitations
61
pgbench custom tests
pgbench custom tests
Insert speed test
62
Transaction Processing Performance Council benchmarks
Transaction Processing Performance Council benchmarks
63
Summary
Summary
64
Chapter 9. Database Indexing
Chapter 9. Database Indexing
65
Indexing example walkthrough
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
Clustering against an index
Explain with buffer counts
66
Index creation and maintenance
Index creation and maintenance
Unique indexes
Concurrent index creation
Clustering an index
Reindexing
67
Index types
Index types
B-tree
Hash
GIN
GiST
68
Advanced index use
Advanced index use
Multicolumn indexes
Indexes for sorting
Partial indexes
Expression-based indexes
Indexing for full-text search
69
Summary
Summary
70
Chapter 10. Query Optimization
Chapter 10. Query Optimization
71
Sample data sets
Sample data sets
Pagila
Dell Store 2
72
EXPLAIN basics
EXPLAIN basics
Timing overhead
Hot and cold cache behavior
73
Query plan node structure
Query plan node structure
Basic cost computation
74
Explain analysis tools
Explain analysis tools
Visual explain
Verbose output
Machine readable explain output
Plan analysis tools
75
Assembling row sets
Assembling row sets
Tuple id
Sequential scan
Index scan
Bitmap heap and index scans
76
Processing nodes
Processing nodes
Sort
Limit
Aggregate
HashAggregate
Unique
Result
Append
Group
Subquery Scan and Subplan
Set operations
Materialize
CTE Scan
77
Joins
Joins
Nested loop
Merge Join
Hash Joins
78
Statistics
Statistics
Viewing and estimating with statistics
Statistics targets
Difficult areas to estimate
79
Other query planning parameters
Other query planning parameters
effective_cache_size
work_mem
constraint_exclusion
cursor_tuple_fraction
80
Executing other statement types
Executing other statement types
81
Improving queries
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
82
SQL Limitations
SQL Limitations
Numbering rows in SQL
Using Window functions for numbering
Using Window functions for cumulatives
83
Summary
Summary
84
Chapter 11. Database Activity and Statistics
Chapter 11. Database Activity and Statistics
85
Statistics views
Statistics views
86
Cumulative and live views
Cumulative and live views
87
Table statistics
Table statistics
Table I/O
88
Index statistics
Index statistics
Index I/O
89
Database wide totals
Database wide totals
90
Connections and activity
Connections and activity
91
Locks
Locks
Virtual transactions
Decoding lock information
Transaction lock waits
Table lock waits
Logging lock information
92
Disk usage
Disk usage
Note
93
Buffer, background writer, and checkpoint activity
Buffer, background writer, and checkpoint activity
Saving pg_stat_bgwriter snapshots
Tuning using background writer statistics
94
Summary
Summary
95
Chapter 12. Monitoring and Trending
Chapter 12. Monitoring and Trending
96
UNIX monitoring tools
UNIX monitoring tools
Sample setup
vmstat
iostat
top
sysstat and sar
97
Windows monitoring tools
Windows monitoring tools
Task Manager
Windows System Monitor
98
Trending software
Trending software
Types of monitoring and trending software
Nagios
Cacti
Munin
Other trending packages
99
Summary
Summary
100
Chapter 13. Pooling and Caching
Chapter 13. Pooling and Caching
101
Connection pooling
Connection pooling
Pooling connection counts
pgpool-II
pgBouncer
102
Database caching
Database caching
memcached
pgmemcache
103
Summary
Summary
104
Chapter 14. Scaling with Replication
Chapter 14. Scaling with Replication
105
Hot Standby
Hot Standby
Terminology
Setting up WAL shipping
Streaming Replication
Tuning Hot Standby
106
Replication queue managers
Replication queue managers
Slony
Londiste
Read scaling with replication queue software
107
Special application requirements
Special application requirements
Bucardo
pgpool-II
108
Other interesting replication projects
Other interesting replication projects
109
Summary
Summary
110
Chapter 15. Partitioning Data
Chapter 15. Partitioning Data
111
Table range partitioning
Table range partitioning
Determining a key field to partition over
Sizing the partitions
Creating the partitions
Redirecting INSERT statements to the partitions
Empty partition query plans
Date change update trigger
Live migration of a partitioned table
Partitioned queries
Creating new partitions
Partitioning advantages
Common partitioning mistakes
112
Horizontal partitioning with PL/Proxy
Horizontal partitioning with PL/Proxy
Hash generation
Scaling with PL/Proxy
Scaling with GridSQL
113
Summary
Summary
114
Chapter 16. Avoiding Common Problems
Chapter 16. Avoiding Common Problems
115
Bulk loading
Bulk loading
Loading methods
Tuning for bulk loads
Skipping WAL acceleration
Recreating indexes and adding constraints
Parallel restore
Post load cleanup
116
Common performance issues
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
Materialized views
117
Profiling the database
Profiling the database
gprof
OProfile
Visual Studio
DTrace
118
Performance related features by version
Performance related features by version
Aggressive PostgreSQL version upgrades
8.1
8.2
8.3
8.4
9.0
119
Summary
Summary

Inspection of the buffer cache queries

Note that due to multiple changes between PostgreSQL in Version 8.3, few of these queries will work without modification on earlier versions. You will have to remove all references to usagecount and in some cases may need to adjust how values are cast between types.

For the following examples, output (which are scripted into the code sample named bufcache.sh included with the book), shared_buffers is set to 256 MB, and pgbench is initialized with a scale of 50 (full coverage of pgbench, another database contrib utility, appears in Chapter 8, Database Benchmarking):

$ pgbench -i -s 50 pgbench
$ psql -x -c "select pg_size_pretty(pg_database_size('pgbench')) as db_size"
-[ RECORD 1 ]---
db_size | 711 MB
$ pgbench -S -c 8 -t 25000 pgbench

This makes for a total database size greater than the shared buffer cache can hold, forcing some prioritization of what is and isn't kept in there, via the usagecount mechanism. After creation...

Previous Section
End of Section 2
Next Section