Re: Autoanalyze and OldestXmin

On Wed, Jun 8, 2011 at 10:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> writes:
> > I first thought that analyze and vacuum can not run concurrently on the
> same
> > table since they take a conflicting lock on the table. So even if we
> ignore
> > the analyze process while calculating the OldestXmin for vacuum, we
> should
> > be fine since we know they are working on different tables. But I see
> > analyze also acquires sample rows from the inherited tables with a
> > non-conflicting lock. I probably do not understand the analyze code well,
> > but is that the reason why we can't ignore analyze snapshot while
> > determining OldestXmin for vacuum ?
>
> The reason why we can't ignore that snapshot is that it's being set for
> the use of user-defined functions, which might do practically anything.
> They definitely could access tables other than the one under analysis.
> (I believe that PostGIS does such things, for example --- it wants to
> look at its auxiliary tables for metadata.)
>
> Also keep in mind that we allow ANALYZE to be run inside a transaction
> block, which might contain other operations sharing the same snapshot.
>
>
Ah, I see. Would there will be benefits if we can do some special handling
for cases where we know that ANALYZE is running outside a transaction block
and that its not going to invoke any user-defined functions ? If user is
running ANALYZE inside a transaction block, he is probably already aware and
ready to handle long-running transaction. But running them under the covers
as part of auto-analyze does not see quite right. The pgbench test already
shows the severe bloat that a long running analyze may cause for small
tables and many wasteful vacuum runs on those tables.

Another idea would be to split the ANALYZE into multiple small transactions,
each taking a new snapshot. That might result in bad statistics if the table
is undergoing huge change, but in that case, the stats will be outdated soon
anyways if we run with a old snapshot. I understand there could be issues
like counting the same tuple twice or more, but would that be a common case
to worry about ?

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com