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