From: Scott Neville <scott.neville@bluestar-software.co.uk>
To: pgsql-admin@postgresql.org
Subject: Slow planning time
Date: Wed, 23 Dec 2015 12:00:18 +0000
Message-ID: <2214194.xuzAFCE6T3@blswork15> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi,
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.
The only other thing to note is that while all of the tables are big, but most of them are not crazy (the one that is most commonly selected from has 718,000 rows in it), there are some very big tables which are reaching 325,000,000 rows. There is quite a lot of change too I estimate about 7,500,000 row changes a day on average, but this is also very focused (about 7 million of the changes happen on two tables, yet all tables suffer from slow query planning). Most of these changes occur overnight where bulk changes occur then the rest happens in a more steady stream through the day. I could understand it more if the execution time was slow, but its the planning time.
Auto-vacuum is turned on and set to 1 worker, in addition to this we have a process that runs every night and runs "vacuum analyze" on as many tables as it can in a 2 hour period (starting with the oldest vacuumed first).
Just wondering if anyone has any thoughts as to why planning takes so long and anything I can do to address the issue.
Thanks
Scott
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DISCLAIMER: This email message and any attachments is for the sole
use of the intended recipient(s) and may contain confidential and
privileged information. Any unauthorised review, use, disclosure
or distribution is prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of
the original message.
The views expressed in this message may not necessarily reflect the
views of Bluestar Software Ltd.
Bluestar Software Ltd, Registered in England
Company Registration No. 03537860, VAT No. 709 2751 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
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: scott.neville@bluestar-software.co.uk
Subject: Re: Slow planning time
In-Reply-To: <2214194.xuzAFCE6T3@blswork15>
* 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