Re: BUG #14714: long running sessions from remote instance seems to hang some times

On Mon, Jun 19, 2017 at 7:49 AM, <josef(dot)machytka(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14714
> Logged by: Josef Machytka
> Email address: josef(dot)machytka(at)gmail(dot)com
> PostgreSQL version: 9.6.3
> Operating system: Debian jessie
> Description:
>
> On pg 9.6.* we have sometimes strange problem with some sessions. We
> experienced it from bash scripts, golang program and node.js applications.
> Environment are Google Compute Engine instances with Debian 8.
>
> Sometimes some session goes into some kind of "drowsy" state and runs
> incredibly slowly. It causes some very low disk IO and runs like 20x slower
> then usual. This happens mainly when database is under heavy load and it
> happens randomly to different tasks. It happens randomly to both
> connections
> from local instance (cronjob running on instance with PG) and remote
> connections from other instances.
>

One possibility is that another process inserted a bunch of new tuples and
committed them, and now the existing session has to dig through all those
new tuples and ignore them because they are too new to be visible to it.
This would particularly be a problem where the queries have things like
"MAX(indexed_column)" or "ORDER BY indexed_column desc LIMIT 1" and where
the newly insert tuples all have values which are at the end of the range
being probed by that query. Although I don't know why this would have
gotten worse from 9.5 to 9.6. The change should have been in the other
direction.

Is this on a master or on a hot standby?

Cheers,

Jeff