Postgresql

更新速度慢

  • May 26, 2021

有 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 條件。可以看到,選擇部分工作得很快。慢是插入的一部分。

因此,查看答案,我看到了下一個要嘗試的項目:

  1. 測試如果將索引的填充因子設置為 50%、重新索引並查看結果會發生什麼。可以改善情況嗎?如果 PostgreSQL 中的表是堆並且填充因子不應該影響堆,那麼如何只影響索引?
  2. 嘗試將更新分成更小的塊。如果有必要在塊之間對錶進行真空處理,如何在此處提高速度?
  3. 對於大插入(將 400k-500k 行插入到大約 1 億行的表中)而不是使用 insert.. select .. where not exists(..) 使用另一種模式:將 select 的結果儲存到臨時未記錄表中,接下來使用副本將其儲存在主表中。它是一種廣泛使用的模式嗎?
  4. 我會測試未記錄的,它如何影響性能。但我不能讓表在生產中取消登錄,只能在開發環境中
  5. 在插入過程中,我無法刪除目標表上的所有索引,因為表中存在對數據的查詢。

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

引用自:https://dba.stackexchange.com/questions/259151