PostgreSQL 9X之巅（原书第2版） (数据库技术丛书)

内容简介

本书从操作系统到数据库实现的多个层面,讲解了PostgreSQL数据库进行常规性能调优的各个场景。
无论对于从其他数据库转战PostgreSQL的DBA还是使用过PostgreSQL一段时间的DBA,它都是一本不可多得的参考资料。

目录

赞誉
推荐序
译者序
译者简介
中文版序
关于作者
关于审校者
前言
第1章 PostgreSQL版本 1
1.1 PostgreSQL历代版本的性能 2
1.1.1 选择要部署的版本 3
1.1.2 升级到一个新的主版本 3
1.2 选择PostgreSQL还是其他数据库 4
1.3 PostgreSQL工具 5
1.3.1 PostgreSQL contrib 5
1.3.2 pgFoundry 8
1.3.3 其他PostgreSQL相关软件 8
1.4 PostgreSQL应用程序的扩展生命周期 8
1.5 在实践中做性能优化 9
1.6 小结 10
第2章 数据库硬件 11
2.1 平衡硬件支出 11
2.1.1 CPU 11
2.1.2 内存 13
2.1.3 磁盘 13
2.1.4 磁盘控制器 17
2.2 可靠的控制器及磁盘安装 21
2.2.1 驱动器可靠性研究 21
2.2.2 回写缓存 22
2.2.3 直写式缓存的性能影响 25
2.3 小结 25
第3章 数据库硬件的基准评测 27
3.1 CPU和内存基准评测 27
3.1.1 Memtest86+ 28
3.1.2 STREAM内存测试 28
3.1.3 CPU基准评测 29
3.1.4 内存及处理器速度慢的根源 31
3.2 物理磁盘的性能 32
3.2.1 随机存取和IOPS 32
3.2.2 顺序存取和ZCAV 33
3.2.3 提交率 34
3.3 硬盘基准评测工具 34
3.3.1 使用HD Tune进行硬盘基准评测 35
3.3.2 磁盘顺序读写吞吐量 38
3.3.3 Bonnie++ 38
3.3.4 sysbench 42
3.3.5 寻道速率 43
3.3.6 复杂磁盘基准评测 45
3.4 样品磁盘测试结果 45
3.5 小结 48
第4章 磁盘设置 50
4.1 文件系统最大值 50
4.2 文件系统的崩溃恢复 51
4.3 Linux文件系统 52
4.3.1 ext2 52
4.3.2 ext3 53
4.3.3 ext4 54
4.3.4 XFS 55
4.3.5 其他Linux文件系统 56
4.3.6 写入屏障 56
4.3.7 常规Linux文件系统调优 58
4.4 Solaris与FreeBSD文件系统 62
4.4.1 Solaris UFS 62
4.4.2 FreeBSD UFS2 64
4.4.3 ZFS 64
4.4.4 FAT32 66
4.4.5 NTFS 66
4.5 PostgreSQL的磁盘布局 67
4.5.1 符号链接 67
4.5.2 表空间 67
4.5.3 数据库目录树 68
4.5.4 磁盘阵列、RAID和磁盘布局 69
4.6 小结 71
第5章 数据库缓存内存 72
5.1 postgresql.conf中的内存单位 72
5.2 增加UNIX共享内存参数以增大共享内存块 73
5.2.1 内核信号量 74
5.2.2 估算共享内存块的分配 75
5.2.3 检查数据库缓存 76
5.2.4 在数据库中安装pg_buffercache模块 76
5.2.5 数据库磁盘布局 77
5.2.6 在数据库中创建新块 80
5.2.7 写脏块到磁盘 80
5.3 崩溃恢复和缓冲区缓存 81
5.3.1 检查点处理基础 81
5.3.2 预写日志和恢复处理 81
5.3.3 检查点的时间选择 82
5.3.4 数据库块的生命周期 84
5.4 数据库缓存与操作系统缓存对比 85
5.4.1 双重缓存数据 85
5.4.2 检查点开销 86
5.4.3 起始大小指导 86
5.5 分析缓存内容 87
5.5.1 检查缓冲区缓存查询 88
5.5.2 使用缓冲区缓存检查来调整反馈 91
5.6 小结 92
第6章 服务器配置调优 93
6.1 与实时配置交互 93
6.1.1 默认值和重置值 94
6.1.2 允许更改的上下文信息 94
6.1.3 重新加载配置文件 95
6.1.4 数据库连接 97
6.1.5 共享内存 98
6.1.6 日志记录 98
6.1.7 Vacuuming和统计 100
6.1.8 检查点 101
6.1.9 PITR和WAL复制 103
6.1.10 调整以防患于未然 106
6.2 小结 109
第7章 日常维护 110
7.1 事务可见性与多版本并发控制 110
7.1.1 可见性的内部计算 111
7.1.2 更新 111
7.1.3 行锁的冲突 113
7.1.4 删除操作 114
7.1.5 MVCC的优点 115
7.1.6 MVCC的缺点 115
7.1.7 事务ID重叠 116
7.2 vacuum 117
7.2.1 vacuum实现 117
7.2.2 基于开销的vacuum 119
7.2.3 autovacuum 120
7.2.4 vacuum和autovacuum的常见问题 123
7.3 自动分析 126
7.4 索引膨胀 127
7.4.1 测定索引膨胀 127
7.4.2 修复索引膨胀 128
7.5 转储和恢复 129
7.6 清空数据库/表 129
7.7 CLUSTER命令 129
7.8 重建索引 130
7.9 详细数据和索引页的监控 131
7.10 监控查询日志 131
7.10.1 基本的PostgreSQL日志设置 131
7.10.2 对棘手的查询进行日志记录 134
7.10.3 日志文件分析 136
7.11 小结 140
第8章 数据库基准评测 141
8.1 pgbench默认测试 141
8.1.1 表的定义 141
8.1.2 规模检测 143
8.1.3 查询脚本定义 143
8.1.4 为pgbench配置数据库服务器 145
8.2 使用pgbench-tools图形化输出结果 147
8.3 pgbench测试结果样例 148
8.3.1 只读测试 148
8.3.2 类TPC-B测试(读、写) 149
8.3.3 延迟分析 150
8.4 导致不良结果以及变化的缘由 152
8.4.1 PostgreSQL开发人员模式 153
8.4.2 worker线程和pgbench程序的限制 154
8.5 自定义pgbench测试 154
8.6 TPC基准评测 155
8.7 小结 156
第9章 数据库索引 157
9.1 索引例子详解 158
9.1.1 测量查询的磁盘和索引块统计信息 158
9.1.2 运行例子 159
9.1.3 例子数据设置 159
9.1.4 简单索引查找 160
9.1.5 全表扫描 161
9.1.6 索引创建 162
9.1.7 用低效的索引查找 163
9.1.8 联合索引 164
9.1.9 依靠索引聚簇 166
9.1.10 带有缓冲区计数的EXPLAIN 167
9.2 索引创建和维护 168
9.2.1 唯一索引 168
9.2.2 并发创建索引 169
9.2.3 对索引聚簇 169
9.2.4 重建索引 170
9.3 索引类型 170
9.3.1 B-tree 170
9.3.2 哈希 171
9.3.3 GIN 171
9.3.4 GiST 172
9.3.5 SP-GiST 172
9.3.6 BRIN 172
9.3.7 仅索引扫描 173
9.4 count(*) 174
9.5 可见性映射 175
9.6 高级索引使用 175
9.6.1 多列索引 175
9.6.2 索引用于排序 175
9.6.3 部分索引 176
9.6.4 基于表达式的索引 176
9.6.5 索引用于全文搜索 176
9.7 小结 177
第10章 查询优化 178
10.1 样例数据集 178
10.1.1 Pagila 179
10.1.2 Dell Store 2 179
10.2 EXPLAIN基础 180
10.2.1 计时开销 181
10.2.2 冷热缓存行为 181
10.3 执行计划节点结构 183
10.3.1 基本代价计算 184
10.3.2 可视化解释 185
10.3.3 详细输出 186
10.3.4 机器可读的解释输出 186
10.3.5 规划分析工具 188
10.4 组装行集合 188
10.4.1 元组ID 188
10.4.2 顺序扫描 189
10.4.3 索引扫描 189
10.4.4 仅索引扫描 190
10.4.5 位图堆扫描和索引扫描 190
10.5 处理节点 191
10.5.1 排序 191
10.5.2 Limit 192
10.5.3 聚集 193
10.5.4 HashAggregate 194
10.5.5 唯一 194
10.5.6 Result 195
10.5.7 Append 195
10.5.8 Group 196
10.5.9 子查询扫描和子计划 197
10.5.10 集合操作 198
10.5.11 物化 199
10.6 CTE扫描 199
10.7 copy命令 199
10.7.1 COPY TO 200
10.7.2 COPY FROM 200
10.7.3 COPY FREEZE 200
10.8 连接 201
10.8.1 嵌套循环 201
10.8.2 归并连接 203
10.8.3 哈希连接 204
10.8.4 连接顺序 205
10.9 收集统计信息 207
10.9.1 查看和使用统计信息 207
10.9.2 统计信息目标 210
10.9.3 难以估算的部分 211
10.10 其他查询规划参数 211
10.10.1 effective_cache_size 212
10.10.2 work_mem 212
10.10.3 constraint_exclusion 213
10.10.4 cursor_tuple_fraction 213
10.11 执行其他语句类型 214
10.12 改进查询 214
10.12.1 优化完全缓存的数据集 214
10.12.2 查询等价测试 215
10.12.3 禁用优化器特性 215
10.12.4 优化器缺陷的解决方案 218
10.12.5 用OFFSET避免计划重构 219
10.12.6 外部故障点 220
10.13 SQL的限制 221
10.13.1 在SQL中对数据行进行编号 221
10.13.2 使用窗口函数编号 222
10.13.3 使用窗口函数进行累计 222
10.14 小结 223
第11章 数据库活动和统计信息 224
11.1 统计信息视图 224
11.2 累积视图和实时视图 226
11.3 表统计信息 227
11.4 索引统计信息 230
11.5 数据库级别的汇总 231
11.6 连接与行为 232
11.7 锁 232
11.7.1 虚拟事务 233
11.7.2 解码锁信息 234
11.7.3 表级锁的模式 236
11.7.4 事务锁等待 236
11.7.5 表的锁等待 237
11.7.6 记录锁信息 238
11.7.7 保存pg_stat_bgwriter快照 242
11.7.8 通过后台写入进程的统计信息进行调优 243
11.8 小结 245
第12章 监控和趋势预测 247
12.1 UNIX监控工具 247
12.1.1 例子设置 247
12.1.2 vmstat 248
12.1.3 iostat 250
12.1.4 top 257
12.1.5 SysStat和sar 258
12.2 Windows监控工具 260
12.2.1 任务管理器 260
12.2.2 Windows系统监控器 261
12.3 趋势分析软件 262
12.3.1 监控和趋势分析软件类型 263
12.3.2 Nagios 264
12.3.3 Cacti 265
12.3.4 Munin 265
12.3.5 其他趋势分析软件包 266
12.4 小结 268
第13章 连接池和缓存 269
13.1 连接池技术 269
13.1.1 连接池中的连接数 270
13.1.2 pgpool-II 271
13.1.3 连接池 271
13.1.4 复制 271
13.1.5 看门狗 271
13.1.6 故障切换 272
13.1.7 负载均衡 272
13.1.8 pgBouncer 273
13.1.9 memcached是什么 274
13.1.10 pgmemcache 275
13.2 小结 275
第14章 扩展复制 277
14.1 热备份 277
14.1.1 专用术语 278
14.1.2 设置WAL文件传送 279
14.1.3 流复制 279
14.1.4 级联复制 279
14.1.5 同步复制 279
14.1.6 热备份调优 280
14.2 复制队列管理器 281
14.2.1 Slony 281
14.2.2 Londiste 282
14.2.3 使用复制队列软件进行读取扩展 282
14.2.4 特殊的应用程序需求 282
14.2.5 Bucardo 283
14.2.6 pglogical 283
14.2.7 xDB 283
14.2.8 pgpool-II 283
14.3 其他有趣的复制相关的项目 284
14.4 复制方案对比 285
14.5 小结 285
第15章 分区数据 286
15.1 表继承 286
15.2 dellstore2数据库 287
15.3 PostgreSQL中的分区技术 288
15.4 范围分区 288
15.5 确定要进行分区的关键字段 289
15.6 调整分区大小 290
15.7 重定向INSERT语句至分区 290
15.7.1 动态的触发器函数 291
15.7.2 分区规则 292
15.8 空分区的查询规则 293
15.9 修改日期的UPDATE触发器 293
15.10 实时迁移分区表 294
15.11 分区查询 296
15.12 创建新的分区 297
15.12.1 定期创建分区 297
15.12.2 动态创建分区 297
15.13 分区的优势 298
15.14 分区中的常见错误 298
15.15 使用PL/Proxy水平分区 299
15.15.1 哈希生成 300
15.15.2 使用PL/Proxy扩展 301
15.15.3 使用GridSQL扩展 302
15.16 小结 302
第16章 数据库性能分析 304
16.1 使用gprof进行性能分析 304
16.2 使用OProfile进行性能分析 305
16.3 使用Valgrind进行调试 306
16.4 Visual Studio 306
16.5 使用DTrace进行性能分析 307
16.5.1 FreeBSD中的DTrace 307
16.5.2 Linux中SystemTap的DTrace仿真 307
16.6 小结 308
第17章 避免常见问题 309
17.1 批量加载 309
17.1.1 加载方式 309
17.1.2 批量加载调优 310
17.1.3 省略预写式日志加速 311
17.1.4 重建索引和添加约束 312
17.1.5 并行恢复 312
17.1.6 加载后清理 313
17.2 常见性能问题 313
17.2.1 计算行数 313
17.2.2 不明写入 314
17.2.3 慢函数和预处理语句执行 315
17.2.4 PL/pgSQL基准评测 315
17.2.5 高外键开销 316
17.2.6 触发内存使用 317
17.2.7 过重的统计收集器开销 317
17.2.8 物化视图 318
17.3 小结 318
第18章 发行版本性能特征 319
18.1 积极升级PostgreSQL版本 319
18.2 版本8.1中的性能特征 320
18.3 版本8.2中的性能特征 320
18.4 版本8.3中的性能特征 321
18.5 版本8.4中的性能特征 322
18.6 版本9.0中的性能特征 324
18.6.1 复制 324
18.6.2 查询和EXPLAIN 324
18.6.3 数据库开发 325
18.6.4 设置和监控 326
18.7 版本9.1中的性能特征 328
18.8 版本9.2中的性能特征 328
18.9 版本9.3中的性能特征 329
18.10 版本9.4中的性能特征 329
18.11 版本9.5中的性能特征 330
18.12 版本9.6中的性能特征 330
18.13 小结 331