目錄

第1章 PostgreSQL版本 1
1.1 PostgreSQL歷史版本的性能 2
1.1.1 選擇部署的版本 3
1.1.2 升級(jí)到更新的主要版本 3
1.2 使用PostgreSQL還是其他數(shù)據(jù)庫(kù) 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)用程序擴(kuò)展生命周期 10
1.5 作為實(shí)踐的性能優(yōu)化 10
1.6 小結(jié) 12
第2章 數(shù)據(jù)庫(kù)硬件 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ù)庫(kù)硬件基準(zhǔn)評(píng)測(cè) 30
3.1 CPU和內(nèi)存基準(zhǔn)評(píng)測(cè) 30
3.1.1 memtest86+ 31
3.1.2 STREAM內(nèi)存測(cè)試 31
3.1.3 CPU基準(zhǔn)評(píng)測(cè) 33
3.1.4 內(nèi)存以及處理器速度慢的緣由 34
3.2 物理磁盤的性能 35
3.2.1 每秒隨機(jī)存取以及I/O數(shù)量 35
3.2.2 順序存取和區(qū)域恒定角速度(ZCAV) 36
3.2.3 提交速率 37
3.3 磁盤基準(zhǔn)評(píng)測(cè)工具 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)評(píng)測(cè) 48
3.4 樣品磁盤的測(cè)試結(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 符號(hào)鏈接 74
4.6.2 表空間 74
4.6.3 數(shù)據(jù)庫(kù)目錄樹 75
4.6.4 磁盤陣列、RAID以及磁盤布局 76
4.7 小結(jié) 79
第5章 數(shù)據(jù)庫(kù)高速緩存內(nèi)存 81
5.1 postgresql.conf文件中的內(nèi)存單元 81
5.2 檢查數(shù)據(jù)庫(kù)高速緩存 86
5.2.1 在數(shù)據(jù)庫(kù)中安裝pg_buffercache 86
5.2.2 數(shù)據(jù)庫(kù)磁盤布局 87
5.2.3 在數(shù)據(jù)庫(kù)中創(chuàng)建新的塊 90
5.2.4 將“臟”塊寫到磁盤 90
5.3 故障恢復(fù)和緩沖區(qū)高速緩存 91
5.3.1 檢查點(diǎn)處理基礎(chǔ) 91
5.3.2 預(yù)寫式日志和恢復(fù)處理 91
5.3.3 檢查點(diǎn)的時(shí)機(jī)掌握 92
5.3.4 數(shù)據(jù)庫(kù)塊的生命周期 94
5.4 數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存與操作系統(tǒng)高速緩存 95
5.4.1 雙重緩存數(shù)據(jù) 95
5.4.2 檢查點(diǎn)開銷 96
5.4.3 起始大小指南 96
5.5 分析緩沖區(qū)高速緩存當(dāng)中的內(nèi)容 98
5.5.1 緩沖區(qū)緩存查詢的檢查 98
5.5.2 使用緩沖區(qū)高速緩存檢查進(jìn)行規(guī)模的反饋 102
5.6 小結(jié) 103
第6章 服務(wù)器配置調(diào)整 104
6.1 與當(dāng)前配置進(jìn)行交互 104
6.1.1 默認(rèn)值和重置值 105
6.1.2 允許更改的上下文信息 105
6.1.3 重新加載配置文件 106
6.2 服務(wù)器范圍內(nèi)的設(shè)置 108
6.2.1 數(shù)據(jù)庫(kù)連接 108
6.2.2 共享內(nèi)存 109
6.2.3 日志 110
6.2.4 清理與統(tǒng)計(jì)信息 112
6.2.5 檢查點(diǎn) 114
6.2.6 WAL設(shè)置 115
6.2.7 PITR和WAL復(fù)制 117
6.3 每個(gè)客戶端的設(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章 日常維護(hù) 125
7.1 事務(wù)可見性以及多版本并發(fā)控制 125
7.1.1 可見性的內(nèi)部計(jì)算 125
7.1.2 Updates 126
7.1.3 行鎖的沖突 128
7.1.4 刪除 130
7.1.5 MVCC的優(yōu)點(diǎn) 130
7.1.6 MVCC的缺點(diǎn) 131
7.1.7 事務(wù)ID重疊 131
7.2 Vacuum 132
7.2.1 Vacuum實(shí)現(xiàn) 133
7.2.2 基于開銷的vacuum 135
7.2.3 autovacuum 136
7.2.4 常見的一些有關(guān)vacuum以及autovacuum的問(wèn)題 140
7.3 Autoanalyze 144
7.4 索引膨脹 144
7.5 詳細(xì)數(shù)據(jù)和索引頁(yè)面監(jiān)控 147
7.6 監(jiān)控查詢?nèi)罩?148
7.6.1 基本的PostgreSQL日志設(shè)置 148
7.6.2 對(duì)棘手的查詢進(jìn)行日志記錄 152
7.6.3 日志文件分析 153
7.7 小結(jié) 159
第8章 數(shù)據(jù)庫(kù)基準(zhǔn)評(píng)測(cè) 161
8.1 pgbench默認(rèn)測(cè)試 161
8.1.1 表的定義 161
8.1.2 規(guī)模檢測(cè) 162
8.1.3 查詢腳本定義 163
8.1.4 為pgbench測(cè)試配置數(shù)據(jù)庫(kù)服務(wù)器 165
8.2 手動(dòng)運(yùn)行pgbench測(cè)試 166
8.3 使用pgbench-tools圖形化輸出結(jié)果 167
8.4 pgbench測(cè)試結(jié)果樣例 168
8.4.1 SELECT測(cè)試 168
8.4.2 類TPC-B測(cè)試 169
8.4.3 延遲分析 170
8.5 糟糕的結(jié)果以及變化的緣由 173
8.6 自定義pgbench測(cè)試 175
8.7 事務(wù)處理性能委員會(huì)(TPC)基準(zhǔn)評(píng)測(cè) 176
8.8 小結(jié) 177
第9章 數(shù)據(jù)庫(kù)索引 179
9.1 索引樣例的演練 180
9.1.1 查詢磁盤以及索引塊的統(tǒng)計(jì)信息的測(cè)量 180
9.1.2 樣例的運(yùn)行 181
9.1.3 裝載樣例數(shù)據(jù) 181
9.1.4 簡(jiǎn)單的索引查找 183
9.1.5 全表掃描 184
9.1.6 索引的創(chuàng)建 185
9.1.7 使用低效的索引進(jìn)行數(shù)據(jù)查找 186
9.1.8 組合索引 187
9.1.9 從索引轉(zhuǎn)換為順序掃描 188
9.1.10 對(duì)索引進(jìn)行聚集 190
9.1.11 使用緩沖區(qū)計(jì)數(shù)來(lái)解釋查詢計(jì)劃 191
9.2 索引的創(chuàng)建與維護(hù) 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 高級(jí)索引應(yīng)用 198
9.4.1 多列索引 198
9.4.2 排序索引 198
9.4.3 部分索引 199
9.4.4 基于表達(dá)式的索引 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 時(shí)間開銷 205
10.2.2 熱門以及冷門高速緩存行為 206
10.3 查詢計(jì)劃的節(jié)點(diǎn)結(jié)構(gòu) 208
10.4 Explain分析工具 211
10.4.1 Visual explain 211
10.4.2 Verbose輸出 212
10.4.3 機(jī)器可讀的解釋輸出 212
10.4.4 計(jì)劃分析工具 213
10.5 組合行集 214
10.5.1 元組ID 214
10.5.2 順序掃描 215
10.5.3 索引掃描 215
10.5.4 位圖堆與索引掃描 216
10.6 處理節(jié)點(diǎn) 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)與子計(jì)劃(Subplan) 226
10.6.10 集操作 227
10.6.11 物化 229
10.6.12 公共表表達(dá)式掃描 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)計(jì)信息 239
10.8.1 查看與估計(jì)統(tǒng)計(jì)信息 239
10.8.2 統(tǒng)計(jì)信息指標(biāo) 243
10.8.3 估計(jì)一些比較難處理的領(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í)行其他的語(yǔ)句類型 247
10.11 改進(jìn)查詢 248
10.11.1 對(duì)完全緩存的數(shù)據(jù)集進(jìn)行優(yōu)化 249
10.11.2 查詢等價(jià)測(cè)試 249
10.11.3 禁止優(yōu)化器功能 250
10.11.4 繞開優(yōu)化器問(wèn)題 254
10.11.5 避免計(jì)劃使用OFFSET進(jìn)行重組 255
10.11.6 外部故障點(diǎn) 258
10.12 SQL限制 258
10.12.1 在SQL當(dāng)中對(duì)數(shù)據(jù)行進(jìn)行編號(hào) 258
10.12.2 使用窗口函數(shù)進(jìn)行編號(hào) 260
10.12.3 使用窗口函數(shù)進(jìn)行累計(jì) 260
10.13 小結(jié) 262
第11章 數(shù)據(jù)庫(kù)活動(dòng)和統(tǒng)計(jì)信息 264
11.1 統(tǒng)計(jì)信息視圖 264
11.2 漸增視圖以及實(shí)時(shí)視圖 266
11.3 表統(tǒng)計(jì)信息 267
11.4 索引統(tǒng)計(jì)信息 270
11.5 數(shù)據(jù)庫(kù)范圍內(nèi)的總計(jì) 272
11.6 連接及活動(dòng) 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ū)、后臺(tái)寫入器以及檢查點(diǎn)活動(dòng) 284
11.9.1 保存pg_stat_bgwriter快照信息 286
11.9.2 使用后臺(tái)寫入器統(tǒng)計(jì)信息進(jìn)行調(diào)整 289
11.10 小結(jié) 290
第12章 監(jiān)控與趨勢(shì)預(yù)測(cè) 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 趨勢(shì)預(yù)測(cè)軟件 312
12.3.1 監(jiān)控以及趨勢(shì)預(yù)測(cè)軟件的類型 312
12.3.2 Nagios 314
12.3.3 Cacti 315
12.3.4 Munin 316
12.3.5 其他的一些趨勢(shì)預(yù)測(cè)程序包 316
12.4 小結(jié) 319
第13章 池化與高速緩存 321
13.1 連接池 321
13.1.1 連接池計(jì)數(shù) 322
13.1.2 pgpool-II 323
13.1.3 pgBouncer 324
13.2 數(shù)據(jù)庫(kù)高速緩存 325
13.2.1 memcached 325
13.2.2 pgmemcache 326
13.3 小結(jié) 327
第14章 擴(kuò)展復(fù)制 329
14.1 完全熱備(Hot Standby) 329
14.1.1 術(shù)語(yǔ) 330
14.1.2 設(shè)置WAL文件傳送 331
14.1.3 流復(fù)制 331
14.1.4 完全熱備的調(diào)整 332
14.2 復(fù)制隊(duì)列管理器 333
14.2.1 Slony 334
14.2.2 Londiste 334
14.2.3 從擴(kuò)展復(fù)制隊(duì)列軟件當(dāng)中讀取信息 335
14.3 特殊的應(yīng)用程序需求 335
14.3.1 Bucardo 335
14.3.2 pgpool-II 336
14.4 其他一些有趣的與復(fù)制有關(guān)的開源項(xiàng)目 336
14.5 小結(jié) 338
第15章 數(shù)據(jù)分區(qū) 340
15.1 表范圍分區(qū) 340
15.1.1 確定要進(jìn)行分區(qū)的關(guān)鍵字段 341
15.1.2 調(diào)整分區(qū)大小 342
15.1.3 創(chuàng)建分區(qū) 343
15.1.4 重定向INSERT語(yǔ)句至分區(qū) 344
15.1.5 空分區(qū)的查詢規(guī)劃 346
15.1.6 修改日期的update觸發(fā)器 347
15.1.7 實(shí)時(shí)遷移分區(qū)表 348
15.1.8 分區(qū)查詢 350
15.1.9 創(chuàng)建新的分區(qū) 353
15.1.10 分區(qū)的優(yōu)勢(shì) 354
15.1.11 分區(qū)當(dāng)中的一些常見錯(cuò)誤 354
15.2 使用PL/Proxy水平分區(qū) 355
15.2.1 Hash生成 356
15.2.2 使用PL/Proxy擴(kuò)展 357
15.2.3 使用GridSQL擴(kuò)展 359
15.3 小結(jié) 360
第16章 應(yīng)該避免的一些常見問(wèn)題 362
16.1 批量裝載 362
16.1.1 裝載方法 362
16.1.2 批量裝載的調(diào)整 364
16.1.3 略過(guò)WAL進(jìn)行加速 365
16.1.4 重新創(chuàng)建索引及添加約束信息 365
16.1.5 并行恢復(fù) 366
16.1.6 裝載之后的數(shù)據(jù)整理 366
16.2 常見的一些性能問(wèn)題 367
16.2.1 行的計(jì)數(shù) 367
16.2.2 不明寫入 368
16.2.3 執(zhí)行緩慢的函數(shù)與預(yù)處理語(yǔ)句的執(zhí)行 369
16.2.4 PL/pgSQL基準(zhǔn)評(píng)測(cè) 370
16.2.5 高外鍵開銷 370
16.2.6 觸發(fā)器內(nèi)存使用 371
16.2.7 沉重的統(tǒng)計(jì)信息收集器開銷 372
16.2.8 物化視圖 373
16.3 分析數(shù)據(jù)庫(kù) 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 積極地進(jìn)行PostgreSQL版本升級(jí) 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