Re: Slow planning time

Yep, your memory settings look like they might be OK. Now, when you say a
bulk update with a vacuum done afterwards, is this a regular vacuum or a
vacuum full? You do know that a vacuum as opposed to a vacuum full doesn't
actually shrink the table by removing the deleted tuples? Only a vacuum
full does that, Of course, the vacuum full locks the entire table so use
with caution. Also, after a full vacuum, I believe you should run another
normal vacuum as that will rebuild the planner statistics on the newly
shrunk table.

As far as your last comment, on the insert of 100 rows being slow. Was that
slow prior to the insert or did you actually measure prior? Could these
simply be where you're missing or have corrupted indexes? What does explain
tell you about the execution strategy? Do you see a lot of sequential scans
or index scans? Could you try dropping an index and recreating it in the
event you might have a corrupted index? I know this last suggestion is a
very rare occurrence, but it has happened to me.

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

> Hi,
>
> Thanks for that I think the memory settings are OK, I have:
>
> shared_buffered = 32GB
> temp_buffers = 32MB
> work_mem = 16MB
> maintenance_work_mem = 256MB
> effective_cache_size = 96GB
>
> With regard to the updates they are done in bulk (one statement), with a
> vacuum done straight afterwards, so its
>
> delete from table where date < 3 months ago
>
> 3.5 million rows changed
>
> vacuum table.
>
> The planning time is also slow on tables that had an insert of a few 100
> rows when the database went live which have not subsequently been changed.
>
> Hope that helps.
>
> Scott
>
> On Wednesday 23 Dec 2015 06:55:07 John Scalia wrote:
> > 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
> > >
> --
> Scott Neville
> Software Developer, Bluestar Software
> Telephone: +44 (0)1256 882695
> Web site: www.bluestar-software.co.uk
> Facebook: www.facebook.com/bluestarsoftware
> Email: scott(dot)neville(at)bluestar-software(dot)co(dot)uk
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 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
>