Hi, while measuring NOTIFY execution time, I noticed a significant performance drop.
Please find a patch attached, together with some tests; more details
are shown below.
Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.cio...@2ndquadrant.it | www.2ndquadrant.it
---8<------8<------8<------8<------8<------8<------8<------8<------8<---
h1. The problem
We record pending notifications in a transaction-based list.
Notifications are recorded in the same order as they are issued.
Because of PostgreSQL's asynchronous notification semantics, we don't
need to record two identical notifications; we record a notification
only if there are no duplicates.
This is implemented by scanning the list and checking for duplicates.
The list is scanned backwards because the last element is easily
accessible, which is a sensible optimisation in the case of many
notifications which are identical to the previous one (scenario A).
However, scanning the list is quite expensive in the case of many
notifications which are not identical to the previous one (scenario B,
see Test 1 below when m > 1).
h1. Proposed solution
To check only the last element in that list, which is efficient in
both scenarios (see Test 2 below).
h1. Tests
"PostgreSQL HEAD" has been fetched as after commit #a0e8df52 (Wed Apr
20 22:49:37 2011 -0400).
Test 1 has been executed against PostgreSQL HEAD.
Test 2 has been executed against patched version of PostgreSQL HEAD.
In the tables below:
* "n" denotes the number of notifications issued in a single
transaction;
* "m" denotes the number of distinct channels used for these
notifications;
* "iter" is the number of times each transaction has been repeated (to
reduce the importance of occasional spikes);
* "avg_usec" denotes the average time in microseconds required by each
NOTIFY statement.
h2. Test 1 - PostgreSQL HEAD
n | m | iter | avg_usec
-------+-------+------+----------
10 | 1 | 10 | 43.730
100 | 1 | 10 | 37.630
1000 | 1 | 10 | 42.990
10000 | 1 | 10 | 36.225
10 | 10 | 10 | 43.960
100 | 100 | 10 | 46.537
1000 | 1000 | 10 | 126.115
10000 | 10000 | 10 | 906.501
h2. Test 2 - patched PostgreSQL
n | m | iter | avg_usec
-------+-------+------+----------
10 | 1 | 10 | 43.810
100 | 1 | 10 | 38.256
1000 | 1 | 10 | 36.950
10000 | 1 | 10 | 36.638
10 | 10 | 10 | 44.830
100 | 100 | 10 | 38.684
1000 | 1000 | 10 | 38.924
10000 | 10000 | 10 | 38.032
diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c
index 588e9f2..16c0d96 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -2160,7 +2160,6 @@ NotifyMyFrontEnd(const char *channel, const char *payload, int32 srcPid)
static bool
AsyncExistsPendingNotify(const char *channel, const char *payload)
{
- ListCell *p;
Notification *n;
if (pendingNotifies == NIL)
@@ -2175,9 +2174,11 @@ AsyncExistsPendingNotify(const char *channel, const char *payload)
* backwards in order to make duplicate-elimination a tad faster when the
* same condition is signaled many times in a row. So as a compromise we
* check the tail element first which we can access directly. If this
- * doesn't match, we check the whole list.
+ * doesn't match, we used to check the whole list; we don't do that
+ * anymore, because it was inefficient in the case when many distinct
+ * channels are notified.
*
- * As we are not checking our parents' lists, we can still get duplicates
+ * Also, as we are not checking our parents' lists, we can get duplicates
* in combination with subtransactions, like in:
*
* begin;
@@ -2192,15 +2193,6 @@ AsyncExistsPendingNotify(const char *channel, const char *payload)
strcmp(n->payload, payload) == 0)
return true;
- foreach(p, pendingNotifies)
- {
- n = (Notification *) lfirst(p);
-
- if (strcmp(n->channel, channel) == 0 &&
- strcmp(n->payload, payload) == 0)
- return true;
- }
-
return false;
}
test.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
