PostgreSQL9X之巅

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

Contents?目　　录
赞誉
推荐序
译者序
译者简介
中文版序
关于作者
关于审校者
前言
第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

　　《PostgreSQL 9X之巅（原书第2版）》：
　　服务器中的CPU和内存的速度要远快于硬盘驱动器的速度。因此让系统中的其余部件等待硬盘操作完成，特别是等待数据写入完成时，会大大降低整体性能。如果系统在进入下一个任务之前需要等待磁盘完成写操作，说明系统具有直写式缓存（write—through cache）。虽然数据可以临时存储在内存缓存中，但是应用程序的任何写入请求都不被视为完成，直到数据全部写入物理磁盘中。
　　为了让上面这种过程速度更快，通常的做法是在程序执行写操作和磁盘设备之间引入一种不同类型的写缓存。回写缓存（write—back cache）就是将数据拷贝到内存中，然后控制应答应用程序的写请求。然后，这些写操作会在未来的某个时间被异步处理，这个时间由回写缓存的设计所决定，有时可能需要花费几分钟的时间数据才会实际写到磁盘上。
　　当PostgreSQL向WAL日志写信息，有时当它也写到常规的数据库文件时，该信息必须被“刷新到永久存储器上”，以确保数据库发生崩溃时数据库的保护机制可以运行。所以如果用户使用了回写缓存，声称数据写入完成，但是实际还没有真正写入，这样会有什么后果？人们把这种现象称为“说谎的驱动器（驱动器欺骗）”，而且这种情况的后果可能会非常严重。
　　……

Preface?前　　言无论是传统的企业型应用，还是当下的Web应用，作为一种数据库平台，PostgreSQL在为这些应用程序提供数据存储服务方面的可行性日趋成熟。不过，要想获取PostgreSQL的最佳性能，一直以来都并非易事。用户需遵循适宜的经验法则，需持续监控和维护以保障数据库系统健康运行，需以合理化建议定位和处理当前问题，需对数据库相关的附件工具有所涉猎，以便遇到核心数据库无法解决的问题时，可以为其进行功能扩展。
本书内容概述第1章介绍了PostgreSQL最近几个版本的性能改进情况。通常，人们认为所有软件的新版本会问题百出，而且会比以前的版本运行得更慢，但PostgreSQL不存在这个问题。
第2章讨论了如何仔细挑选服务器硬件中的主要部件，包括处理器、内存以及磁盘等，在预算允许的情况下如何组建一个稳定可靠的数据库存储服务器。特别是在磁盘控制器和驱动器方面，如果误用了易失性回写缓存，很容易导致数据库损坏。
第3章讨论了不同数据库硬件性能方面的量化标准。比如在你的系统当中，内存读取到底有多快？磁盘的原始性能有多强？在添加更多的磁盘后，数据库性能是否能正常按比例提升？第4章研究现行文件系统的可选方案，并就如何权衡硬盘中数据库的不同布局提出建议。同时也讨论了一些常见的有效文件系统的调优问题。
第5章深入研究了数据库在磁盘、内存中的存储机制，并解释了checkpoint进程是如何协调这两种存储机制以保证数据安全的。此外，该章还讨论了如何深入查看数据库所缓存的数据，以及在此基础上，确认当下存储在系统内存里的数据与你的预期数据是否一致。
第6章涵盖了postgresql.conf文件中的最重要的一些设置选项，介绍了这些选项的含义以及如何设置这些选项。此外，也指出了可能会引起问题的设置。
第7章首先阐释了PostgreSQL如何判定哪些行数据对哪些用户可见。这些可见性信息的存储方法需要一个名为VACUUM的清理进程来正常地重新使用剩余空间。此外，该章也涉及了一些常见问题以及针对这些问题的调节方式，也涵盖了一直运行的autovacuum。最后，该章介绍了数据库日志的数据量调节方式，以及如何使用查询日志分析器对结果进行分析，从而帮助用户找出查询瓶颈。
第8章研究如何使用PostgreSQL自带的pgbench测试程序来获取有用的基准评测结果。
第9章介绍索引在响应查询时是如何减少数据块的读取量的。这种方法可以彻底探索一些常见问题，比如为什么一个查询会使用顺序扫描而不是使用强大的索引扫描。
第10章是PostgreSQL optimizer（优化器）指南，根据一些查询例子的不同执行方式以及数据库参数的不同设置，来说明数据库的不同优化方式。
第11章介绍了数据库内部所收集的统计信息，以及哪些信息有助于查找问题。同时，也介绍了能够让你看到查询活动以及锁行为的一些视图。
第12章先介绍了如何使用操作系统所提供的基本监视工具以确定数据库的当前动作。然后，该章针对可按照时间对信息趋势进行图形化表示的软件给出了建议。
第13章对在同一时间内对数据库进行大量连接时所遇到的困难进行了说明。同时，建议使用两种软件包——连接池（以更好地对请求进行排序）和缓存（在不连接数据库的情况下响应用户请求）——来帮助解决这些问题。
第14章讲述了如何通过跨节点复制数据来减轻数据库的负载压力，典型的解决方式是，多个同步只读的从节点搭配一个可读写的主节点。
第15章探讨如何有效地将数据划分为子集，这样可以在数据库的一小部分上执行查询。所讨论的方法包括单节点数据库表分区和使用PL/Proxy及其相关工具集来构建跨多节点的共享数据库。
第16章探讨那些在PostgreSQL中看起来阻碍初学者使用的部分。重点是如何找到代码不工作的原因，以及有哪些现有工具可以帮助诊断问题。
第17章以统计记录数和外键处理作为例子，涵盖了所有批量导入部分。同时，该章还包括人们使用PostgreSQL时所遇到的常见问题。
第18章详细介绍了从PostgreSQL 8.1到9.6各个版本中与性能相关的功能变化情况。有时，避免常见问题并获得更好性能的最佳方式就是升级到不再存在问题的新版本。
你需要为阅读本书准备什么为了更好地利用本书，你至少需要一个能够连接服务器并执行查询的PostgreSQL客户端。理想情况下，你最好也是服务器管理员。从http://www.postgresql.org/download 上可以下载支持各种主流操作系统的完整客户端与服务端PostgreSQL安装包。本书的所有例子都是通过命令行方式执行的，通常是运行psql程序。这样可以使它们适合大多数操作系统平台。这样可以直接进行许多操作，而不必使用PostgreSQL的图形界面（GUI）工具，比如pgAdmin III程序。
本书提供的一些脚本是用bash脚本语言编写的，如果你使用的是Windows操作系统，可以从http://www.cygwin.com/ 下载cygwin软件套件，cygwin可以在Windows系统中提供常见的UNIX工具，比如bash。
本书读者本书适用于那些使用或计划使用PostgreSQL的中高级数据库管理员和开发人员。系统管理员可以在安装、配置和监视数据库服务器方面受益。本书对