Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
PostgreSQL 9.0 High Performance

You're reading from   PostgreSQL 9.0 High Performance If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.

Arrow left icon
Product type Paperback
Published in Oct 2010
Publisher Packt
ISBN-13 9781849510301
Length 468 pages
Edition 1st Edition
Languages
Arrow right icon
Author (1):
Arrow left icon
Gregory Smith Gregory Smith
Author Profile Icon Gregory Smith
Gregory Smith
Arrow right icon
View More author details
Toc

Table of Contents (120) Chapters Close

Preface
1. What this book covers
2. What you need for this book FREE CHAPTER
3. Who this book is for
4. Conventions 5. Reader feedback
6. Customer support 7. Chapter 1. PostgreSQL Versions
8. Performance of historical PostgreSQL releases 9. PostgreSQL or another database?
10. PostgreSQL tools 11. PostgreSQL application scaling lifecycle
12. Performance tuning as a practice
13. Summary
14. Chapter 2. Database Hardware
15. Balancing hardware spending 16. Reliable controller and disk setup 17. Summary
18. Chapter 3. Database Hardware Benchmarking
19. CPU and memory benchmarking 20. Physical disk performance 21. Disk benchmarking tools 22. Sample disk results 23. Summary
24. Chapter 4. Disk Setup
25. Maximum filesystem sizes
26. Filesystem crash recovery 27. Linux filesystems 28. Solaris and FreeBSD filesystems 29. Windows filesystems 30. Disk layout for PostgreSQL 31. Summary
32. Chapter 5. Memory for Database Caching 33. Inspecting the database cache 34. Crash recovery and the buffer cache 35. Database buffer cache versus operating system cache 36. Analyzing buffer cache contents 37. Summary
38. Chapter 6. Server Configuration Tuning
39. Interacting with the live configuration 40. Server-wide settings 41. Per-client settings 42. New server tuning
43. Dedicated server guidelines
44. Shared server guidelines
45. pgtune
46. Summary
47. Chapter 7. Routine Maintenance
48. Transaction visibility with multiversion concurrency control 49. Vacuum 50. Autoanalyze
51. Index bloat 52. Detailed data and index page monitoring
53. Monitoring query logs 54. Summary
55. Chapter 8. Database Benchmarking
56. pgbench default tests 57. Running pgbench manually
58. Graphing results with pgbench-tools 59. Sample pgbench test results 60. Sources for bad results and variation 61. pgbench custom tests 62. Transaction Processing Performance Council benchmarks
63. Summary
64. Chapter 9. Database Indexing
65. Indexing example walkthrough 66. Index creation and maintenance 67. Index types 68. Advanced index use 69. Summary
70. Chapter 10. Query Optimization
71. Sample data sets 72. EXPLAIN basics 73. Query plan node structure 74. Explain analysis tools 75. Assembling row sets 76. Processing nodes 77. Joins 78. Statistics 79. Other query planning parameters 80. Executing other statement types
81. Improving queries 82. SQL Limitations 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
91. Locks 92. Disk usage 93. Buffer, background writer, and checkpoint activity 94. Summary
95. Chapter 12. Monitoring and Trending
96. UNIX monitoring tools 97. Windows monitoring tools 98. Trending software 99. Summary
100. Chapter 13. Pooling and Caching
101. Connection pooling 102. Database caching 103. Summary
104. Chapter 14. Scaling with Replication
105. Hot Standby 106. Replication queue managers 107. Special application requirements 108. Other interesting replication projects
109. Summary
110. Chapter 15. Partitioning Data
111. Table range partitioning 112. Horizontal partitioning with PL/Proxy 113. Summary
114. Chapter 16. Avoiding Common Problems
115. Bulk loading 116. Common performance issues 117. Profiling the database 118. Performance related features by version 119. Summary

Sample disk results

Here's a summary of what was measured for the laptop drive tested in detail previously, as well as a desktop drive both alone and in a RAID array as a second useful data point:

Disks

Seq Read

Seq Write

bonnie++ seeks

sysbench seeks

Commits per sec

Seagate 320GB 7200.4 Laptop

71

58

232 @ 4GB

194 @ 4GB

105 or 1048

WD160GB 7200RPM

59

54

177 @ 16GB

56 @ 100GB

10212

3X WD160GB RAID0

125

119

371 @ 16GB

60 @ 100GB

10855

Note how all the seek-related information is reported here relative to the size of the area being used to seek over. This is a good habit to adopt. Also note that in the laptop rate, two commit rates are reported. The lower value is without the write cache enabled (just under the rotation rate of 120 rotations/second), while the higher one has it turned on—and therefore providing an unsafe, volatile write cache.

The other two samples use an Areca ARC-1210 controller with a 256 MB...

lock icon The rest of the chapter is locked
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image