From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: lubennikovaav@gmail.com
Cc: pgsql-hackers@lists.postgresql.org
Cc: pavan.deolasee@gmail.com
Cc: ibrar.ahmad@gmail.com
Subject: Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits
Date: Tue, 12 Jan 2021 20:30:35 +0100
Message-ID: <8669d452-d078-bdf9-5a62-6f1acd55f3c6@enterprisedb.com> (raw)
In-Reply-To: <93b767d6-4cde-7326-8de4-d71d81817c16@postgrespro.ru>
References: <CABOikdN-ptGv0mZntrK2Q8OtfUuAjqaYMGmkdU1dCKFtUxVLrg@mail.gmail.com>
<CALtqXTfpEOiyJchYQoUKw9pvEPhVgnkxL=JvKNj57BTxva8bTA@mail.gmail.com>
<160382617166.1204.475752573208579406.pgcf@coridan.postgresql.org>
<20201028.144653.966121790856478893.t-ishii@sraoss.co.jp>
<20201030004252.d63rwclys4bniluu@development>
<1d2e0f4a-6ab8-10f1-c177-af97475df001@postgrespro.ru>
<20201102144453.tumlk7qhw3gzn2dk@development>
<7e6a006f-c59a-93fc-3ddf-f9c661319bc8@2ndquadrant.com>
<75f3760f-1077-07c1-aea4-975d5c127d6d@postgrespro.ru>
<f6e0af44-b147-d27f-cc21-2404b4f11f0e@enterprisedb.com>
<93b767d6-4cde-7326-8de4-d71d81817c16@postgrespro.ru>
Thanks. These patches seem to resolve the TOAST table issue, freezing it
as expected. I think the code duplication is not an issue, but I wonder
why heap_insert uses this condition:
/*
* ...
*
* No need to update the visibilitymap if it had all_frozen bit set
* before this insertion.
*/
if (all_frozen_set && ((vmstatus & VISIBILITYMAP_ALL_FROZEN) == 0))
while heap_multi_insert only does this:
if (all_frozen_set) { ... }
I haven't looked at the details, but shouldn't both do the same thing?
I've done some benchmarks, comparing master and patched version on a
bunch of combinations (logged/unlogged, no before-insert trigger,
trigger filtering everything/nothing). On master, the results are:
group copy vacuum
-----------------------------------------------
logged / no trigger 4672 162
logged / trigger (all) 4914 162
logged / trigger (none) 1219 11
unlogged / no trigger 4132 156
unlogged / trigger (all) 4292 156
unlogged / trigger (none) 1275 11
and patched looks like this:
group copy vacuum
-----------------------------------------------
logged / no trigger 4669 12
logged / trigger (all) 4874 12
logged / trigger (none) 1233 11
unlogged / no trigger 4054 11
unlogged / trigger (all) 4185 12
unlogged / trigger (none) 1222 10
This looks pretty nice - there are no regressions, just speedups in the
vacuum step. The SQL script used is attached.
However, I've also repeated the test counting all-frozen pages in both
the main table and TOAST table, and I get this:
master
======
select count(*) from pg_visibility((select reltoastrelid from pg_class
where relname = 't'));
count
--------
100000
(1 row)
select count(*) from pg_visibility((select reltoastrelid from pg_class
where relname = 't')) where not all_visible;
count
--------
100000
(1 row)
patched
=======
select count(*) from pg_visibility((select reltoastrelid from pg_class
where relname = 't'));
count
--------
100002
(1 row)
select count(*) from pg_visibility((select reltoastrelid from pg_class
where relname = 't')) where not all_visible;
count
--------
0
(1 row)
That is - all TOAST pages are frozen (as expected, which is good). But
now there are 100002 pages, not just 100000 pages. That is, we're now
creating 2 extra pages, for some reason. I recall Pavan reported similar
issue with every 32768-th page not being properly filled, but I'm not
sure if that's the same issue.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
test.sql (application/sql, 776B) download
bench.sql (application/sql, 11.0K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@enterprisedb.com, a.lubennikova@postgrespro.ru, tomas.vondra@2ndquadrant.com, ishii@sraoss.co.jp, lubennikovaav@gmail.com, pgsql-hackers@lists.postgresql.org, pavan.deolasee@gmail.com, ibrar.ahmad@gmail.com
Subject: Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits
In-Reply-To: <8669d452-d078-bdf9-5a62-6f1acd55f3c6@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox