更新速度慢
有 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 環境中這樣做,除非您了解並接受在伺服器崩潰的情況下失去所有數據的風險。