Обсуждение: FPW stats?

Hi,

Recently I've heard people complaining that Postgres doesn't expose any
statistics about how many full page writes happened during some time frame.
Indeed, I couldn't find any easy way to do so, and judging from my
understanding of xloginsert.c it actually can be done per database with the
attached poc patch.

I guess it can be implemented in a more effective and optimized way, but with
what I have right now first naive pgbench tests show that slowdown is about 3%.
Before I'll dig into it more, it would be nice to hear your opinion about this
idea -  does it make sense to have something like this?

On Wed, May 02, 2018 at 12:22:34PM +0200, Dmitry Dolgov wrote:
> Recently I've heard people complaining that Postgres doesn't expose any
> statistics about how many full page writes happened during some time
> frame.

pg_waldump --stats?

> I guess it can be implemented in a more effective and optimized way, but with
> what I have right now first naive pgbench tests show that slowdown is about 3%.
> Before I'll dig into it more, it would be nice to hear your opinion about this
> idea -  does it make sense to have something like this?

The bar to add new fields into pgstat structures is usually quite high
depending on the location where those are added.  For example not so
long ago there was a patch discussed about adding more fields to
PgStat_StatTabEntry, which has been rejected as pgstat can be a problem
for users with many tables.  See here:
https://www.postgresql.org/message-id/1323.1511624064%40sss.pgh.pa.us

Your patch adds a new field to PgStat_StatDBEntry?  Wouldn't you
increase the bottleneck of deployments with many databases?  What's
actually your use case?
--
Michael

> On 2 May 2018 at 13:10, Michael Paquier <michael@paquier.xyz> wrote:
> On Wed, May 02, 2018 at 12:22:34PM +0200, Dmitry Dolgov wrote:
>> Recently I've heard people complaining that Postgres doesn't expose any
>> statistics about how many full page writes happened during some time
>> frame.
>
> pg_waldump --stats?

Yep, pg_waldump is the only option so far, but I thought about something that
will directly expose this info.

> The bar to add new fields into pgstat structures is usually quite high
> depending on the location where those are added.  For example not so
> long ago there was a patch discussed about adding more fields to
> PgStat_StatTabEntry, which has been rejected as pgstat can be a problem
> for users with many tables.  See here:
> https://www.postgresql.org/message-id/1323.1511624064%40sss.pgh.pa.us
>
> Your patch adds a new field to PgStat_StatDBEntry?  Wouldn't you
> increase the bottleneck of deployments with many databases?  What's
> actually your use case?

This was discussed mostly in the context of benchmarking and understanding IO
for different workloads. I actually never realized that adding a new stats
field can have significant impact in those cases when there are too many
databases, and yeah, I'm afraid it may be not justified in this context.

On Wed, May 2, 2018 at 7:10 AM, Michael Paquier <michael@paquier.xyz> wrote:
> Your patch adds a new field to PgStat_StatDBEntry?  Wouldn't you
> increase the bottleneck of deployments with many databases?  What's
> actually your use case?

I'm a little doubtful about whether this particular thing is generally
useful but I think the bar for adding a field to PgStat_StatDBEntry is
probably a lot lower than for a per-table counter.  I think adding
table-level counters is basically not happening without some kind of
rework of the infrastructure; whereas adding db-level counters seems
like it might be OK if we were convinced that they had real value.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company