Re: Autoanalyze CPU usage

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