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.neville@xxxxxxxxxxxxxxxxxxxxxxx> 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.neville@xxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
> > 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.neville@xxxxxxxxxxxxxxxxxxxxxxx
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
