等待PostgreSQL V13 –允许vacuum命令并行处理索引

开源软件联盟PostgreSQL分会 2020-02-18

1047

作者：Masahiko Sawada和Amit Kapila

Mahendra Singh和Sergei Kornilov

2020年1月20日，阿米特·卡皮拉（Amit Kapila）进行了修补：

允许vacuum命令并行处理索引。

此功能允许vacuum利用多个CPU来处理索引。这使我们能够与后台工作人员一起执行索引清理。这会在VACUUM 命令中添加一个PARALLEL选项，用户可以在其中指定可用于执行该命令的工作程序数量，该工作程序数量受表上索引数量的限制。其值为零将禁用并行性。此选项不能与FULL选项一起使用。

每个索引最多可以通过一个vacuum进程进行处理。因此，当表具有至少两个索引时，可以使用并行vacuum。

并行度可以由用户指定，也可以基于表具有的索引数确定，并进一步受

max_parallel_maintenance_workers 限制。如果索引的大小大于min_parallel_index_scan_size，则该索引可以参与并行vacuum。

作者：Masahiko Sawada和Amit Kapila ；Mahendra Singh和Sergei Kornilov

讨论：

https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com
https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com

描述很长，所以让我们看看它是如何工作的。

首先，需要一张带有几个索引的示例表：

=$ CREATE TABLE test (
    id               INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    some_int         INT4,
    some_timestamp   TIMESTAMPTZ,
    other_int        INT4,
    other_timestamp  TIMESTAMPTZ);CREATE TABLE
 
=$ INSERT INTO test (some_int, some_timestamp, other_int, other_timestamp)SELECT
    random() * 500000000,
    '2000-01-01'::DATE + '20 years'::INTERVAL * random(),
    random() * 500000000,
    '1970-01-01'::DATE + '20 years'::INTERVAL * random()FROM
  generate_series(1,100000000) i;
  INSERT 0 100000000

好的，我们有一些行数据，如下所示：

=$ SELECT * FROM test LIMIT 10;
 id | some_int |       some_timestamp       | other_int |       other_timestamp----+-----------+-----------------------------+-----------+-----------------------------
  1 | 179275930 | 2010-01-28 23:02:57.0048+01 |  31223069 | 1975-10-21 03:30:13.536+01
  2 | 119009254 | 2019-05-01 14:40:19.5168+02 | 390536066 | 1974-02-18 16:12:31.7952+01
  3 | 153965899 | 2010-04-26 00:36:46.3968+02 | 109395281 | 1985-10-09 01:30:36.0288+01
  4 | 123106154 | 2006-06-28 18:59:21.0624+02 | 399537003 | 1982-12-08 21:13:32.5056+01
  5 | 338157258 | 2006-11-04 07:21:34.7328+01 | 487378393 | 1975-02-14 05:59:28.7232+01
  6 | 108837322 | 2006-10-10 04:07:35.2704+02 |  53539283 | 1987-05-17 00:59:49.5744+02
  7 | 434671405 | 2011-04-09 00:21:43.4304+02 | 374841058 | 1980-05-13 17:12:37.1808+02
  8 | 407587896 | 2013-08-02 15:26:41.3376+02 | 180180561 | 1985-01-06 04:22:03.6768+01
  9 | 450852732 | 2008-10-27 18:14:00.4992+01 |  81128068 | 1975-06-26 01:55:29.8848+01
 10 | 306987401 | 2013-12-17 08:39:19.1232+01 |  28668776 | 1970-08-01 15:43:29.0208+01
(10 ROWS)

现在，让我们添加一些索引：

=$ CREATE INDEX i1 ON test (some_int);
CREATE INDEX
 =$ CREATE INDEX i2 ON test (some_timestamp);
CREATE INDEX
 =$ CREATE INDEX i3 ON test (other_int);
CREATE INDEX
 =$ CREATE INDEX i4 ON test (other_timestamp);
CREATE INDEX
 =$ CREATE INDEX i5 ON test (some_int, some_timestamp);
CREATE INDEX
 =$ CREATE INDEX i6 ON test (other_int, other_timestamp);
CREATE INDEX

因此，目前有7个索引以及表本身：

=$ SELECT c.relname, c.relkind, pg_size_pretty(pg_relation_size(c.oid))
    FROM pg_class c
    WHERE c.relname = 'test' OR
        c.oid IN ( SELECT i.indexrelid FROM pg_index i WHERE i.indrelid = 'test'::regclass );


  relname | relkind | pg_size_pretty
-----------+---------+----------------
 test     | r      | 5744 MB
 test_pkey | i      | 2142 MB
 i1       | i      | 2142 MB
 i2       | i      | 2142 MB
 i3       | i      | 2142 MB
 i4       | i      | 2142 MB
 i5       | i      | 3004 MB
 i6       | i      | 3004 MB
(8 ROWS)

有了这些索引，并禁用了自动vacuum，运行如下指令：

=$ DELETE FROM test WHERE random() < 0.5;

这样索引（和表）将需要进行一些清理。

然后，在没有任何并行化的情况下运行vacuum：

=$ vacuum (verbose ON, analyze ON, parallel 0) test;

之后，重新创建了整个内容，然后运行：

=$ SET max_parallel_maintenance_workers = 2;
=$ vacuum (verbose ON, analyze ON) test;

最后，重新创建：

=$ SET max_parallel_maintenance_workers = 8;
=$ vacuum (verbose ON, analyze ON) test;

日志显示，对于连续vacuum：

=$ vacuum (verbose ON, analyze ON, parallel 0) test;
psql:test1.sql:2: INFO: vacuuming 'public.test'...
CPU: USER: 663.45 s, system: 87.05 s, elapsed: 1505.04 s.
psql:test1.sql:2: INFO: analyzing 'public.test'
psql:test1.sql:2: INFO: 'test': scanned 30000 OF 735295 pages, containing 2040581 live ROWS AND 0 dead ROWS;
30000 ROWS IN sample, 50014300 estimated total ROWS
VACUUM
TIME: 1505238.738 ms (25:05.239)

2位工人（进程的形象描述）的vacuum清理工作：

=$ SET max_parallel_maintenance_workers = 2;
SET
=$ vacuum (verbose ON, analyze ON) test;
psql:test2.sql:3: INFO: vacuuming 'public.test'
psql:test2.sql:3: INFO: launched 2 parallel vacuum workers FOR INDEX vacuuming (planned: 2)...
CPU: USER: 119.29 s, system: 43.63 s, elapsed: 694.13 s.
psql:test2.sql:3: INFO: analyzing 'public.test'
psql:test2.sql:3: INFO: 'test': scanned 30000 OF 735295 pages, containing 2039828 live ROWS AND 0 dead ROWS;
30000 ROWS IN sample, 49995844 estimated total ROWS
VACUUM
TIME: 694336.035 ms (11:34.336)

当我选了8个工人时：

=$ SET max_parallel_maintenance_workers = 8;
SET
=$ vacuum (verbose ON, analyze ON) test;
psql:test3.sql:3: INFO: vacuuming 'public.test'
psql:test3.sql:3: INFO: launched 6 parallel vacuum workers FOR INDEX vacuuming (planned:6)
CPU: USER: 134.24 s, system: 51.37 s, elapsed: 776.12 s.
psql:test3.sql:3: INFO: analyzing 'public.test'
psql:test3.sql:3: INFO: 'test': scanned 30000 OF 735295 pages, containing 2040985 live ROWS AND 0 dead ROWS; 30000 ROWS IN sample, 50024202 estimated total ROWS
VACUUM
TIME: 776326.118 ms (12:56.326)