PostgreSQL 9.0性能调校电子书

内容提要

前言

译者序

作者简介

第1章 PostgreSQL版本

1.1 PostgreSQL历史版本的性能

1.1.1 选择部署的版本

1.1.2 升级到更新的主要版本

1.2 使用PostgreSQL还是其他数据库

1.3 PostgreSQL工具

1.3.1 PostgreSQL contrib

1.3.2 pgFoundry

1.3.3 其他一些与PostgreSQL相关的软件

1.4 PostgreSQL应用程序扩展生命周期

1.5 作为实践的性能优化

1.6 小结

第2章 数据库硬件

2.1 平衡硬件支出

2.1.1 CPU

2.1.2 内存

2.1.3 磁盘

2.1.4 磁盘控制器

2.2 可靠的控制器及磁盘安装

2.2.1 回写缓存

2.2.2 直写式缓存的性能影响

2.3 小结

第3章 数据库硬件基准评测

3.1 CPU和内存基准评测

3.1.1 memtest86+

3.1.2 STREAM内存测试

3.1.3 CPU基准评测

3.1.4 内存以及处理器速度慢的缘由

3.2 物理磁盘的性能

3.2.1 每秒随机存取以及I/O数量

3.2.2 顺序存取和区域恒定角速度（ZCAV）

3.2.3 提交速率

3.3 硬盘基准评测工具

3.3.1 hdtune

3.3.2 dd

3.3.3 bonnie++

3.3.4 sysbench

3.3.5 复杂的磁盘基准评测

3.4 样品磁盘的测试结果

磁盘性能的期望值

3.5 小结

第4章 磁盘设置

4.1 文件系统规模的最大值

4.2 文件系统的崩溃恢复

4.3 Linux文件系统

4.3.1 ext2

4.3.2 ext3

4.3.3 ext4

4.3.4 XFS

4.3.5 其他的Linux文件系统

4.3.6 写屏障

4.3.7 常规Linux文件系统调整

4.4 Solaris和FreeBSD文件系统

4.4.1 Solaris UFS

4.4.2 FreeBSD UFS2

4.4.3 ZFS

4.5 Windows文件系统

4.5.1 FAT32

4.5.2 NTFS

4.6 PostgreSQL的磁盘布局

4.6.1 符号链接

4.6.2 表空间

4.6.3 数据库目录树

4.6.4 磁盘阵列、RAID以及磁盘布局

4.7 小结

第5章 数据库高速缓存内存

5.1 postgresql.conf文件中的内存单元

为更大的缓冲区大小而增加UNIX共享内存

5.2 检查数据库高速缓存

5.2.1 在数据库中安装pg_buffercache

5.2.2 数据库磁盘布局

5.2.3 在数据库中创建新的块

5.2.4 将“脏”块写到磁盘

5.3 故障恢复和缓冲区高速缓存

5.3.1 检查点处理基础

5.3.2 预写式日志和恢复处理

5.3.3 检查点的时机掌握

5.3.4 数据库块的生命周期

5.4 数据库缓冲区高速缓存与操作系统高速缓存

5.4.1 双重缓存数据

5.4.2 检查点开销

5.4.3 起始大小指南

5.5 分析缓冲区高速缓存当中的内容

5.5.1 缓冲区缓存查询的检查

5.5.2 使用缓冲区高速缓存检查进行规模的反馈

5.6 小结

第6章 服务器配置调整

6.1 与当前配置进行交互

6.1.1 默认值和重置值

6.1.2 允许更改的上下文信息

6.1.3 重新加载配置文件

6.2 服务器范围内的设置

6.2.1 数据库连接

6.2.2 共享内存

6.2.3 日志

6.2.4 清理与统计信息

6.2.5 检查点

6.2.6 WAL设置

6.2.7 PITR和WAL复制

6.3 每个客户端的设置

6.3.1 effective_cache_size

6.3.2 调整以避免某些情况的出现

6.4 调整新服务器

6.5 专用服务器指南

6.6 共享服务器指南

6.7 pgtune

6.8 小结

第7章 日常维护

7.1 事务可见性以及多版本并发控制

7.1.1 可见性的内部计算

7.1.2 Updates

7.1.3 行锁的冲突

7.1.4 删除

7.1.5 MVCC的优点

7.1.6 MVCC的缺点

7.1.7 事务ID重叠

7.2 Vacuum

7.2.1 Vacuum实现

7.2.2 基于开销的vacuum

7.2.3 autovacuum

7.2.4 常见的一些有关vacuum以及autovacuum的问题

7.3 Autoanalyze

7.4 索引膨胀

7.5 详细数据和索引页面监控

7.6 监控查询日志

7.6.1 基本的PostgreSQL日志设置

7.6.2 对棘手的查询进行日志记录

7.6.3 日志文件分析

7.7 小结

第8章 数据库基准评测

8.1 pgbench默认测试

8.1.1 表的定义

8.1.2 规模检测

8.1.3 查询脚本定义

8.1.4 为pgbench测试配置数据库服务器

8.2 手动运行pgbench测试

8.3 使用pgbench-tools图形化输出结果

8.4 pgbench测试结果样例

8.4.1 SELECT测试

8.4.2 类TPC-B测试

8.4.3 延迟分析

8.5 糟糕的结果以及变化的缘由

8.6 自定义pgbench测试

8.7 事务处理性能委员会（TPC）基准评测

8.8 小结

第9章 数据库索引

9.1 索引样例的演练

9.1.1 查询磁盘以及索引块的统计信息的测量

9.1.2 样例的运行

9.1.3 装载样例数据

9.1.4 简单的索引查找

9.1.5 全表扫描

9.1.6 索引的创建

9.1.7 使用低效的索引进行数据查找

9.1.8 组合索引

9.1.9 从索引转换为顺序扫描

9.1.10 对索引进行聚集

9.1.11 使用缓冲区计数来解释查询计划

9.2 索引的创建与维护

9.2.1 惟一索引

9.2.2 并发索引的创建

9.2.3 聚集索引

9.2.4 重建索引

9.3 索引的类型

9.3.1 B-树

9.3.2 Hash

9.3.3 GIN

9.3.4 GiST

9.4 高级索引应用

9.4.1 多列索引

9.4.2 排序索引

9.4.3 部分索引

9.4.4 基于表达式的索引

9.4.5 全文检索索引

9.5 小结

第10章 查询优化

10.1 数据集样例

10.1.1 Pagila

10.1.2 Dell Store 2

10.2 EXPLAIN基础

10.2.1 时间开销

10.2.2 热门以及冷门高速缓存行为

10.3 查询计划的节点结构

10.4 Explain分析工具

10.4.1 Visual explain

10.4.2 Verbose输出

10.4.3 机器可读的解释输出

10.4.4 计划分析工具

10.5 组合行集

10.5.1 元组ID

10.5.2 顺序扫描

10.5.3 索引扫描

10.5.4 位图堆与索引扫描

10.6 处理节点

10.6.1 Sort

10.6.2 Limit

10.6.3 Aggregate

10.6.4 HashAggregate

10.6.5 Unique

10.6.6 Result

10.6.7 Append

10.6.8 Group

10.6.9 子查询扫描（Subquery Scan）与子计划（Subplan）

10.6.10 集操作

10.6.11 物化

10.6.12 公共表表达式扫描

10.7 连接

10.7.1 循环嵌套

10.7.2 合并连接（Merge Join）

10.7.3 Hash连接

10.8 统计信息

10.8.1 查看与估计统计信息

10.8.2 统计信息指标

10.8.3 估计一些比较难处理的领域

10.9 其他一些查询规划参数

10.9.1 effective_cache_size

10.9.2 work_mem

10.9.3 constraint_exclusion

10.9.4 cursor_tuple_fraction

10.10 执行其他的语句类型

10.11 改进查询

10.11.1 对完全缓存的数据集进行优化

10.11.2 查询等价测试

10.11.3 禁止优化器功能

10.11.4 绕开优化器问题

10.11.5 避免计划使用OFFSET进行重组

10.11.6 外部故障点

10.12 SQL限制

10.12.1 在SQL当中对数据行进行编号

10.12.2 使用窗口函数进行编号

10.12.3 使用窗口函数进行累计

10.13 小结

第11章 数据库活动和统计信息

11.1 统计信息视图

11.2 渐增视图以及实时视图

11.3 表统计信息

11.4 索引统计信息

11.5 数据库范围内的总计

11.6 连接及活动

11.7 锁

11.7.1 虚拟事务

11.7.2 解码锁信息

11.7.3 事务锁等待

11.7.4 表锁等待

11.7.5 记录锁定信息

11.8 磁盘使用

11.9 缓冲区、后台写入器以及检查点活动

11.9.1 保存pg_stat_bgwriter快照信息

11.9.2 使用后台写入器统计信息进行调整

11.10 小结

第12章 监控与趋势预测

12.1 UNIX监控工具

12.1.1 样例安装

12.1.2 vmstat

12.1.3 iosta

12.1.4 top

12.1.5 sysstat及sar

12.2 Windows监控工具

12.2.1 任务管理器

12.2.2 Windows系统监视器

12.3 趋势预测软件

12.3.1 监控以及趋势预测软件的类型

12.3.2 Nagios

12.3.3 Cacti

12.3.4 Munin

12.3.5 其他的一些趋势预测程序包

12.4 小结

第13章 池化与高速缓存

13.1 连接池

13.1.1 连接池计数

13.1.2 pgpool-II

13.1.3 pgBouncer

13.2 数据库高速缓存

13.2.1 memcached

13.2.2 pgmemcache

13.3 小结

第14章 扩展复制

14.1 完全热备（Hot Standby）

14.1.1 术语

14.1.2 设置WAL文件传送

14.1.3 流复制

14.1.4 完全热备的调整

14.2 复制队列管理器

14.2.1 Slony

14.2.2 Londiste

14.2.3 从扩展复制队列软件当中读取信息

14.3 特殊的应用程序需求

14.3.1 Bucardo

14.3.2 pgpool-II

14.4 其他一些有趣的与复制有关的开源项目

14.5 小结

第15章 数据分区

15.1 表范围分区

15.1.1 确定要进行分区的关键字段

15.1.2 调整分区大小

15.1.3 创建分区

15.1.4 重定向INSERT语句至分区

15.1.5 空分区的查询规划

15.1.6 修改日期的update触发器

15.1.7 实时迁移分区表

15.1.8 分区查询

15.1.9 创建新的分区

15.1.10 分区的优势

15.1.11 分区当中的一些常见错误

15.2 使用PL/Proxy水平分区

15.2.1 Hash生成

15.2.2 使用PL/Proxy扩展

15.2.3 使用GridSQL扩展

15.3 小结

第16章 应该避免的一些常见问题

16.1 批量装载

16.1.1 装载方法

16.1.2 批量装载的调整

16.1.3 略过WAL进行加速

16.1.4 重新创建索引及添加约束信息

16.1.5 并行恢复

16.1.6 装载之后的数据整理

16.2 常见的一些性能问题

16.2.1 行的计数

16.2.2 不明写入

16.2.3 执行缓慢的函数与预处理语句的执行

16.2.4 PL/pgSQL基准评测

16.2.5 高外键开销

16.2.6 触发器内存使用

16.2.7 沉重的统计信息收集器开销

16.2.8 物化视图

16.3 分析数据库

16.3.1 gprof

16.3.2 OProfile

16.3.3 Visual Studio

16.3.4 DTrace

16.4 不同版本当中的与性能相关的功能

16.4.1 积极地进行PostgreSQL版本升级

16.4.2 8.1

16.4.3 8.2

16.4.4 8.3

16.4.5 8.4

16.4.6 9.0

16.5 小结