From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Habib Nahas <habibnahas@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Autoanalyze CPU usage
Date: Tue, 19 Dec 2017 23:03:18 +0100
Message-ID: <d965ed83-4dd4-bde4-f6fe-62bf5cf8adfd@2ndquadrant.com> (raw)
In-Reply-To: <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
References: <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
On 12/19/2017 05:47 PM, Habib Nahas wrote:
> Hi,
>
> We operate an RDS postgres 9.5 instance and have periodic CPU spikes to
> 100%. These spikes appear to be due to autoanalyze kicking on our larger
> tables.
>
> Our largest table has 75 million rows and the autoanalyze scale factor
> is set to 0.05.
>
> The documentation I've read suggests that the analyze always operates on
> the entire table and is not incremental. Given that supposition are
> there ways to control cost(especially CPU) of the autoanalyze operation?
> Would a more aggressive autoanalyze scale factor (0.01) help. With the
> current scale factor we see an autoanalyze once a week, query
> performance has been acceptable so far, which could imply that scale
> factor could be increased if necessary.
>
No, reducing the scale factor to 0.01 will not help at all, it will
actually make the issue worse. The only thing autoanalyze does is
running ANALYZE, which *always* collects a fixed-size sample. Making it
more frequent will not reduce the amount of work done on each run.
So the first question is if you are not using the default (0.1), i.e.
have you reduced it to 0.05.
The other question is why it's so CPU-intensive. Are you using the
default statistics_target value (100), or have you increased that too?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, habibnahas@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Autoanalyze CPU usage
In-Reply-To: <d965ed83-4dd4-bde4-f6fe-62bf5cf8adfd@2ndquadrant.com>
* 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