Re: Performance of the listen command

On Sat, Jul 29, 2006 at 12:44:14PM +0200, Flemming Frandsen wrote:
> Michael Fuhr wrote:
> >Also, based on a 60ms-per-listen time I suspect you're not doing
> >the listens in a transaction, so each listen is its own transaction
> >that has to be committed, resulting in a disk hit.  Try doing them
> >all in one transaction.
>
> I think I am doing the listens in a transaction, as I connect via DBI
> with AutoCommit=>0, unless there is some bug that causes the listens to
> not start a new transaction when using DBI.

Disabling autocommit should put all the listens in a single transaction
unless you're committing each one, but it sounds like you aren't doing
that.  We can probably reject my hypothesis.

> I also see a problem with the first query I run in a transaction, it
> takes a very long time, even if it's simply a "select 6*7", I'm guessing
> this is because a new transaction is started, is there any way to
> improve performance of that?

How long is "a very long time"?  Does the first query's time include
the time to connect or do you start timing after the connection has
been made?

> Alvaro Herrera wrote:
> > Do you regularly vacuum the pg_listener table?
>
> No, but this is on a system that has been running for a couple of days.

How often are you doing the listens?  I just did a test in which I
connected to a database 100 times and issued 150 listens in each
connection.  By the 100th connection the time to execute the listens
had increased by an order of magnitude due to bloat in pg_listener.
Vacuuming pg_listener brought the times down again.

What's the output of "VACUUM VERBOSE pg_listener"?  If you vacuum
pg_listener do the listens run faster?

--
Michael Fuhr