sql - 优化PostgreSQL以进行快速测试

Question

对于典型的Rails应用程序，我将从SQLite切换到PostgreSQL。

问题在于PG的运行规范变慢了。
在SQLite上花了〜34秒，在PG上花了〜76秒，比原来慢了2倍。

因此，现在我想应用一些技术来使规范的性能与SQLite保持一致，而无需修改代码（理想情况下仅通过设置连接选项，这可能是不可能的）。

从我的脑海中显而易见的几件事是：


RAM磁盘（在OSX上使用RSpec进行良好的设置很高兴看到）
未记录的表（可以将其应用于整个数据库，这样就不必更改所有脚本了吗？）


正如您可能已经了解的，我不在乎可靠性和其他方面（DB在这里只是一堆东西）。
我需要充分利用PG，并使其尽可能快。

最佳答案将理想地描述这样做的技巧，设置以及这些技巧的缺点。

更新：fsync = off + full_page_writes = off仅将时间减少到〜65秒（〜-16秒）。良好的开端，但距离34的目标还很远。

更新2：我tried to use RAM disk，但是性能提升在误差范围内。所以似乎不值得。

更新3：*
我发现了最大的瓶颈，现在我的规格与SQLite一样快。

问题是进行了截断的数据库清理。显然，SQLite的速度太快了。

为了“修复”它，我在每次测试之前打开一个事务，并在最后将其回滚。

约700个测试的一些数字。


截断：SQLite-34s，PG-76s。
交易：SQLite-17s，PG-18s。


SQLite的速度提高了2倍。
PG的速度提高了4倍。

Answer 1

首先，请始终使用最新版本的PostgreSQL。性能改进总是会不断出现，因此如果您要调整旧版本，可能会浪费时间。例如，PostgreSQL 9.2 significantly improves the speed of TRUNCATE并且当然会添加仅索引扫描。即使是较小的发行版也应始终遵循；参见version policy。

不要

Do NOT put a tablespace on a RAMdisk or other non-durable storage。

如果丢失表空间，则整个数据库可能会损坏，并且如果不进行大量工作就很难使用。与仅使用UNLOGGED表和仍然拥有大量RAM进行缓存相比，此方法几乎没有优势。

如果您确实需要基于ramdisk的系统，请通过initdb在ramdisk上的新PostgreSQL实例在 ramdisk上的整个新群集，因此您拥有一个完全可抛弃的PostgreSQL实例。

PostgreSQL服务器配置

测试时，可以为 non-durable but faster operation配置服务器。

这是PostgreSQL中 initdb设置的唯一可接受的用法之一。这个设置几乎告诉PostgreSQL不要打扰有序的写操作或任何其他令人讨厌的数据完整性保护和崩溃安全性的东西，如果断电或发生操作系统崩溃，它可以完全破坏您的数据。

不用说，除非将Pg用作可从其他地方重新生成的数据的临时数据库，否则切勿在生产中启用 fsync=off。当且仅当您要关闭fsync时，也可以关闭 fsync=off，因为它不再起作用。注意 full_page_writes和 fsync=off在集群级别适用，因此它们会影响PostgreSQL实例中的所有数据库。

对于生产用途，您可以使用 full_page_writes并设置 synchronous_commit=off，因为您将获得与 commit_delay相同的许多好处，而没有巨大的数据损坏风险。如果启用异步提交，则确实会丢失一小段丢失最近数据的窗口-就是这样。

如果可以选择稍微更改DDL，则还可以使用Pg 9.1+中的 fsync=off表完全避免WAL日志记录，并在服务器崩溃时以删除表为代价获得真正的速度提升。没有使所有表都注销的配置选项，必须在 UNLOGGED期间设置它。如果您的数据库中有包含已生成或不重要的数据的表，这些表除了包含测试内容外，还可以很好地进行测试，否则它很安全。

检查您的日志，看看是否收到有关太多检查点的警告。如果是，则应增加 checkpoint_segments。您可能还需要调整您的checkpoint_completion_target来平滑写出。

调整 CREATE TABLE以适合您的工作量。这取决于操作系统，取决于计算机上发生的其他情况，并且需要反复试验。默认值非常保守。如果在PostgreSQL 9.2及更低版本上增加 shared_buffers，则可能需要增加操作系统的最大共享内存限制；否则，可能需要增加操作系统的最大共享内存限制。 9.3及更高版本更改了如何使用共享内存来避免这种情况。

如果您仅使用几个连接来完成大量工作，请增加 shared_buffers以便为它们提供更多的RAM以进行排序等。请注意， work_mem设置过高可能会导致内存不足的问题因为它是按排序而不是按连接的，所以一个查询可以有许多嵌套的排序。如果您可以看到排序溢出到磁盘中或用 work_mem setting记录（建议），则只需要增加 work_mem，但是更高的值也可以让Pg选择更明智的计划。

正如另一位发布者所说，将xlog和主表/索引放置在单独的HDD上是明智的。单独的分区毫无意义，您确实需要单独的驱动器。如果使用 EXPLAIN运行，则这种分离的好处要少得多，如果使用 log_temp_files表，则几乎没有好处。

最后，调整您的查询。确保 fsync=off和 UNLOGGED反映系统的性能，确保 random_page_cost正确，等等。使用 seq_page_cost检查单个查询计划，并打开 effective_cache_size模块以报告所有慢速查询。通常，仅通过创建适当的索引或调整成本参数，即可显着提高查询性能。

AFAIK无法将整个数据库或群集设置为 EXPLAIN (BUFFERS, ANALYZE)。能够做到这一点很有趣。考虑在PostgreSQL邮件列表上询问。

主机操作系统调优

您也可以在操作系统级别进行一些调整。您可能要做的主要事情是说服操作系统不要主动清除对磁盘的写入操作，因为您实际上并不在乎何时/是否将它们写入磁盘。

在Linux中，您可以使用 virtual memory subsystem的 auto_explain设置（例如 UNLOGGED）来控制它。

将写回设置调整得太松弛的唯一问题是，其他一些程序执行的刷新操作可能也会导致PostgreSQL的所有累积缓冲区也被刷新，从而在所有写入阻塞时导致较大的停顿。您可以通过在不同的文件系统上运行PostgreSQL来减轻这种情况，但是某些刷新可能是设备级的或整个主机级的，而不是文件系统级的，因此您不能依靠它。

这种调整确实需要调整设置，以了解最适合您的工作负载的情况。

在较新的内核上，您可能希望确保 dirty_*设置为零，因为与PostgreSQL管理 dirty_writeback_centisecs的方式相互作用，它可能导致NUMA系统（当今大多数系统）出现严重的性能问题。

查询和工作负载调整

这些确实需要更改代码。他们可能不适合您。您可能可以应用某些内容。

如果您不将工作分批处理到较大的事务中，请开始。许多小额交易非常昂贵，因此您应该在可行的情况下尽可能地批量处理。如果您使用的是异步提交，那么它的重要性就不那么重要了，但还是极力推荐。

尽可能使用临时表。它们不会产生WAL流量，因此插入和更新的速度更快。有时值得将大量数据放入临时表中，然后按需要进行处理，然后执行 vm.zone_reclaim_mode将其复制到最终表中。请注意，临时表是每个会话的；如果会话结束或失去连接，则临时表将消失，并且其他任何连接都无法看到会话的临时表的内容。

如果您使用的是PostgreSQL 9.1或更高版本，则可以使用 shared_buffers表来存储可能会丢失的数据，例如会话状态。这些在不同的会话中可见，并在连接之间保留。如果服务器关闭不干净，它们将被截断，因此它们不能用于您无法重新创建的任何事物，但是它们非常适合缓存，物化视图，状态表等。

通常，不要 INSERT INTO ... SELECT ...。使用 UNLOGGED代替；当您转储表中的所有行时，这要快得多。如果可以，在一个 DELETE FROM blah;调用中截断许多表。但是，如果您要一遍又一遍地做很多 TRUNCATE TABLE blah;的小桌子，那是个警告。参见： Postgresql Truncation speed

如果您没有外键索引，那么包含那些外键引用的主键的 TRUNCATE将会非常慢。如果您希望从引用的表中 TRUNCATES，请确保创建此类索引。 DELETE不需要索引。

不要创建不需要的索引。每个索引都有维护成本。尝试使用最少的索引集，并让位图索引扫描将它们组合在一起，而不要维护太多，昂贵的多列索引。在需要索引的地方，请尝试首先填充表，然后在最后创建索引。

硬件

如果您可以管理它，那么拥有足够的RAM来容纳整个数据库是一个巨大的胜利。

如果您没有足够的RAM，则更快的存储将变得更好。即使是廉价的SSD也比旋转的锈蚀有很大的不同。但是，不要相信便宜的SSD来进行生产，因为它们通常不安全，可能会吞噬您的数据。

学习

格雷格·史密斯（Greg Smith）的书 PostgreSQL 9.0 High Performance仍然有意义，尽管它引用的是较旧的版本。它应该是一个有用的参考。

加入PostgreSQL常规邮件列表并遵循它。

阅读：


Tuning your PostgreSQL server - PostgreSQL wiki
Number of database connections - PostgreSQL wiki