优化PostgreSQL进行快速测试

对于一个典型的Rails应用程序，我正在从SQLite切换到PostgreSQL。

问题是使用PG时运行specs变慢了 在SQLite上花了大约34秒，在PG上花了大约76秒，即慢2倍以上.

所以现在我想在不修改代码的情况下将一些技术应用于使规范的性能与SQLite相媲美(理想情况下只是通过设置连接选项，这可能是不可能的)。

我首先想到的是:

  • RAM磁盘(在OSX上与RSpec的良好设置将很好地看到)
  • Unlogged table(它可以应用于整个数据库，这样我就不用改变所有的脚本了吗?)
你可能已经理解了，我不关心可靠性和其他的(DB在这里只是一个扔掉的东西) 我需要得到最大的PG，使其越快越好.

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

更新: fsync = off + full_page_writes = off只将时间减少到~65秒(~-16秒)。好的开始，但距离34的目标还很远。

更新2:我尝试使用内存盘，但性能增益在一个误差范围内。所以看起来不值得。

< >强更新3:* 我发现了最大的瓶颈，现在我的规格运行得和SQLite一样快

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

为了“修复”它，我在每次测试之前打开事务，并在结束时回滚它。

大约700个测试的一些数字。

  • 截断:SQLite - 34s, PG - 76s。
  • 交易:SQLite - 17s, PG - 18s。

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

70581 次浏览
小开
最佳答案

首先，始终使用最新版本的PostgreSQL。性能改进总是会出现，所以如果您调优旧版本，可能是在浪费时间。例如，PostgreSQL 9.2显著提高了TRUNCATE的速度当然会添加索引扫描。即使是小版本也应该遵循;参见版本的政策。

不该做的事

NOT是否将表空间放在RAMdisk或其他非持久存储上。

如果您丢失了一个表空间，整个数据库可能会被损坏，并且在没有大量工作的情况下很难使用。与仅仅使用UNLOGGED表和有大量内存用于缓存相比，这样做的优势非常小。

如果你真的想要一个基于ramdisk的系统，通过在ramdisk上initdbing一个新的PostgreSQL实例，在ramdisk上initdb一个全新的集群，这样你就有了一个完全可丢弃的PostgreSQL实例。

PostgreSQL服务器配置

在测试时，您可以为不耐用，但操作更快配置服务器。

这是PostgreSQL中fsync=off设置唯一可接受的用法之一。这个设置基本上告诉PostgreSQL不要为有序写入或任何其他讨厌的数据完整性保护和崩溃安全问题而烦恼，允许它在你断电或操作系统崩溃时完全丢弃你的数据。

不用说，你永远不应该在生产中启用fsync=off，除非你使用Pg作为一个临时数据库，用于你可以从其他地方重新生成的数据。当且仅当你正在关闭fsync时也可以关闭full_page_writes，因为它不再有任何好处。注意，fsync=off和full_page_writes应用于集群级别，因此它们会影响PostgreSQL实例中的所有数据库。

对于生产使用，你可以使用synchronous_commit=off并设置一个commit_delay，因为你将获得许多与fsync=off相同的好处，而没有巨大的数据损坏风险。如果启用异步提交，您确实会有一个丢失最近数据的小窗口——但仅此而已。

如果你有稍微改变DDL的选项，你也可以在Pg 9.1+中使用UNLOGGED表来完全避免WAL日志记录，并以在服务器崩溃时删除表为代价获得真正的速度提升。没有使所有表不记录日志的配置选项，它必须在CREATE TABLE期间设置。除了用于测试之外，如果您在数据库中有满是生成的或不重要的数据的表，而这些数据包含您需要确保安全的内容，那么这种方法也很方便。

检查您的日志，看看您是否得到关于检查点太多的警告。如果是，你应该增加checkpoint_segments。您可能还想调优checkpoint_completion_target以平滑写入。

调优shared_buffers以适应您的工作负载。这取决于操作系统，取决于您的机器上发生了什么，并且需要一些试验和错误。默认值非常保守。如果在PostgreSQL 9.2及以下版本上增加shared_buffers，则可能需要增加操作系统的最大共享内存限制;9.3及以上版本改变了共享内存的使用方式以避免这种情况。

如果你只使用了几个做很多工作的连接，增加work_mem来给它们更多的RAM来进行排序等。注意，过高的work_mem设置会导致内存不足的问题，因为它是每个排序而不是每个连接，所以一个查询可以有很多嵌套排序。如果你能在EXPLAIN中看到排序溢出到磁盘或使用log_temp_files设置记录(推荐)，则只有真的需要增加work_mem，但更高的值也可以让Pg选择更聪明的计划。

正如另一个帖子所说，如果可能的话，将xlog和主表/索引放在不同的hdd上是明智的。单独的分区是没有意义的，你真的需要单独的驱动器。如果你使用fsync=off运行，这种分离的好处就小得多，如果你使用UNLOGGED表，这种分离几乎没有好处。

最后，调优您的查询。确保你的random_page_cost和seq_page_cost反映了你的系统性能，确保你的effective_cache_size是正确的，等等。使用EXPLAIN (BUFFERS, ANALYZE)检查单个查询计划，并打开auto_explain模块以报告所有慢速查询。通过创建适当的索引或调整成本参数，通常可以显著提高查询性能。

AFAIK没有办法将整个数据库或集群设置为UNLOGGED。如果能做到这一点，那就太有趣了。考虑在PostgreSQL邮件列表上询问。

主机操作系统调优

您还可以在操作系统级别进行一些调优。您可能要做的主要事情是说服操作系统不要积极地将写入写入磁盘，因为您实际上并不关心它们何时/是否写入磁盘。

在Linux中，你可以通过虚拟内存子系统的dirty_*设置来控制这一点，比如dirty_writeback_centisecs。

调优回写设置过于宽松的唯一问题是，其他程序的刷新可能会导致所有PostgreSQL的累积缓冲区也被刷新，在写入时所有内容都阻塞时导致大的停顿。你可以通过在不同的文件系统上运行PostgreSQL来缓解这个问题，但是有些刷新可能是设备级或整个主机级的，而不是文件系统级的，所以你不能依赖它。

这种调优实际上需要摆弄各种设置，以确定哪些设置最适合您的工作负载。

在更新的内核上，你可能希望确保vm.zone_reclaim_mode被设置为零，因为它会导致NUMA系统(现在的大多数系统)严重的性能问题，因为它与PostgreSQL管理shared_buffers的方式相互作用。

查询和工作负载调优

这些事情确实需要修改代码;它们可能不适合你。有些东西你也许可以应用。

如果你不打算把工作分批处理成更大的事务，那就开始吧。许多小的事务是昂贵的，所以你应该在可能和实际的情况下批量处理。如果你正在使用异步提交，这就不那么重要了，但仍然强烈推荐。

尽可能使用临时表。它们不会产生WAL流量，所以它们在插入和更新时要快得多。有时值得将一堆数据放入临时表中，按需要对其进行操作，然后执行INSERT INTO ... SELECT ...将其复制到最终表中。注意，临时表是每个会话;如果会话结束或失去连接，那么临时表就会消失，其他连接就不能看到会话的临时表的内容。

如果你使用的是PostgreSQL 9.1或更新版本，你可以使用UNLOGGED表来保存你可以丢失的数据，比如会话状态。这些在不同的会话中可见，并在连接之间保存。如果服务器不干净地关闭，它们就会被截断，因此它们不能用于您无法重新创建的任何内容，但它们对于缓存、物化视图、状态表等非常有用。

一般来说，不要DELETE FROM blah;。使用TRUNCATE TABLE blah;代替;当您转储表中的所有行时，这要快得多。如果可以，在一个TRUNCATE调用中截断多个表。不过，如果你一遍又一遍地对小表做大量的TRUNCATES，有一个警告;看:截断速度

如果在外键上没有索引，那么涉及这些外键引用的主键的__abc0将非常慢。如果你希望从引用的表中DELETE，请确保创建这样的索引。TRUNCATE不需要索引。

不要创建你不需要的索引。每个索引都有维护成本。尽量使用最小的索引集，让位图索引扫描将它们组合起来，而不是维护太多巨大、昂贵的多列索引。在需要索引的地方，尝试先填充表，然后在最后创建索引。

硬件

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

如果没有足够的RAM，那么存储越快越好。即使是一个便宜的SSD也比旋转生锈有巨大的不同。不要相信廉价的ssd硬盘，它们通常不安全，可能会吃掉你的数据。

学习

格雷格·史密斯的书，PostgreSQL 9.0高性能仍然相关，尽管参考了一个有点旧的版本。这应该是一个有用的参考。

加入PostgreSQL通用邮件列表并关注它。

阅读:

小开

使用不同的磁盘布局:

  • 不同的磁盘$PGDATA
  • $PGDATA/pg_xlog在不同的磁盘
  • 不同的tem文件磁盘(每个数据库$PGDATA/base//pgsql_tmp)(参见关于work_mem的说明)

postgresql.conf调整:

  • shared_memory:可用内存的30%，但不超过6 ~ 8GB。对于写密集型工作负载，使用更少的共享内存(2GB - 4GB)似乎更好
  • Work_mem:主要用于带有排序/聚合的选择查询。这是每个连接设置和查询可以多次分配该值。如果数据不适合，则使用磁盘(pgsql_tmp)。检查“解释分析”，看看你需要多少内存
  • fsync和synchronous_commit:默认值是安全的，但如果你可以容忍数据丢失，那么你可以关闭它们
  • random_page_cost:如果您有SSD或快速RAID阵列，您可以将其降低到2.0 (RAID)，对于SSD甚至更低(1.1)
  • Checkpoint_segments:你可以选择更高的32或64，并将checkpoint_completion_target更改为0.9。更低的值允许更快的崩溃后恢复

提交答案