巨大的表格同时进行读写

Question

我有一个PostgreSQL表格，其中有几百万行数据。我有多达20个进程向该表格写入（每秒几百个插入/更新），同时我也有一些进程从中读取（偶尔进行大型查询）。这导致读写两端都出现了很多故障（流关闭、输入/输出错误）。

我现在考虑将该表格分成多个表格。我将按对象的“类型”进行拆分，这基本上是一个只有20个可能值且分布相当的字段。

问题是，为了保证对数据的非阻塞访问，我应该使用多个表格、多个模式还是多个数据库。或者我应该使用完全不同的设置。另一个数据库？也许是HTable？

数据的完整性并不是那么重要。最终必须存在，但我确实不需要隔离级别或事务。我只需要一个快速的系统，可以从多个进程中读取和写入，而不会影响性能，并允许基于字段值进行查询。

现在我使用带有事务隔离级别TRANSACTION_READ_UNCOMMITTED和每个进程连接的JDBC。

更新：

模式如下：

CREATE TABLE rev
(
  id integer NOT NULL,
  source text,
  date timestamp with time zone,
  title text,
  summary text,
  md5sum text,
  author text,
  content text,
  CONSTRAINT rev_id_pk PRIMARY KEY (id),
  CONSTRAINT md5sum_un UNIQUE (md5sum)
)

CREATE TABLE resp
(
  id integer NOT NULL,
  source text,
  date timestamp with time zone,
  title text,
  summary text,
  md5sum text,
  author text,
  content text,
  CONSTRAINT resp_id_pk PRIMARY KEY (id),
  CONSTRAINT md5sum_un UNIQUE (md5sum)
)

我有一些字段上的索引。

一个样例查询看起来像：

SELECT * FROM rev LEFT JOIN resp ON rev.id = resp.parent_id WHERE rev.date > ? LIMIT 1000 OFFSET ?

resp表要小得多，但它也会更新并在联接中进行查询。

- morja

---

请问您能告诉我们正在更新和查询的数据的性质吗？提供模式和示例查询将会非常有帮助。您提到数据完整性并不是很重要，这让我想到这可能是某种事件日志记录。请澄清一下。 - Ocaj Nires

我更新了我的回答。这不是事件日志，而是从多个来源获取的大量数据（文章等）。每个“获取”过程都尽可能快地运行。目前插入似乎是瓶颈。另一个客户端偶尔请求此数据。 - morja

一个未知的OFFSET可能会在大表上成为问题，你最好使用数据库CURSOR。jdbc-connector支持此功能。 - Frank Heikens

好的，我切换到CURSOR并进行了更多的测试。 - morja

Answer 1

这导致了双方读写的许多失败。在PostgreSQL中，读写同一张表根本不应该是问题，MVCC工作正常。没有关于系统和进程正在执行什么操作的任何信息，很难告诉您如何解决问题。您可以告诉我们更多信息吗？并展示数据库模式吗？"READ UNCOMMITTED"在PostgreSQL中不存在，它被视为“{{link1：Read Committed}}”：在PostgreSQL中，您可以请求四个标准事务隔离级别之一。但在内部，只有两个不同的隔离级别，对应于Read Committed和Serializable级别。当您选择Read Uncommitted级别时，您真正获得的是Read Committed级别；当您选择Repeatable Read级别时，您真正获得的是Serializable级别，因此实际隔离级别可能比您选择的严格。

- Frank Heikens

---

大多数故障都是连接超时和流关闭异常。数据库似乎有一段时间完全没有响应。感谢您对隔离级别的洞察！ - morja

这些失败似乎与数据库的使用无关，是其他问题。来自数据库的无响应听起来像是性能问题，例如没有足够的I/O处理所有写入。在更改代码之前，您应该检查此问题：当写入其他表时，硬件不会变得更快，您仍然需要写入数据。 - Frank Heikens

是的，我理解。只要没有同时进行读取，写入看起来是没问题的。这就是为什么我认为它与在单个表上进行太多事务有关。你认为这不是原因吗？ - morja

不，许多交易根本不是问题。而且20个进程永远不会接近“很多”……插入和选择可以并发进行，这就是MVCC的全部意义！即使在更新和删除时，当您不尝试在不同的事务中更新或删除相同的记录时，也可以同时进行。那可能会减慢一些交易。 - Frank Heikens

Answer 2

我不确定什么程度的延迟才能获取可读数据，但你可能需要了解一下Slony Replication。基本上，你有一个主数据库和一个从数据库。所有的插入/写入都会放入主数据库中，然后Slony会将这些新条目复制到从数据库中（这需要一点时间，但并不是很长。也许几分钟）。然后你可以让你的应用程序只从从数据库中读取。

如果Slony对你来说不合适，你可以看看一些“多主”替代方案here。这些方案可以让你拥有多台可写机器，并将它们的所有内容复制到读取机器上。

- Mike Trpcic

---

Slony看起来很有趣。谢谢。但是这样的复制是否也有助于对表进行同时写入？它不仅有助于同时读取，对吗？ - morja

对于同时写入的问题，您可能需要多个从库 - 这样您可以将写入分散到多个数据库中以减轻压力，然后让它们相互复制以实现最终一致性。 - Mike Trpcic