Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/08/2017 01:12 AM, Alban Hertroys wrote:
>
>> On 7 Jan 2017, at 15:44, Job <Job(at)colliniconsulting(dot)it> wrote:
...
>> This is what it happens:
>>
>> Postgres 8.4.22
>> Medium average load 1.5/2.0
>> Further queries respond very quickly
>>
>> Postgres 9.6.1
>> Medium average load 18.0/20.0 !!
>> Further queries are really very slow
>> There is a bottle neck
>
> I see.
>

This behavior is typical when a resource gets saturated. You have
probably ran out of CPU time or I/O, resulting in growing latencies.
Thus more processes are running (or waiting for a CPU) at the same time,
which is what average load is based on.

What is the CPU and I/O usage in those cases?

FWIW you still haven't explained how the upgrade was performed. That
might be a very important piece of information, because the 9.4 cluster
might have hint bits set and/or the data may be mostly frozen, but the
9.6 cluster may not have that yet, resulting in higher CPU usage.

>> By removing *only* this condition in the query function:
>>
>> "exists ( select 1 from gruorari where
>> gruorari.idgrucate=grucategorie.id and ( (('{'||gg_sett||'}')::int[] &&
>> array[EXTRACT(DOW FROM NOW())::int])='t' and now()::time between
>> gruorari.dalle::time and gruorari.alle::time) )"
>
> Then most likely the slow-down you're experiencing is indeed in the
> above subquery. It could also be the addition of the exists though,
> let's not rule that out!
>
> Note that I'm not on either of the versions involved (9.3.15 here),
> so I can't easily observe what you're seeing.
>
> A general observation; I think now() calls gettimeofday() each time,
the performance of which can differ significantly depending on which
hardware clock is being used by your OS (there are often multiple
options). On the contrary, CURRENT_TIMESTAMP, CURRENT_TIME and friends
are only updated at the start of the transaction, requiring but a single
call to gettimeofday().
> Judging from your queries, you don't actually seem to need the
accuracy that NOW() provides…
>

No. now() calls GetCurrentTransactionStartTimestamp(), so it does not
call gettimeofday() and so the clock source overhead is pretty much
irrelevant. Moreover it's marked as 'stable' which makes repeated calls
unnecessary.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services