如何在 PostgreSQL 上保持高插入性能

我正在開發一個項目，該項目將測量文件中的數據解析到 Posgres 9.3.5 數據庫中。

核心是一個表（按月分區），其中包含每個測量點的一行：

CREATE TABLE "tblReadings2013-10-01"
(
-- Inherited from table "tblReadings_master":  "sessionID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "fieldSerialID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "timeStamp" timestamp without time zone NOT NULL,
-- Inherited from table "tblReadings_master":  value double precision NOT NULL,
 CONSTRAINT "tblReadings2013-10-01_readingPK" PRIMARY KEY ("sessionID", "fieldSerialID", "timeStamp"),
 CONSTRAINT "tblReadings2013-10-01_fieldSerialFK" FOREIGN KEY ("fieldSerialID")
     REFERENCES "tblFields" ("fieldSerial") MATCH SIMPLE
     ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT "tblReadings2013-10-01_sessionFK" FOREIGN KEY ("sessionID")
 REFERENCES "tblSessions" ("sessionID") MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT "tblReadings2013-10-01_timeStamp_check" CHECK ("timeStamp" >= '2013-10-01 00:00:00'::timestamp without time zone AND "timeStamp" < '2013-11-01 00:00:00'::timestamp without time zone)
)

我們正在使用已收集的數據填充表。每個文件代表大約 48,000 個點的交易，有幾千個文件。它們是使用INSERT INTO "tblReadings_master" VALUES (?,?,?,?);

最初，文件以 1000+ 次插入/秒的速度導入，但一段時間後（數量不一致，但從不超過 30 分鐘左右），該速度驟降至 10-40 次/秒，並且 Postgres 程序執行 CPU。恢復原始速率的唯一方法是執行完全真空和分析。這最終將在每個月表中儲存大約 1,000,000,000 行，因此清理需要一些時間。

編輯：這是一個範例，它在較小的文件上執行了一段時間，然後在較大的文件啟動後失敗。較大的文件看起來更不穩定，但我認為這是因為事務僅在文件末尾送出，大約 40 秒。

將有一個 Web 前端選擇一些項目但沒有更新或刪除，並且在沒有其他活動連接的情況下可以看到這一點。

我的問題是：

1. 我們如何判斷是什麼導致了 CPU 的減速/軌道（這是在 Windows 上）？
2. 我們可以做些什麼來保持原來的性能？

有幾件事可能會導致這個問題，但我不能確定其中任何一個是真正的問題。故障排除都涉及在數據庫中打開額外的日誌記錄，然後查看慢速部分是否與那裡的消息一致。確保您在 log_line_prefix 設置中放置了一個時間戳，以便查看有用的日誌。請參閱我的調整介紹以從此處開始： https ://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Postgres 將所有的寫入都寫入作業系統記憶體，然後再寫入磁碟。您可以通過打開 log_checkpoints 並閱讀消息來觀察。當事情變慢時，可能只是所有的記憶體現在都滿了，所有的寫入都卡在等待 I/O 的最慢部分。您可以通過更改 Postgres 檢查點設置來改善這一點。

人們有時會遇到數據庫內部問題，即大量插入卡住等待數據庫中的鎖定。打開 log_lock_waits 看看你是否擊中了那個。

有時，一旦系統自動清理程序啟動，您可以執行突發插入的速率高於您可以維持的速率。打開 log_autovacuum 以查看問題是否與發生時並發。

我們知道，數據庫的私有 shared_buffers 記憶體中的大量記憶體在 Windows 上不如在其他作業系統上工作得那麼好。當它發生時，也沒有太多的可見性。我不會嘗試託管在 Windows PostgreSQL 數據庫中每秒插入 1000 次以上的內容。對於真正繁重的寫作來說，這還不是一個好的平台。

我不是 Postgres 專家，所以這可能是錯誤的！您的主鍵有 3 列，第一個欄位為 sessionID。該文件是否包含適當的時間戳分佈？您可能會考慮在主鍵中設置第一個欄位或使用代理鍵，因為目前這相當廣泛。

從您的腳本中，我認為您沒有集群。與 SQL Server 不同，但我認為您必須使用“集群”命令在 Postgres 中指定表的物理順序。該連結談到了這一點：

https://stackoverflow.com/questions/4796548/about-clustered-index-in-postgres

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