Thread: Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)

Pavan Deolasee <pavan.deolasee@gmail.com> writes:
> While looking at this particular case on -general, I realized that there is
> no way to *only* disable auto-analyze on a table. While one can cheat like
> what I suggested to the OP by setting threshold very high, I think it will
> be useful to be able to just off analyze. In this particular case, the OP
> is inserting and then deleting the same rows from the parent table, thus
> keeping it almost empty. Of course, he would want to run auto-vacuum on the
> table to remove the dead rows. Usually auto-analyze would have returned
> quite fast, especially because we vacuum a table first and then analyze it.
> But in this case, since the table is a parent of a number of large child
> tables, we end up analyzing the child tables too, which takes significantly
> longer time and is quite unnecessary because in this case the activity on
> the parent table must not have changed any stats for the child tables.

> A new reloption such as autovacuum_analyze_enabled is what we need.

This seems to me to be a wart that doesn't fix the actual problem ---
the actual problem is to make the autovac daemon smarter about when an
inheritance-tree ANALYZE pass is needed.  That should be done somehow
based on the total row churn across the parent + children.  Looking
at the parent only, as we do now, can result in analyzing too often
(the OP's case) or too seldom (the much more common case).  A manual
"off" switch fixes only the less common case, and requires user
intervention that we'd be better off without.
        regards, tom lane

Pavan Deolasee escribió:

> While looking at this particular case on -general, I realized that there is
> no way to *only* disable auto-analyze on a table. While one can cheat like
> what I suggested to the OP by setting threshold very high, I think it will
> be useful to be able to just off analyze. In this particular case, the OP
> is inserting and then deleting the same rows from the parent table, thus
> keeping it almost empty. Of course, he would want to run auto-vacuum on the
> table to remove the dead rows. Usually auto-analyze would have returned
> quite fast, especially because we vacuum a table first and then analyze it.
> But in this case, since the table is a parent of a number of large child
> tables, we end up analyzing the child tables too, which takes significantly
> longer time and is quite unnecessary because in this case the activity on
> the parent table must not have changed any stats for the child tables.
>
> A new reloption such as autovacuum_analyze_enabled is what we need.

I was thinking in this option just three days ago, so yeah.

I think we also want an option to turn off just vacuum.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Feb 1, 2013 at 9:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Pavan Deolasee <pavan.deolasee@gmail.com> writes:

>> A new reloption such as autovacuum_analyze_enabled is what we need.
>
> This seems to me to be a wart that doesn't fix the actual problem ---

IMHO this case is just an example, but I'm sure there would be similar
such examples which may not involve inheritance. For example, say user
has a very large table which is updated very frequently but not in a
way that his query plans will be affected. The user may want to turn
auto analyze in such cases. And given that we allow the user to
control all other parameters, I don't understand why we would not let
him turn it off completely.

There is another problem that I noticed while looking at this case.
The analyze took close to 500sec on a fairly good hardware (40GB RAM,
10K rpm disks on RAID10) because many large child tables were scanned
at once. We analyze all of them in a single transaction. This long
running transaction will cause a lot of bloat for heavily updated
tables since HOT will fail to keep up. I wonder if we should set up
the child tables in the tableoid_list just like we do for toast tables
so that each table is analyzed in its own transaction. This is also
important because partitioning will typically involve very large
tables.

Of course, if we could ever run analyze on a single table in multiple
smaller transactions, that will be even better. But I'm not sure if
thats feasible.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Fri, Feb 1, 2013 at 9:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Pavan Deolasee escribió:
>
>>
>> A new reloption such as autovacuum_analyze_enabled is what we need.
>
> I was thinking in this option just three days ago, so yeah.
>
> I think we also want an option to turn off just vacuum.
>

+1. I think that will be useful too in some situations, especially
when user wants to let autovacuum take care of some tables  and
manually vacuum the rest.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Fri, Feb  1, 2013 at 12:37:21PM -0300, Alvaro Herrera wrote:
> Pavan Deolasee escribió:
> 
> > While looking at this particular case on -general, I realized that there is
> > no way to *only* disable auto-analyze on a table. While one can cheat like
> > what I suggested to the OP by setting threshold very high, I think it will
> > be useful to be able to just off analyze. In this particular case, the OP
> > is inserting and then deleting the same rows from the parent table, thus
> > keeping it almost empty. Of course, he would want to run auto-vacuum on the
> > table to remove the dead rows. Usually auto-analyze would have returned
> > quite fast, especially because we vacuum a table first and then analyze it.
> > But in this case, since the table is a parent of a number of large child
> > tables, we end up analyzing the child tables too, which takes significantly
> > longer time and is quite unnecessary because in this case the activity on
> > the parent table must not have changed any stats for the child tables.
> > 
> > A new reloption such as autovacuum_analyze_enabled is what we need.
> 
> I was thinking in this option just three days ago, so yeah.
> 
> I think we also want an option to turn off just vacuum.

Are these TODO items?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Fri, Feb  1, 2013 at 12:37:21PM -0300, Alvaro Herrera wrote:
>
>> > A new reloption such as autovacuum_analyze_enabled is what we need.
>>
>> I was thinking in this option just three days ago, so yeah.
>>
>> I think we also want an option to turn off just vacuum.
>
> Are these TODO items?
>

I can write a patch in next couple of days if we are willing to accept
for this release. I think it should be fairly easy and non-intrusive.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Fri, Feb 1, 2013 at 12:33 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
> I can write a patch in next couple of days if we are willing to accept
> for this release. I think it should be fairly easy and non-intrusive.

I think it's too late to consider this for 9.3, but I think we should
entertain it for 9.4.

The biggest problem I see is that the naming of the new reloptions
might end up being something kind of unintuitive, like
autovacuum_vacuum_enabled and autovacuum_analyze_enabled.  You need a
degree in PostgreSQLology to understand what that means, but I haven't
got a better idea.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Feb 4, 2013 at 9:05 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Feb 1, 2013 at 12:33 PM, Pavan Deolasee
> <pavan.deolasee@gmail.com> wrote:
>> I can write a patch in next couple of days if we are willing to accept
>> for this release. I think it should be fairly easy and non-intrusive.
>
> I think it's too late to consider this for 9.3, but I think we should
> entertain it for 9.4.
>

Its going to be fairly non

> The biggest problem I see is that the naming of the new reloptions
> might end up being something kind of unintuitive, like
> autovacuum_vacuum_enabled and autovacuum_analyze_enabled.  You need a
> degree in PostgreSQLology to understand what that means, but I haven't
> got a better idea.
>

Yeah, I also thought of those two names. May be autovacuum should have
been called autovacanalyze or something like that. But that's too
late. May be someday we overhaul the maintenance activities and call
it bgmaintainer ? Or have two different threads to do autovacuum and
autoanalyze ?

For now, I can't think of anything better than
autovacuum_vacuum_enabled and autovacuum_analyze_enabled.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
>
> Are these TODO items?
>

BTW, there are couple of TODOs.

1. Analyze should be done  based on the total row churn across the
parent + children.  Looking at the parent only, as we do now, can
result in analyzing too often or too seldom.

2. Auto-analyze should analyze parent and each child table in a
separate transaction.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Tue, Feb  5, 2013 at 01:41:05PM +0530, Pavan Deolasee wrote:
> On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> >
> > Are these TODO items?
> >
> 
> BTW, there are couple of TODOs.
> 
> 1. Analyze should be done  based on the total row churn across the
> parent + children.  Looking at the parent only, as we do now, can
> result in analyzing too often or too seldom.
> 
> 2. Auto-analyze should analyze parent and each child table in a
> separate transaction.

I added a link to this thread on the TODO list under:
Improve autovacuum tuning 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +