Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

On Thu, Aug 27, 2020 at 2:14 AM Anastasia Lubennikova <
a(dot)lubennikova(at)postgrespro(dot)ru> wrote:

> On 21.08.2020 19:43, Ibrar Ahmed wrote:
>
>
>
> On Wed, Aug 19, 2020 at 6:15 PM Anastasia Lubennikova <
> a(dot)lubennikova(at)postgrespro(dot)ru> wrote:
>
>> On 18.08.2020 02:54, Alvaro Herrera wrote:
>> > On 2020-Aug-14, Ibrar Ahmed wrote:
>> >
>> >> The table used for the test contains three columns (integer, text,
>> >> varchar).
>> >> The total number of rows is 10000000 in total.
>> >>
>> >> Unpatched (Master: 92c12e46d5f1e25fc85608a6d6a19b8f5ea02600)
>> >> COPY: 9069.432 ms vacuum; 2567.961ms
>> >> COPY: 9004.533 ms vacuum: 2553.075ms
>> >> COPY: 8832.422 ms vacuum: 2540.742ms
>> >>
>> >> Patched (Master: 92c12e46d5f1e25fc85608a6d6a19b8f5ea02600)
>> >> COPY: 10031.723 ms vacuum: 127.524 ms
>> >> COPY: 9985.109 ms vacuum: 39.953 ms
>> >> COPY: 9283.373 ms vacuum: 37.137 ms
>> >>
>> >> Time to take the copy slightly increased but the vacuum time
>> significantly
>> >> decrease.
>> > "Slightly"? It seems quite a large performance drop to me -- more than
>> > 10%. Where is that time being spent? Andres said in [1] that he
>> > thought the performance shouldn't be affected noticeably, but this
>> > doesn't seem to hold true. As I understand, the idea was that there
>> > would be little or no additional WAL records .. only flags in the
>> > existing record. So what is happening?
>> >
>> > [1]
>> https://postgr.es/m/20190408010427.4l63qr7h2fjcyp77@alap3.anarazel.de
>>
>> I agree that 10% performance drop is not what we expect with this patch.
>> Ibrar, can you share more info about your tests? I'd like to reproduce
>> this slowdown and fix it, if necessary.
>>
>>
> Here is my test;
>
>
> postgres=# BEGIN;
>
> BEGIN
>
>
> postgres=*# TRUNCATE foo;
>
> TRUNCATE TABLE
>
>
> postgres=*# COPY foo(id, name, address) FROM '/home/ibrar/bar.csv'
> DELIMITER ',' FREEZE;
>
> COPY 10000000
>
>
>
> --
> Ibrar Ahmed
>
>
> I've repeated the test and didn't notice any slowdown for COPY FREEZE.
> Test data is here [1].
>
> The numbers do fluctuate a bit, but there is no dramatic difference
> between master and patched version. So I assume that the performance drop
> in your test has something to do with the measurement error. Unless, you
> have some non-default configuration that could affect it.
>
> patched:
>
> COPY: 12327,090 ms vacuum: 37,555 ms
> COPY: 12939,540 ms vacuum: 35,703 ms
> COPY: 12245,819 ms vacuum: 36,273 ms
>
> master:
> COPY
> COPY: 13253,605 ms vacuum: 3592,849 ms
> COPY: 12619,428 ms vacuum: 4253,836 ms
> COPY: 12512,940 ms vacuum: 4009,847 ms
>
> I also slightly cleaned up comments, so the new version of the patch is
> attached. As this is just a performance optimization documentation is not
> needed. It would be great, if other reviewers could run some independent
> performance tests, as I believe that this patch is ready for committer.
>
> [1] https://drive.google.com/file/d/11r19NX6yyPjvxdDub8Ce-kmApRurp4Nx/view
>
> --
> Anastasia Lubennikova
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Companyt
>
> I gave another try with latest v3 patch on latest master branch
(ff60394a8c9a7af8b32de420ccb54a20a0f019c1) with all default settings.
11824.495 is median with master and 11884.089 is median value with patch.

Note: There are two changes such as (1) used the v3 patch (2) now test is
done on latest master (ff60394a8c9a7af8b32de420ccb54a20a0f019c1).

Master (ff60394a8c9a7af8b32de420ccb54a20a0f019c1)

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 11824.495 ms (00:11.824)

postgres=*# COMMIT;

Restart

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 14096.987 ms (00:14.097)

postgres=*# commit;

Restart

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 11108.289 ms (00:11.108)

postgres=*# commit;

Patched (ff60394a8c9a7af8b32de420ccb54a20a0f019c1)

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 10749.945 ms (00:10.750)

postgres=*# commit;

Restart

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 14274.361 ms (00:14.274)

postgres=*# commit;

Restart

postgres=# \timing

postgres=# BEGIN;

postgres=*# TRUNCATE foo;

postgres=*# COPY foo(id, name, address) FROM '/Users/ibrar/bar.csv'
DELIMITER ',' FREEZE;

Time: 11884.089 ms (00:11.884)

postgres=*# commit;

--
Ibrar Ahmed