Autoanalyze CPU usage

From: Habib Nahas <habibnahas(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Autoanalyze CPU usage
Date: 2017-12-19 16:47:52
Message-ID: CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Thanks,
Habib Nahas

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2017-12-19 17:09:58 Re: Autoanalyze CPU usage
Previous Message Dave Johansen 2017-12-19 00:29:15 Re: WHERE IN for JOIN subquery?