|From:||Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>|
|To:||Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com>|
|Cc:||Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Daniel Gustafsson <daniel(at)yesql(dot)se>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>|
|Subject:||Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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 <mailto:a(dot)lubennikova(at)postgrespro(dot)ru>>
> 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
> >> decrease.
> > "Slightly"? It seems quite a large performance drop to me --
> more than
> > 10%. Where is that time being spent? Andres said in  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?
> > 
> I agree that 10% performance drop is not what we expect with this
> Ibrar, can you share more info about your tests? I'd like to
> this slowdown and fix it, if necessary.
> Here is my test;
> postgres=# 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 .
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.
COPY: 12327,090 ms vacuum: 37,555 ms
COPY: 12939,540 ms vacuum: 35,703 ms
COPY: 12245,819 ms vacuum: 36,273 ms
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
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Companyt
|Next Message||Jim Nasby||2020-08-26 21:22:51||Typo in procarray.c comment about GlobalVisDataRels|
|Previous Message||Bossart, Nathan||2020-08-26 21:12:36||Re: XMAX_LOCK_ONLY and XMAX_COMMITTED (fk/multixact code)|