pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Julien Rouhaud <rjuju123@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Kuntal Ghosh <kuntalghosh.2007@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: Kirill Bychik <kirill.bychik@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: WAL usage calculation patch
Date: Mon, 6 Apr 2020 11:33:34 +0200
Message-ID: <20200406093334.GJ1206@nol> (raw )
In-Reply-To: <CAA4eK1JCkJFAx+hj3HS_MmM0nvnAphAZz1SubZMngOxNVy23=A@mail.gmail.com >
References: <CAA4eK1KqiWqAgw9ETchcQf-z9ZY4aUN-4QvTh0R26+DpNnTDHg@mail.gmail.com >
<CAA4eK1+sSXvf45x6CbXTEywzXLNcuwWUy-m1rg2KyJKcEXBPvw@mail.gmail.com >
<20200404060303.n2q74lbccjm2swdc@nol >
<CAA4eK1Kep_Wv1ZK+ODLhioPdP8VD4HuHx5d-C2h9s83yriYejg@mail.gmail.com >
<20200404085423.GC1206@nol >
<CAA4eK1JmoDH1zoxAi9BEszVe+5BNquJDN3E-RRADdrYHv3TCRQ@mail.gmail.com >
<20200404092015.GE1206@nol >
<CAA4eK1+OzW92A=nrJqNg6+M5jZ_fdpSAW45tBnFpdWv4035QRQ@mail.gmail.com >
<20200406082307.GI1206@nol >
<CAA4eK1JCkJFAx+hj3HS_MmM0nvnAphAZz1SubZMngOxNVy23=A@mail.gmail.com >
On Mon, Apr 06, 2020 at 02:34:36PM +0530, Amit Kapila wrote:
> On Mon, Apr 6, 2020 at 1:53 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > On Mon, Apr 06, 2020 at 08:55:01AM +0530, Amit Kapila wrote:
> > >
> > > Here, we are not displaying Buffers related data, so why do we think
> > > it is important to display WAL data? I see some point in displaying
> > > Buffers and WAL data in a vacuum (verbose), but I feel it is better to
> > > make a case for both the statistics together rather than just
> > > displaying one and leaving other. I think the other change related to
> > > autovacuum stats seems okay to me.
> >
> > One thing is that the amount of WAL, and more precisely FPW, is quite
> > unpredictable wrt. vacuum and even more anti-wraparound vacuum, so this is IMHO
> > a very useful metric.
> >
>
> I agree but we already have a way via pg_stat_statements to find it if
> the metric is so useful.
>
Agreed.
>
> > That being said I totally agree with you that both
> > should be displayed. Should I send a patch to also expose it?
> >
>
> I think this should be a separate proposal. Let's not add things
> unless they are really essential. We can separately discuss of
> enhancing vacuum verbose for Buffer and WAL usage stats and see if
> others also find that information useful. I think you can send a
> patch by removing the code I mentioned above if you agree. Thanks for
> working on this.
Thanks! v15 attached.
From 9f4cbb1817372628a3fc7b3fffe82ec2a3942f91 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 19 Mar 2020 16:08:47 +0100
Subject: [PATCH v15] Expose WAL usage counters in verbose (auto)vacuum output.
Author: Julien Rouhaud
Reviewed-by: Fuji Masao, Amit Kapila, Dilip Kumar
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 14 +++++++++++++-
1 file changed, 13 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 3ca7f5d136..877512fae4 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -410,6 +410,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
int nindexes;
PGRUsage ru0;
TimestampTz starttime = 0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
long secs;
int usecs;
double read_rate,
@@ -614,6 +616,9 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
TimestampDifference(starttime, endtime, &secs, &usecs);
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+
read_rate = 0;
write_rate = 0;
if ((secs > 0) || (usecs > 0))
@@ -666,7 +671,13 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
(long long) VacuumPageDirty);
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
- appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf, _("system usage: %s\n"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf,
+ _("WAL usage: %ld records, %ld full page writes, "
+ UINT64_FORMAT " bytes"),
+ walusage.wal_records,
+ walusage.wal_num_fpw,
+ walusage.wal_bytes);
ereport(LOG,
(errmsg_internal("%s", buf.data)));
@@ -1727,6 +1738,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.20.1
Attachments:
[text/plain] v15-0001-Expose-WAL-usage-counters-in-verbose-auto-vacuum.patch (2.2K, ../20200406093334.GJ1206@nol/2-v15-0001-Expose-WAL-usage-counters-in-verbose-auto-vacuum.patch)
download | inline diff:
From 9f4cbb1817372628a3fc7b3fffe82ec2a3942f91 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 19 Mar 2020 16:08:47 +0100
Subject: [PATCH v15] Expose WAL usage counters in verbose (auto)vacuum output.
Author: Julien Rouhaud
Reviewed-by: Fuji Masao, Amit Kapila, Dilip Kumar
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 14 +++++++++++++-
1 file changed, 13 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 3ca7f5d136..877512fae4 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -410,6 +410,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
int nindexes;
PGRUsage ru0;
TimestampTz starttime = 0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
long secs;
int usecs;
double read_rate,
@@ -614,6 +616,9 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
TimestampDifference(starttime, endtime, &secs, &usecs);
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+
read_rate = 0;
write_rate = 0;
if ((secs > 0) || (usecs > 0))
@@ -666,7 +671,13 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
(long long) VacuumPageDirty);
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
- appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf, _("system usage: %s\n"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf,
+ _("WAL usage: %ld records, %ld full page writes, "
+ UINT64_FORMAT " bytes"),
+ walusage.wal_records,
+ walusage.wal_num_fpw,
+ walusage.wal_bytes);
ereport(LOG,
(errmsg_internal("%s", buf.data)));
@@ -1727,6 +1738,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.20.1
view thread (130+ messages) latest in thread
Message-ID: <20200406093334.GJ1206@nol>
Permalink: ../20200406093334.GJ1206@nol/
Also on: postgresql.org/message-id/20200406093334.GJ1206@nol
copy link · copy postgr.es · copy git am
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-hackers@postgresql.org
Cc: rjuju123@gmail.com, amit.kapila16@gmail.com, dilipbalaut@gmail.com, kuntalghosh.2007@gmail.com, masao.fujii@oss.nttdata.com, kirill.bychik@gmail.com, thomas.munro@gmail.com
Subject: Re: WAL usage calculation patch
In-Reply-To: <20200406093334.GJ1206@nol>
* 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