pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutoanalyze CPU usage
10+ messages / 6 participants
[nested] [flat]
* Autoanalyze CPU usage
@ 2017-12-19 16:47 Habib Nahas <habibnahas@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Habib Nahas @ 2017-12-19 16:47 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-19 17:09 Justin Pryzby <pryzby@telsasoft.com>
parent: Habib Nahas <habibnahas@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Justin Pryzby @ 2017-12-19 17:09 UTC (permalink / raw)
To: Habib Nahas <habibnahas@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-19 22:03 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Habib Nahas <habibnahas@gmail.com>
2 siblings, 3 replies; 10+ messages in thread
From: Tomas Vondra @ 2017-12-19 22:03 UTC (permalink / raw)
To: Habib Nahas <habibnahas@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-19 22:53 Habib Nahas <habibnahas@gmail.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 1 reply; 10+ messages in thread
From: Habib Nahas @ 2017-12-19 22:53 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
The autoanalyze factor is set to 0.05 for the db, and we have not changed
the default statistics target.
The CPU spike occurred between 13:05 - 13:15. last_autoanalyze for the
table shows a time of 12:49; last_autovacuum does not show any activity
around this time for any table. Checkpoint logs are also normal around this
time. I'd like to understand if there are any other sources of activity I
should be checking for that would account for the spike.
User workload is throttled to avoid excess load on the db, so a query is
unlikely to have caused the spike. But we can dig deeper if other causes
are ruled out.
Thanks
On Tue, Dec 19, 2017 at 2:03 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
>
>
> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-19 22:53 Habib Nahas <habibnahas@gmail.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 0 replies; 10+ messages in thread
From: Habib Nahas @ 2017-12-19 22:53 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
As it happens our larger tables operate as a business log and are also
insert only.
- There is no partitioning at this time since we expect to have an
automated process to delete rows older than a certain date.
- Analyzing doing off-hours sounds like a good idea; if there is no other
way to determine effect on db we may end up doing that.
- We have an open schema and heavily depend on jsonb, so I'm not sure if
increasing the statistics target will be helpful.
Thanks
On Tue, Dec 19, 2017 at 2:03 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
>
>
> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-19 22:55 Justin Pryzby <pryzby@telsasoft.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 10+ messages in thread
From: Justin Pryzby @ 2017-12-19 22:55 UTC (permalink / raw)
To: Habib Nahas <habibnahas@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Dec 19, 2017 at 02:37:18PM -0800, Habib Nahas wrote:
> As it happens our larger tables operate as a business log and are also
> insert only.
>
> - There is no partitioning at this time since we expect to have an
> automated process to delete rows older than a certain date.
This is a primary use case for partitioning ; bulk DROP rather than DELETE.
> - Analyzing doing off-hours sounds like a good idea; if there is no other
> way to determine effect on db we may end up doing that.
You can also implement a manual analyze job and hope to avoid autoanalyze.
> - We have an open schema and heavily depend on jsonb, so I'm not sure if
> increasing the statistics target will be helpful.
If the increased stats target isn't useful for that, I would recommend to
decrease it.
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-20 01:10 Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 0 replies; 10+ messages in thread
From: Michaeldba@sqlexec.com @ 2017-12-20 01:10 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Habib Nahas <habibnahas@gmail.com>; pgsql-performance@lists.postgresql.org
Perhaps consider running manual vacuum analyze at low load times daily if you have that opportunity. This may stop autovacuums from hitting thresholds during high load times or do the normal/aggressive autovacuum tuning to make it more aggressive during low load times and less aggressive during high load times.
Sent from my iPad
> On Dec 19, 2017, at 5:03 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
>
>
>> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-20 07:15 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Habib Nahas <habibnahas@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2017-12-20 07:15 UTC (permalink / raw)
To: Habib Nahas <habibnahas@gmail.com>; Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
Habib Nahas wrote:
> The CPU spike occurred between 13:05 - 13:15. last_autoanalyze for the table
> shows a time of 12:49; last_autovacuum does not show any activity around
> this time for any table. Checkpoint logs are also normal around this time.
> I'd like to understand if there are any other sources of activity I
> should be checking for that would account for the spike.
last_autoanalyze is set after autoanalyze is done, so that would suggest
that autoanalyze is not the problem.
It can be tough to figure out where the activity is coming from unless
cou can catch it in the act. You could log all statements (though the amount
of log may be prohibitive and can cripple performance), you could log
just long running statements in the hope that these are at fault, you
could log connections and disconnections and hope to find the problem
that way. Maybe logging your applications can help too.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-20 08:24 Nikolay Samokhvalov <samokhvalov@gmail.com>
parent: Habib Nahas <habibnahas@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Nikolay Samokhvalov @ 2017-12-20 08:24 UTC (permalink / raw)
To: Habib Nahas <habibnahas@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Dec 19, 2017 at 7:47 PM, Habib Nahas <habibnahas@gmail.com> 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.
>
How did you draw such conclusion? How did you find that autoanalyze is the
reason of CPU spikes?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autoanalyze CPU usage
@ 2017-12-20 14:38 Habib Nahas <habibnahas@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 10+ messages in thread
From: Habib Nahas @ 2017-12-20 14:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Thanks for confirming that it is the end timestamp, the doc wasn't quite
clear if it was the start or end.
There is a gap in our monitoring that makes diagnosis of such events very
difficult after the fact. Something like a 10-sec periodic dump of
pg_stat_activity along with a similar dump of pg_top would have been very
helpful here.
-Habib
On Tue, Dec 19, 2017 at 11:15 PM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> Habib Nahas wrote:
> > The CPU spike occurred between 13:05 - 13:15. last_autoanalyze for the
> table
> > shows a time of 12:49; last_autovacuum does not show any activity around
> > this time for any table. Checkpoint logs are also normal around this
> time.
> > I'd like to understand if there are any other sources of activity I
> > should be checking for that would account for the spike.
>
> last_autoanalyze is set after autoanalyze is done, so that would suggest
> that autoanalyze is not the problem.
>
> It can be tough to figure out where the activity is coming from unless
> cou can catch it in the act. You could log all statements (though the
> amount
> of log may be prohibitive and can cripple performance), you could log
> just long running statements in the hope that these are at fault, you
> could log connections and disconnections and hope to find the problem
> that way. Maybe logging your applications can help too.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2017-12-20 14:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-19 16:47 Autoanalyze CPU usage Habib Nahas <habibnahas@gmail.com>
2017-12-19 17:09 ` Justin Pryzby <pryzby@telsasoft.com>
2017-12-19 22:55 ` Justin Pryzby <pryzby@telsasoft.com>
2017-12-19 22:03 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2017-12-19 22:53 ` Habib Nahas <habibnahas@gmail.com>
2017-12-20 07:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-12-20 14:38 ` Habib Nahas <habibnahas@gmail.com>
2017-12-19 22:53 ` Habib Nahas <habibnahas@gmail.com>
2017-12-20 01:10 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2017-12-20 08:24 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
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