From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Neville <scott.neville@bluestar-software.co.uk>
Cc: pgsql-admin@postgresql.org
Subject: Re: Slow planning time
Date: Wed, 23 Dec 2015 10:24:24 -0500
Message-ID: <26165.1450884264@sss.pgh.pa.us> (raw)
In-Reply-To: <2214194.xuzAFCE6T3@blswork15>
References: <2214194.xuzAFCE6T3@blswork15>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Scott Neville <scott.neville@bluestar-software.co.uk> writes:
> We have a database that for some reason has started to be really slow at planning all queries. The database is running version 9.4.2 since July 28th (it was freshly installed then - compiled from source). The response time is fairly sporadic, but the quickest plan time I have seen (on any query) using explain analyze is 39ms with an execution time of 1ms, however we have slow query logging on and we are getting queries taking over 6000 ms in the planning stage with then only a few ms to execute. There is nothing complex about the queries so even something like this:
> select max(datetime) from audit;
> (where datetime is an indexed field takes 200ms to plan and 0.5ms to execute).
> The databases are involved in a replication chain so I have
> M1 -> S1 -> S2
> I have restarted S2 and S1 and this appears to have made the problem go away (though for how long....). S1 has a replication slot listed on M1.
Please clarify: the slowness occurs on the slaves but not the master?
I am suspicious that the problem has to do with bloat in pg_statistic,
which I will bet that your homegrown vacuuming protocol isn't covering
adequately. I concur with Kevin's nearby advice that you'd be better
off to forget that and use 10 or so autovacuum workers; you can use
autovacuum_cost_limit to throttle their I/O impact, and still be a
lot better off than with just 1 worker.
There is probably something else going on that's replication-specific,
but I'm not sufficiently up on that aspect of things to theorize.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott.neville@bluestar-software.co.uk
Subject: Re: Slow planning time
In-Reply-To: <26165.1450884264@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox