Re: bad COPY performance with NOTIFY in a trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



patch submitted on -hackers list. http://www.postgresql.org/message-id/CAP_rwwn2z0gPOn8GuQ3qDVS5+HgEcG2EzEOyiJtcA=vpDEhoCg@xxxxxxxxxxxxxx

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@xxxxxxxxx> wrote:
On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip.rembialkowski@xxxxxxxxx> 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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux