Date: Tue, 19 Dec 2017 11:09:58 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Habib Nahas <habibnahas@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Autoanalyze CPU usage
Message-ID: <20171219170958.GF18184@telsasoft.com>
References: 
 <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Tue, Dec 19, 2017 at 08:47:52AM -0800, Habib Nahas wrote:
> 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.

Not sure if it'll help you, but for our large, insert-only tables partitioned
by time, I made several changes from default:
 
 - near the end of month, report queries for previous day's data had poor
   statistics, because autoanalyze scale factor defaults to 0.1, so a table
   analyzed on the 24th of the month won't be analyzed again until the 26th, so
   the histogram shows that there's zero rows for previous day, causing nested
   loop over thousands of rows.
 - for separate reasons, I increased statistics target on our key columns (up
   to 3000 for one column).
 - large stats target on large tables caused (auto)analyze to use large amount
   of RAM.  Therefor I changed our largest tables from monthly partition
   granuliarity (YYYYMM) to daily (YYYYMMDD).  That creates what's
   traditionally considered to be an excessive number of partitions (and very
   large pg_attribute/attrdef and pg_statistic tables), but avoids the huge RAM
   issue, and works for our purposes (and I hope the traditional advice for
   number of child tables is relaxed in upcoming versions, too).

One possibility is a cronjob to set deafult "scale factor" to a modest/default
values (0.1) during business hours and an aggressive value (0.005) off-hours.
You could do similar with autovacuum_max_workers ... but beware if they're
causing high RAM use.  I believe autovacuum workers try to "play nice" and the
cost are shared between all workers.  But I suspect that's not true for CPU
cost or RAM use, so there's nothing stopping you from having 9 workers each
looping around 2+GB RAM and 100% CPU doing MCV/histogram computation.

Maybe that's of some use.

Justin