pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTiming of pgstats updates
2+ messages / 2 participants
[nested] [flat]
* Timing of pgstats updates
@ 2004-11-18 16:43 Tom Lane <tgl@sss.pgh.pa.us>
2004-11-18 19:29 ` Re: Timing of pgstats updates Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Lane @ 2004-11-18 16:43 UTC (permalink / raw)
To: David Parker <dparker@tazznetworks.com>; +Cc: pgsql-hackers@postgreSQL.org, Jan Wieck <JanWieck@Yahoo.com>; Matthew T. O'Connor <matthew@zeut.net>
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Timing of pgstats updates
2004-11-18 16:43 Timing of pgstats updates Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-11-18 19:29 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jan Wieck @ 2004-11-18 19:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Parker <dparker@tazznetworks.com>; pgsql-hackers@postgreSQL.org, "Matthew T. O'Connor" <matthew@zeut.net>
On 11/18/2004 11:43 AM, Tom Lane wrote:
> "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
Sounds reasonable here.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-11-18 19:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-18 16:43 Timing of pgstats updates Tom Lane <tgl@sss.pgh.pa.us>
2004-11-18 19:29 ` Jan Wieck <JanWieck@Yahoo.com>
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