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.
Table of Contents (120 chapters)
Preface
Free Chapter
1
What this book covers
2
What you need for this book
3
Who this book is for
4
Conventions
5
Reader feedback
7
Chapter 1. PostgreSQL Versions
9
PostgreSQL or another database?
11
PostgreSQL application scaling lifecycle
12
Performance tuning as a practice
13
Summary
14
Chapter 2. Database Hardware
17
Summary
18
Chapter 3. Database Hardware Benchmarking
23
Summary
24
Chapter 4. Disk Setup
25
Maximum filesystem sizes
26
Filesystem crash recovery
28
Solaris and FreeBSD filesystems
29
Windows filesystems
31
Summary
37
Summary
38
Chapter 6. Server Configuration Tuning
42
New server tuning
43
Dedicated server guidelines
44
Shared server guidelines
45
pgtune
46
Summary
47
Chapter 7. Routine Maintenance
50
Autoanalyze
52
Detailed data and index page monitoring
54
Summary
55
Chapter 8. Database Benchmarking
57
Running pgbench manually
58
Graphing results with pgbench-tools
61
pgbench custom tests
62
Transaction Processing Performance Council benchmarks
63
Summary
64
Chapter 9. Database Indexing
67
Index types
69
Summary
70
Chapter 10. Query Optimization
71
Sample data sets
73
Query plan node structure
80
Executing other statement types
83
Summary
84
Chapter 11. Database Activity and Statistics
85
Statistics views
86
Cumulative and live views
87
Table statistics
88
Index statistics
89
Database wide totals
90
Connections and activity
92
Disk usage
94
Summary
95
Chapter 12. Monitoring and Trending
99
Summary
100
Chapter 13. Pooling and Caching
102
Database caching
103
Summary
104
Chapter 14. Scaling with Replication
107
Special application requirements
108
Other interesting replication projects
109
Summary
110
Chapter 15. Partitioning Data
113
Summary
114
Chapter 16. Avoiding Common Problems
119
Summary

What this book covers

Chapter 1, PostgreSQL Versions introduces how PostgreSQL performance has improved in the most recent versions of the databases. It makes a case for using the most recent version feasible, in contrast to the common presumption that newer versions of any software are buggier and slower than their predecessors.

Chapter 2, Database Hardware discusses how the main components in server hardware, including processors, memory, and disks, need to be carefully selected for reliable database storage and a balanced budget. In particular, accidentally using volatile write-back caching in disk controllers and drives can easily introduce database corruption.

Chapter 3, Database Hardware Benchmarking moves on to quantifying the different performance aspects of database hardware. Just how fast is the memory and raw drives in your system? Does performance scale properly as more drives are added?

Chapter 4, Disk Setup looks at popular filesystem choices and suggests the trade-offs of various ways to layout your database on disk. Some common, effective filesystem tuning tweaks are also discussed.

Chapter 5, Memory for Database Caching digs into how the database is stored on disk, in memory, and how the checkpoint process serves to reconcile the two safely. It also suggests how you can actually look at the data being cached by the database, to confirm whether what's being stored in memory matches what you'd expect to be there.

Chapter 6, Server Configuration Tuning covers the most important settings in the postgresql.conf file, what they mean, and how you should set them. And the settings you can cause trouble by changing are pointed out, too.

Chapter 7, Routine Maintenance starts by explaining how PostgreSQL determines what rows are visible to which clients. The way visibility information is stored requires a cleanup process named VACUUM to reuse leftover space properly. Common issues and general tuning suggestions for it and the always running autovacuum are covered. Finally, there's a look at adjusting the amount of data logged by the database, and using a query log analyzer on the result to help find query bottlenecks.

Chapter 8, Database Benchmarking investigates how to get useful benchmark results from the built-in pgbench testing program included with PostgreSQL.

Chapter 9, Database Indexing introduces indexes in terms of how they can reduce the amount of data blocks read to answer a query. That approach allows for thoroughly investigating common questions like why a query is using a sequential scan instead of an index in a robust way.

Chapter 10, Query Optimization is a guided tour of the PostgreSQL optimizer, exposed by showing the way sample queries are executed differently based on what they are asking for and how the database parameters are set.

Chapter 11, Database Activity and Statistics looks at the statistics collected inside the database, and which of them are useful to find problems. The views that let you watch query activity and locking behavior are also explored.

Chapter 12, Monitoring and Trending starts with how to use basic operating system monitoring tools to determine what the database is doing. Then it moves onto suggestions for trending software that can be used to graph this information over time.

Chapter 13, Pooling and Caching explains the difficulties you can encounter when large numbers of connections are made to the database at once. Two types of software packages are suggested to help: connection poolers, to better queue incoming requests, and caches that can answer user requests without connecting to the database.

Chapter 14, Scaling with Replication covers approaches for handling heavier system loads by replicating the data across multiple nodes, typically a set of read-only nodes synchronized to a single writeable master.

Chapter 15, Partitioning Data explores how data might be partitioned into subsets usefully, such that queries can execute against a smaller portion of the database. Approaches discussed include the standard single node database table partitioning, and using PL/Proxy with its associated toolset to build sharded databases across multiple nodes.

Chapter 16, Avoiding Common Problems discusses parts of PostgreSQL that regularly seem to frustrate newcomers to the database. Bulk loading, counting records, and foreign key handling are examples. This chapter ends with a detailed review of what performance related features changed between each version of PostgreSQL from 8.1 to 9.0. Sometimes, the best way to avoid a common problem is to upgrade to version where it doesn't happen anymore.