pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Parker <dparker@tazznetworks.com>
Cc: pgsql-hackers@postgreSQL.org, Jan Wieck <JanWieck@Yahoo.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Subject: Timing of pgstats updates
Date: Thu, 18 Nov 2004 11:43:12 -0500
Message-ID: <17519.1100796192@sss.pgh.pa.us> (raw)
In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C26BAC3@corpsrv2.tazznetworks.com>
References: <07FDEE0ED7455A48AC42AC2070EDFF7C26BAC3@corpsrv2.tazznetworks.com>

"David Parker" <dparker@tazznetworks.com> writes:
> What I think is happening with the missing pg_statistic entries:
> The install of our application involves a lot of data importing (via
> JDBC) in one large transaction, which can take up to 30 minutes. (I
> realize I left out this key piece of info in my original post...)

> The pg_autovacuum logic is relying on data from pg_stat_all_tables to
> make the decision about running analyze. As far as I can tell, the data
> in this view gets updated outside of the transaction, because I saw the
> numbers growing while I was importing. I saw pg_autovacuum log messages
> for running analyze on several tables, but no statistics data showed up
> for these, I assume because the actual data in the table wasn't yet
> visible to pg_autovacuum because the import transaction had not finished
> yet.

> When the import finished, not all of the tables affected by the import
> were re-visited because they had not bumped up over the threshold again,
> even though the analyze run for those tables had not generated any stats
> because of the still-open transaction.

Bingo.  The per-table activity stats are sent to the collector whenever
the backend waits for a client command.  Given a moderately long
transaction block doing updates, it's not hard at all to imagine that
autovacuum would kick off vacuum and/or analyze while the updating
transaction is still in progress.  The resulting operation is of course
a waste of time.

It'd be trivial to adjust postgres.c so that per-table stats are
only transmitted when we exit the transaction (basically move the
pgstat_report_tabstat call down a couple lines so it's not called if
IsTransactionOrTransactionBlock).

This seems like a good change to me.  Does anyone not like it?

			regards, tom lane



view thread (2+ messages)  latest in thread

Message-ID: <17519.1100796192@sss.pgh.pa.us>
Permalink:  ../17519.1100796192@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17519.1100796192@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dparker@tazznetworks.com, JanWieck@Yahoo.com, matthew@zeut.net
  Subject: Re: Timing of pgstats updates
  In-Reply-To: <17519.1100796192@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox