Horton

Thread

Commits

  1. Use COPY FREEZE in pgbench for faster benchmark table population.

  2. Revert most of 39b66a91bd

  3. Set PD_ALL_VISIBLE and visibility map bits in COPY FREEZE

  1. Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-08T05:39:07Z

    Currently pgbench uses plain COPY to populate pgbench_accounts
table. With adding FREEZE option to COPY, the time to perform "pgbench
-i" will be significantly reduced.

Curent master:
pgbench -i -s 100
:
:
done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).

Using FREEZE:
done in 16.86 s (drop tables 0.20 s, create tables 0.01 s, client-side generate 11.86 s, vacuum 0.25 s, primary keys 4.53 s).

As you can see total time drops from 70.78 seconds to 16.86 seconds,
that is 4.1 times faster. This is mainly because vacuum takes only
0.25 seconds after COPY FREEZE while unpatched pgbench takes 51.11
seconds, which is 204 times slower.

Thanks for the COPY FREEZE patch recently committed:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2

Attached is one line patch for this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  2. Re: Using COPY FREEZE in pgbench

    Laurenz Albe <laurenz.albe@cybertec.at> — 2021-03-08T10:19:55Z

    On Mon, 2021-03-08 at 14:39 +0900, Tatsuo Ishii wrote:
> Currently pgbench uses plain COPY to populate pgbench_accounts
> table. With adding FREEZE option to COPY, the time to perform "pgbench
> -i" will be significantly reduced.
> 
> Curent master:
> pgbench -i -s 100
> :
> :
> done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).
> 
> Using FREEZE:
> done in 16.86 s (drop tables 0.20 s, create tables 0.01 s, client-side generate 11.86 s, vacuum 0.25 s, primary keys 4.53 s).
> 
> As you can see total time drops from 70.78 seconds to 16.86 seconds,
> that is 4.1 times faster. This is mainly because vacuum takes only
> 0.25 seconds after COPY FREEZE while unpatched pgbench takes 51.11
> seconds, which is 204 times slower.
> 
> Thanks for the COPY FREEZE patch recently committed:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2
> 
> Attached is one line patch for this.

That is indeed low hanging fruit and an improvement.

> -	res = PQexec(con, "copy pgbench_accounts from stdin");
> +	res = PQexec(con, "copy pgbench_accounts from stdin freeze");

I think it would be better to use the official syntax and put the "freeze"
in parentheses.  Perhaps the old syntax will be desupported some day.

Yours,
Laurenz Albe

  3. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-08T10:32:42Z

    Hello Tatsuo-san,

> Currently pgbench uses plain COPY to populate pgbench_accounts
> table. With adding FREEZE option to COPY, the time to perform "pgbench
> -i" will be significantly reduced.
>
> Curent master:
> pgbench -i -s 100
> done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).
>
> Using FREEZE:
> done in 16.86 s (drop tables 0.20 s, create tables 0.01 s, client-side generate 11.86 s, vacuum 0.25 s, primary keys 4.53 s).

That looks good!

As COPY FREEZE was introduced in 9.3, this means that loading data would 
break with previous versions. Pgbench attempts at being compatible with 
older versions. I'm wondering whether we should not care or if we should 
attempt some compatibility layer. It seems enough to test 
"PQserverVersion() >= 90300"?

-- 
Fabien.

  4. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-08T11:22:21Z

    Hi Fabien,

> That looks good!
> 
> As COPY FREEZE was introduced in 9.3, this means that loading data
> would break with previous versions. Pgbench attempts at being
> compatible with older versions. I'm wondering whether we should not
> care or if we should attempt some compatibility layer. It seems enough
> to test "PQserverVersion() >= 90300"?

Good point.

Unfortunately with pre-14 COPY FREEZE we cannot get the speed up
effect because it requires the commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2
which was there only in the master branch as of Jan 17, 2021.

So I think adding "freeze" to the copy statement should only happen in
PostgreSQL 14 or later.  Probably the test should be
"PQserverVersion() >= 140000" I think. Attached is the patch doing
what you suggest.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  5. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-08T11:23:07Z

    >> -	res = PQexec(con, "copy pgbench_accounts from stdin");
>> +	res = PQexec(con, "copy pgbench_accounts from stdin freeze");
> 
> I think it would be better to use the official syntax and put the "freeze"
> in parentheses.  Perhaps the old syntax will be desupported some day.

Agreed.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  6. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-08T11:52:41Z

    > Hi Fabien,
> 
>> That looks good!
>> 
>> As COPY FREEZE was introduced in 9.3, this means that loading data
>> would break with previous versions. Pgbench attempts at being
>> compatible with older versions. I'm wondering whether we should not
>> care or if we should attempt some compatibility layer. It seems enough
>> to test "PQserverVersion() >= 90300"?
> 
> Good point.
> 
> Unfortunately with pre-14 COPY FREEZE we cannot get the speed up
> effect because it requires the commit:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2
> which was there only in the master branch as of Jan 17, 2021.
> 
> So I think adding "freeze" to the copy statement should only happen in
> PostgreSQL 14 or later.  Probably the test should be
> "PQserverVersion() >= 140000" I think. Attached is the patch doing
> what you suggest.

I have created a CommitFest entry for this.
https://commitfest.postgresql.org/33/3034/

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  7. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-13T21:16:47Z

    Hello Tatsuo-san,

>> So I think adding "freeze" to the copy statement should only happen in
>> PostgreSQL 14 or later.  Probably the test should be
>> "PQserverVersion() >= 140000" I think. Attached is the patch doing
>> what you suggest.
>
> I have created a CommitFest entry for this.
> https://commitfest.postgresql.org/33/3034/

My 0.02 €

After giving it some thought, ISTM that there could also be a performance 
improvement with copy freeze from older version, so I'd suggest to add it 
after 9.3 where the option was added, i.e. 90300.

Also, I do not think it is worth to fail on a 0 pqserverversion, just keep 
the number and consider it a very old version?

Question: should there be a word about copy using the freeze option? I'd 
say yes, in the section describing "g".

-- 
Fabien.

  8. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-14T00:22:21Z

    >> I have created a CommitFest entry for this.
>> https://commitfest.postgresql.org/33/3034/
> 
> My 0.02 €
> 
> After giving it some thought, ISTM that there could also be a
> performance improvement with copy freeze from older version, so I'd
> suggest to add it after 9.3 where the option was added, i.e. 90300.

You misunderstand about COPY FREEZE. Pre-13 COPY FREEZE does not
contribute a performance improvement. See discussions for more details.
https://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ@mail.gmail.com
https://www.postgresql.org/message-id/flat/CABOikdN-ptGv0mZntrK2Q8OtfUuAjqaYMGmkdU1dCKFtUxVLrg%40mail.gmail.com

> Also, I do not think it is worth to fail on a 0 pqserverversion, just
> keep the number and consider it a very old version?

If pqserverversion fails, then following PQ calls are likely fail
too. What's a benefit to continue after pqserverversion returns 0?

> Question: should there be a word about copy using the freeze option?
> I'd say yes, in the section describing "g".

Can you elaborate about "section describing "g"? I am not sure what
you mean.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  9. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-14T00:28:34Z

    >> After giving it some thought, ISTM that there could also be a
>> performance improvement with copy freeze from older version, so I'd
>> suggest to add it after 9.3 where the option was added, i.e. 90300.
> 
> You misunderstand about COPY FREEZE. Pre-13 COPY FREEZE does not

Oops. I meant Pre-14, not pre-13.

> contribute a performance improvement. See discussions for more details.
> https://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ@mail.gmail.com
> https://www.postgresql.org/message-id/flat/CABOikdN-ptGv0mZntrK2Q8OtfUuAjqaYMGmkdU1dCKFtUxVLrg%40mail.gmail.com
> 
>> Also, I do not think it is worth to fail on a 0 pqserverversion, just
>> keep the number and consider it a very old version?
> 
> If pqserverversion fails, then following PQ calls are likely fail
> too. What's a benefit to continue after pqserverversion returns 0?
> 
>> Question: should there be a word about copy using the freeze option?
>> I'd say yes, in the section describing "g".
> 
> Can you elaborate about "section describing "g"? I am not sure what
> you mean.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>

  10. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-14T08:48:22Z

    Hello,

>> After giving it some thought, ISTM that there could also be a
>> performance improvement with copy freeze from older version, so I'd
>> suggest to add it after 9.3 where the option was added, i.e. 90300.
>
> You misunderstand about COPY FREEZE. Pre-13 COPY FREEZE does not
> contribute a performance improvement. See discussions for more details.
> https://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ@mail.gmail.com
> https://www.postgresql.org/message-id/flat/CABOikdN-ptGv0mZntrK2Q8OtfUuAjqaYMGmkdU1dCKFtUxVLrg%40mail.gmail.com

Ok. ISTM that the option should be triggered as soon as it is available, 
but you do as you wish.

>> Also, I do not think it is worth to fail on a 0 pqserverversion, just
>> keep the number and consider it a very old version?
>
> If pqserverversion fails, then following PQ calls are likely fail
> too. What's a benefit to continue after pqserverversion returns 0?

I'm unsure how this could happen ever. The benefit of not caring is less 
lines of codes in pgbench and a later call will catch the issue anyway, if 
libpq is corrupted.

>> Question: should there be a word about copy using the freeze option?
>> I'd say yes, in the section describing "g".
>
> Can you elaborate about "section describing "g"? I am not sure what
> you mean.

The "g" item in the section describing initialization steps (i.e. option 
-I). I'd suggest just to replace "COPY" with "COPY FREEZE" in the 
sentence.

-- 
Fabien.

  11. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-14T12:14:49Z

    > Ok. ISTM that the option should be triggered as soon as it is
> available, but you do as you wish.

Can you elaborate why you think that using COPY FREEZE before 14 is
beneficial? Or do you want to standardize to use COPY FREEZE?

> I'm unsure how this could happen ever. The benefit of not caring is
> less lines of codes in pgbench and a later call will catch the issue
> anyway, if libpq is corrupted.

I have looked in the code of PQprotocolVersion. The only case in which
it returns 0 is there's no connection. Yes, you are right. Once the
connection has been successfuly established, there's no chance it
fails. So I agree with you.

>>> Question: should there be a word about copy using the freeze option?
>>> I'd say yes, in the section describing "g".
>>
>> Can you elaborate about "section describing "g"? I am not sure what
>> you mean.
> 
> The "g" item in the section describing initialization steps
> (i.e. option -I). I'd suggest just to replace "COPY" with "COPY
> FREEZE" in the sentence.

Ok. The section is needed to be modified.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  12. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-19T04:53:34Z

    > I have looked in the code of PQprotocolVersion. The only case in which
> it returns 0 is there's no connection. Yes, you are right. Once the
> connection has been successfuly established, there's no chance it
> fails. So I agree with you.

Attached v3 patch addresses this.

>> The "g" item in the section describing initialization steps
>> (i.e. option -I). I'd suggest just to replace "COPY" with "COPY
>> FREEZE" in the sentence.
> 
> Ok. The section is needed to be modified.

This is also addressed in the patch.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  13. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-20T13:35:00Z

    Hello Tatsuo-san,

>> I have looked in the code of PQprotocolVersion. The only case in which
>> it returns 0 is there's no connection. Yes, you are right. Once the
>> connection has been successfuly established, there's no chance it
>> fails. So I agree with you.
>
> Attached v3 patch addresses this.
>
>>> The "g" item in the section describing initialization steps
>>> (i.e. option -I). I'd suggest just to replace "COPY" with "COPY
>>> FREEZE" in the sentence.
>>
>> Ok. The section is needed to be modified.
>
> This is also addressed in the patch.

V3 works for me and looks ok. I changed it to ready in the CF app.

-- 
Fabien.

  14. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-20T23:26:48Z

    Hi Fabien,

> Hello Tatsuo-san,
> 
>>> I have looked in the code of PQprotocolVersion. The only case in which
>>> it returns 0 is there's no connection. Yes, you are right. Once the
>>> connection has been successfuly established, there's no chance it
>>> fails. So I agree with you.
>>
>> Attached v3 patch addresses this.
>>
>>>> The "g" item in the section describing initialization steps
>>>> (i.e. option -I). I'd suggest just to replace "COPY" with "COPY
>>>> FREEZE" in the sentence.
>>>
>>> Ok. The section is needed to be modified.
>>
>> This is also addressed in the patch.
> 
> V3 works for me and looks ok. I changed it to ready in the CF app.

Thank you for your review!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  15. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-21T06:10:15Z

    >> V3 works for me and looks ok. I changed it to ready in the CF app.
> 
> Thank you for your review!

Unfortunately it seems cfbot is not happy with the patch.

#   Failed test 'pgbench scale 1 initialization status (got 1 vs expected 0)'
#   at t/001_pgbench_with_server.pl line 116.
#   Failed test 'pgbench scale 1 initialization stderr /(?^:creating foreign keys)/'
#   at t/001_pgbench_with_server.pl line 116.
#                   'dropping old tables...
# creating tables...
# creating 2 partitions...
# creating primary keys...
# vacuuming...
# generating data (client-side)...
# 100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
# ERROR:  cannot perform COPY FREEZE on a partitioned table
# pgbench: fatal: PQendcopy failed

I think pgbench needs to check whether partitioning option is
specified or not. If specified, pgbench should not use COPY
FREEZE. Attached v4 patch does this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  16. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-21T08:49:43Z

    >>> V3 works for me and looks ok. I changed it to ready in the CF app.
>>
>> Thank you for your review!
>
> Unfortunately it seems cfbot is not happy with the patch.

Argh. Indeed, I did not thought of testing on a partitioned table:-( ISTM 
I did "make check" in pgbench to trigger tap tests, but possibly it was in 
a dream:-(

The feature is a little disappointing because if someone has partition 
tables then probably they have a lot of data and probably they would like 
freeze to work there. Maybe freeze would work on table partitions 
themselves, but I do not think it is worth the effort to do that.

About v4 there is a typo in the doc "portioning":

      <command>pgbench</command> uses FREEZE option with 14 or later
      version of <productname>PostgreSQL</productname> to speed up
      subsequent <command>VACUUM</command> if portioning option is not
      specified.

I'd suggest:

      <command>pgbench</command> uses the FREEZE option with 14 or later
      version of <productname>PostgreSQL</productname> to speed up
      subsequent <command>VACUUM</command>, unless partitions are enabled.

-- 
Fabien.

  17. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-21T09:30:59Z

    > The feature is a little disappointing because if someone has partition
> tables then probably they have a lot of data and probably they would
> like freeze to work there. Maybe freeze would work on table partitions
> themselves, but I do not think it is worth the effort to do that.

Agreed.

> About v4 there is a typo in the doc "portioning":
> 
>      <command>pgbench</command> uses FREEZE option with 14 or later
>      version of <productname>PostgreSQL</productname> to speed up
>      subsequent <command>VACUUM</command> if portioning option is not
>      specified.
> 
> I'd suggest:
> 
>      <command>pgbench</command> uses the FREEZE option with 14 or later
>      version of <productname>PostgreSQL</productname> to speed up
>      subsequent <command>VACUUM</command>, unless partitions are enabled.

Thanks for pointing it out. Also I think that in "with 14 or later
version", "version" should be "versions".

Attached is the v5 patch.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  18. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-21T16:30:57Z

    > Attached is the v5 patch.

About v5: doc gen ok, global and local make check ok.

I did a few tests on my laptop. Is seems that copying takes a little more 
time, say about 10%, but vacuum is indeed very significantly reduced, so 
that the total time for copying and vacuuming is reduced by 10% on 
overall.

So it is okay for me.

-- 
Fabien.

  19. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-03-22T00:22:54Z

    > I did a few tests on my laptop. Is seems that copying takes a little
> more time, say about 10%, but vacuum is indeed very significantly
> reduced, so that the total time for copying and vacuuming is reduced
> by 10% on overall.
> 
> So it is okay for me.

Thanks for the test.

I wrote:
> Curent master:
> pgbench -i -s 100
> :
> :
> done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).
> 
> Using FREEZE:
> done in 16.86 s (drop tables 0.20 s, create tables 0.01 s, client-side generate 11.86 s, vacuum 0.25 s, primary keys 4.53 s).
> 
> As you can see total time drops from 70.78 seconds to 16.86 seconds,
> that is 4.1 times faster. This is mainly because vacuum takes only
> 0.25 seconds after COPY FREEZE while unpatched pgbench takes 51.11
> seconds, which is 204 times slower.

I did same test again.

13.2 pgbench + master branch server:
done in 15.47 s (drop tables 0.19 s, create tables 0.01 s, client-side generate 9.07 s, vacuum 2.07 s, primary keys 4.13 s).

With patch on master branch:
done in 13.38 s (drop tables 0.19 s, create tables 0.01 s, client-side generate 9.68 s, vacuum 0.23 s, primary keys 3.27 s).

This time current pgbench performs much faster than I wrote (15.47 s
vs. 70.78 s). I don't why.

Anyway, this time total pgbench time is reduced by 14% over all
here. I hope people agree that the patch is worth the gain.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  20. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-03-22T07:47:42Z

    Hello Tatsuo-san,

> 13.2 pgbench + master branch server:
> done in 15.47 s (drop tables 0.19 s, create tables 0.01 s, client-side generate 9.07 s, vacuum 2.07 s, primary keys 4.13 s).
>
> With patch on master branch:
> done in 13.38 s (drop tables 0.19 s, create tables 0.01 s, client-side generate 9.68 s, vacuum 0.23 s, primary keys 3.27 s).

Yes, this is the kind of figures I got on my laptop.

> This time current pgbench performs much faster than I wrote (15.47 s vs. 
> 70.78 s). I don't why.

Duno.

> Anyway, this time total pgbench time is reduced by 14% over all
> here. I hope people agree that the patch is worth the gain.

Yes, because (1) why not take +10% and (2) it exercises an option.

-- 
Fabien.

  21. Re: Using COPY FREEZE in pgbench

    Peter Geoghegan <pg@bowt.ie> — 2021-04-02T22:55:54Z

    On Sun, Mar 21, 2021 at 5:23 PM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> Anyway, this time total pgbench time is reduced by 14% over all
> here. I hope people agree that the patch is worth the gain.

Most of the time when I run pgbench I use my own shell script, which does this:

PGOPTIONS='-c vacuum_freeze_min_age=0 -c wal_compression=off' pgbench
-i -s $SCALE

Have you considered this case? In other words, have you considered the
benefits of this patch for users that currently deliberately force
freezing by VACUUM, just because it matters to their benchmark?

(BTW you might be surprised how much wal_compression=off matters here.)

-- 
Peter Geoghegan

  22. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-04-02T23:58:41Z

    > Most of the time when I run pgbench I use my own shell script, which does this:
> 
> PGOPTIONS='-c vacuum_freeze_min_age=0 -c wal_compression=off' pgbench
> -i -s $SCALE
> 
> Have you considered this case? In other words, have you considered the
> benefits of this patch for users that currently deliberately force
> freezing by VACUUM, just because it matters to their benchmark?

I am not sure how many people use this kind of options while running
pgbench -i but we could add yet another switch to fall back to none
FREEZE COPY if you want.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  23. Re: Using COPY FREEZE in pgbench

    Peter Geoghegan <pg@bowt.ie> — 2021-04-03T01:03:59Z

    On Fri, Apr 2, 2021 at 4:58 PM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> I am not sure how many people use this kind of options while running
> pgbench -i but we could add yet another switch to fall back to none
> FREEZE COPY if you want.

I was unclear. What I meant was that your patch isn't just useful
because it speeds up "pgbench -i" for everybody. It's also useful
because having all of the tuples already frozen after bulk loading
seems like a good benchmarking practice, at least most of the time.

The patch changes the initial state of the database with "pgbench -i",
I think. But that's good.

-- 
Peter Geoghegan

  24. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-04-03T01:34:42Z

    > I was unclear. What I meant was that your patch isn't just useful
> because it speeds up "pgbench -i" for everybody. It's also useful
> because having all of the tuples already frozen after bulk loading
> seems like a good benchmarking practice, at least most of the time.
> 
> The patch changes the initial state of the database with "pgbench -i",
> I think. But that's good.

Oh, ok. Thanks for the explanation!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  25. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-07-04T02:11:36Z

    After this commit:
https://git.postgresql.org/pg/commitdiff/8e03eb92e9ad54e2f1ed8b5a73617601f6262f81
I was worried about that the benefit of COPY FREEZE patch is somewhat
reduced or gone.  So I ran a pgbench test again.

Current master:

$ pgbench -i -s 100 test
:
:
done in 20.23 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 13.54 s, vacuum 2.34 s, primary keys 4.33 s).

With v5 patch:
done in 16.92 s (drop tables 0.21 s, create tables 0.01 s, client-side generate 12.68 s, vacuum 0.24 s, primary keys 3.77 s).

So overall gain by the patch is around 15%, whereas the last test
before the commit was 14%.  It seems the patch is still beneficial
after the commit.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  26. Re: Using COPY FREEZE in pgbench

    Fabien COELHO <coelho@cri.ensmp.fr> — 2021-07-04T05:22:12Z

    Hello Tatsuo-san,

> So overall gain by the patch is around 15%, whereas the last test before 
> the commit was 14%.  It seems the patch is still beneficial after the 
> commit.

Yes, that's good!

I had a quick look again, and about the comment:

  /*
   * If partitioning is not enabled and server version is 14.0 or later, we
   * can take account of freeze option of copy.
   */

I'd suggest instead the shorter:

  /* use COPY with FREEZE on v14 and later without partioning */

Or maybe even to fully drop the comment, because the code is clear enough 
and the doc already says it.

-- 
Fabien.

  27. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-07-04T08:31:56Z

    Hi fabien,

>> So overall gain by the patch is around 15%, whereas the last test
>> before the commit was 14%.  It seems the patch is still beneficial
>> after the commit.
> 
> Yes, that's good!

Yeah!

> I had a quick look again, and about the comment:
> 
>  /*
>   * If partitioning is not enabled and server version is 14.0 or later, we
>   * can take account of freeze option of copy.
>   */
> 
> I'd suggest instead the shorter:
> 
>  /* use COPY with FREEZE on v14 and later without partioning */
> 
> Or maybe even to fully drop the comment, because the code is clear
> enough and the doc already says it.

I'd prefer to leave a comment. People (including me) tend to forget
things in the future, that are obvious now:-)

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  28. Re: Using COPY FREEZE in pgbench

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2021-07-06T20:10:02Z

    On Sun, 4 Jul 2021 at 09:32, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>
> >> So overall gain by the patch is around 15%, whereas the last test
> >> before the commit was 14%.  It seems the patch is still beneficial
> >> after the commit.
> >
> > Yes, that's good!
>
> Yeah!
>

I tested this with -s100 and got similar results, but with -s1000 it
was more like 1.5x faster:

master:
done in 111.33 s (drop tables 0.00 s, create tables 0.01 s,
client-side generate 52.45 s, vacuum 32.30 s, primary keys 26.58 s)

patch:
done in 74.04 s (drop tables 0.46 s, create tables 0.04 s, client-side
generate 51.81 s, vacuum 2.11 s, primary keys 19.63 s)

Nice!

Regards,
Dean

  29. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-08-30T05:11:43Z

    > I tested this with -s100 and got similar results, but with -s1000 it
> was more like 1.5x faster:
> 
> master:
> done in 111.33 s (drop tables 0.00 s, create tables 0.01 s,
> client-side generate 52.45 s, vacuum 32.30 s, primary keys 26.58 s)
> 
> patch:
> done in 74.04 s (drop tables 0.46 s, create tables 0.04 s, client-side
> generate 51.81 s, vacuum 2.11 s, primary keys 19.63 s)
> 
> Nice!
> 
> Regards,
> Dean

If there's no objection, I am going to commit/push to master branch in
early September.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  30. Re: Using COPY FREEZE in pgbench

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2021-09-02T01:56:57Z

    >> I tested this with -s100 and got similar results, but with -s1000 it
>> was more like 1.5x faster:
>> 
>> master:
>> done in 111.33 s (drop tables 0.00 s, create tables 0.01 s,
>> client-side generate 52.45 s, vacuum 32.30 s, primary keys 26.58 s)
>> 
>> patch:
>> done in 74.04 s (drop tables 0.46 s, create tables 0.04 s, client-side
>> generate 51.81 s, vacuum 2.11 s, primary keys 19.63 s)
>> 
>> Nice!
>> 
>> Regards,
>> Dean
> 
> If there's no objection, I am going to commit/push to master branch in
> early September.

I have pushed the patch to master branch.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=06ba4a63b85e5aa47b325c3235c16c05a0b58b96

Thank you for those who gave me the valuable reviews!
Reviewed-by: Fabien COELHO, Laurenz Albe, Peter Geoghegan, Dean Rasheed
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp