>> 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
> 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,
On Sun, 4 Jul 2021 at 09:32, Tatsuo Ishii 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
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
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
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
> 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
On Fri, Apr 2, 2021 at 4:58 PM Tatsuo Ishii 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
> 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
On Sun, Mar 21, 2021 at 5:23 PM Tatsuo Ishii 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
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
> 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
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
> 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.
>
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
>> 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
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
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
> 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
> 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
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
>> 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
>> 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
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() >= 14" I think. Attached is the patch doing
what you suggest.
I have created a CommitFest entry for this.
> 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
>> -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.
--
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
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
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
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,
30 matches
Mail list logo