开源 PostgreSQL 的硬件性能调优(1)(2)

批量排序的内存规模

另一项能调节性能的参数是， 用做批量排序的内存容量。当对大量数据排序时， POSTGRESQL 会将他们拆分成许多小的数据块进行排序。然后将中间结果存在临时文件里面。这些文件最终被合并，重新排序，直到所有的数据行的排序完毕。增加批量处理的内存规模， 能减少临时文件的数量。从而提高排序速度。不过， 如果批量处理的规模设置太大， 会导致交换区的分页操作变得更频繁。这种情况下，使用大量临时文件的小规模批量排序速度比较快。所以， 由交换区分页活跃程度， 决定内存是不是被过量分配。记住， 这个参数是给后台执行排序用的。如： ORDER BY， CREATE INDEX，或者数据合并。有几个并行排序任务， 就需要几倍这样的内存容量。 

这个参数的值， 可以通过 postmaster 命令行参数， 或者配置文件 postgresql.conf 中的 sort_mem 来设置。

缓存规模和排序规模

缓存规模和排序规模都会影响内存的使用。你不可能增加一个的规模， 而不影响另外一个。记住，缓存的规模是在 postmaster 启动的时候， 就设好的。 而排序的规模择由排序的数量决定。一般情况下，缓存规模要大过排序的规模。不过， 某些用到 ORDER BY， CREATE INDEX 或数据合并的查询， 可以通过加大排序规模来提速。 

此外， 许多操作系统对共享内存的分配有限制。修改这一限制， 就意味着， 要重新编译或者配置内核。也就是说， 你要对操作系统这方面相当熟练才行。更多信息， 参考 POSTGRESQL 管理员操作手册，http://developer.postgresql.org/docs/postgres/kernel-resources.html.

在调整的开始，使用15%的RAM作为缓存大小，如果有几个大的事物就用2-4%的内存做排序大小，如果你有很多小事物的话就使用更小的内存。你可以尝试提高它来看看性能是否提升，swapping交换是否发生。如果共享缓存过大，你就花费太多时间来维护大量的缓存，而且它会浪费掉本可以被其他进程使用的RAM，无法作为额外的内核磁盘的缓存。

有价值的服务器参数是effective_cache_size。这个参数被优化器用来估计内核磁盘缓存的大小。在使用统一缓存的内核里，这个值应该设为内核未使用RAM的平均值，因为这样内核就可以使用未使用的RAM来缓存最近访问的磁盘页。在有固定磁盘缓存的内核里，这个值应该设为内核缓存的大小，一般为RAM的10%。

Disk Locality
 

磁盘本身的特点， 决定了他的性能跟上面提到的其他存储方式不同。别的存储方式， 访问数据中的任何一个字节， 速度都是一样的。 而磁盘，由于磁盘片在不断的转动， 磁头在不断的移动，访问离磁头当前位置近的数据， 速度要比离磁头远的数据快。 

磁头从一个柱面， 移动到同一个磁盘片的另外一个柱面， 比较耗时间。Unix 内核开发人员当然知道这一点。所以在磁盘上存储大文件的时候，他们尽可能把同一个文件的存储块紧挨在一起存放。例如：我们有一个文件， 在磁盘上保存， 需要占10个存储块。操作系统会把 1-5 存储块放在一个柱面， 而 6-10 存在另外一个柱面。从头到尾读取这个文件， 只需要磁头移动两次 -- 一次移到存放 1-5 存储块的柱面， 另外一次移到存放 6-10 那个柱面。但是， 如果文件的读取不按存储块的顺序来，比如 1,6,2,7,3,8,4,9,5,10， 那么读完整个文件就要移动磁头十次。 所以， 对于磁盘来说，按顺序访问要比随机访问快的多。这也是为什么， POSTGRESQL 在读取表中的大量数据时， 宁可选择顺序扫描， 也不用索引扫描。 磁盘的这个缺点， 让我们看到了缓存的价值。 

多磁盘

数据库操作期间, 磁头会频繁移动.  太多的读/写请求, 会导致磁盘队列饱和, 性能急剧下降.  (我们可以通过 Vmstat 和 sar 这两种工具, 查看磁盘的活动情况 )

其中一个解决磁盘队列饱和的办法是, 将部分 POSTGRESQL 数据文件移到其他磁盘. 注意, 别把文件移到同一个磁盘的其他文件系统. 因为同一个磁盘上的所有文件系统共享一个磁头.

数据库分布到不同磁盘的方式, 有下列几种:

• 转移数据库, 表, 索引(Databases,_Tables,_Indexes)

• 利用表空间(Tablespace)在不同磁盘上创建对象.

• 转移预写日志

• 通过 initdb -X 和 符号链接文件(symbolic link) 将 pg_xlog 目录移到其他磁盘. 跟其他写操作不同, POSTGRESQL 日志写操作, 必须在事务结束前, 提交给磁盘. 就算使用缓存, 也无法推迟这些写操作. 在不同磁盘上保存日志, 可以减少磁头的移动造成的延时.  ( postgres -F 参数 可以关闭"日志保存(在磁盘上)"这项功能.  但是, 如果遇到操作系统崩溃, 只能通过备份文件恢复.)

其他方法还有, 利用 RAID 磁盘阵列将单个文件系统分布到多个磁盘中. 虽然, 镜像导致数据库写入速度变慢, 但是可以提高读取的速度.  因为, 数据可以同时从多个磁盘上读出来. 很多网站都喜欢用 RAID 1+0 或 RAID0+1, 原因是, 它有分段操作提高性能, 镜像文件保障可靠性. RAID 5 在磁盘数量不少于 6 个的时候, 速度最快. 理论上, RAID5 都有用电池做后备电源的写缓存, 所以磁盘写入操作效率很高, 不至于会拖程序的后腿.

磁盘缓冲

现在的磁盘都有读和写缓冲。读缓冲保存最近的读请求在磁盘的内存里面。 写缓冲保存最近的写请求， 知道他们能有效地存到磁盘上。可能你已经看到了， 这里有个问题 -- 要是写缓冲区里面的数据还没来得及存到磁盘上， 就断电了怎么办？有些磁盘和 RAID 控制器， 都有电池做后备电源， 能将写缓存里面的数据保持到供电回复位置。不过， 多数磁盘和 RAID 控制器都没有这个功能， 所以可靠性是个问题。  

好在， 大部分磁盘都可以关闭写缓存。SCSI 磁盘写缓存一般都是关闭的。 IDE 磁盘的写缓存默认是开启的， 但是可以在操作系统中， 使用命令行 hdparm -W0, sysctl hw.ata.wc = 0, 或 scsicmd 关闭。那是， 有些 IDE 磁盘虽然提示写缓存已经关闭， 其实还是在用。结果导致磁盘变得不稳定。没有代价高昂的测试， 是很难看出来磁盘的写缓存是否真的关闭了。 

由于 PostgreSQL 每次都要调用  fsync() 将预写日志写入磁盘，并等到日志写操作执行完毕，才提交事务。所以， 如果使用写缓存，用户会发现， 性能变快了很多。 因此，对于 PostgreSQL 来说，从性能和可靠性这两方面衡量，最好能使用有电池做后备电源的写缓存。

SCSI vs. IDE

SCSI 盘通常要比 IDE 磁盘贵的多. SCSI 磁盘有个协议, 用于控制器和操作系统之间通信. 而 IDE 磁盘则简单得多, 一次只能接受一个请求.  SCSI 磁盘的带标队列(tagged queueing) 能同时接受多个请求, 并自动排序, 以便提高效率. 这是为什么, 在单用户, 单文件操作的时候,  SCSI 和 IDE 磁盘在性能上如此相似。不过在多用户, 多处理器的情况下,  SCSI 性能要比 IDE 好得多。也正是这个原因, SCSI 更适合用在高负载的数据库服务器上。

其实, SCSI 和 IDE 的差别就只有一种 -- 一个是专为高性能, 高可靠性设计的企业级磁盘；另外一个是优先考虑成本的个人电脑磁盘. 这篇文章, http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf, 详细的描述了, 厂商是如何以性能, 可靠性和成本为衡量标准, 生产制造磁盘的.  是一篇相当不错的磁盘选购指南。

文件系统

一些操作系统支出多磁盘文件系统。一些情况下，这将很难看到哪一个文件系统最好。PostgresSQL通常在传统的Unix文件系统中表现最好，比如很多操作系统支持的BSD UFS/FFS 文件系统。UFS默认的8K块大小和PostgresSQL的分页大小一样。你可以在其上运行日志文件系统或基于日志的文件系统，但是这会增加fsync的先写日志开销。稍早的基于SvR3的文件系统变得太破碎化而无法达到很高的性能。

Linux的文件系统实在太多了，因此很难做出选择。并且没有一个是十全十美的：ext2不是完全崩溃安全，ext3,XFS和JFSare 是基于日志的，而Reiser对小文件很完美而且也登载日志。journalling文件系统比ext2慢了一大截，不过它支持崩溃恢复，ext2最好别用。如果必须要用ext2的话，给它设置下同步。有些人建议ext3系统应该设置data=writebck。

NFS和别的远程文件系统不推荐用在PostgresSQL上。NFS的文件系统的 语义和本地文件系统的语义不同，而这些差别将导致数据可靠度和奔溃恢复的出现问题。

多中央处理器

POSTRGESQL使用多进程模式，意味着每一个数据库都有自己的处理单元。因此，所有的多中央处理器操作系统都可以通过可用的CPU来spread多数据库。然后，如果只有一个数据库连接，那么它只能使用一个CPU。POSTGRESQL不允许使用多线程来让一个进程使用多个CPU。

检查点checkpoint 事件）

当先写日志文件填满后，一个checkpoint事件会强制所有缓冲块进入硬盘好让日志文件再使用，Checkpoints也会定时自动执行，通常时间间隔是5分钟。如果有大量数据库写入，那么先写文件日志将被迅速填满，导致极度缓慢，因为所有的缓冲块都涌向了硬盘。

检查点checkpoint事件）

checkpoint应该每几分钟产生一次。如果一分钟产生好几次的话，性能将会变差。为了判断checkpoint是否过于频繁，检查由checkpoint_warning产生的日志信息。如果你的checkpoint每30s内不止一次就会产生这个信息。

减少checkpoint频率包括增加在data/pg_xlog的先写日志文件。每一个文件为16M，因此对硬盘的影响是可见的。默认安装使用了最小数量的日志文件。为了减少checkpoint的频率，你需要增加这个参数：

• checkpoint_segment=3

默认的值是3，增加这个值直到checkpoint每几分钟才产生一次。另一个日志文件：

• LOG：XLogWrite:new log file created-consider increasing WAL_FILES

这条信息表明postgresql.conf中的wal_files参数应该增加。

总结

幸运的是，POSTGERSQL不需要太多的调整。大部分参数会自动调整以维持最佳性能。管理员也可以控制高速缓存的大小和排序规模的大小来优化可用内存的使用。硬盘存取也可通过驱动延展。其它的参数也可以通过share/pstgresql/conf.sample设置。你可以复制此文件到data/postgresql.conf来尝试POSTGRESQL的一些更另类的参数。

英文原文：PostgreSQL Hardware Performance Tuning

译文出自：http://www.oschina.net/translate/postgresql-hw-performance

---

---