On 11/19/15 4:21 PM, Tom Lane wrote:
Marko Tiikkaja <ma...@joh.to> writes:
I've in the past wanted to listen on all notification channels in the
current database for debugging purposes. But recently I came across
another use case. Since having multiple postgres backends listening for
notifications is very inefficient (one Thursday I found out 30% of our
CPU time was spent spinning on s_locks around the notification code), it
makes sense to implement a notification server of sorts which then
passes on notifications from postgres to interested clients.
... and then you gotta get the notifications to the clients, so it seems
like this just leaves the performance question hanging.
I'm not sure which performance question you think is left hanging. If
every client is connected to postgres, you're waking up tens if not
hundreds of processes tens if not hundreds of times per second. Each of
them start a transaction, check which notifications are visible in the
queue from clog and friends, go through the tails of every other process
to see whether they should advance the tail pointer of the queue, commit
the transaction and go back to sleep only to be immediately woken up again.
If they're not connected to postgres directly, this kind of complex
processing only happens once, and then the notification server just
unconditionally serves all notifications to the clients based on a
simple map lookup. It should be trivial to see how the overhead is avoided.
Why don't we find
and fix the actual performance issue (assuming that 07e4d03fb wasn't it)?
07e4d03fb wasn't it, no.
The reason I'm not terribly enthused about this proposal is that some
implementations of LISTEN (for example, our pre-9.0 one) would be unable
to support LISTEN *. It's not too hard to imagine that at some point we
might wish to redo the existing implementation to reduce the overhead of
all listeners seeing all messages, and then having promised we could do
LISTEN * would be a serious restriction on our flexibility. So while
I'm not necessarily trying to veto the idea, I think it has significant
opportunity cost, and I'd like to see a more solid rationale than this
one before we commit to it.
A reasonable point.
In any case, it would be good to understand exactly what's the performance
issue that's biting you. Can you provide a test case that reproduces
that behavior?
I've attached a Go program which simulates quite accurately the
LISTEN/NOTIFY-part of our setup. What it does is:
1) Open 50 connections, and issue a LISTEN in all of them
2) Open another 50 connections, and deliver one notification every
750 milliseconds from each of them
(My apologies for the fact that it's written in Go. It's the only thing
I can produce without spending significant amount of time working on this.)
On the test server I'm running on, it doesn't look quite as bad as the
profiles we had in production, but s_lock is still the worst offender in
the profiles, called from:
- 80.33% LWLockAcquire
+ 48.34% asyncQueueReadAllNotifications
+ 23.09% SIGetDataEntries
+ 16.92% SimpleLruReadPage_ReadOnly
+ 10.21% TransactionIdIsInProgress
+ 1.27% asyncQueueAdvanceTail
which roughly looks like what I recall from our actual production profiles.
.m
package main
import (
"github.com/lib/pq"
"database/sql"
"fmt"
"log"
"time"
)
const connInfo = "host=/var/run/postgresql sslmode=disable"
func listener(i int) {
l := pq.NewListener(connInfo, time.Second, time.Second, nil)
err := l.Listen(fmt.Sprintf("channel%d", i))
if err != nil {
log.Fatal(err)
}
for {
<-l.Notify
}
}
func notifier(dbh *sql.DB, i int) {
for {
_, err := dbh.Exec(fmt.Sprintf("NOTIFY channel%d", i))
if err != nil {
log.Fatal(err)
}
time.Sleep(750 * time.Millisecond)
}
}
func main() {
openDb := func() *sql.DB {
db, _ := sql.Open("postgres", connInfo)
err := db.Ping()
if err != nil {
log.Fatal(err)
}
db.SetMaxIdleConns(2)
db.SetMaxOpenConns(2)
return db
}
for i := 0; i < 50; i++ {
go listener(i)
go notifier(openDb(), i)
time.Sleep(20 * time.Millisecond)
}
select{}
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
