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

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