Date: Sat, 14 Nov 2015 16:07:26 +0000 (UTC)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
To: Jim Nasby <Jim.Nasby@BlueTreble.com>, Jason Jho <jasonjho@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Message-ID: <808512339.63860.1447517246839.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <564658DA.2000804@BlueTreble.com>
References: <564658DA.2000804@BlueTreble.com>
Subject: Re: Hanging query on a fresh restart
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Length: 2069
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Friday, November 13, 2015 3:41 PM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
> On 11/10/15 3:42 PM, Jason Jho wrote:

>> We using Postgres 9.3.10

>> What is the most confusing part in all of this is why a DROP
>> SCHEMA CASCADE and a fresh pg_restore would somehow fix the
>> problem. Even a fresh reboot didn't fix it.
>
> Without more info we're stuck guessing. You might try submitting
> a ticket with amazon, especially if you can reproduce this.

There have been occasional reports of corrupted indexes causing
endless loops which could cause these symptoms if one core was
pegged at 100% during the incident.  There are many possible causes
for such corruption -- see:

http://rhaas.blogspot.com/2012/03/why-is-my-database-corrupted.html

That said, there was a long-standing bug in btree index page
deletion (which could only happen during vacuum or autovacuum)
which was fixed in 9.4:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=efada2b8e920adfdf7418862e939925d2acd1b89

It was pretty hard to hit, and normally wouldn't cause these
symptoms, but if there was a "perfect storm" of events before the
problem self-corrected, I think it might possibly lead to this.  If
we could somehow confirm that this old bug was the cause, it might
justify pushing this patch back into older branches.  As the commit
message said:

| This bug is old, all supported versions are affected, but this patch is too
| big to back-patch (and changes the WAL record formats of related records).
| We have not heard any reports of the bug from users, so clearly it's not
| easy to bump into. Maybe backpatch later, after this has had some field
| testing.

Did you make a filesystem-level copy of the data directory?  If so,
the first step in checking this theory would be to restore a copy
and reindex all indexes used by the problem query to see if that
fixes it.  If it does, close examination of the corrupted index
might provide clues about how the corruption occurred.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance