Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases now! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
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 FREE CHAPTER
2. What you need for this book
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

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...

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