Re: Thousands of schemas and ANALYZE goes out of memory - Mailing list pgsql-general

Is there a reason the following patch wasn't applied?

---------------------------------------------------------------------------

On Thu, Oct  4, 2012 at 07:14:31PM -0400, Tom Lane wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
> > For the record, the culprit that causes "analyze;" of a database with
> > a large number of small objects to be quadratic in time is
> > "get_tabstat_entry" and it is not fixed for 9.3.
>
> I was a bit surprised by this assertion, as I'd thought that tabstats
> were flushed to the collector at transaction end, and thus that the
> internal transaction boundaries in a VACUUM or ANALYZE should prevent
> the tabstats table from getting unreasonably large.  However, a look
> at the code shows that pgstat_report_stat() is only called when the main
> loop in postgres.c is about to wait for client input.
>
> We could build a lot of infrastructure to try to index the tabstat
> arrays more efficiently ... or we could just do something like the
> attached.
>
> It appears that the next tallest mole in the VACUUM case is
> CleanupTempFiles.  This workload is not creating any temp files, I hope,
> so the implication is that have_pending_fd_cleanup is getting set by
> FileSetTransient (probably from blind writes).  We might want to revisit
> how that works --- particularly since I see no reason that there would
> be any actually-blind writes in this example.  But in any case, that
> innocent looking flag setting can result in a lot of work.
>
>             regards, tom lane
>

> diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
> index 14d1c08..a5d00fc 100644
> *** a/src/backend/commands/vacuum.c
> --- b/src/backend/commands/vacuum.c
> *************** vacuum(VacuumStmt *vacstmt, Oid relid, b
> *** 251,256 ****
> --- 251,257 ----
>                   {
>                       PopActiveSnapshot();
>                       CommitTransactionCommand();
> +                     pgstat_report_stat(false);
>                   }
>               }
>           }
> *************** vacuum_rel(Oid relid, VacuumStmt *vacstm
> *** 1071,1080 ****
>           relation_close(onerel, NoLock);
>
>       /*
> !      * Complete the transaction and free all temporary memory used.
>        */
>       PopActiveSnapshot();
>       CommitTransactionCommand();
>
>       /*
>        * If the relation has a secondary toast rel, vacuum that too while we
> --- 1072,1083 ----
>           relation_close(onerel, NoLock);
>
>       /*
> !      * Complete the transaction and free all temporary memory used.  Also,
> !      * flush table-access statistics to the stats collector after each table.
>        */
>       PopActiveSnapshot();
>       CommitTransactionCommand();
> +     pgstat_report_stat(false);
>
>       /*
>        * If the relation has a secondary toast rel, vacuum that too while we

>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +