Re: bad COPY performance with NOTIFY in a trigger
| От | Filip Rembiałkowski |
|---|---|
| Тема | Re: bad COPY performance with NOTIFY in a trigger |
| Дата | |
| Msg-id | CAP_rwwk_ueFyPn0-djegLJCrpPrCXRC7ns8__rQgy7Dmd_-PLQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: bad COPY performance with NOTIFY in a trigger (Filip Rembiałkowski <filip.rembialkowski@gmail.com>) |
| Ответы |
Re: bad COPY performance with NOTIFY in a trigger
(Merlin Moncure <mmoncure@gmail.com>)
|
| Список | pgsql-performance |
patch submitted on -hackers list. http://www.postgresql.org/message-id/CAP_rwwn2z0gPOn8GuQ3qDVS5+HgEcG2EzEOyiJtcA=vpDEhoCg@mail.gmail.com
results after the patch:
trigger= BEGIN RETURN NULL; END
rows=40000
228ms COPY test.tab FROM '/tmp/test.dat'
205ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
494ms COPY test.tab FROM '/tmp/test.dat'
395ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
678ms COPY test.tab FROM '/tmp/test.dat'
652ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
956ms COPY test.tab FROM '/tmp/test.dat'
822ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
1184ms COPY test.tab FROM '/tmp/test.dat'
1072ms COPY test.tab FROM '/tmp/test.dat'
trigger= BEGIN PERFORM pg_notify('test',NEW.id::text); RETURN NULL; END
rows=40000
440ms COPY test.tab FROM '/tmp/test.dat'
406ms COPY test.tab FROM '/tmp/test.dat'
rows=80000
887ms COPY test.tab FROM '/tmp/test.dat'
769ms COPY test.tab FROM '/tmp/test.dat'
rows=120000
1346ms COPY test.tab FROM '/tmp/test.dat'
1171ms COPY test.tab FROM '/tmp/test.dat'
rows=160000
1710ms COPY test.tab FROM '/tmp/test.dat'
1709ms COPY test.tab FROM '/tmp/test.dat'
rows=200000
2189ms COPY test.tab FROM '/tmp/test.dat'
2206ms COPY test.tab FROM '/tmp/test.dat'
On Fri, Feb 5, 2016 at 1:45 PM, Filip Rembiałkowski <filip.rembialkowski@gmail.com> wrote:
On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Filip Rembiałkowski <filip.rembialkowski@gmail.com> writes:
> A table has a trigger.
> The trigger sends a NOTIFY.
> Test with COPY FROM shows non-linear correlation between number of inserted
> rows and COPY duration.
No surprise, see AsyncExistsPendingNotify. You would have a lot of other
performance issues with sending hundreds of thousands of distinct notify
events from one transaction anyway, so I can't get terribly excited about
this.What kind of issues? Do you mean, problems in postgres or problems in client?Is there an additional non-linear cost on COMMIT (extra to the cost I already showed)?The 8GB internal queue (referenced in a Note at http://www.postgresql.org/docs/current/static/sql-notify.html) should be able to keep ~ 1E8 such notifications (assumed one notification will fit in 80 bytes).On client side, this seems legit - the LISTENer deamon will collect these notifications and process them in line.There might be no LISTENer running at all.Still, the main problem I get with this approach is quadratic cost on big insert transactions.I wonder if this behavior is possible to change in future postgres versions. And how much programming work does it require.Is duplicate-elimination a fundamental, non-negotiable requirement?Thank you,Filip
В списке pgsql-performance по дате отправления: