DB slowness after upgrade from Postgres 9.1 to 9.4

From: p kirti <kirtip92(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: DB slowness after upgrade from Postgres 9.1 to 9.4
Date: 2017-11-10 10:58:41
Message-ID: CAFgnfScjp=-joWVAyXE+v+zDRZ98SvcovUinmKLx2h2kGq6ePg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,

We have recently upgraded our project with a huge DB from Postgres v9.1 to
v9.4. The whole system performance has degraded alarmingly after the
upgrade. Simple operations that were taking only a few seconds in Postgres
9.1 are now taking minutes of time.

The problem is not specific to one query orany particular kind of query.
Its been generic and overall system has become very slow.

We tried running 'VACUUM ANALYZE' on the DB and that seemed to be helpful
too. But the improvement after this is nowhere close to the performance we
had in 9.1.

We tried changing some of the performance parameters in the
postgres.confirm as follows (our Postgres server has an 8GB RAM) -

shared_buffers = 200MB
maintenance_work_mem = 1000MB
default_statistics_target = 1000
effective_cache_size = 4000MB
And these made absolutely no difference to the query execution time.

The strangest part of the problem is when I EXPLAIN ANALYZE the same query
multiple times in the same Postgres server, it gives me different execution
times every time ranging from 45 ms to 181 ms.

We are absolutely clueless on how to proceed. Any help would be greatly
appreciated.
Thanks in advance.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2017-11-10 20:40:43 overestimate on empty table
Previous Message Ulf Lohbrügge 2017-11-08 09:31:42 Re: Slow execution of SET ROLE, SET search_path and RESET ROLE