以可能的数据丢失为代价增加PostgreSQL写入速度？ [英] Increase PostgreSQL write speed at the cost of likely data loss?

问题描述

我喜欢PostgreSQL是抗崩溃的，因为我不想花时间修复数据库。但是，我确定必须有一些东西，我可以禁用/修改，以便插入/更新将工作更快，即使我在电源中断/崩溃之前丢失几个记录。我不担心几个记录 - 只是数据库作为一个整体。

I love that PostgreSQL is crash resistant, as I don't want to spend time fixing a database. However, I'm sure there must be some things I can disable/modify so that inserts/updates will work faster even if I lose a couple records prior to a power-outage / crash. I'm not worried about a couple records - just the database as a whole.

我试图优化PostgreSQL大量的写入。它目前需要22分钟，以插入100万行，这似乎有点慢。

I am trying to optimize PostgreSQL for large amounts of writes. It currently takes 22 minutes to insert 1 million rows which seems a bit slow.

我如何加快PostgreSQL写入速度？

已经调查（像full_page_writes），似乎也运行损坏数据的风险，这不是我想要的东西。我不介意丢失数据 - 我只是不想要腐败。

Some of the options I have looked into (like full_page_writes), seem to also run the risk of corrupting data which isn't something I want. I don't mind lost data - I just don't want corruption.

我使用的表 - 这是因为大多数表将包含int和小字符串这个示例表似乎是我应该期望的最好的例子。

Here is the table I am using - this since most of the tables will contain ints and small strings this "sample" table seems to be the best example of what I should expect.

CREATE TABLE "user"
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(70),
  website character varying(100),
  created integer,
  CONSTRAINT user_pkey PRIMARY KEY (id)
)
WITH ( OIDS=FALSE );
CREATE INDEX id ON "user" USING btree (id);

我有大约10个脚本，每次使用预准备语句发出100,000个请求。这是为了模拟我的应用程序将给予数据库的真实负载。

I have about 10 scripts each issuing 100,000 requests at a time using prepared statements. This is to simulate a real-life load my application will be giving the database. In my application each page has 1+ inserts.

我已经使用异步提交，因为我有

I am using asynchronous commits already, because I have

synchronous_commit = off

synchronous_commit = off

推荐答案

在22分钟内插入的1M记录为758记录/秒。这里的每个INSERT都是一个单独的提交到磁盘，最终有写入日志和数据库组件。通常我期望，甚至好的硬件与电池支持的缓存和一切你会幸运的命中3000提交/秒。所以你实际上不是太糟糕，如果这是常规硬件没有这样的写加速。这里的正常限制是在你遇到的情况下在500到1000个提交/秒范围内，没有对这种情况进行特殊调整。

1M records inserted in 22 minutes works out to be 758 records/second. Each INSERT here is an individual commit to disk, with both write-ahead log and database components to it eventually. Normally I expect that even good hardware with a battery-backed cache and everything you will be lucky to hit 3000 commit/second. So you're not actually doing too bad if this is regular hardware without such write acceleration. The normal limit here is in the 500 to 1000 commits/second range in the situation you're in, without special tuning for this situation.

喜欢，如果你不能让提交包含更多的记录，你的选项加速这包括：

As for what that would look like, if you can't make the commits include more records each, your options for speeding this up include:

• 关闭synchronous_commit（already
  done）

• Turn off synchronous_commit (already done)

增加wal_writer_delay。当
sync_commit关闭时，
数据库管理器提交，每200ms写入
。你可以使
换一些秒而不是
，如果你想通过向上调整
，它只是增加了
的数据丢失后的大小。

Increase wal_writer_delay. When synchronous_commit is off, the database spools commits up to be written every 200ms. You can make that some number of seconds instead if you want to by tweaking this upwards, it just increases the size of data loss after a crash.

将wal_buffers增加到16MB，只需
即可让整个操作更有效
。

Increase wal_buffers to 16MB, just to make that whole operation more efficient.

增加checkpoint_segments，将常规数据
写入磁盘的频率减少
。你可能想要
在这里至少64。

Increase checkpoint_segments, to cut down on how often the regular data is written to disk. You probably want at least 64 here. Downsides are higher disk space use and longer recovery time after a crash.

增加shared_buffers。这里的默认
很小，通常为32MB。你
必须增加UNIX共享
内存，系统必须分配多少。
一旦完成，有用的值是
通常>总RAM的1/4，高达
8GB。

Increase shared_buffers. The default here is tiny, typically 32MB. You have to increase how much UNIX shared memory the system has to allocate. Once that's done, useful values are typically >1/4 of total RAM, up to 8GB. The rate of gain here falls off above 256MB, the increase from the default to there can be really helpful though.

这是很多。你触摸的任何其他可能有帮助可能会导致崩溃数据损坏;这些都是完全安全的。

That's pretty much it. Anything else you touched that might help could potentially cause data corruption in a crash; these are all completely safe.

这篇关于以可能的数据丢失为代价增加PostgreSQL写入速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！