sql - PostgreSQL DELETE/INSERT 吞吐量问题

Question

我在 PostgreSQL 9.0 上遇到 DELETE/INSERT 序列的吞吐量问题。我正在寻找改善这种情况的想法。

在我们可用的硬件上，我可以以 3000/s 的持续速率(平均跨 10 个表)将新行插入数据库，远远超过我通常测试的每个表中的 100 万行。但是，如果我切换到我们删除一行并用不同数据重新插入它的模式，性能下降超过一个数量级，达到 250 行/秒(同样，平均分布在 10 个表中)。

任何表都没有约束。每个表中有 2 个索引列，总索引大小(每个表 1m 行)为 1GB，这在 shared_buffers (2GB) 内很舒服。总数据大小(每个表 100 万行)为 12GB，远小于总系统 RAM。这是一个影子数据库，我们可以在其中进行紧急重建，因此我们在关闭 fsync 的情况下运行。

看起来，当我们处于填充模式时，我们受益于非常低的磁盘寻道时间，因为正在追加数据。但是，当我们切换到更新模式时，会进行大量查找(大概是为了删除旧行)。随机磁盘寻找成本 ~8ms(=~125 每秒)。有什么方法(不改变硬件)可以显着提高 UPDATE/re-INSERT 操作的性能？

编辑 1:我正在两个不同规范的硬件平台上进行性能测试。我之前引用的数字来自更高规范的平台。我刚刚在较低规范的平台上完成了测试运行。在这个测试中，我尽可能快地插入新行，每 10 秒记录一次插入率，直到我插入 100 万行。此时我的测试脚本切换到更新随机行。

此图显示，在填充期间，测得的更新速率是对所有 10 个表进行约 150 次更新/秒，并且更新速率为 <对所有 10 个表进行 10 次更新/秒。

@wildplasser - 机器是真机，不是虚拟机。这 10 个表都具有以下架构。

CREATE TABLE objecti_servicea_item1
(
  iss_scs_id text,
  iss_generation bigint,
  boolattr1 boolean,
  boolattr2 boolean,
  boolattr3 boolean,
  boolattr4 boolean,
  boolattr5 boolean,
  boolattr6 boolean,
  boolattr7 boolean,
  boolattr8 boolean,
  boolattr9 boolean,
  boolattr10 boolean,
  boolattr11 boolean,
  boolattr12 boolean,
  boolattr13 boolean,
  boolattr14 boolean,
  boolattr15 boolean,
  boolattr16 boolean,
  boolattr17 boolean,
  intattr1 bigint,
  intattr2 bigint,
  intattr3 bigint,
  intattr4 bigint,
  intattr5 bigint,
  intattr6 bigint,
  intattr7 bigint,
  intattr8 bigint,
  intattr9 bigint,
  intattr10 bigint,
  intattr11 bigint,
  intattr12 bigint,
  intattr13 bigint,
  intattr14 bigint,
  intattr15 bigint,
  intattr16 bigint,
  intattr17 bigint,
  strattr1 text[],
  strattr2 text[],
  strattr3 text[],
  strattr4 text[],
  strattr5 text[],
  strattr6 text[],
  strattr7 text[],
  strattr8 text[],
  strattr9 text[],
  strattr10 text[],
  strattr11 text[],
  strattr12 text[],
  strattr13 text[],
  strattr14 text[],
  strattr15 text[],
  strattr16 text[],
  strattr17 text[]
)
WITH (
  OIDS=FALSE
);
CREATE INDEX objecti_servicea_item1_idx_iss_generation
  ON objecti_servicea_item1
  USING btree
  (iss_generation );
CREATE INDEX objecti_servicea_item1_idx_iss_scs_id
  ON objecti_servicea_item1
  USING btree
  (iss_scs_id );

正在执行的“更新”涉及 10 个表中每个表的以下 SQL。

DELETE FROM ObjectI_ServiceA_Item1 WHERE iss_scs_id = 'ObjUID39'
INSERT INTO ObjectI_ServiceA_Item1 
VALUES ('ObjUID39', '2', '0', NULL, '0'
, NULL, NULL, NULL, '1', '1', NULL, '0'
, NULL, NULL, NULL, NULL, '0', '1', '1'
, '-70131725335162304', NULL, NULL, '-5241412302283462832'
, NULL, '310555201689715409', '575266664603129486'
, NULL, NULL, NULL, NULL, NULL, NULL
, '-8898556182251816700', NULL, '3325820251460628173'
, '-3434461681822953613'
, NULL
, E'{pvmo2mt7dma37roqpuqjeu4p8b,"uo1kjt1b3eu9g5vlf0d02l6iaq\\\\\\",",45kfns1j80gc7fri0dm29hnrjo}'
, NULL, NULL
, E'{omjv460do8cb7abn8t3eg5b6ki,"a7hrlninbk1rmu6h3rd4787l7f\\\\\\",",24n3ipfua5spma2vrj2aji98g3}'
, NULL
, E'{1821v2n2ermm4jujrucu5tekmm,"ukgst224964uhthkhjj9v189ft\\\\\\",",6dfsaniq9mftvbdr8g1sr8e6as}'
, E'{c2a9gvf0fnd38m8vprlhkp2n74,"ts86vbat12lfr0d7l4tc29k9uk\\\\\\",",32b5j9r5evmrie4h21hi10dpot}'
, E'{18pve4cmcbrjiom9bpvoo1l4n0,"hrqcsane6r0n7u2oj79bj605rh\\\\\\",",32q5n18q3qbkuit605fv47270o}'
, E'{l3bf96shrpnnqgt35m7574t5n4,"cpol4k8296hbdqc9kac79oj0ua\\\\\\",",eqioulmb7vav10lbnc5jg752df}'
, E'{5fai108h163hpjcv0ofgfi7c28,"ci958009ddak3li7bp37slcs8i\\\\\\",",2itstj01tkprlul8f530uhs6s2}'
, E'{ueqfkdold8vc84jllr4b2cakt5,"t5vbea4r7tva091pa8j6886t60\\\\\\",",ul82aovhil1lpd290s14vd0p3i}'
, NULL, NULL, NULL, NULL, NULL)

请注意，在我的性能测试的第一阶段，DELETE 命令将始终不执行任何操作。

@Frank Heikens - 在我运行的性能测试中，更新是从 10 个线程完成的。但是，更新分配给线程的方式可确保同一行的多个更新始终由同一线程处理。

Answer 1

这个数据模型并不漂亮，DELETE - INSERT 也一样。 UPDATE 有什么问题？如果 iss_generation 和 iss_scs_id 在 UPDATE 中没有改变，数据库可以执行 HOT update (堆溢出元组)以提高性能。 UPDATE 还将受益于较低的填充因子。

当您执行 DELETE 记录时，该记录可能位于与 INSERT 所在位置不同的 block 中。使用较低的填充因子并使用 UPDATE，可能会为数据库提供在磁盘上同一 block 中删除和插入更新记录的选项。这将导致更少的随机 I/O。当可以使用 HOT 时，情况会变得更好，因为不需要更新索引。