Postgresql

是什麼導致大型 INSERT 變慢和磁碟使用量激增？

  • September 15, 2015

我有一個大約 310 萬行的表，其中包含以下定義和索引：

CREATE TABLE digiroad_liikenne_elementti (
   ogc_fid serial NOT NULL,
   wkb_geometry geometry(Geometry,4258),
   tiee_tila numeric(9,0),
   vaylatyypp numeric(9,0),
   toiminnall numeric(9,0),
   eurooppati character varying(254),
   kansalline numeric(9,0),
   tyyppi numeric(9,0),
   liikennevi numeric(9,0),
   ens_talo_o numeric(9,0),
   talonumero numeric(9,0),
   ens_talo_v numeric(9,0),
   oik_puol_t character varying(254),
   tieosan_ta numeric(9,0),
   viim_talo_ numeric(9,0),
   viim_tal_1 numeric(9,0),
   vas_puol_t character varying(254),
   laut_tyypp numeric(9,0),
   lautta_lii numeric(9,0),
   inv_paalu_ numeric(19,11),
   inv_paal_1 numeric(19,11),
   liitalue_o numeric(9,0),
   ketju_oid numeric(9,0),
   tietojoukk numeric(9,0),
   ajoratanum numeric(4,0),
   viite_guid character varying(254),
   "timestamp" date,
   tiee_kunta numeric(9,0),
   toissij_ti character varying(254),
   viite_oid numeric(9,0),
   k_elem_id numeric(9,0),
   region character varying(40) DEFAULT 'REGION'::character varying,
   CONSTRAINT digiroad_liikenne_elementti_pkey PRIMARY KEY (ogc_fid)
);

CREATE INDEX digiroad_liikenne_elementti_wkb_geometry_geom_idx
 ON digiroad_liikenne_elementti USING gist (wkb_geometry);

CREATE INDEX dle_k_elem_id_idx
 ON digiroad_liikenne_elementti USING btree (k_elem_id);

CREATE INDEX dle_ogc_fid_idx
 ON digiroad_liikenne_elementti USING btree (ogc_fid);

CREATE INDEX dle_region_idx
 ON digiroad_liikenne_elementti USING btree (region COLLATE pg_catalog."default");

另一個有 860 萬行的表包含第一個表的行的屬性，這些表可以使用k_elem_idAND連接region。

CREATE TABLE digiroad_segmentti (
   ogc_fid serial NOT NULL,
   wkb_geometry geometry(Geometry,4258),
   segm_tila numeric(9,0),
   tyyppi numeric(9,0),
   loppupiste numeric(19,11),
   alkupiste numeric(19,11),
   vaikutuska numeric(9,0),
   vaikutussu numeric(9,0),
   vaikutusai character varying(254),
   tieosanume numeric(19,11),
   tienumero numeric(9,0),
   dyn_arvo numeric(9,0),
   dyn_tyyppi numeric(9,0),
   omistaja_t numeric(9,0),
   pysakki_va numeric(9,0),
   pysakki_ty numeric(9,0),
   pysakki_su numeric(9,0),
   pysakki_ka numeric(9,0),
   pysakki_yl character varying(254),
   palvelu_pa numeric(9,0),
   toissijain numeric(9,0),
   siltataitu numeric(9,0),
   rdtc_tyypp numeric(9,0),
   rdtc_alaty numeric(9,0),
   rdtc_paikk numeric(19,11),
   rdtc_luokk numeric(9,0),
   rdtc_liitt character varying(254),
   palvelu_ob numeric(9,0),
   ketju_oid numeric(9,0),
   tietojoukk numeric(9,0),
   ajoratanum numeric(4,0),
   viite_guid character varying(254),
   "timestamp" date,
   sivusiirty numeric(19,11),
   toissij_ti character varying(254),
   viite_oid numeric(9,0),
   k_elem_id numeric(9,0),
   region character varying(40) DEFAULT 'REGION'::character varying,
   CONSTRAINT digiroad_segmentti_pkey PRIMARY KEY (ogc_fid)
);

CREATE INDEX digiroad_segmentti_wkb_geometry_geom_idx
 ON digiroad_segmentti USING gist (wkb_geometry);

CREATE INDEX ds_dyn_arvo_idx
 ON digiroad_segmentti USING btree (dyn_arvo);

CREATE INDEX ds_dyn_tyyppi_idx
 ON digiroad_segmentti USING btree (dyn_tyyppi);

CREATE INDEX ds_k_elem_id_idx
 ON digiroad_segmentti USING btree (k_elem_id);

CREATE INDEX ds_ogc_fid_idx
 ON digiroad_segmentti USING btree (ogc_fid);

CREATE INDEX ds_region_idx
 ON digiroad_segmentti USING btree (region COLLATE pg_catalog."default");

CREATE INDEX ds_tyyppi_idx
 ON digiroad_segmentti USING btree (tyyppi);

我正在嘗試將第一個表的行（經過一些修改）插入到一個新表中：

CREATE TABLE edge_table (
   id serial NOT NULL,
   geom geometry,
   source integer,
   target integer,
   km double precision,
   kmh double precision DEFAULT 60,
   kmh_winter double precision DEFAULT 50,
   cost double precision,
   cost_winter double precision,
   reverse_cost double precision,
   reverse_cost_winter double precision,
   x1 double precision,
   y1 double precision,
   x2 double precision,
   y2 double precision,
   k_elem_id integer,
   region character varying(40),
   CONSTRAINT edge_table_pkey PRIMARY KEY (id)
);

由於執行單個插入語句需要很長時間，而且我無法查看語句是否被卡住或其他原因，所以我決定在函式的循環內以較小的塊執行它。

該函式如下所示：

DROP FUNCTION IF EXISTS insert_function();
CREATE OR REPLACE FUNCTION insert_function()
   RETURNS VOID AS
   $$
DECLARE
   const_type_1 CONSTANT int := 5;
   const_type_2 CONSTANT int := 11;
   i int := 0;
   row_count int;
BEGIN

   CREATE TABLE IF NOT EXISTS edge_table (
       id serial PRIMARY KEY,
       geom geometry,
       source integer,
       target integer,
       km double precision,
       kmh double precision DEFAULT 60,
       kmh_winter double precision DEFAULT 50,
       cost double precision,
       cost_winter double precision,
       reverse_cost double precision,
       reverse_cost_winter double precision,
       x1 double precision,
       y1 double precision,
       x2 double precision,
       y2 double precision,
       k_elem_id integer,
       region varchar(40)
   );


   batch_size := 1000;
   SELECT COUNT(*) FROM digiroad_liikenne_elementti INTO row_count;

   WHILE i*batch_size < row_count LOOP

       RAISE NOTICE 'insert: % / %', i * batch_size, row_count;

       INSERT INTO edge_table (kmh, kmh_winter, k_elem_id, region)
       SELECT      CASE WHEN DS.dyn_arvo IS NULL THEN 60 ELSE DS.dyn_arvo END,
                   CASE WHEN DS.dyn_Arvo IS NULL THEN 50 ELSE DS.dyn_arvo END,
                   DR.k_elem_id,
                   DR.region
       FROM        (
                       SELECT  DLE.k_elem_id,
                               DLE.region,
                       FROM    digiroad_liikenne_elementti DLE
                       WHERE   DLE.ogc_fid >= i * batch_size
                               AND
                               DLE.ogc_fid <= i * batch_size + batch_size
                   ) AS DR
                   LEFT JOIN
                   digiroad_segmentti DS ON
                       DS.k_elem_id = DR.k_elem_id
                       AND
                       DS.region = DR.region
                       AND
                       DS.tyyppi = const_type_1
                       AND
                       DS.dyn_tyyppi = const_type_2;

       i := i + 1;
   END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;

問題是它開始時非常快地通過循環，但在某些時候會減慢到爬行。當它變慢時，同時我的 Windows 8 任務管理器中的磁碟使用率上升到 99%，所以我懷疑這與問題有關。

INSERT使用一些隨機值自行執行語句會i非常快地執行，因此問題似乎只在函式內的循環中執行時出現。這是EXPLAIN (ANALYZE,BUFFERS)一個這樣的單一執行：

Insert on edge_table  (cost=0.86..361121.68 rows=1031 width=23) (actual time=3405.101..3405.101 rows=0 loops=1)
 Buffers: shared hit=36251 read=3660 dirtied=14
 ->  Nested Loop Left Join  (cost=0.86..361121.68 rows=1031 width=23) (actual time=61.901..3377.609 rows=986 loops=1)
       Buffers: shared hit=32279 read=3646
       ->  Index Scan using dle_ogc_fid_idx on digiroad_liikenne_elementti dle  (cost=0.43..85.12 rows=1031 width=19) (actual time=31.918..57.309 rows=986 loops=1)
             Index Cond: ((ogc_fid >= 200000) AND (ogc_fid < 201000))
             Buffers: shared hit=27 read=58
       ->  Index Scan using ds_k_elem_id_idx on digiroad_segmentti ds  (cost=0.44..350.16 rows=1 width=23) (actual time=2.861..3.337 rows=0 loops=986)
             Index Cond: (k_elem_id = dle.k_elem_id)
             Filter: ((tyyppi = 5::numeric) AND (dyn_tyyppi = 11::numeric) AND (vaikutussu = 3::numeric) AND ((region)::text = (dle.region)::text))
             Rows Removed by Filter: 73
             Buffers: shared hit=31266 read=3588
Total runtime: 3405.270 ms

我的系統在具有 8Gb RAM 的 Windows 8 上執行 PostgreSQL 9.3.5。

我已經嘗試過不同的批量大小，以不同的方式進行查詢並增加 Postgres 配置中的記憶體變數，但似乎沒有什麼能真正解決這個問題。

已從預設值更改的配置變數：

shared_buffers = 2048MB
work_mem = 64MB
effective_cache_size = 6000MB

我想找出導致這種情況發生的原因以及可以採取的措施。

創建新表時，避免**完全** 使用**CREATE TABLE AS**.

請參閱@Kassandry 的答案，了解 WAL 如何計算這一點。

CREATE OR REPLACE FUNCTION insert_function()
 RETURNS void AS
$func$
DECLARE
  const_type_1 CONSTANT int := 5;
  const_type_2 CONSTANT int := 11;
BEGIN    
  CREATE SEQUENCE edge_table_id_seq;

  CREATE TABLE edge_table AS
  SELECT nextval('edge_table_id_seq'::regclass)::int AS id
       , NULL::geometry         AS geom
       , NULL::integer          AS source
       , target::integer        AS target
       , NULL::float8           AS km
       , COALESCE(DS.dyn_arvo::float8, float8 '60') AS kmh
       , COALESCE(DS.dyn_Arvo::float8, float8 '50') AS kmh_winter
       , NULL::float8           AS cost
       , NULL::float8           AS cost_winter
       , NULL::float8           AS reverse_cost
       , NULL::float8           AS reverse_cost_winter
       , NULL::float8           AS x1
       , NULL::float8           AS y1
       , NULL::float8           AS x2
       , NULL::float8           AS y2
       , D.k_elem_id::integer   AS k_elem_id
       , D.region::varchar(40)  AS region
  FROM   digiroad_liikenne_elementti D
  LEFT   JOIN digiroad_segmentti DS
            ON DS.k_elem_id = D.k_elem_id
           AND DS.region = D.region
           AND DS.tyyppi = const_type_1
           AND DS.dyn_tyyppi = const_type_2;

  ALTER TABLE edge_table
     ADD CONSTRAINT edge_table_pkey PRIMARY KEY(id)
   , ALTER COLUMN id SET NOT NULL
   , ALTER COLUMN id SET DEFAULT nextval('edge_table_id_seq'::regclass)
   , ALTER COLUMN kmh SET DEFAULT 60
   , ALTER COLUMN kmh_winter SET DEFAULT 50;

  ALTER SEQUENCE edge_table_id_seq OWNED BY edge_table.id;    
END
$func$ LANGUAGE plpgsql;

文件：

除了避免歸檔器或 WAL 發送者處理 WAL 數據的時間之外，這樣做實際上會使某些命令更快，因為它們被設計為根本不寫wal_levelWAL minimal。fsync（與編寫 WAL 相比，他們可以通過在末尾執行更便宜的方式來保證碰撞安全 。）這適用於以下命令：

  • CREATE TABLE AS SELECT
  • CREATE INDEX（以及變體，例如ALTER TABLE ADD PRIMARY KEY）
  • ALTER TABLE SET TABLESPACE
  • CLUSTER
  • COPY FROM，當目標表在同一事務中較早創建或截斷時

也很重要

  • CREATE TABLE AS使得無法直接使用偽類型serial。但由於這只是一個“makro”，您可以手動完成所有操作：創建序列，使用它來生成id值。最後，設置列預設值並使列擁有序列。有關的：

  • plpgsql 函式包裝器是可選的（便於重複使用），您可以在事務中執行純 SQL：BEGIN; ... COMMIT;

  • 在插入數據之後添加也更快，因為在一個片段中創建（基礎）索引比增量添加值更快。PRIMARY KEY

  • 您的分區出現邏輯錯誤：

WHERE DLE.ogc_fid >= i * batch_size
AND   DLE.ogc_fid <= i * batch_size + batch_size

最後一行會與下一個分區重疊，行會被重複插入，導致PK中的唯一違規。使用<而不是<=可以解決這個問題 - 但我完全刪除了分區。

  • 如果您重複執行此操作，則可能會支付**多列索引**，具體取決於數據分佈。digiroad_segmentti (k_elem_id, tyyppi, dyn_tyyppi, region)

小事

  • 不要引用語言plpgsql名稱，它是一個標識符。
  • 將沒有參數的函式標記為 是沒有意義的STRICT。
  • VOLATILE是預設值，只是噪音。
  • 用於COALESCE為 NULL 值提供預設值。
  • 您的某些double precision( float8) 列可能會更好， integer因為您的舊表中大部分都有numeric (9,0)，這可能會被更便宜的 plain 替換integer。
  • 該列region varchar(40)看起來像是規範化的候選者（除非區域大多是唯一的？）創建一個區域表並僅用region_id作主表中的 FK 列。

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

相關問答

Postgresql

如何禁用或限制 Postgres 複製創建導致生成太多 WAL 文件？

  • October 5, 2019
檢視問答
Postgresql

巨大的數據庫（> 600M 記錄/表）查詢超時或達到記憶體限制？

  • March 8, 2019
檢視問答
Postgresql

X時如何結束功能

  • September 11, 2018
檢視問答
Postgresql

postgresql 使用 ‘where not in’ 子句優化查詢

  • January 24, 2018
檢視問答
Postgresql

PostgreSQL 從 9.3 升級到 9.4 後速度極慢

  • November 22, 2017
檢視問答
Postgresql

加快查詢計算。我在哪裡可以添加索引或優化查詢或伺服器？

  • March 3, 2017
檢視問答