Multi-second pauses blocking even trivial activity

From: Patrick Molgaard <draaglom(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Multi-second pauses blocking even trivial activity
Date: 2018-09-07 11:56:12
Message-ID: CA+Zan4vzTKfpkMznCVkndO7XW6s7sMKpBYqNSToM8nOpjVidsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi folks,

I've been seeing some curious behaviour on a postgres server I administer.

Intermittently (one or two times a week), all queries on that host are
simultaneously blocked for extended periods (10s of seconds).

The blocked queries are trivial & not related to locking - I'm seeing
slowlogs of the form:

`LOG: duration: 22627.299 ms statement: SET client_encoding='''utf-8''';`

where this is the first statement on a fresh connection.

It happens even for connections from the same host - so it doesn't appear
to be e.g. network slowness, if that is even counted in query duration.

Does anyone have any hints for where to look for a cause?

Thanks,

Patrick

-----

Set up information:

Postgres version: PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

Full Table and Index Schema: not applicable, I think

EXPLAIN ANALYSE: n/a

Hardware: AWS i3.2xlarge

Maintenance Setup: autovacuum yes, but the times it runs don't correlate to
the incidences of slow queries

WAL settings: shipped to S3 with wal-e, stored on same disk for interim
period

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2018-09-07 14:32:00 Re: Multi-second pauses blocking even trivial activity
Previous Message Dinesh Kumar 2018-09-07 03:28:42 Re: Performance difference in accessing differrent columns in a Postgres Table