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

Write-back caches

The CPUs and memory in your server are quite fast compared to its disk drives. Accordingly, making the rest of the system wait for the disks, particulary when things need to be written out, can drag overall performance down heavily. Systems that wait for the disks to complete their writes before moving into their next task are referred to as having a write-through cache. While the data may be stored temporarily in a memory cache, until it's made it all the way through to the physical disk, any write an application requested isn't considered complete.

The normal solution to making that faster is to introduce a different type of write cache between the program doing the writing and disks. A write-back cache is one where data is copied into memory, and then control returns to the application that requested the write. Those writes are then handled asynchronously, at some future time dictated by the design of the write-back cache. It can take minutes before the...

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