更新速度慢
有 Postgres 10 (10.5 (Ubuntu 10.5-1.pgdg16.04+1))。伺服器有 256 GB RAM。
shared_buffers = 64GB effective_cache_size = 192GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 500 effective_io_concurrency = 200 work_mem = 111MB min_wal_size = 4GB max_wal_size = 16GB max_worker_processes = 8有一個大約有 1.33 億行的表。該表在磁碟上佔用 52 GB，實際數據大小為 26 GB。
表的 DDL：
CREATE TABLE tmd.t_metadata ( id bigserial NOT NULL, hash uuid NOT NULL, parent_hash uuid NULL, dataset_id int2 NOT NULL, status_id int2 NULL, create_ts timestamp NOT NULL, update_ts timestamp NOT NULL, source_path varchar(1024) NULL, target_path varchar(1024) NULL, info jsonb NULL, file_modified timestamptz NULL, CONSTRAINT t_metadata_pk PRIMARY KEY (id), CONSTRAINT t_metadata_ux UNIQUE (hash) ); CREATE INDEX metadata_file_modified_ix ON tmd.t_metadata USING btree (file_modified); CREATE INDEX metadata_search_ix ON tmd.t_metadata USING btree (dataset_id) WHERE ((parent_hash IS NULL) AND (status_id = ANY (ARRAY[0, 1]))); CREATE INDEX metadata_search_old_ix ON tmd.t_metadata USING btree (dataset_id) WHERE ((parent_hash IS NULL) AND (file_modified IS NULL)); CREATE INDEX t_metadata_file_modified_filter_ix ON tmd.t_metadata USING btree (dataset_id, file_modified) WHERE ((status_id = ANY (ARRAY[0, 1])) AND (parent_hash IS NOT NULL)); CREATE INDEX t_metadata_file_modified_ix ON tmd.t_metadata USING btree (file_modified) WHERE (parent_hash IS NULL); CREATE UNIQUE INDEX t_metadata_hash_uindex ON tmd.t_metadata USING btree (hash);並且有查詢：
UPDATE tmd.t_metadata SET status_id=2, update_ts='2020-02-07T13:45:46.519667'::timestamp WHERE tmd.t_metadata.dataset_id = '20' AND tmd.t_metadata.status_id IN (0) AND tmd.t_metadata.file_modified > '2017-01-01' AND tmd.t_metadata.file_modified < '2019-01-11' AND tmd.t_metadata.dataset_id = '20' AND tmd.t_metadata.parent_hash IS NOT null;查詢使用索引 tmd.t_metadata_file_modified_filter_ix 進行工作：
QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Update on t_metadata (cost=747718.24..4804650.70 rows=25992028 width=293) | -> Bitmap Heap Scan on t_metadata (cost=747718.24..4804650.70 rows=25992028 width=293) | Recheck Cond: ((dataset_id = '20'::smallint) AND (file_modified > '2017-01-01 00:00:00+03'::timestamp with time zone) AND (file_modified < '2019-01-11 00:00:00+03'::timestamp with time zone) AND (status_id = ANY ('{0,1}'::integer[])) AND (parent_h| Filter: (status_id = 0) | -> Bitmap Index Scan on t_metadata_file_modified_filter_ix (cost=0.00..741220.23 rows=26007973 width=0) | Index Cond: ((dataset_id = '20'::smallint) AND (file_modified > '2017-01-01 00:00:00+03'::timestamp with time zone) AND (file_modified < '2019-01-11 00:00:00+03'::timestamp with time zone)) |它更新了大約 100k 行。當我測試它時，它可以工作大約 30 秒。我看到它現在可以工作超過 2 個小時。它顯示它在 DataFileRead 上等待 IO。同樣的事情發生了很多次。我將執行計劃記錄到 PostgreSQL 日誌中，這與我的測試期間相同。
來自 PostgreSQL 日誌的執行計劃：
2020-02-07 17:28:04.839 [24465] dataware@dev_zakupki-44 LOG: duration: 2538066.932 ms plan: Query Text: UPDATE tmd.t_metadata SET status_id=2, update_ts='2020-02-07T13:45:46.519667'::timestamp WHERE tmd.t_metadata.dataset_id = '20' AND tmd.t_metadata.status_id IN (0) AND tmd.t_metadata.file_mod ified > '2017-01-01' AND tmd.t_metadata.file_modified < '2019-01-11' AND tmd.t_metadata.dataset_id = '20' AND tmd.t_metadata.parent_hash IS NOT NULL Update on tmd.t_metadata (cost=747718.24..4804650.70 rows=25992028 width=293) (actual time=2538066.916..2538066.916 rows=0 loops=1) Buffers: shared hit=2012810 read=206960 dirtied=201143 -> Bitmap Heap Scan on tmd.t_metadata (cost=747718.24..4804650.70 rows=25992028 width=293) (actual time=12.003..300.566 rows=103148 loops=1) Output: id, hash, parent_hash, dataset_id, '2'::smallint, create_ts, '2020-02-07 13:45:46.519667'::timestamp without time zone, source_path, target_path, info, file_modified, ctid Recheck Cond: ((t_metadata.dataset_id = '20'::smallint) AND (t_metadata.file_modified > '2017-01-01 00:00:00+03'::timestamp with time zone) AND (t_metadata.file_modified < '2019-01-11 00:00:00+03 '::timestamp with time zone) AND (t_metadata.status_id = ANY ('{0,1}'::integer[])) AND (t_metadata.parent_hash IS NOT NULL)) Filter: (t_metadata.status_id = 0) Heap Blocks: exact=2041 Buffers: shared hit=2443 -> Bitmap Index Scan on t_metadata_file_modified_filter_ix (cost=0.00..741220.23 rows=26007973 width=0) (actual time=11.623..11.623 rows=103148 loops=1) Index Cond: ((t_metadata.dataset_id = '20'::smallint) AND (t_metadata.file_modified > '2017-01-01 00:00:00+03'::timestamp with time zone) AND (t_metadata.file_modified < '2019-01-11 00:00:0 0+03'::timestamp with time zone)) Buffers: shared hit=402其他一些插入也發生類似情況，它們在單個語句中插入 300k-500k 行。他們工作幾個小時。但是當我在同一台伺服器上用相同的數據測試它們時，它們通常工作得很快。當應用程序在伺服器上啟動並開始將數據載入到數據庫中時，性能會下降很多倍。這是一個開發伺服器，因此幾乎可以在配置中進行任何測試以進行測試，但同樣的問題也會發生在產品伺服器上。
在 io 寫操作中，幾乎所有的核心都在等待。
我很不明白，如果事務還沒有送出，為什麼 PostgreSQL 會向磁碟寫入一些東西？我認為未送出事務的數據應該由後台編寫者編寫？
在我看來，後端程序等待寫入。通常他們不應該這樣做嗎？可能由於dirty_ratio而等待刷新？dirty_ratio 是 20，background_dirty_ratio 是 5。我們可以在這裡改變一些東西嗎？
如何提高查詢的性能？
創建帳戶和初始文章是在沒有創建帳戶的情況下編寫的，我不知道如何連結它們。
關於測試。是的，我在同一台伺服器上測試了查詢。做了什麼：在長更新工作的時候，我將表的內容複製到一個單獨的表中，使用 select into。接下來，將更新應用到該表。它工作得很快，大約 30 秒。
至於關於將 status_id 添加到索引的問題 - 已經是，索引與 where 條件。可以看到，選擇部分工作得很快。慢是插入的一部分。
因此，查看答案，我看到了下一個要嘗試的項目：
- 測試如果將索引的填充因子設置為 50%、重新索引並查看結果會發生什麼。可以改善情況嗎？如果 PostgreSQL 中的表是堆並且填充因子不應該影響堆，那麼如何只影響索引？
- 嘗試將更新分成更小的塊。如果有必要在塊之間對錶進行真空處理，如何在此處提高速度？
- 對於大插入（將 400k-500k 行插入到大約 1 億行的表中）而不是使用 insert.. select .. where not exists(..) 使用另一種模式：將 select 的結果儲存到臨時未記錄表中，接下來使用副本將其儲存在主表中。它是一種廣泛使用的模式嗎？
- 我會測試未記錄的，它如何影響性能。但我不能讓表在生產中取消登錄，只能在開發環境中
- 在插入過程中，我無法刪除目標表上的所有索引，因為表中存在對數據的查詢。
關於 IO 等待。是的，首先來自 pg_stat_activity，其次來自 perf 實用程序。
關於共享緩衝區。但是伺服器有 256GB 記憶體，只有 PostgreSQL 使用伺服器，shared_buffers 設置為 64GB 記憶體，所以至少所有索引都應該適合記憶體。
IO系統很慢。RAID10 中的幾個 10k rpm 磁碟，大約 2000 IOPS 隨機讀取和 700 IOPS 隨機寫入。我考慮建議升級到 SSD 驅動器，但有必要了解預期結果。查詢會變得更快兩倍還是 10 倍以上？我不知道。DB的總大小約為1.2 TB。
我想海量數據載入和海量數據更新的問題對於 PostgreSQL 來說是很常見的。有沒有關於它的論文？
並感謝所有回答您的時間的人。
它顯示它在 DataFileRead 上等待 IO。
在 io 寫操作中，幾乎所有的核心都在等待。
這些似乎是矛盾的。我假設第一個結論來自 pg_stat_activity。第二個是哪裡來的？
我很不明白，如果事務還沒有送出，為什麼 PostgreSQL 會在磁碟上寫一些東西？
臟緩衝區只有這麼多空間。所以你最終必須寫出一些東西來騰出空間來讀入不同的東西。一般來說，事務管理和緩衝區管理只是鬆散耦合的。或者至少，以不同於你的心智模型所說的方式耦合。讓您決定每個元組是否已送出的資訊是緩衝區/頁面本身的一部分，並與它一起被寫出，然後與它一起讀回。它們不是僅駐留記憶體的。並且在送出時，您只需要寫出並刷新您生成的 WAL，而不是您弄髒的所有其他數據。
增加 shared_buffers 有時會有所幫助，為臟緩衝區在被迫寫入之前積累更多空間。如果您的所有索引都可以放入 shared_buffers 中，那麼這真的很有幫助，因為您可以一遍又一遍地弄髒相同的索引頁，但是在弄髒之間會有時間間隔，而不會在它們之間寫入。如果您不能使 shared_buffers 變得那麼大，那麼最終仍需要寫入相同數量的數據，因此您可能只是在轉移痛苦，而不是修復它。您可以通過更改 *_flush_after 參數來獲得改進。我從來沒有對他們有過好運，但其他人有。
我認為未送出事務的數據應該由後台編寫者編寫？
如果後台寫的跟不上，那麼普通的後端自己寫。總的來說，我覺得後台寫手沒什麼用。如果寫入被核心自由吸收，那麼它們是如此之快，沒有理由將該任務解除安裝到後台寫入器。如果寫入在核心中被阻塞，那麼後台寫入器將無法跟上，因此後端必須自己做。
可能由於dirty_ratio而等待刷新？dirty_ratio 是 20，background_dirty_ratio 是 5。可以在這裡改變一些東西嗎？
所有臟數據最終都需要寫入磁碟。調整這些設置，您也許可以讓您的更新更快，只是讓您的檢查點在它開始發出同步請求後長時間佔用。
也許您只需要更快寫入的儲存。您目前的 IO 系統是什麼樣的？
PostgreSQL 會將數據寫入預寫日誌 (WAL)，並在 DML 操作期間寫入數據文件。沒有什麼能像 Oracle 中的 UNDO 表空間那樣工作。
考慮到 PostgreSQL 中沒有就地更新，更新只是一系列的插入和刪除。如果設置表的填充因子，請記住可能的更新（預設情況下不是，預設為 100，這意味著如果可能，在插入期間將數據頁填充到 100%），則可以進行 HOT（僅堆元組）更新（“新”行將物理放置在與“舊”相同的數據頁上）。
但是，如果索引列（或者，至少對於 PostgreSQL 10，任何參與過濾索引謂詞的列）被更新，HOT 更新將不會發生。
我建議將一個巨大的更新分成一系列較小的更新，在更新之間有一些延遲，以讓自動真空清理表格。在更新之間手動執行吸塵（不完整！沒有
ANALYZE選項）也可能不是一個壞主意。要載入數據，您可以使用複製而不是插入。另一種選擇是將數據載入到沒有索引的表中，然後再創建它們。
可以嘗試用於調查、測試或學習目的的另一件事是使表 UNLOGGED，執行大量插入/更新，並將其切換回 LOGGED。
免責聲明
我不建議在 prod 環境中這樣做，除非您了解並接受在伺服器崩潰的情況下失去所有數據的風險。