Re: Thousands of schemas and ANALYZE goes out of memory

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(dot)janes(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +