优化 PostgreSQL 以进行快速测试 [英] Optimise PostgreSQL for fast testing
问题描述
对于一个典型的 Rails 应用程序，我正在从 SQLite 切换到 PostgreSQL.
I am switching to PostgreSQL from SQLite for a typical Rails application.
问题是使用 PG 时运行规范变慢了.
在 SQLite 上花费了大约 34 秒，在 PG 上花费了大约 76 秒，慢了 2 倍以上.
The problem is that running specs became slow with PG.
On SQLite it took ~34 seconds, on PG it's ~76 seconds which is more than 2x slower.
所以现在我想应用一些技术来使规范的性能与 SQLite 相当，而无需修改代码(理想情况下只需设置连接选项，这可能是不可能的).
So now I want to apply some techniques to bring the performance of the specs on par with SQLite with no code modifications (ideally just by setting the connection options, which is probably not possible).
从我头顶上看的几个明显的事情是:
Couple of obvious things from top of my head are:
- RAM 磁盘(在 OSX 上使用 RSpec 进行良好设置会很高兴)
- 未记录的表(是否可以将其应用于整个数据库，以便我不必更改所有脚本?)
正如您可能已经理解的那样，我不关心可靠性和其他(DB 在这里只是一个一次性的东西).
我需要充分利用 PG 并使其尽可能快.
As you may have understood I don't care about reliability and the rest (the DB is just a throwaway thingy here).
I need to get the most out of the PG and make it as fast as it can possibly be.
最佳答案最理想的是描述技巧来做到这一点、设置以及这些技巧的缺点.
Best answer would ideally describe the tricks for doing just that, setup and the drawbacks of those tricks.
更新: fsync = off + full_page_writes = off 仅将时间减少到 ~65 秒(~-16 秒).良好的开端，但离目标 34 还差得很远.
UPDATE: fsync = off + full_page_writes = off only decreased time to ~65 seconds (~-16 secs). Good start, but far from the target of 34.
更新 2:我尝试使用 RAM 磁盘，但性能有所提升在误差范围内.所以似乎不值得.
UPDATE 2: I tried to use RAM disk but the performance gain was within an error margin. So doesn't seem to be worth it.
更新 3:*我发现了最大的瓶颈，现在我的规格运行速度与 SQLite 一样快.
UPDATE 3:* I found the biggest bottleneck and now my specs run as fast as the SQLite ones.
问题在于执行截断的数据库清理.显然 SQLite 在那里太快了.
The issue was the database cleanup that did the truncation. Apparently SQLite is way too fast there.
为了修复"它，我在每次测试之前打开一个事务，并在最后回滚它.
To "fix" it I open a transaction before each test and roll it back at the end.
大约 700 次测试的一些数字.
Some numbers for ~700 tests.
- 截断:SQLite - 34s，PG - 76s.
- 事务:SQLite - 17 秒，PG - 18 秒.
SQLite 的速度提高了 2 倍.PG 速度提升 4 倍.
2x speed increase for SQLite. 4x speed increase for PG.
推荐答案
首先，始终使用最新版本的 PostgreSQL.性能改进总是会到来，因此如果您正在调整旧版本，则可能是在浪费时间.例如，PostgreSQL 9.2 显着提高了TRUNCATE的速度当然，还添加了仅索引扫描.即使是次要版本也应始终遵循；请参阅版本政策.
First, always use the latest version of PostgreSQL. Performance improvements are always coming, so you're probably wasting your time if you're tuning an old version. For example, PostgreSQL 9.2 significantly improves the speed of TRUNCATE and of course adds index-only scans. Even minor releases should always be followed; see the version policy.
Do NOT put a tablespace on a RAMdisk or other non-durable storage.
如果你丢失了一个表空间，整个数据库可能会被损坏并且在没有大量工作的情况下难以使用.与仅使用 UNLOGGED 表并有大量 RAM 用于缓存相比，这几乎没有什么优势.
If you lose a tablespace the whole database may be damaged and hard to use without significant work. There's very little advantage to this compared to just using UNLOGGED tables and having lots of RAM for cache anyway.
如果你真的想要一个基于 ramdisk 的系统，initdb 通过 initdb 在 ramdisk 上创建一个新的 PostgreSQL 实例，在 ramdisk 上创建一个全新的集群，这样你就有了一个完整的一次性 PostgreSQL 实例.
If you truly want a ramdisk based system, initdb a whole new cluster on the ramdisk by initdbing a new PostgreSQL instance on the ramdisk, so you have a completely disposable PostgreSQL instance.
测试时，您可以将服务器配置为不耐用但速度更快操作.
When testing, you can configure your server for non-durable but faster operation.
这是 fsync=off 设置.这个设置几乎告诉 PostgreSQL 不要打扰有序写入或任何其他讨厌的数据完整性保护和崩溃安全的东西，如果你断电或操作系统崩溃，它允许它完全破坏你的数据.
This is one of the only acceptable uses for the fsync=off setting in PostgreSQL. This setting pretty much tells PostgreSQL not to bother with ordered writes or any of that other nasty data-integrity-protection and crash-safety stuff, giving it permission to totally trash your data if you lose power or have an OS crash.
不用说，除非您将 Pg 用作临时数据库，否则您不应在生产中启用 fsync=off，否则您可以从其他地方重新生成数据.当且仅当您关闭 fsync 时也可以关闭 full_page_writes 关闭，因为它不再有任何好处.请注意 fsync=off 和 full_page_writes 适用于 集群 级别，因此它们会影响 PostgreSQL 实例中的所有数据库.
Needless to say, you should never enable fsync=off in production unless you're using Pg as a temporary database for data you can re-generate from elsewhere. If and only if you're doing to turn fsync off can also turn full_page_writes off, as it no longer does any good then. Beware that fsync=off and full_page_writes apply at the cluster level, so they affect all databases in your PostgreSQL instance.
对于生产用途，您可以使用 synchronous_commit=off 并设置 commit_delay，因为您将获得许多与 fsync=off<相同的好处/code> 没有巨大的数据损坏风险.如果您启用异步提交，您的近期数据确实会丢失一小段时间 - 仅此而已.
For production use you can possibly use synchronous_commit=off and set a commit_delay, as you'll get many of the same benefits as fsync=off without the giant data corruption risk. You do have a small window of loss of recent data if you enable async commit - but that's it.
如果您可以选择稍微改变 DDL，您还可以使用 Pg 9.1+ 中的 UNLOGGED 表来完全避免 WAL 日志记录并以表被擦除为代价获得真正的速度提升如果服务器崩溃.没有配置选项可以取消所有表的记录，必须在 CREATE TABLE 期间设置.除了有利于测试之外，如果您的数据库中包含充满生成数据或不重要的数据的表，而这些表包含您需要确保安全的内容，则这很方便.
If you have the option of slightly altering the DDL, you can also use UNLOGGED tables in Pg 9.1+ to completely avoid WAL logging and gain a real speed boost at the cost of the tables getting erased if the server crashes. There is no configuration option to make all tables unlogged, it must be set during CREATE TABLE. In addition to being good for testing this is handy if you have tables full of generated or unimportant data in a database that otherwise contains stuff you need to be safe.
检查您的日志，看看您是否收到有关检查点过多的警告.如果你是，你应该增加你的 checkpoint_segments.您可能还想调整 checkpoint_completion_target 以平滑写入.
Check your logs and see if you're getting warnings about too many checkpoints. If you are, you should increase your checkpoint_segments. You may also want to tune your checkpoint_completion_target to smooth writes out.
调整 shared_buffers 以适应您的工作负载.这取决于操作系统，取决于您的机器正在发生的其他事情，并且需要一些反复试验.默认值非常保守.如果您在 PostgreSQL 9.2 及更低版本上增加 shared_buffers，您可能需要增加操作系统的最大共享内存限制；9.3 及更高版本更改了他们使用共享内存的方式以避免这种情况.
Tune shared_buffers to fit your workload. This is OS-dependent, depends on what else is going on with your machine, and requires some trial and error. The defaults are extremely conservative. You may need to increase the OS's maximum shared memory limit if you increase shared_buffers on PostgreSQL 9.2 and below; 9.3 and above changed how they use shared memory to avoid that.
如果您只使用几个连接来完成大量工作，请增加 work_mem 以给它们更多的 RAM 用于排序等.注意 work_mem 太高 设置可能会导致内存不足问题，因为它是按排序而不是按连接进行的，因此一个查询可以有许多嵌套排序.如果您可以在 EXPLAIN 中看到排序溢出到磁盘或使用 log_temp_files 设置(推荐)，但更高的值也可能让 Pg 选择更明智的计划.
If you're using a just a couple of connections that do lots of work, increase work_mem to give them more RAM to play with for sorts etc. Beware that too high a work_mem setting can cause out-of-memory problems because it's per-sort not per-connection so one query can have many nested sorts. You only really have to increase work_mem if you can see sorts spilling to disk in EXPLAIN or logged with the log_temp_files setting (recommended), but a higher value may also let Pg pick smarter plans.
正如这里的另一位海报所说，如果可能，将 xlog 和主表/索引放在单独的 HDD 上是明智的.单独的分区毫无意义，您确实需要单独的驱动器.如果您使用 fsync=off 运行，这种分离的好处要少得多，而如果您使用 UNLOGGED 表，则几乎没有好处.
As said by another poster here it's wise to put the xlog and the main tables/indexes on separate HDDs if possible. Separate partitions is pretty pointless, you really want separate drives. This separation has much less benefit if you're running with fsync=off and almost none if you're using UNLOGGED tables.
最后，调整您的查询.确保您的 random_page_cost 和 seq_page_cost 反映您的系统性能，确保您的 effective_cache_size 正确等.使用 EXPLAIN (BUFFERS, ANALYZE) 检查单个查询计划，并打开 auto_explain 模块以报告所有慢查询.您通常可以通过创建适当的索引或调整成本参数来显着提高查询性能.
Finally, tune your queries. Make sure that your random_page_cost and seq_page_cost reflect your system's performance, ensure your effective_cache_size is correct, etc. Use EXPLAIN (BUFFERS, ANALYZE) to examine individual query plans, and turn the auto_explain module on to report all slow queries. You can often improve query performance dramatically just by creating an appropriate index or tweaking the cost parameters.
AFAIK 没有办法将整个数据库或集群设置为 UNLOGGED.能够这样做会很有趣.考虑在 PostgreSQL 邮件列表上询问.
AFAIK there's no way to set an entire database or cluster as UNLOGGED. It'd be interesting to be able to do so. Consider asking on the PostgreSQL mailing list.
您也可以在操作系统级别进行一些调整.您可能想要做的主要事情是说服操作系统不要积极地将写入写入磁盘，因为您真的不关心它们何时/是否写入磁盘.
There's some tuning you can do at the operating system level, too. The main thing you might want to do is convince the operating system not to flush writes to disk aggressively, since you really don't care when/if they make it to disk.
在 Linux 中，您可以使用 虚拟内存子系统's dirty_* 设置，例如 dirty_writeback_centisecs.
In Linux you can control this with the virtual memory subsystem's dirty_* settings, like dirty_writeback_centisecs.
将写回设置调得太松的唯一问题是其他程序的刷新可能会导致所有 PostgreSQL 累积的缓冲区也被刷新，从而导致大停顿，而所有内容都在写入时受阻.您可以通过在不同的文件系统上运行 PostgreSQL 来缓解这种情况，但某些刷新可能是设备级别或整个主机级别而不是文件系统级别，因此您不能依赖它.
The only issue with tuning writeback settings to be too slack is that a flush by some other program may cause all PostgreSQL's accumulated buffers to be flushed too, causing big stalls while everything blocks on writes. You may be able to alleviate this by running PostgreSQL on a different file system, but some flushes may be device-level or whole-host-level not filesystem-level, so you can't rely on that.
这种调整确实需要调整设置，以了解什么最适合您的工作负载.
This tuning really requires playing around with the settings to see what works best for your workload.
在较新的内核上，您可能希望确保 vm.zone_reclaim_mode 设置为零，因为由于与 PostgreSQL管理shared_buffers.
On newer kernels, you may wish to ensure that vm.zone_reclaim_mode is set to zero, as it can cause severe performance issues with NUMA systems (most systems these days) due to interactions with how PostgreSQL manages shared_buffers.
这些是确实需要更改代码的；他们可能不适合你.有些是您可能可以申请的.
These are things that DO require code changes; they may not suit you. Some are things you might be able to apply.
如果您没有将工作批处理为更大的事务，请开始.许多小额交易的成本很高，因此您应该在可能且可行的情况下进行批处理.如果您使用异步提交，这不是那么重要，但仍然强烈推荐.
If you're not batching work into larger transactions, start. Lots of small transactions are expensive, so you should batch stuff whenever it's possible and practical to do so. If you're using async commit this is less important, but still highly recommended.
尽可能使用临时表.它们不会产生 WAL 流量，因此它们的插入和更新速度要快得多.有时值得将一堆数据放入临时表中，根据需要对其进行操作，然后执行 INSERT INTO ... SELECT ... 将其复制到最终表中.请注意，临时表是针对每个会话的；如果您的会话结束或您失去连接，那么临时表就会消失，并且没有其他连接可以看到会话临时表的内容.
Whenever possible use temporary tables. They don't generate WAL traffic, so they're lots faster for inserts and updates. Sometimes it's worth slurping a bunch of data into a temp table, manipulating it however you need to, then doing an INSERT INTO ... SELECT ... to copy it to the final table. Note that temporary tables are per-session; if your session ends or you lose your connection then the temp table goes away, and no other connection can see the contents of a session's temp table(s).
如果您使用的是 PostgreSQL 9.1 或更新版本，您可以使用 UNLOGGED 数据表，您可以承受丢失的数据，例如会话状态.这些在不同的会话中可见并在连接之间保留.如果服务器不正常关闭，它们就会被截断，因此它们不能用于您无法重新创建的任何内容，但它们非常适合缓存、物化视图、状态表等.
If you're using PostgreSQL 9.1 or newer you can use UNLOGGED tables for data you can afford to lose, like session state. These are visible across different sessions and preserved between connections. They get truncated if the server shuts down uncleanly so they can't be used for anything you can't re-create, but they're great for caches, materialized views, state tables, etc.
一般来说，不要DELETE FROM blah;.使用 TRUNCATE TABLE blah; 代替；当您转储表中的所有行时，速度要快得多.如果可以，在一个 TRUNCATE 调用中截断多个表.但是，如果您一遍又一遍地执行大量 TRUNCATES 小表，则需要注意；请参阅:Postgresql 截断速度
In general, don't DELETE FROM blah;. Use TRUNCATE TABLE blah; instead; it's a lot quicker when you're dumping all rows in a table. Truncate many tables in one TRUNCATE call if you can. There's a caveat if you're doing lots of TRUNCATES of small tables over and over again, though; see: Postgresql Truncation speed
如果您没有外键索引，则涉及这些外键引用的主键的 DELETE 将非常慢.如果您希望从引用的表中DELETE，请确保创建此类索引.TRUNCATE 不需要索引.
If you don't have indexes on foreign keys, DELETEs involving the primary keys referenced by those foreign keys will be horribly slow. Make sure to create such indexes if you ever expect to DELETE from the referenced table(s). Indexes are not required for TRUNCATE.
不要创建不需要的索引.每个索引都有维护成本.尝试使用最少的索引集，让位图索引扫描将它们组合起来，而不是维护太多庞大、昂贵的多列索引.需要索引的地方，尽量先填表，最后再创建索引.
Don't create indexes you don't need. Each index has a maintenance cost. Try to use a minimal set of indexes and let bitmap index scans combine them rather than maintaining too many huge, expensive multi-column indexes. Where indexes are required, try to populate the table first, then create indexes at the end.
如果您可以管理它，拥有足够的 RAM 来容纳整个数据库是一个巨大的胜利.
Having enough RAM to hold the entire database is a huge win if you can manage it.
如果您没有足够的 RAM，则存储速度越快越好.即使是便宜的 SSD 也会对旋转锈产生巨大的影响.不过，不要相信廉价的 SSD 用于生产，它们通常不安全，可能会占用您的数据.
If you don't have enough RAM, the faster storage you can get the better. Even a cheap SSD makes a massive difference over spinning rust. Don't trust cheap SSDs for production though, they're often not crashsafe and might eat your data.
Greg Smith 的书 PostgreSQL 9.0 High Performance到一个稍微旧的版本.它应该是一个有用的参考.
Greg Smith's book, PostgreSQL 9.0 High Performance remains relevant despite referring to a somewhat older version. It should be a useful reference.
加入 PostgreSQL 通用邮件列表并关注它.
Join the PostgreSQL general mailing list and follow it.
这篇关于优化 PostgreSQL 以进行快速测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
