From: Justin Pryzby <pryzby@telsasoft.com>
To: Habib Nahas <habibnahas@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Autoanalyze CPU usage
Date: Tue, 19 Dec 2017 11:09:58 -0600
Message-ID: <20171219170958.GF18184@telsasoft.com> (raw)
In-Reply-To: <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
References: <CAE1bBP4vxmMxtC0w37OhDv36M9f8222AbXfy0LWnynWf88ZS-Q@mail.gmail.com>
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
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: pryzby@telsasoft.com, habibnahas@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Autoanalyze CPU usage
In-Reply-To: <20171219170958.GF18184@telsasoft.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