Postgresql 11 - 表達到約 500 萬條記錄時插入性能緩慢

我有一個大約 600 萬條記錄的表，這些記錄被載入到一台小型機器（使用標準 PC 硬碟）中的 postgresql 實例上。

這似乎是一個 I/O 問題，因為有可用的記憶體和處理能力。

該表有一個 UUID（隨機，v4）主鍵並從中央伺服器獲取記錄 - 它是本地副本。

載入表開始非常快（每 500 毫秒約 1000 條記錄），隨著表填滿記錄，它變得非常慢（每 30 秒約 1000 條記錄）。

首先想到的是“索引”，所以我刪除了表上的所有索引（保留了主鍵）——幾乎沒有改進。

AFAIK，postgresql 沒有像 SQL Server 這樣的聚集索引，這可能會導致隨機 UUID 插入時出現頁面拆分。

還有什麼可能是問題？

更新：在使用 SSD 而不是 HDD 的機器上，相同的過程和設置不是問題（在 3000 多台機器上無法更改）。

設置：

• 共享緩衝區 = 256MB
• 工作記憶體 = 10MB
• 維護工作記憶體 = 256MB
• dynamic_shared_memory_type = posix
• 有效 io_concurrency = 2
• max_worker_processes = 2
• max_parallel_workers_per_gather = 1
• max_parallel_workers = 2
• wal_level = 最小
• max_wal_size = 1GB
• min_wal_size = 100MB
• checkpoint_completion_target = 0.5
• max_wal_senders = 0
• random_page_cost = 4.0
• 有效記憶體大小 = 1GB
• 自動真空 = 開啟
• autovacuum_max_workers = 1

編輯：

做了一些更新以提高性能：

• 將 noatime 添加到 /etc/fstab（ext4 分區）；
• 禁用磁碟寫記憶體；
• 禁用 autovacuum 並創建一個 systemd 服務在關機時執行此操作；
• set Effective_io_concurrency = 1（由於高I/O，機器很慢）；
• 更改為 UUIDv1 - 因此它將是順序的，並避免在主鍵索引 b-tree 或任何其他 b-tree 上進行輪換；
• 使用雜湊技術對錶進行分區（嘗試使用 10 個分區和 50 個分區）；
• 是否進行了正確的填充因子配置：填充因子 = 75 / 85，具體取決於它獲得多少無序數據；

沒有明顯的變化。。

它在每 1000 行 500 毫秒到 1 秒內以大約 500 萬行的速度填充表，然後開始變得非常慢，至少 30 秒時以 1000 行。

嘗試 REINDEX all 以查看是否是填充索引問題 - 沒有結果。

編輯2：

表格：

create table customer (
   field1  serial      not null,
   field2  uuid        not null default (uuid_generate_v1()),
   field3  varchar(11) null,
   field4  varchar(2)  not null,
   field5  varchar(1)  not null,
   field6  varchar(40) not null,
   field7  varchar(40) null,
   field8  varchar(40),
   field9  bigint,
   field10 varchar(15),
   field11 varchar(20),
   field12 integer,
   field13 date,
   field14 varchar(1),
   field15 integer,
   field16 varchar(100),
   field17 varchar(100),
   field18 boolean,
   field19 integer,
   field20 varchar(20),
   field21 timestamp,
   field22 timestamp,
   field23 varchar(2),
   field24 varchar(40) not null,
   field25 varchar(40),
   field26 boolean,
   field27 boolean     null
) partition by hash (
   field1
);

-- Fillfactor = 85 because this table is a copy from the original so it might
-- eventually get unordered data
alter table customer
   add constraint customer_pk
       primary key (field1) with (fillfactor = 85);

-- CREATE 10 PARTITIONS...

create index idx_customer_01 on customer (field2) with (fillfactor =75);
create index idx_customer_02 on customer (field9, field14) with (fillfactor =75);
create index idx_customer_03 on customer (field25, field14) with (fillfactor =75);

create table customer_contacts (
   field1 serial,
   field2 uuid default (uuid_generate_v1()),
   field3 uuid    not null,
   field4 integer not null,
   field5 smallint,
   field6 bigint,
   field7 boolean not null
) partition by hash (
   field1
);

alter table customer_contacts
   add constraint customer_contacts_pk
       primary key (field1) with (fillfactor =85);

-- CREATE 10 PARTITIONS...

create index idx_customer_contacts_01 on customer_contacts (field2) with (fillfactor =85);
create index idx_customer_contacts_02 on  customer_contacts (field3, field4) with (fillfactor =85);

編輯3：

表上沒有外鍵，也沒有觸發器。

雜湊分區導致了問題。它會導致不利用記憶體/緩衝區的“隨機”插入。

將其更改為順序/範圍分區解決了該問題。

這是你可以分成小批量而不是一次全部 6+ 百萬的東西嗎？如果硬體上限，我會考慮對這些進行細分。

引用自：https://dba.stackexchange.com/questions/243657