Re: Reducing stats collection overhead

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> The first design that comes to mind is that at transaction end
>> (pgstat_report_tabstat() time) we send a stats message only if at least
>> X milliseconds have elapsed since we last sent one, where X is
>> PGSTAT_STAT_INTERVAL or closely related to it. We also make sure to
>> flush stats out before process exit. This approach ensures that in a
>> lots-of-short-transactions scenario, we only need to send one stats
>> message every X msec, not one per query.

> If you're going to make it depend on the timestamp set by transaction
> start, I'm all for it.

Ah, you're worried about not adding an extra gettimeofday() call.
Actually I was going to make it use the transaction-commit timestamp,
which xact.c already does a kernel call for so it can put a timestamp in
the xlog commit or abort record. We don't save that aside at the moment
but easily could.

Doing this would probably mean wanting to convert the timestamps
stored in xlog commit/abort records from time_t to timestamptz;
anyone have a problem with that?

> FWIW, am I reading the code wrong or do we send the number of xact
> commit and rollback multiple times in pgstat_report_one_tabstat, with
> only the first one having non-zero counts? Maybe we could put these
> counters in a separate message to reduce the size of the tabstat
> messages themselves. (It may be that the total impact in bytes is
> minimal, and the added overhead of an additional message is greater?)

Yeah, that design seems fine to me as-is. We'd only be sending multiple
messages if we have more than about 1K of tabstat records, so the
overhead is only 16 bytes out of each additional 1K ... not a lot.

regards, tom lane