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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
