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.

Product type Paperback

Published in Oct 2010

Publisher Packt

ISBN-13 9781849510301

Length 468 pages

Edition 1st Edition

Languages

SQL

Tools

PostgreSQL

Concepts

Database Administration

Author (1):

Gregory Smith

View More author details

Table of Contents (120) Chapters

Preface

1. What this book covers FREE CHAPTER

2. What you need for this book

3. Who this book is for

4. Conventions

Note
Note

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

Disk performance expectations

23. Summary

24. Chapter 4. Disk Setup

25. Maximum filesystem sizes

26. Filesystem crash recovery

Journaling filesystems

27. Linux filesystems

28. Solaris and FreeBSD filesystems

29. Windows filesystems

FAT32
NTFS

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

Measuring 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

Configuring pgbench-tools

59. Sample pgbench test results

60. Sources for bad results and variation

61. pgbench custom tests

Insert speed test

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

Basic cost computation

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

Table I/O

88. Index statistics

Index I/O

89. Database wide totals

90. Connections and activity

91. Locks

92. Disk usage

Note

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

Log file analysis

Once you have some log files saved, next you'll want to analyze the results. The common information you'll get is a profile of what queries were run, how long each took individually, and sums showing the totals broken down by type of query. There are several tools available for this purpose, with different trade-offs in terms of things like how active their development community is, how fast the tool runs, and how extensive the installation requirements are.

Normalized query fingerprints

To a person it would be obvious that the following two queries are fundamentally the same:

UPDATE pgbench_accounts SET abalance = abalance + 1631 WHERE aid = 5829858;
UPDATE pgbench_accounts SET abalance = abalance + 4172 WHERE aid = 567923;

This isn't necessarily obvious to a log file parsing program though. Good database programming practice will often convert these to executing with prepared statements, which offers both a security improvement (resistance to...

The rest of the chapter is locked

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.

Table of Contents (120) Chapters

Log file analysis

Normalized query fingerprints

Unlock this book and the full library FREE for 7 days

Personalised recommendations for you