From 40d964ec997f64227bc0ff5e058dc4a5770a70a9 Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Mon, 20 Jan 2020 07:57:49 +0530 Subject: Allow vacuum command to process indexes in parallel. This feature allows the vacuum to leverage multiple CPUs in order to process indexes. This enables us to perform index vacuuming and index cleanup with background workers. This adds a PARALLEL option to VACUUM command where the user can specify the number of workers that can be used to perform the command which is limited by the number of indexes on a table. Specifying zero as a number of workers will disable parallelism. This option can't be used with the FULL option. Each index is processed by at most one vacuum process. Therefore parallel vacuum can be used when the table has at least two indexes. The parallel degree is either specified by the user or determined based on the number of indexes that the table has, and further limited by max_parallel_maintenance_workers. The index can participate in parallel vacuum iff it's size is greater than min_parallel_index_scan_size. Author: Masahiko Sawada and Amit Kapila Reviewed-by: Dilip Kumar, Amit Kapila, Robert Haas, Tomas Vondra, Mahendra Singh and Sergei Kornilov Tested-by: Mahendra Singh and Prabhat Sahu Discussion: https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com --- doc/src/sgml/config.sgml | 18 +++++++------ doc/src/sgml/ref/vacuum.sgml | 61 +++++++++++++++++++++++++++++++++++++++----- 2 files changed, 65 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5d45b6f7cba..3ccacd528b4 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2308,13 +2308,13 @@ include_dir 'conf.d' Sets the maximum number of parallel workers that can be - started by a single utility command. Currently, the only - parallel utility command that supports the use of parallel - workers is CREATE INDEX, and only when - building a B-tree index. Parallel workers are taken from the - pool of processes established by , limited by . Note that the requested + started by a single utility command. Currently, the parallel + utility commands that support the use of parallel workers are + CREATE INDEX only when building a B-tree index, + and VACUUM without FULL + option. Parallel workers are taken from the pool of processes + established by , limited + by . Note that the requested number of workers may not actually be available at run time. If this occurs, the utility operation will run with fewer workers than expected. The default value is 2. Setting this @@ -4915,7 +4915,9 @@ ANY num_sync ( . If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 512 kilobytes (512kB). diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index f9b0fb87945..846056a353d 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -34,6 +34,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ boolean ] INDEX_CLEANUP [ boolean ] TRUNCATE [ boolean ] + PARALLEL integer and table_and_columns is: @@ -75,10 +76,14 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ + + PARALLEL + + + Perform vacuum index and cleanup index phases of VACUUM + in parallel using integer + background workers (for the detail of each vacuum phases, please + refer to ). If the + PARALLEL option is omitted, then + VACUUM decides the number of workers based on number + of indexes that support parallel vacuum operation on the relation which + is further limited by . + The index can participate in a parallel vacuum if and only if the size + of the index is more than . + Please note that it is not guaranteed that the number of parallel workers + specified in integer will + be used during execution. It is possible for a vacuum to run with fewer + workers than specified, or even with no workers at all. Only one worker + can be used per index. So parallel workers are launched only when there + are at least 2 indexes in the table. Workers for + vacuum launches before starting each phase and exit at the end of + the phase. These behaviors might change in a future release. This + option can't be used with the FULL option. + + + + boolean @@ -237,6 +269,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ integer + + + Specifies a non-negative integer value passed to the selected option. + + + + table_name @@ -316,11 +357,19 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ for details. + it is sometimes advisable to use the cost-based vacuum delay feature. For + parallel vacuum, each worker sleeps proportional to the work done by that + worker. See for + details. -- cgit v1.2.3