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

Disk performance expectations

So what are the reasonable expectations for how your disks should perform? The last example shown demonstrates how things should work. Any good drive nowadays should have sequential transfers of well over 50 MB/s on its fastest area, with 100 MB/s being easy to find. The slowest part of the drive will be closer to half that speed. It's good practice to try and test an individual drive before building more complicated arrays using them. If a single drive is slow, you can be sure an array of them will be bad too.

The tricky part of estimating how fast your system should be is when you put multiple drives into an array.

For multiple disks into a RAID1 array, the sequential read and write speed will not increase. However, a good controller or software RAID implementation will use both drives at once for seeking purposes, which might as much as double measurements of that rate.

When multiple drives are added to a RAID0 array, you should get something close...