On Wed, Sep 7, 2016 at 4:11 PM, Simon Riggs <si...@2ndquadrant.com> wrote: > On 7 September 2016 at 04:13, Masahiko Sawada <sawada.m...@gmail.com> wrote: > >> Since current HEAD could scan visibility map twice, the execution time >> of Patched is approximately half of HEAD. > > Sounds good. > > To ensure we are doing exactly same amount of work as before, did you > see the output of VACUUM VEROBOSE?
Sorry, the previous test result I posted was something wrong. I rerun the performance test and results are, * 1TB Table(visibility map size is 32MB) HEAD : 4853.250 ms (00:04.853) Patched : 3805.789 ms (00:03.806) * 8TB Table(visibility map size is 257MB) HEAD : 37853.891 ms (00:37.854) Patched : 30153.943 ms (00:30.154) * 32TB Table(visibility map size is 1GB) HEAD: 151908.344 ms (02:31.908) Patched: 120560.037 ms (02:00.560) Since visibility map page can be cached onto shared buffer or OS cache by first scanning, the benefit of this patch seems not to be large. Here are outputs of VACUUM VERBOSE for 32TB table. * HEAD INFO: vacuuming "public.vm_skip_test" INFO: "vm_skip_test": found 0 removable, 0 nonremovable row versions in 0 out of 4294967294 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. Skipped 0 pages due to buffer pins. Skipped 4294967294 all-frozen pages according to visibility map. 0 pages are entirely empty. CPU 1.06s/148.11u sec elapsed 149.20 sec. VACUUM Time: 151908.344 ms (02:31.908) * Patched INFO: vacuuming "public.vm_skip_test" INFO: "vm_skip_test": found 0 removable, 0 nonremovable row versions in 0 out of 4294967294 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. Skipped 0 pages due to buffer pins. Skipped 4294967294 all-frozen pages according to visibility map. 0 pages are entirely empty. CPU 0.65s/117.15u sec elapsed 117.81 sec. VACUUM Time: 120560.037 ms (02:00.560) Current manual vacuum doesn't output how may all_frozen pages we skipped according to visibility map. That's why I attached 0001 patch which makes the manual vacuum emit such information. > > Can we produce a test that verifies the result patched/unpatched? > Attached test shell script but because I don't have such a large disk, I've measured performance benefit using by something like unofficial way. To make a situation where table is extremly large and make corresponding visibility map, I applied 0002 patch and made a fake visibility map. Attached 0002 patch adds GUC parameter cheat_vacuum_table_size which artificially defines table size being vacuumed . For example, If we do, SET cheat_vacuum_table_size = 4; VACUUM vm_test; then in lazy_scan_heap, vm_test table is processed as an 8TB(MaxBlockNumber / 4) table. Attached test shell script makes fake visibility map files and executes the performance tests for 1TB, 8TB and 32TB table. Please confirm it. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
From bbda8e4144101a41804865d88592a15bf0150340 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.m...@gmail.com>
Date: Thu, 8 Sep 2016 11:24:21 -0700
Subject: [PATCH 1/2] lazy_scan_heap outputs how many all_frozen pages are
skipped.
---
src/backend/commands/vacuumlazy.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index b5fb325..20d8334 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -1331,6 +1331,10 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
"Skipped %u pages due to buffer pins.\n",
vacrelstats->pinskipped_pages),
vacrelstats->pinskipped_pages);
+ appendStringInfo(&buf, ngettext("Skipped %u all-frozen page according to visibility map.\n",
+ "Skipped %u all-frozen pages according to visibility map.\n",
+ vacrelstats->frozenskipped_pages),
+ vacrelstats->frozenskipped_pages);
appendStringInfo(&buf, ngettext("%u page is entirely empty.\n",
"%u pages are entirely empty.\n",
empty_pages),
--
2.8.1
From 01e493a44ba6c5bb9b59a1016bfce4b9bcf75e95 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.m...@gmail.com>
Date: Thu, 8 Sep 2016 11:36:28 -0700
Subject: [PATCH 2/2] Add cheat_vacuum_table_size.
---
contrib/pg_visibility/pg_visibility.c | 6 +++++-
src/backend/commands/vacuumlazy.c | 9 +++++++--
src/backend/utils/misc/guc.c | 10 ++++++++++
src/include/commands/vacuum.h | 1 +
4 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/contrib/pg_visibility/pg_visibility.c b/contrib/pg_visibility/pg_visibility.c
index 7034066..37ace99 100644
--- a/contrib/pg_visibility/pg_visibility.c
+++ b/contrib/pg_visibility/pg_visibility.c
@@ -12,6 +12,7 @@
#include "access/visibilitymap.h"
#include "catalog/pg_type.h"
#include "catalog/storage_xlog.h"
+#include "commands/vacuum.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "storage/bufmgr.h"
@@ -242,7 +243,10 @@ pg_visibility_map_summary(PG_FUNCTION_ARGS)
bool nulls[2];
rel = relation_open(relid, AccessShareLock);
- nblocks = RelationGetNumberOfBlocks(rel);
+ if (cheat_vacuum_table_size != 0)
+ nblocks = MaxBlockNumber / cheat_vacuum_table_size;
+ else
+ nblocks = RelationGetNumberOfBlocks(rel);
for (blkno = 0; blkno < nblocks; ++blkno)
{
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 20d8334..7ea6d20 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -125,6 +125,7 @@ typedef struct LVRelStats
bool lock_waiter_detected;
} LVRelStats;
+int cheat_vacuum_table_size = 0;
/* A few variables that don't seem worth passing around as parameters */
static int elevel = -1;
@@ -486,7 +487,11 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
indstats = (IndexBulkDeleteResult **)
palloc0(nindexes * sizeof(IndexBulkDeleteResult *));
- nblocks = RelationGetNumberOfBlocks(onerel);
+ if (cheat_vacuum_table_size != 0)
+ nblocks = MaxBlockNumber / cheat_vacuum_table_size;
+ else
+ nblocks = RelationGetNumberOfBlocks(onerel);
+
vacrelstats->rel_pages = nblocks;
vacrelstats->scanned_pages = 0;
vacrelstats->nonempty_pages = 0;
@@ -593,7 +598,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
/* see note above about forcing scanning of last page */
#define FORCE_CHECK_PAGE() \
- (blkno == nblocks - 1 && should_attempt_truncation(vacrelstats))
+ (blkno == nblocks - 1 && should_attempt_truncation(vacrelstats) && cheat_vacuum_table_size == 0)
pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED, blkno);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index c5178f7..39c090c 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2811,6 +2811,16 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"cheat_vacuum_table_size", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Set table size being vacuumed."),
+ NULL,
+ },
+ &cheat_vacuum_table_size,
+ 0, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 80cd4a8..8e4812f 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -155,6 +155,7 @@ extern int vacuum_freeze_table_age;
extern int vacuum_multixact_freeze_min_age;
extern int vacuum_multixact_freeze_table_age;
+extern int cheat_vacuum_table_size;
/* in commands/vacuum.c */
extern void ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel);
--
2.8.1
test_vacuum_skip_with_large_vm.sh
Description: Bourne shell script
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
