PostgreSQL 9.0性能调校

出版时间：2013-4  出版社：人民邮电出版社  作者：史密斯  
Tag标签：无  

内容概要

《PostgreSQL 9.0性能调校》是针对PostgreSQL 9.0的性能调校指南，能够帮助读者加速PostgreSQL系统，并且避免一些可能导致系统缓慢的常见缺陷。全书共分为16章，分别介绍了PostgreSQL的各个版本、数据库硬件环境和基准评测、磁盘设置、数据库高速缓存、服务器配置、日常维护、数据库索引、查询优化、数据库活动和统计信息、监控与趋势预测、池化与高速缓存、扩展复制、数据分区等性能调校的方方面面，最后总结概括了应该避免的一些常见问题。

作者简介

Gregory Smith是国际数据库专业服务公司2ndQuadrant的首席顾问，并且是公司在美国的第一个办事处的创始人。编著PostgreSQL是他进军数据库性能调整教学领域的第二个扩展。在1995年，Greg（格里格）写了一本较小的免费电子书《Progress Performance FAQ》，主要内容是如何使Progress 4GL和相关数据库能够运行更快。2001年，他专注于PostgreSQL 7.0，一直关注复杂性问题，该数据库的发行版都能够解决这些问题。从PostgreSQL 8.3开始，他对每个PostgreSQL版本都做出了贡献，添加附加的功能。他还为数据库编写了一些额外的工具集，目前包括有pgtune、pgbench-tools、peg和2warm等。

书籍目录

第1章 PostgreSQL版本 1 1.1 PostgreSQL历史版本的性能 2 1.1.1 选择部署的版本 3 1.1.2 升级到更新的主要版本 3 1.2 使用PostgreSQL还是其他数据库 5 1.3 PostgreSQL工具 6 1.3.1 PostgreSQLcontrib 6 1.3.2 pgFoundry 9 1.3.3 其他一些与PostgreSQL相关的软件 9 1.4 PostgreSQL应用程序扩展生命周期 10 1.5 作为实践的性能优化 10 1.6 小结 12 第2章 数据库硬件 13 2.1 平衡硬件支出 13 2.1.1 CPU 13 2.1.2 内存 14 2.1.3 磁盘 15 2.1.4 磁盘控制器 20 2.2 可靠的控制器及磁盘安装 24 2.2.1 回写缓存 25 2.2.2 直写式缓存的性能影响 28 2.3 小结 29 第3章 数据库硬件基准评测 30 3.1 CPU和内存基准评测 30 3.1.1 memtest86+ 31 3.1.2 STREAM内存测试 31 3.1.3 CPU基准评测 33 3.1.4 内存以及处理器速度慢的缘由 34 3.2 物理磁盘的性能 35 3.2.1 每秒随机存取以及I/O数量 35 3.2.2 顺序存取和区域恒定角速度（ZCAV） 36 3.2.3 提交速率 37 3.3 磁盘基准评测工具 38 3.3.1 hdtune 38 3.3.2 dd 42 3.3.3 bonnie++ 43 3.3.4 sysbench 46 3.3.5 复杂的磁盘基准评测 48 3.4 样品磁盘的测试结果 49 3.5 小结 53 第4章 磁盘设置 55 4.1 文件系统规模的最大值 55 4.2 文件系统的崩溃恢复 56 4.3 Linux文件系统 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文件系统 61 4.3.6 写屏障 62 4.3.7 常规Linux文件系统调整 64 4.4 Solaris和FreeBSD文件系统 68 4.4.1 SolarisUFS 69 4.4.2 FreeBSDUFS2 70 4.4.3 ZFS 71 4.5 Windows文件系统 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 数据库目录树 75 4.6.4 磁盘阵列、RAID以及磁盘布局 76 4.7 小结 79 第5章 数据库高速缓存内存 81 5.1 postgresql.conf文件中的内存单元 81 5.2 检查数据库高速缓存 86 5.2.1 在数据库中安装pg_buffercache 86 5.2.2 数据库磁盘布局 87 5.2.3 在数据库中创建新的块 90 5.2.4 将“脏”块写到磁盘 90 5.3 故障恢复和缓冲区高速缓存 91 5.3.1 检查点处理基础 91 5.3.2 预写式日志和恢复处理 91 5.3.3 检查点的时机掌握 92 5.3.4 数据库块的生命周期 94 5.4 数据库缓冲区高速缓存与操作系统高速缓存 95 5.4.1 双重缓存数据 95 5.4.2 检查点开销 96 5.4.3 起始大小指南 96 5.5 分析缓冲区高速缓存当中的内容 98 5.5.1 缓冲区缓存查询的检查 98 5.5.2 使用缓冲区高速缓存检查进行规模的反馈 102 5.6 小结 103 第6章 服务器配置调整 104 6.1 与当前配置进行交互 104 6.1.1 默认值和重置值 105 6.1.2 允许更改的上下文信息 105 6.1.3 重新加载配置文件 106 6.2 服务器范围内的设置 108 6.2.1 数据库连接 108 6.2.2 共享内存 109 6.2.3 日志 110 6.2.4 清理与统计信息 112 6.2.5 检查点 114 6.2.6 WAL设置 115 6.2.7 PITR和WAL复制 117 6.3 每个客户端的设置 117 6.3.1 effective_cache_size 117 6.3.2 调整以避免某些情况的出现 120 6.4 调整新服务器 121 6.5 专用服务器指南 121 6.6 共享服务器指南 122 6.7 pgtune 123 6.8 小结 123 第7章 日常维护 125 7.1 事务可见性以及多版本并发控制 125 7.1.1 可见性的内部计算 125 7.1.2 Updates 126 7.1.3 行锁的冲突 128 7.1.4 删除 130 7.1.5 MVCC的优点 130 7.1.6 MVCC的缺点 131 7.1.7 事务ID重叠 131 7.2 Vacuum 132 7.2.1 Vacuum实现 133 7.2.2 基于开销的vacuum 135 7.2.3 autovacuum 136 7.2.4 常见的一些有关vacuum以及autovacuum的问题 140 7.3 Autoanalyze 144 7.4 索引膨胀 144 7.5 详细数据和索引页面监控 147 7.6 监控查询日志 148 7.6.1 基本的PostgreSQL日志设置 148 7.6.2 对棘手的查询进行日志记录 152 7.6.3 日志文件分析 153 7.7 小结 159 第8章 数据库基准评测 161 8.1 pgbench默认测试 161 8.1.1 表的定义 161 8.1.2 规模检测 162 8.1.3 查询脚本定义 163 8.1.4 为pgbench测试配置数据库服务器 165 8.2 手动运行pgbench测试 166 8.3 使用pgbench—tools图形化输出结果 167 8.4 pgbench测试结果样例 168 8.4.1 SELECT测试 168 8.4.2 类TPC—B测试 169 8.4.3 延迟分析 170 8.5 糟糕的结果以及变化的缘由 173 8.6 自定义pgbench测试 175 8.7 事务处理性能委员会（TPC）基准评测 176 8.8 小结 177 第9章 数据库索引 179 9.1 索引样例的演练 180 9.1.1 查询磁盘以及索引块的统计信息的测量 180 9.1.2 样例的运行 181 9.1.3 装载样例数据 181 9.1.4 简单的索引查找 183 9.1.5 全表扫描 184 9.1.6 索引的创建 185 9.1.7 使用低效的索引进行数据查找 186 9.1.8 组合索引 187 9.1.9 从索引转换为顺序扫描 188 9.1.10 对索引进行聚集 190 9.1.11 使用缓冲区计数来解释查询计划 191 9.2 索引的创建与维护 192 9.2.1 惟一索引 193 9.2.2 并发索引的创建 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 高级索引应用 198 9.4.1 多列索引 198 9.4.2 排序索引 198 9.4.3 部分索引 199 9.4.4 基于表达式的索引 199 9.4.5 全文检索索引 200 9.5 小结 200 第10章 查询优化 202 10.1 数据集样例 202 10.1.1 Pagila 203 10.1.2 DellStore2 203 10.2 EXPLAIN基础 205 10.2.1 时间开销 205 10.2.2 热门以及冷门高速缓存行为 206 10.3 查询计划的节点结构 208 10.4 Explain分析工具 211 10.4.1 Visualexplain 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 处理节点 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 子查询扫描（SubqueryScan）与子计划（Subplan） 226 10.6.10 集操作 227 10.6.11 物化 229 10.6.12 公共表表达式扫描 229 10.7 连接 230 10.7.1 循环嵌套 230 10.7.2 合并连接（MergeJoin） 233 10.7.3 Hash连接 234 10.8 统计信息 239 10.8.1 查看与估计统计信息 239 10.8.2 统计信息指标 243 10.8.3 估计一些比较难处理的领域 244 10.9 其他一些查询规划参数 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 执行其他的语句类型 247 10.11 改进查询 248 10.11.1 对完全缓存的数据集进行优化 249 10.11.2 查询等价测试 249 10.11.3 禁止优化器功能 250 10.11.4 绕开优化器问题 254 10.11.5 避免计划使用OFFSET进行重组 255 10.11.6 外部故障点 258 10.12 SQL限制 258 10.12.1 在SQL当中对数据行进行编号 258 10.12.2 使用窗口函数进行编号 260 10.12.3 使用窗口函数进行累计 260 10.13 小结 262 第11章 数据库活动和统计信息 264 11.1 统计信息视图 264 11.2 渐增视图以及实时视图 266 11.3 表统计信息 267 11.4 索引统计信息 270 11.5 数据库范围内的总计 272 11.6 连接及活动 273 11.7 锁 274 11.7.1 虚拟事务 275 11.7.2 解码锁信息 276 11.7.3 事务锁等待 279 11.7.4 表锁等待 280 11.7.5 记录锁定信息 281 11.8 磁盘使用 282 11.9 缓冲区、后台写入器以及检查点活动 284 11.9.1 保存pg_stat_bgwriter快照信息 286 11.9.2 使用后台写入器统计信息进行调整 289 11.10 小结 290 第12章 监控与趋势预测 292 12.1 UNIX监控工具 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监控工具 309 12.2.1 任务管理器 309 12.2.2 Windows系统监视器 310 12.3 趋势预测软件 312 12.3.1 监控以及趋势预测软件的类型 312 12.3.2 Nagios 314 12.3.3 Cacti 315 12.3.4 Munin 316 12.3.5 其他的一些趋势预测程序包 316 12.4 小结 319 第13章 池化与高速缓存 321 13.1 连接池 321 13.1.1 连接池计数 322 13.1.2 pgpool—II 323 13.1.3 pgBouncer 324 13.2 数据库高速缓存 325 13.2.1 memcached 325 13.2.2 pgmemcache 326 13.3 小结 327 第14章 扩展复制 329 14.1 完全热备（HotStandby） 329 14.1.1 术语 330 14.1.2 设置WAL文件传送 331 14.1.3 流复制 331 14.1.4 完全热备的调整 332 14.2 复制队列管理器 333 14.2.1 Slony 334 14.2.2 Londiste 334 14.2.3 从扩展复制队列软件当中读取信息 335 14.3 特殊的应用程序需求 335 14.3.1 Bucardo 335 14.3.2 pgpool—II 336 14.4 其他一些有趣的与复制有关的开源项目 336 14.5 小结 338 第15章 数据分区 340 15.1 表范围分区 340 15.1.1 确定要进行分区的关键字段 341 15.1.2 调整分区大小 342 15.1.3 创建分区 343 15.1.4 重定向INSERT语句至分区 344 15.1.5 空分区的查询规划 346 15.1.6 修改日期的update触发器 347 15.1.7 实时迁移分区表 348 15.1.8 分区查询 350 15.1.9 创建新的分区 353 15.1.10 分区的优势 354 15.1.11 分区当中的一些常见错误 354 15.2 使用PL/Proxy水平分区 355 15.2.1 Hash生成 356 15.2.2 使用PL/Proxy扩展 357 15.2.3 使用GridSQL扩展 359 15.3 小结 360 第16章 应该避免的一些常见问题 362 16.1 批量装载 362 16.1.1 装载方法 362 16.1.2 批量装载的调整 364 16.1.3 略过WAL进行加速 365 16.1.4 重新创建索引及添加约束信息 365 16.1.5 并行恢复 366 16.1.6 装载之后的数据整理 366 16.2 常见的一些性能问题 367 16.2.1 行的计数 367 16.2.2 不明写入 368 16.2.3 执行缓慢的函数与预处理语句的执行 369 16.2.4 PL/pgSQL基准评测 370 16.2.5 高外键开销 370 16.2.6 触发器内存使用 371 16.2.7 沉重的统计信息收集器开销 372 16.2.8 物化视图 373 16.3 分析数据库 373 16.3.1 gprof 373 16.3.2 OProfile 373 16.3.3 VisualStudio 374 16.3.4 DTrace 374 16.4 不同版本当中的与性能相关的功能 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 小结 385

章节摘录

版权页：   插图：   当向RAID阵列中添加更多的硬盘时，用户更期望看到这几乎完美的比例。显然不能只根据平均性能来做出决定，可能因为所创建的文件不一定会分配在磁盘的相同部分。在本章中，有关ZCAV的问题已经被多次提及，因为它经常在用户要进行公平的磁盘基准评测时干扰结果。 磁舟件能的期望信 磁盘合理的预期性能表现应该是怎么样？在上一个例子当中显示了其执行的情况。当前性能良好的硬盘的顺序传输速率在最快的区域应该为50MB／s以上，并且应该很容易达到100MB／s。硬盘最慢的部分接近该速度的一半。在创建更为复杂的阵列并要投入使用之前，测试单个硬盘的性能是很好的做法。如果单块硬盘的速度较慢，用户几乎可以确定该硬盘所在的阵列也不怎么样。 预估用户组建多块硬盘的阵列中系统的速度是一件棘手的事。 对于RAID 1阵列中的硬盘，顺序读和写的速度不会增加。但是，较好的控制器或者实施较好的软RAID会在寻道的时候同时使用几块硬盘，其速度差不多是单块硬盘的两倍。 向RAID 0阵列中添加多块硬盘时，会得到类似于上一节中讲到的线性扩展的速度。两块速度为50MB／s的硬盘，在RAID 0中的速度应该很接近100MB／s。在大多数情况下可能不能有这样完美的表现，但肯定比单块硬盘的速度要快。 像RAID 10这样的阵列应该将顺序读／写等操作按照RAID 0模式工作，而提高寻道时间则使用RAID 1镜像的模式。这种组合是多数数据库选择的磁盘结构的原因。 如果使用的是大多数数据库不推荐使用的RAID 5模式，读的速度与所使用的磁盘个数成比例，而写的速度则不会提高。 磁盘和阵列性能较低的缘由 在大多数情况下，如果顺序读取和写的速度满足用户的期望值，则用户的磁盘子系统工作得很好。用户可以测试寻道时间，但除了添加更多的磁盘外，用户能做的事情不多。更重要的是单个物理磁盘的功能比用户能做的事情更多。速度较慢的问题表现为读速度和写速度都会很慢。 控制器的驱动质量差应该是速度较慢的一个主要原因。通常可以将相同的磁盘接到其他的控制器来弄清楚这个情况。例如，如果当有一块SATA硬盘连接到RAID控制器时速度很慢，但连接到主板时速度很快，那就很有可能是驱动的问题。

媒体关注与评论

本书内容包括· 为处理高要求的应用程序而升级PostgreSQL安装的最佳实践；·揭秘硬件适合或不适合高性能数据库应用程序的原因；·深入研究在速度与稳定性之间进行取舍的手段；·调整操作系统以获得最佳的数据库性能；·从硬件到应用，全方位评测整体系统；·通过实例学习如何调整服务器参数，以及这些参数如何影响性能；·监视服务器上的数据库的内外部情况；·寻找最佳插件工具来扩展核心PostgreSQL数据库；·探索如何使用PostgreSQL 9.0新增特性来架构复制的系统。

编辑推荐

PostgreSQL是近年来应用非常广泛、热度逐渐提升的数据库软件。而数据库性能的问题，一直是众多数据库管理员、开发人员所关心的话题，也是难题。《PostgreSQL 9.0性能调校》能有效帮助读者加速PostgreSQL系统，避免影响性能的常见陷阱。《PostgreSQL 9.0性能调校》内容包括· 为处理高要求的应用程序而升级PostgreSQL安装的最佳实践；·揭秘硬件适合或不适合高性能数据库应用程序的原因；·深入研究在速度与稳定性之间进行取舍的手段；·调整操作系统以获得最佳的数据库性能；·从硬件到应用，全方位评测整体系统；·通过实例学习如何调整服务器参数，以及这些参数如何影响性能；·监视服务器上的数据库的内外部情况；·寻找最佳插件工具来扩展核心PostgreSQL数据库；·探索如何使用PostgreSQL 9.0新增特性来架构复制的系统。

用户评论 (总计5条)

•   读过的翻译最烂的一本书, 多处不知所云.
•   翻译太烂了，毁了这本书，可惜pg的书本来就很少
•   看了一下，讲得还行吧
•   一般把，不过国内的中文资料比较少，算是可以的
•   5分是给出版社引进PGSQL书的行为,翻译质量还不清楚,才拿到还没看