From: | Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> |
---|---|
To: | Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru> |
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 |
Date: | 2020-08-21 16:43:46 |
Message-ID: | CALtqXTeEpfYwwAAtbW4RkZLNQoCJKfvhCF6syKGTmc5VEpuh8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
>
> I've run some tests on my laptop and COPY FREEZE shows the same time for
> both versions, while VACUUM is much faster on the patched version. I've
> also checked WAL generation and it shows that the patch works correctly
> as it doesn't add any records for COPY.
>
> Not patched:
>
> Time: 54883,356 ms (00:54,883)
> Time: 65373,333 ms (01:05,373)
> Time: 64684,592 ms (01:04,685)
> VACUUM Time: 60861,670 ms (01:00,862)
>
> COPY wal_bytes 3765 MB
> VACUUM wal_bytes 6015 MB
> table size 5971 MB
>
> Patched:
>
> Time: 53142,947 ms (00:53,143)
> Time: 65420,812 ms (01:05,421)
> Time: 66600,114 ms (01:06,600)
> VACUUM Time: 63,401 ms
>
> COPY wal_bytes 3765 MB
> VACUUM wal_bytes 30 kB
> table size 5971 MB
>
> The test script is attached.
>
> > Also, when Andres posted this patch first, he said this was only for
> > heap_multi_insert because it was a prototype. But I think we expect
> > that the table_insert path (CIM_SINGLE mode in copy) should also receive
> > that treatment.
>
> I am afraid that extra checks for COPY FREEZE in heap_insert() will
> slow down normal insertions.
>
> --
> Anastasia Lubennikova
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
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
postgres=*# COMMIT;
COMMIT
postgres=# VACUUM;
VACUUM
postgres=# SELECT count(*) FROM foo;
count
----------
10000000
(1 row)
--
Ibrar Ahmed
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2020-08-21 16:50:40 | Re: run pgindent on a regular basis / scripted manner |
Previous Message | Fujii Masao | 2020-08-21 16:04:04 | Re: [PG13] Planning (time + buffers) data structure in explain plan (format text) |