POSTGRESQL是由来自世界各地的网络开发人员团队开发的对象关系数据库。它是一个开源版本，可以替代Oracle和Informix等商业数据库。POSTGRESQL最初由加州大学伯克利分校开发。1996年，一个小组开始在Internet上开发数据库。他们使用电子邮件分享想法，使用文件服务器分享代码。POSTGRESQL现在可以在功能、性能和可靠性方面与商业数据库相媲美。它支持事务、视图、存储过程和引用完整性约束。它还支持大量的编程接口，包括ODBC、Java（JDBC）、TCL/TK、PHP、Perl和Python。得益于互联网开发者的人才储备，POSTGRESQL还有很大的成长空间。性能概念数据库性能优化有两个方面。一方面是为了提高数据库对计算机CPU、内存和硬盘的使用。另一方面是优化传递给数据库的查询。本文讨论在硬件方面优化数据库性能。通过使用数据库SQL命令，例如：CREATEINDEX、VACUUM、VACUUMFULL、ANALYZE、CLUSTER和EXPLAIN，已完成插入查询的优化。这些在我的书《PostgreSQL:Introduction and Concepts》(http://momjian.us/main/writings/pgsql/aw_pgsql_book/)中都有讨论。为了了解硬件性能问题，有必要了解计算机内部发生的情况。简单地说，计算机可以被认为是一个被内存包围的中央处理器（CPU）。和CPU在同一个芯片上的是不同的寄存器，存放着中间运算结果和各种指针、计数器。围绕着它们的是CPU缓存，它保存着最新的访问信息。除了CPUcache之外，还有大量的随机存取存储器(RAM)，用于保存正在运行的程序和数据。在RAM的外围是硬盘，它存储了更多的信息。硬盘是唯一可以永久存储信息的区域，所以电脑关机后，所有保存的信息都在这里。总而言之，这些是CPU周围的存储区域：存储区域容量CPU寄存器字节数CPU高速缓存内存千字节数RAM兆字节数硬盘驱动器数千兆字节数你可以看到存储空间大小随着与CPU的距离增加而增加。理论上，可以将大型持久存储放在CPU旁边，但这会很慢而且很昂贵。在实践中，最常用的信息放在CPU旁边，用得不多的信息放在离CPU较远的地方。在CPU需要的时候交给CPU。缩短数据与CPU之间的距离各个存储区之间的数据传输是自动进行的。编译器决定将哪些数据存储在寄存器中。CPU决定将哪些数据存储在缓存中。操作系统负责内存和硬盘之间的数据交换。DBA对CPU的寄存器和高速缓存无能为力。要提高数据库的性能，只能通过增加内存中的有用数据量和减少磁盘访问来获得。这看起来很简单，但事实并非如此。内存中的数据包含很多东西：正在执行的程序的数据和堆栈POSTGRESQL共享缓存内核磁盘缓存内核理想的性能调整应该既增加内存中的数据库数据量，又不产生负面影响系统。POSTGRESQL共享缓存POSTGRESQL不直接访问磁盘，而是访问POSTGRESQL的缓存。然后POSTGRESQL的后台程序对这些数据块进行读写，最后写入磁盘。后台先查表，看数据是否已经存在于缓存中。如果是，继续处理。如果没有，操作系统缓存内核磁盘中的数据，或者直接从磁盘加载数据。无论哪种方式，价格都很高。POSTGRESQL默认分配1000个缓冲区。每个缓存为8k字节。增加缓存的数量可以增加后台访问缓存的频率并减少昂贵的系统请求。缓存的数量可以通过postmaster命令行的参数或者配置文件postgresql.conf中shared_buffers的值来设置。多大算太大？您可能会想，“那么我将为POSTGRESQL缓冲区分配所有内存”。如果这样做，系统内核和其他程序将没有可用的内存。理想的POSTGRESQL共享缓冲区大小在不对系统产生不利影响的情况下尽可能大。要了解不利影响是什么，您首先需要了解UNIX如何管理内存。如果内存容量足够大，它可以容纳所有的程序和数据。那么我们就不需要管理内存了。问题是内存的容量是有限的，所以内核需要将内存中的数据分页保存到磁盘中。这就是传说中的数据交换。原理是将不用的数据移动到磁盘。此操作称为交换换页。将页移入交换区并不难，只要在程序的非活动期间执行即可。问题是当页面再次移出交换区时。即，将移至交换区的旧页面移回内存。此操作称为交换页面。这是一个问题，因为当一个页面被移动到内存中时，程序需要终止执行直到移动完成。可以使用vmstat、sar等系统分析工具查看是否有足够的内存来维持系统的正常运行。不要将交换页面删除与常规页面删除混淆。作为系统操作的一部分，常规页面逐出从文件系统读取分页数据。如果看不到，是不是有swappagemoveout操作。但是swap区的页移出操作非常活跃，这也说明有大量的页移出操作。高速缓存（cache）大小的影响你可能想知道为什么高速缓存的大小如此重要。首先，假设PostgreSQL共享缓存大到足以容纳整个表。重复连续扫描这张表不需要硬盘的参与，因为数据已经在缓存中了。现在假设缓存比表小一个单位。连续扫描会将所有单元加载到缓存中，直到最后一个单元。当需要最后一个单元时，删除原始单元。当另一个连续扫描开始时，原来的单元已经不在缓存中，为了加载它，第一个单元将被移除，即第一次扫描中的第二个单元将被移除。这将持续到单元结束。这个例子很极端，但是你可以看到减少一个单位会使缓存效率从100%变为0%。这表明找到合适的缓存大小可以显着改变性能。适当大小的共享缓存理论上，POSTGERSQL共享缓存应该是：它应该足够大以处理通常的表访问操作。它应该足够小以避免交换页面。请记住在数据库管理器运行时分配所有共享存储。即使没有访问数据库的请求，该区域也保持相同大小。一些操作系统调出未指定的共享存储，而其他操作系统将共享存储锁定到RAM中。LOCK贡献存储更好。POSTGERSQL管理员指南包含有关不同操作系统内核配置的信息，http://developer.postgresql.org/docs/postgres/kernel-resources.html。#p#用于批量排序的内存规模另一个可以调整性能的参数是用于批量排序的内存容量。当对大量数据进行排序时，POSTGRESQL会将它们拆分成许多小数据块进行排序。然后将中间结果存储在一个临时文件中。这些文件最终会合并并重新排序，直到所有行都排序完毕。增加批处理的内存大小可以减少临时文件的数量。从而提高分拣速度。但是如果batchprocessingsize设置的太大，会导致swap区的分页操作更加频繁。在这种情况下，使用大量临时文件的小批量排序速度更快。因此，判断内存是否过度分配取决于swappage的活跃度。请记住，此参数用于在后台进行排序。如：ORDERBY，CREATEINDEX，或者数据合并。对于多个并行排序任务，需要数倍于此内存容量。该参数的值可以通过postmaster命令行参数或者配置文件postgresql.conf中的sort_mem来设置。缓存大小和排序大小缓存大小和排序大小都会影响内存使用。你不能在不影响另一个的情况下增加一个的大小。请记住，缓存的大小是在postmaster启动时设置的。排序的大小由排序的数量决定。一般来说，缓存的大小大于排序的大小。然而，一些使用ORDERBY、CREATEINDEX或数据合并的查询可以通过增加排序的大小来加速。此外，许多操作系统对共享内存的分配都有限制。修改此限制意味着重新编译或重新配置内核。换句话说，您必须非常精通操作系统。有关详细信息，请参阅POSTGRESQL管理员手册http://developer.postgresql.org/docs/postgres/kernel-resources.html。刚开始调优的时候，使用15%的RAM作为缓存大小，如果有几个使用2-4%的内存作为sortsize，如果你有很多小东西，就使用较小的内存。您可以尝试增加它以查看性能是否提高并发生交换。如果共享缓存太大，您将花费太多时间来维护一个大缓存，并且它浪费了可以被其他进程使用而不是作为额外的内核磁盘缓存使用的RAM。一个有价值的服务器参数是effective_cache_size。优化器使用此参数来估计内核磁盘缓存的大小。在使用统一缓存的内核中，该值应设置为内核未使用RAM的平均值，因为这样内核可以使用未使用的RAM来缓存最近访问的磁盘页面。在具有固定磁盘缓存的内核中，该值应设置为内核缓存的大小，通常为RAM的10%。DiskLocality磁盘本身的特性决定了其性能有别于上述其他存储方式。对于其他存储方式，访问数据中任意字节的速度是一样的。对于磁盘来说，由于磁盘在不断地旋转，磁头也在不断地移动，所以访问靠近磁头当前位置的数据比远离磁头的数据要快。将磁头从同一磁盘的一个柱面移动到另一个柱面需要时间。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和符号链接文件（symboliclink）传输预写日志将pg_xlog目录移动到其他磁盘。与其他写操作不同，POSTGRESQL日志写操作必须在事务结束前提交到磁盘。即使使用缓存，也不能推迟这些写操作。将日志保存在不同的磁盘上可以减少磁头移动带来的延迟。（postgres-F参数可以关闭“logsave(ondisk)”功能，但是如果操作系统崩溃，只能通过备份文件恢复。）还有其他方法，使用RAID磁盘阵列分布一个单个文件系统到多个磁盘。镜像虽然会导致数据库写入变慢，但是可以提高读取速度。因为可以同时从多个磁盘读取数据。很多网站喜欢使用RAID1+0或RAID0+1，因为它有分段操作提高性能，镜像文件保证可靠性。当磁盘数量不少于6时，RAID5是最快的。理论上，RAID5是用电池作为后备电源的写入缓存，所以磁盘写入操作非常高效，不会拖慢磁盘速度。程序。磁盘缓冲当前磁盘有读写缓冲区。读取缓冲区将最近的读取请求存储在磁盘的内存中。写缓冲区保存最近的写请求，直到它们可以有效地存储在磁盘上。也许你已经看到了，这里有一个问题——如果写缓冲区中的数据还没有来得及保存到磁盘，电源被关闭了怎么办？一些磁盘和RAID控制器有电池作为备用电源，可以将数据保存在写缓存中，直到电源恢复。然而，大多数磁盘和RAID控制器没有此功能，因此可靠性是一个问题。幸运的是，大多数磁盘都可以关闭写缓存。SCSI磁盘写缓存通常是关闭的。IDE磁盘的writecache默认是开启的，但是可以在操作系统中使用命令行hdparm-W0，sysctlhw.ata.wc=0，或者scsicmd来关闭。也就是说，有些IDE盘虽然标明writecache已经关闭，但是它们还在使用。结果磁盘变得不稳定。如果不进行昂贵的测试，就很难看出磁盘的写缓存是否真的关闭了。因为PostgreSQL每次都会调用fsync()将预先写入的日志写入磁盘，等到日志写入操作完成后才提交事务。因此，如果使用写缓存，用户会发现性能会快很多。因此，对于PostgreSQL来说，从性能和可靠性上来说，最好使用带备用电池的写缓存。SCSI与IDESCSI磁盘通常比IDE磁盘贵。SCSI磁盘具有控制器和操作系统之间的通信协议。IDE磁盘要简单得多，一次只能接受一个请求。SCSI磁盘有一个标记队列（taggedqueuing）可以同时接受多个请求，并自动对它们进行排序以提高效率。这就是为什么在单用户、单文件操作中，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文件系统上表现最好，例如许多操作系统支持的BSDUFS/FFS文件系统。UFS的默认8K块大小与PostgresSQL的页面大小相同。您可以在其上运行日志文件系统或基于日志的文件系统，但这会增加fsync的预写日志开销。早期基于SvR3的文件系统变得过于碎片化，无法实现高性能。Linux的文件系统太多，很难选择。没有一个是完美的：ext2不是完全崩溃安全的，ext3、XFS和JFS是基于日志的，而Reiser非常适合小文件和日志。journalling文件系统比ext2慢很多，但是支持崩溃恢复，ext2最好不要用。如果必须使用ext2，请为其设置同步。有人建议ext3系统应该设置data=writebck。不建议将NFS和其他远程文件系统与PostgresSQL一起使用。NFS文件系统的语义与本地文件系统的语义不同，这些差异会导致数据可靠性和崩溃恢复方面的问题。多个CPUPOSTRGESQL使用多进程模型，这意味着每个数据库都有自己的处理单元。因此，所有的多CPU操作系统都可以通过可用的CPU分布多个数据库。那么，如果只有一个数据库连接，那么它就只能使用一个CPU。POSTGRESQL不允许使用多线程来允许一个进程使用多个CPU。检查点（checkpointevent）当第一次写日志文件满时，一个检查点事件会强制所有的缓冲块到硬盘，以便日志文件可以被重用。检查点也会定期自动执行，通常是5分钟一次。如果有大量的数据库写入，预写日志将很快填满，导致极度缓慢，因为所有缓冲块都被写入磁盘。检查点（checkpointevents）检查点应该每隔几分钟发生一次。如果一分钟生成几次，性能将会受到影响。判断checkpoints是否过于频繁，查看checkpoint_warning产生的日志信息。如果您的检查点每30秒不止一次，则会生成此信息。降低检查点频率包括增加data/pg_xlog中的预写日志文件。每个文件16M，对硬盘的影响可见一斑。默认安装使用最少数量的日志文件。为了降低检查点的频率，需要增加这个参数：checkpoint_segment=3默认值为3，增加这个值直到每隔几分钟产生一次检查点。另一个日志文件：LOG:XLogWrite:newlogfilecreated-considerincreasingWAL_FILES此消息表明应该增加postgresql.conf中的wal_files参数。总结幸运的是，POSTGERSQL不需要太多调优。大多数参数会自动调整以保持最佳性能。管理员还可以控制缓存大小和排序大小以优化可用内存的使用。硬盘访问也可以通过驱动器扩展。其他参数也可以通过share/pstgresql/conf.sample设置。您可以将此文件复制到data/postgresql.conf以试验POSTGRESQL的一些更奇特的参数。英文原文：PostgreSQLHardwarePerformanceTuning翻译自：http://www.oschina.net/translate/postgresql-hw-performance
