Re: Performance of the listen command - Mailing list 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