《PostgreSQL 9.0性能調(diào)校》是針對PostgreSQL 9.0的性能調(diào)校指南，能夠幫助讀者加速PostgreSQL系統(tǒng)，并且避免一些可能導(dǎo)致系統(tǒng)緩慢的常見缺陷。全書共分為16章，分別介紹了PostgreSQL的各個版本、數(shù)據(jù)庫硬件環(huán)境和基準(zhǔn)評測、磁盤設(shè)置、數(shù)據(jù)庫高速緩存、服務(wù)器配置、日常維護、數(shù)據(jù)庫索引、查詢優(yōu)化、數(shù)據(jù)庫活動和統(tǒng)計信息、監(jiān)控與趨勢預(yù)測、池化與高速緩存、擴展復(fù)制、數(shù)據(jù)分區(qū)等性能調(diào)校的方方面面，最后總結(jié)概括了應(yīng)該避免的一些常見問題。

目錄

第1章 PostgreSQL版本 1
1.1 PostgreSQL歷史版本的性能 2
1.1.1 選擇部署的版本 3
1.1.2 升級到更新的主要版本 3
1.2 使用PostgreSQL還是其他數(shù)據(jù)庫 5
1.3 PostgreSQL工具 6
1.3.1 PostgreSQL contrib 6
1.3.2 pgFoundry 9
1.3.3 其他一些與PostgreSQL相關(guān)的軟件 9
1.4 PostgreSQL應(yīng)用程序擴展生命周期 10
1.5 作為實踐的性能優(yōu)化 10
1.6 小結(jié) 12
第2章 數(shù)據(jù)庫硬件 13
2.1 平衡硬件支出 13
2.1.1 CPU 13
2.1.2 內(nèi)存 14
2.1.3 磁盤 15
2.1.4 磁盤控制器 20
2.2 可靠的控制器及磁盤安裝 24
2.2.1 回寫緩存 25
2.2.2 直寫式緩存的性能影響 28
2.3 小結(jié) 29
第3章 數(shù)據(jù)庫硬件基準(zhǔn)評測 30
3.1 CPU和內(nèi)存基準(zhǔn)評測 30
3.1.1 memtest86+ 31
3.1.2 STREAM內(nèi)存測試 31
3.1.3 CPU基準(zhǔn)評測 33
3.1.4 內(nèi)存以及處理器速度慢的緣由 34
3.2 物理磁盤的性能 35
3.2.1 每秒隨機存取以及I/O數(shù)量 35
3.2.2 順序存取和區(qū)域恒定角速度(ZCAV) 36
3.2.3 提交速率 37
3.3 磁盤基準(zhǔn)評測工具 38
3.3.1 hdtune 38
3.3.2 dd 42
3.3.3 bonnie++ 43
3.3.4 sysbench 46
3.3.5 復(fù)雜的磁盤基準(zhǔn)評測 48
3.4 樣品磁盤的測試結(jié)果 49
3.5 小結(jié) 53
第4章 磁盤設(shè)置 55
4.1 文件系統(tǒng)規(guī)模的最大值 55
4.2 文件系統(tǒng)的崩潰恢復(fù) 56
4.3 Linux文件系統(tǒng) 58
4.3.1 ext2 58
4.3.2 ext3 58
4.3.3 ext4 60
4.3.4 XFS 60
4.3.5 其他的Linux文件系統(tǒng) 61
4.3.6 寫屏障 62
4.3.7 常規(guī)Linux文件系統(tǒng)調(diào)整 64
4.4 Solaris和FreeBSD文件系統(tǒng) 68
4.4.1 Solaris UFS 69
4.4.2 FreeBSD UFS2 70
4.4.3 ZFS 71
4.5 Windows文件系統(tǒng) 72
4.5.1 FAT32 73
4.5.2 NTFS 73
4.6 PostgreSQL的磁盤布局 74
4.6.1 符號鏈接 74
4.6.2 表空間 74
4.6.3 數(shù)據(jù)庫目錄樹 75
4.6.4 磁盤陣列、RAID以及磁盤布局 76
4.7 小結(jié) 79
第5章 數(shù)據(jù)庫高速緩存內(nèi)存 81
5.1 postgresql.conf文件中的內(nèi)存單元 81
5.2 檢查數(shù)據(jù)庫高速緩存 86
5.2.1 在數(shù)據(jù)庫中安裝pg_buffercache 86
5.2.2 數(shù)據(jù)庫磁盤布局 87
5.2.3 在數(shù)據(jù)庫中創(chuàng)建新的塊 90
5.2.4 將“臟”塊寫到磁盤 90
5.3 故障恢復(fù)和緩沖區(qū)高速緩存 91
5.3.1 檢查點處理基礎(chǔ) 91
5.3.2 預(yù)寫式日志和恢復(fù)處理 91
5.3.3 檢查點的時機掌握 92
5.3.4 數(shù)據(jù)庫塊的生命周期 94
5.4 數(shù)據(jù)庫緩沖區(qū)高速緩存與操作系統(tǒng)高速緩存 95
5.4.1 雙重緩存數(shù)據(jù) 95
5.4.2 檢查點開銷 96
5.4.3 起始大小指南 96
5.5 分析緩沖區(qū)高速緩存當(dāng)中的內(nèi)容 98
5.5.1 緩沖區(qū)緩存查詢的檢查 98
5.5.2 使用緩沖區(qū)高速緩存檢查進行規(guī)模的反饋 102
5.6 小結(jié) 103
第6章 服務(wù)器配置調(diào)整 104
6.1 與當(dāng)前配置進行交互 104
6.1.1 默認值和重置值 105
6.1.2 允許更改的上下文信息 105
6.1.3 重新加載配置文件 106
6.2 服務(wù)器范圍內(nèi)的設(shè)置 108
6.2.1 數(shù)據(jù)庫連接 108
6.2.2 共享內(nèi)存 109
6.2.3 日志 110
6.2.4 清理與統(tǒng)計信息 112
6.2.5 檢查點 114
6.2.6 WAL設(shè)置 115
6.2.7 PITR和WAL復(fù)制 117
6.3 每個客戶端的設(shè)置 117
6.3.1 effective_cache_size 117
6.3.2 調(diào)整以避免某些情況的出現(xiàn) 120
6.4 調(diào)整新服務(wù)器 121
6.5 專用服務(wù)器指南 121
6.6 共享服務(wù)器指南 122
6.7 pgtune 123
6.8 小結(jié) 123
第7章 日常維護 125
7.1 事務(wù)可見性以及多版本并發(fā)控制 125
7.1.1 可見性的內(nèi)部計算 125
7.1.2 Updates 126
7.1.3 行鎖的沖突 128
7.1.4 刪除 130
7.1.5 MVCC的優(yōu)點 130
7.1.6 MVCC的缺點 131
7.1.7 事務(wù)ID重疊 131
7.2 Vacuum 132
7.2.1 Vacuum實現(xiàn) 133
7.2.2 基于開銷的vacuum 135
7.2.3 autovacuum 136
7.2.4 常見的一些有關(guān)vacuum以及autovacuum的問題 140
7.3 Autoanalyze 144
7.4 索引膨脹 144
7.5 詳細數(shù)據(jù)和索引頁面監(jiān)控 147
7.6 監(jiān)控查詢?nèi)罩?148
7.6.1 基本的PostgreSQL日志設(shè)置 148
7.6.2 對棘手的查詢進行日志記錄 152
7.6.3 日志文件分析 153
7.7 小結(jié) 159
第8章 數(shù)據(jù)庫基準(zhǔn)評測 161
8.1 pgbench默認測試 161
8.1.1 表的定義 161
8.1.2 規(guī)模檢測 162
8.1.3 查詢腳本定義 163
8.1.4 為pgbench測試配置數(shù)據(jù)庫服務(wù)器 165
8.2 手動運行pgbench測試 166
8.3 使用pgbench-tools圖形化輸出結(jié)果 167
8.4 pgbench測試結(jié)果樣例 168
8.4.1 SELECT測試 168
8.4.2 類TPC-B測試 169
8.4.3 延遲分析 170
8.5 糟糕的結(jié)果以及變化的緣由 173
8.6 自定義pgbench測試 175
8.7 事務(wù)處理性能委員會(TPC)基準(zhǔn)評測 176
8.8 小結(jié) 177
第9章 數(shù)據(jù)庫索引 179
9.1 索引樣例的演練 180
9.1.1 查詢磁盤以及索引塊的統(tǒng)計信息的測量 180
9.1.2 樣例的運行 181
9.1.3 裝載樣例數(shù)據(jù) 181
9.1.4 簡單的索引查找 183
9.1.5 全表掃描 184
9.1.6 索引的創(chuàng)建 185
9.1.7 使用低效的索引進行數(shù)據(jù)查找 186
9.1.8 組合索引 187
9.1.9 從索引轉(zhuǎn)換為順序掃描 188
9.1.10 對索引進行聚集 190
9.1.11 使用緩沖區(qū)計數(shù)來解釋查詢計劃 191
9.2 索引的創(chuàng)建與維護 192
9.2.1 惟一索引 193
9.2.2 并發(fā)索引的創(chuàng)建 194
9.2.3 聚集索引 194
9.2.4 重建索引 195
9.3 索引的類型 195
9.3.1 B-樹 196
9.3.2 Hash 197
9.3.3 GIN 197
9.3.4 GiST 197
9.4 高級索引應(yīng)用 198
9.4.1 多列索引 198
9.4.2 排序索引 198
9.4.3 部分索引 199
9.4.4 基于表達式的索引 199
9.4.5 全文檢索索引 200
9.5 小結(jié) 200
第10章 查詢優(yōu)化 202
10.1 數(shù)據(jù)集樣例 202
10.1.1 Pagila 203
10.1.2 Dell Store 2 203
10.2 EXPLAIN基礎(chǔ) 205
10.2.1 時間開銷 205
10.2.2 熱門以及冷門高速緩存行為 206
10.3 查詢計劃的節(jié)點結(jié)構(gòu) 208
10.4 Explain分析工具 211
10.4.1 Visual explain 211
10.4.2 Verbose輸出 212
10.4.3 機器可讀的解釋輸出 212
10.4.4 計劃分析工具 213
10.5 組合行集 214
10.5.1 元組ID 214
10.5.2 順序掃描 215
10.5.3 索引掃描 215
10.5.4 位圖堆與索引掃描 216
10.6 處理節(jié)點 217
10.6.1 Sort 218
10.6.2 Limit 219
10.6.3 Aggregate 220
10.6.4 HashAggregate 221
10.6.5 Unique 222
10.6.6 Result 223
10.6.7 Append 224
10.6.8 Group 225
10.6.9 子查詢掃描(Subquery Scan)與子計劃(Subplan) 226
10.6.10 集操作 227
10.6.11 物化 229
10.6.12 公共表表達式掃描 229
10.7 連接 230
10.7.1 循環(huán)嵌套 230
10.7.2 合并連接(Merge Join) 233
10.7.3 Hash連接 234
10.8 統(tǒng)計信息 239
10.8.1 查看與估計統(tǒng)計信息 239
10.8.2 統(tǒng)計信息指標(biāo) 243
10.8.3 估計一些比較難處理的領(lǐng)域 244
10.9 其他一些查詢規(guī)劃參數(shù) 245
10.9.1 effective_cache_size 245
10.9.2 work_mem 246
10.9.3 constraint_exclusion 247
10.9.4 cursor_tuple_fraction 247
10.10 執(zhí)行其他的語句類型 247
10.11 改進查詢 248
10.11.1 對完全緩存的數(shù)據(jù)集進行優(yōu)化 249
10.11.2 查詢等價測試 249
10.11.3 禁止優(yōu)化器功能 250
10.11.4 繞開優(yōu)化器問題 254
10.11.5 避免計劃使用OFFSET進行重組 255
10.11.6 外部故障點 258
10.12 SQL限制 258
10.12.1 在SQL當(dāng)中對數(shù)據(jù)行進行編號 258
10.12.2 使用窗口函數(shù)進行編號 260
10.12.3 使用窗口函數(shù)進行累計 260
10.13 小結(jié) 262
第11章 數(shù)據(jù)庫活動和統(tǒng)計信息 264
11.1 統(tǒng)計信息視圖 264
11.2 漸增視圖以及實時視圖 266
11.3 表統(tǒng)計信息 267
11.4 索引統(tǒng)計信息 270
11.5 數(shù)據(jù)庫范圍內(nèi)的總計 272
11.6 連接及活動 273
11.7 鎖 274
11.7.1 虛擬事務(wù) 275
11.7.2 解碼鎖信息 276
11.7.3 事務(wù)鎖等待 279
11.7.4 表鎖等待 280
11.7.5 記錄鎖定信息 281
11.8 磁盤使用 282
11.9 緩沖區(qū)、后臺寫入器以及檢查點活動 284
11.9.1 保存pg_stat_bgwriter快照信息 286
11.9.2 使用后臺寫入器統(tǒng)計信息進行調(diào)整 289
11.10 小結(jié) 290
第12章 監(jiān)控與趨勢預(yù)測 292
12.1 UNIX監(jiān)控工具 292
12.1.1 樣例安裝 292
12.1.2 vmstat 293
12.1.3 iosta 296
12.1.4 top 304
12.1.5 sysstat及sar 306
12.2 Windows監(jiān)控工具 309
12.2.1 任務(wù)管理器 309
12.2.2 Windows系統(tǒng)監(jiān)視器 310
12.3 趨勢預(yù)測軟件 312
12.3.1 監(jiān)控以及趨勢預(yù)測軟件的類型 312
12.3.2 Nagios 314
12.3.3 Cacti 315
12.3.4 Munin 316
12.3.5 其他的一些趨勢預(yù)測程序包 316
12.4 小結(jié) 319
第13章 池化與高速緩存 321
13.1 連接池 321
13.1.1 連接池計數(shù) 322
13.1.2 pgpool-II 323
13.1.3 pgBouncer 324
13.2 數(shù)據(jù)庫高速緩存 325
13.2.1 memcached 325
13.2.2 pgmemcache 326
13.3 小結(jié) 327
第14章 擴展復(fù)制 329
14.1 完全熱備(Hot Standby) 329
14.1.1 術(shù)語 330
14.1.2 設(shè)置WAL文件傳送 331
14.1.3 流復(fù)制 331
14.1.4 完全熱備的調(diào)整 332
14.2 復(fù)制隊列管理器 333
14.2.1 Slony 334
14.2.2 Londiste 334
14.2.3 從擴展復(fù)制隊列軟件當(dāng)中讀取信息 335
14.3 特殊的應(yīng)用程序需求 335
14.3.1 Bucardo 335
14.3.2 pgpool-II 336
14.4 其他一些有趣的與復(fù)制有關(guān)的開源項目 336
14.5 小結(jié) 338
第15章 數(shù)據(jù)分區(qū) 340
15.1 表范圍分區(qū) 340
15.1.1 確定要進行分區(qū)的關(guān)鍵字段 341
15.1.2 調(diào)整分區(qū)大小 342
15.1.3 創(chuàng)建分區(qū) 343
15.1.4 重定向INSERT語句至分區(qū) 344
15.1.5 空分區(qū)的查詢規(guī)劃 346
15.1.6 修改日期的update觸發(fā)器 347
15.1.7 實時遷移分區(qū)表 348
15.1.8 分區(qū)查詢 350
15.1.9 創(chuàng)建新的分區(qū) 353
15.1.10 分區(qū)的優(yōu)勢 354
15.1.11 分區(qū)當(dāng)中的一些常見錯誤 354
15.2 使用PL/Proxy水平分區(qū) 355
15.2.1 Hash生成 356
15.2.2 使用PL/Proxy擴展 357
15.2.3 使用GridSQL擴展 359
15.3 小結(jié) 360
第16章 應(yīng)該避免的一些常見問題 362
16.1 批量裝載 362
16.1.1 裝載方法 362
16.1.2 批量裝載的調(diào)整 364
16.1.3 略過WAL進行加速 365
16.1.4 重新創(chuàng)建索引及添加約束信息 365
16.1.5 并行恢復(fù) 366
16.1.6 裝載之后的數(shù)據(jù)整理 366
16.2 常見的一些性能問題 367
16.2.1 行的計數(shù) 367
16.2.2 不明寫入 368
16.2.3 執(zhí)行緩慢的函數(shù)與預(yù)處理語句的執(zhí)行 369
16.2.4 PL/pgSQL基準(zhǔn)評測 370
16.2.5 高外鍵開銷 370
16.2.6 觸發(fā)器內(nèi)存使用 371
16.2.7 沉重的統(tǒng)計信息收集器開銷 372
16.2.8 物化視圖 373
16.3 分析數(shù)據(jù)庫 373
16.3.1 gprof 373
16.3.2 OProfile 373
16.3.3 Visual Studio 374
16.3.4 DTrace 374
16.4 不同版本當(dāng)中的與性能相關(guān)的功能 375
16.4.1 積極地進行PostgreSQL版本升級 375
16.4.2 8.1 377
16.4.3 8.2 377
16.4.4 8.3 377
16.4.5 8.4 379
16.4.6 9.0 381
16.5 小結(jié) 385