Shaun Thomas <sthomas@optionshouse.com> wrote:
> It is when 90% of a table's 65M rows are part of the result set.
> Calculating the cursor puts the retrieved rows in a temporary space
> until the cursor is freed. Calculating that many rows with that order
> by, on our particular system took about 75 seconds. The problem wasn't
> really that it was fetching the rows, but that it was closing the cursor
> *right* afterwards. Like this:
>
> 2013-03-04 07:46:06 CST DEBUG1 remoteHelperThread_1_1: 78.010 seconds
> until close cursor
>
> We saw that in the slave log about every 80 seconds. When I set up slony
> on our staging system, it only closed the cursor once all 20M rows were
> fetched and deleted from the subscriber node. Clearly whatever was
> happening in our production environment wasn't normal. But at the time,
> it *looked* like slony was forcing it to calculate and fill the cursor,
> getting the top 500 rows, and closing it right away on purpose. Turns
> out, that was abnormal. :)
>
> So, I'm not shocked anymore. A one time cursor calculation is fine. Now
> I'm confused as to what broke it or why it was acting that way when that
> clearly isn't the design goal.
Exactly what version was this? Just about any queuing system (and
this definitely includes Slony and some JMS implementations) were
vulnerable to an autovacuum bug fixed with this patch from Jan
Wieck:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b19e4250b45e91c9cbdd18d35ea6391ab5961c8d
This fix was backpatched as far as 9.0 and is present in the latest
minor releases, but not earlier ones. It can cause symptoms such
as you describe.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company