At 11:58 PM -0500 2/23/08, Tom Lane wrote:
Joel Stevenson <joelstevenson@xxxxxxx> writes:
That's strange, I would not have thought that listen/notify behavior
would change at all. How are you measuring this delay exactly?
Can you put together a self-contained test case?
Attached is a perl script that sort of simulates what's going on.
Thanks for the script. It's not showing any particular problems here,
though. With log_min_duration_statement = 10, the only statements that
(slightly) exceed 10ms are the select count(*) from generate_series(1,
15000) ones.
Also of note, the iowait percentages on this quad core linux box jump
to 30-40% while this test script is running, event though there's no
table activity involved and the producer consumers pause for up to a
second between iterations.
This sounds a bit like pg_listener has gotten bloated. Try a "VACUUM
VERBOSE pg_listener" (as superuser) and see what it says.
At the moment (server is inactive):
pcdb=# VACUUM VERBOSE pg_listener;
INFO: vacuuming "pg_catalog.pg_listener"
INFO: "pg_listener": removed 1 row versions in 1 pages
INFO: "pg_listener": found 1 removable, 21 nonremovable row versions
in 28 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2319 unused item pointers.
28 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
running the test script and then the above command:
pcdb=# VACUUM VERBOSE pg_listener;
INFO: vacuuming "pg_catalog.pg_listener"
INFO: "pg_listener": removed 693 row versions in 12 pages
INFO: "pg_listener": found 693 removable, 21 nonremovable row
versions in 28 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2308 unused item pointers.
28 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Numerous notifications took 1000ms or so to complete in the test
script execution between those two vacuum runs.
If that is the problem then the next question is why it got so much more
bloated than you were used to --- something wrong with vacuuming
procedures, perhaps?
I have autovacuum on and using default settings. I have an explicit
vacuum routine that runs nightly over the whole DB.
-Joel
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
