Re: Slow planning time

Scott,

A couple of things to try... First, off, have you performed any vacuum full
sequences on your database? Basically, I mean a sequence of vacuum, vacuum
full, and another vacuum. And do you know if you autovacuum's have ever
completed? Depending on the number of tables, you probably need more than 1
worker anyway. With 7.5 million updates/day, you probably really need to
tune your AV settings as well. If you look around the web, I found a query
that builds a view indicating the AV status and need for every table in a
database. That was quite useful in tuning my own AV settings. Finally, you
could have memory settings way too low. Could you post your shared_buffers
and other memory values from your postgresql.conf file?
--
Jay

On Wed, Dec 23, 2015 at 6:00 AM, Scott Neville <
scott(dot)neville(at)bluestar-software(dot)co(dot)uk> wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>