Subject: Re: Hanging query on a fresh restart
To: Jason Jho <jasonjho@gmail.com>, <pgsql-performance@postgresql.org>
References: 
 <CACLReKuBuHU85p38fYoF_h_jMKeeFewOXvS7zSGV+fHWz4ENrQ@mail.gmail.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <564658DA.2000804@BlueTreble.com>
Date: Fri, 13 Nov 2015 15:40:42 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CACLReKuBuHU85p38fYoF_h_jMKeeFewOXvS7zSGV+fHWz4ENrQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Nov 2015 21:40:56.8765 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0681
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 11/10/15 3:42 PM, Jason Jho wrote:
> On one particular day, this query hung for many hours and even while we
> killed pids for running queries and any locks granted, the query would
> never return.  Also no hints of blocking processes.  After some digging
> through some I/O metrics, we didn't see any memory issues or unusual
> spikes that would lead us to believe that we're running low on resources.

Did IO stats indicate IO was happening? Did you see a pegged CPU running 
the query?

> There is 1 caveat, however:  there was a different schema that contained
> a day-old copy of data that isn't normally present when the hang started
> to occur.  However, since these are completely different schema
> namespaces with no crossovers in the queries themselves, I don't see how
> this is relevant.

If search_path wasn't what you thought it was you could have easily been 
running against the wrong set of tables.

> We thought this might be possibly due to some internal vacuuming, but
> this is unlikely since there are no real concurrent reads or updates
> happening.  Auto-vacuum is also on with default settings.

There are other reasons why autovacuum could kick in, notably to prevent 
transaction ID wraparound.

> 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.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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