From: Artur Zając <azajac@ang.com.pl>
To: 'Merlin Moncure' <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: NOTIFY performance
Date: Fri, 24 Aug 2012 23:11:08 +0200
Message-ID: <002601cd823c$fbaba070$f302e150$@ang.com.pl> (raw)
In-Reply-To: <CAHyXU0x5r+3zL6WYpHmuHhONatGYG3f49sdx+2z4Jjc2FhNBxQ@mail.gmail.com>
References: <002401cd8228$ce80e970$6b82bc50$@ang.com.pl>
<CAHyXU0x5r+3zL6WYpHmuHhONatGYG3f49sdx+2z4Jjc2FhNBxQ@mail.gmail.com>
>> I would like to create some application using triggers and
>> LISTEN/NOTIFY framework. I've tested it, and I noticed that
>> performance of NOTIFY significally decreases with increasing number of
>> distinct NOTIFIES in transaction.
>> I found that function AsyncExistsPendingNotify is responsibe for it. I
>> think that complexivity of searching duplicates there is O(N^2). Would
>> be possible to improve performance of it? Maybe by using list for
>> elements precedence and binary search tree for searching duplicates -
>> with complexivity of O(Nlog2(N)).
>>
>> I'v tested with 50000 of NOTICES. Updating table with 20000 NOTICES
>> when searching is not performed took 1,5 second. With searching it
>> took 28 seconds.
>
>I've confirmed the n^2 behavior on 9.2:
>postgres=# select pg_notify(v::text, null) from generate_series(1,10000) v;
>Time: 281.000 ms
>postgres=# select pg_notify(v::text, null) from generate_series(1,50000) v;
>Time: 7148.000 ms
>
>...but i'm curious if you're going about things the right way...typically I'd imagine you'd write out actionable items to a table and issue a much broader NOTIFY which taps listeners on the table to search the action queue. Could you describe your problem in >a little more detail?
When there was only NOTIFY option with simple channel name there was no need to send so many messages - creating 50000 channels would be really stupid. NOTIFY to channel might only mean that there is sth new in table or sth similar. But with payload option it would be possible to make simple system for notify other database clients (or self notify - when changes are made by triggers) that some single record has changed and it should be invalidated in client cache. I would made (and I already made) that system (similar to streaming replication :) but more more simple), but unfortunately even not big update on table would kill my system with complexivity O(N^2). In general , I know that this system would be not efficient, but for my application it would simply solve my many problems.
-------------------------------------------
Artur Zajac
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: azajac@ang.com.pl, mmoncure@gmail.com
Subject: Re: NOTIFY performance
In-Reply-To: <002601cd823c$fbaba070$f302e150$@ang.com.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox