Re: Performance of the listen command

From: Flemming Frandsen <ff(at)partyticket(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Performance of the listen command
Date: 2006-07-29 14:16:36
Message-ID: 44CB6DC4.3000404@partyticket.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Michael Fuhr wrote:
> How long is "a very long time"?

More than 2ms, typically 50-150ms.

The other queries (that actually fetch data from tables and do real
work) execute in 1-4ms.

The funny thing is that if I run the select 6*7 after ping (which does a
select 'somestring') then the select 6*7 runs in normal time and the
ping is slow, so it seems to me that it's the first query that's being hit.

Maybe it's the overhead of starting a new transaction?

> Does the first query's time include
> the time to connect or do you start timing after the connection has
> been made?

This is on a used connection that I just pulled out of my connection
pool, so it's simply the first query in the new transaction.

> How often are you doing the listens?

Every time an apache process connects to the DB.

> 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.

Thank you *so* much for confirming that I'm not out of my mind, I just
did the vacuum and it did indeed fix the problem, listens are now done
in less than 1ms.

I just looked at the pg_listener table:

zepong-> \d+ pg_listener
Table "pg_catalog.pg_listener"
Column | Type | Modifiers | Description
--------------+---------+-----------+-------------
relname | name | not null |
listenerpid | integer | not null |
notification | integer | not null |
Has OIDs: no

... and noticed the complete lack of indexen, surely this must be a bug?

When trying to create the index I get told off by pg:

create unique index pg_listeners on pg_listener (relname, listenerpid);
ERROR: permission denied: "pg_listener" is a system catalog

Any ideas, other than run VACUUM pg_listener every 10 minutes?

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

zepong=> VACUUM VERBOSE pg_listener;
INFO: vacuuming "pg_catalog.pg_listener"
INFO: "pg_listener": removed 243467 row versions in 3290 pages
DETAIL: CPU 0.24s/0.38u sec elapsed 8.61 sec.
INFO: "pg_listener": found 243467 removable, 113 nonremovable row
versions in 3290 pages
DETAIL: 5 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.27s/0.40u sec elapsed 8.65 sec.
VACUUM

zepong=> VACUUM VERBOSE pg_listener;
INFO: vacuuming "pg_catalog.pg_listener"
INFO: "pg_listener": removed 1207 row versions in 17 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_listener": found 1207 removable, 108 nonremovable row
versions in 3290 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 242413 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.00u sec elapsed 0.03 sec.
VACUUM

--
Regards Flemming Frandsen - http://dion.swamp.dk - YAPH

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Flemming Frandsen 2006-07-29 14:46:22 Re: Performance of the listen command
Previous Message Michael Fuhr 2006-07-29 13:22:46 Re: Performance of the listen command