agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v13 4/4] Expose WAL usage counters in verbose (auto)vacuum output. 10+ messages / 2 participants [nested] [flat]
* [PATCH v13 4/4] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index cc7e8521a5..735087dd74 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))); @@ -758,6 +769,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1727,6 +1740,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page writes, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_num_fpw, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --dFWYt1i2NyOo1oI9 Content-Type: application/x-sh Content-Disposition: attachment; filename="wal_test.sh" Content-Transfer-Encoding: quoted-printable #!/bin/bash=0A=0Aset -e=0A=0APSQL=3D"psql -Xc"=0ABASE_LINES=3D1000=0ASCALES= =3D(1 10 100 1000)=0AMIN_WORKERS=3D0=0AMAX_WORKERS=3D8=0A=0Acleanup() {=0A = ${PSQL} "VACUUM;"=0A ${PSQL} "CHECKPOINT;"=0A ${PSQL} "SELECT pg_s= witch_wal();"=0A}=0A=0A${PSQL} "ALTER SYSTEM SET max_parallel_maintenance_w= orkers =3D ${MAX_WORKERS};"=0A${PSQL} "SELECT pg_reload_conf();"=0Aret=3D$(= psql -AXtc "SELECT current_setting('max_parallel_maintenance_workers')::int= <=3D current_setting('max_worker_processes')::int;")=0A=0Aif [[ ${ret} != =3D "t" ]]; then=0A echo "Unexpected: ${ret}"=0A exit 1=0Afi=0A=0A${P= SQL} "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"=0A${PSQL} "SELECT= pg_stat_statements_reset();"=0A=0Afor SCALE in ${SCALES[@]}; do=0A NB= =3D$((${BASE_LINES} * ${SCALE}))=0A echo "Setup with scale ${SCALE} (${N= B} lines)"=0A ${PSQL} "DROP TABLE IF EXISTS t1;"=0A ${PSQL} "CREATE T= ABLE t1 (id integer);"=0A ${PSQL} "INSERT INTO t1 SELECT generate_series= (1, ${NB});"=0A=0A for PASS in "a" "b"; do=0A for WORKER in $(seq= ${MIN_WORKERS} ${MAX_WORKERS}); do=0A echo "Test with ${WORKER}= workers, pass ${PASS}"=0A IDXNAME=3D$(printf "t1_idx_s%03d_p${P= ASS}_w${WORKER}" ${SCALE})=0A ${PSQL} "ALTER TABLE t1 SET (paral= lel_workers =3D ${WORKER});"=0A cleanup=0A ${PSQL} "C= REATE INDEX ${IDXNAME} ON t1 (id);"=0A done=0A done=0Adone=0A=0Ae= cho "Summarry of indexes size:"=0A${PSQL} "SELECT pg_relation_size(oid), st= ring_agg(relname, ', ') FROM pg_class WHERE relname LIKE 't1_idx%' GROUP BY= 1"=0A=0Aecho "CREATE INDEX WAL stats:"=0A${PSQL} "SELECT query, wal_bytes,= wal_records, wal_num_fpw FROM pg_stat_statements WHERE query ILIKE '%creat= e index%' ORDER BY query;"=0A=0Aecho "INSERT WAL testing"=0A${PSQL} "SELECT= pg_stat_statements_reset();"=0A=0Afor SCALE in ${SCALES[@]}; do=0A NB= =3D$((${BASE_LINES} * ${SCALE}))=0A echo "INSERT test with scale ${SCALE= } (${NB} lines)"=0A for PASS in "a" "b"; do=0A TBLNAME=3D$(printf= "t_%03d_${PASS}" ${SCALE})=0A ${PSQL} "DROP TABLE IF EXISTS ${TBLNA= ME};"=0A ${PSQL} "CREATE TABLE ${TBLNAME} (id integer);"=0A c= leanup=0A ${PSQL} "INSERT INTO ${TBLNAME} SELECT generate_series(1, = ${NB});"=0A done=0Adone=0A=0Aecho "INSERT WAL stats:"=0A${PSQL} "SELECT = query, wal_bytes, wal_records, wal_num_fpw FROM pg_stat_statements WHERE qu= ery ILIKE '%INSERT INTO%' ORDER BY query;"=0A --dFWYt1i2NyOo1oI9-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v11 4/4] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index cc7e8521a5..735087dd74 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))); @@ -758,6 +769,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1727,6 +1740,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page writes, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_num_fpw, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --WYTEVAkct0FjGQmd-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v6 3/3] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) --- src/backend/access/heap/vacuumlazy.c | 29 +++++++++++++++++++++++++++- 1 file changed, 28 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 03c43efc32..32e6023738 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -65,6 +65,7 @@ #include "commands/dbcommands.h" #include "commands/progress.h" #include "commands/vacuum.h" +#include "executor/instrument.h" #include "miscadmin.h" #include "optimizer/paths.h" #include "pgstat.h" @@ -381,6 +382,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, @@ -569,6 +572,12 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params, TimestampDifference(starttime, endtime, &secs, &usecs); + walusage.wal_records = pgWalUsage.wal_records - + walusage_start.wal_records; + walusage.wal_fp_records = pgWalUsage.wal_fp_records - + walusage_start.wal_fp_records; + walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes; + read_rate = 0; write_rate = 0; if ((secs > 0) || (usecs > 0)) @@ -620,7 +629,12 @@ 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 records, %ld bytes"), + walusage.wal_records, + walusage.wal_fp_records, + walusage.wal_bytes); ereport(LOG, (errmsg_internal("%s", buf.data))); @@ -713,6 +727,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1690,6 +1706,17 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + walusage.wal_records = pgWalUsage.wal_records - + walusage_start.wal_records; + walusage.wal_fp_records = pgWalUsage.wal_fp_records - + walusage_start.wal_fp_records; + walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes; + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, %ld WAL bytes\n"), + walusage.wal_records, + walusage.wal_fp_records, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --ftEhullJWpWg/VHq-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v7 4/4] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 25 ++++++++++++++++++++++++- 1 file changed, 24 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 03c43efc32..ca4f03f551 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -65,6 +65,7 @@ #include "commands/dbcommands.h" #include "commands/progress.h" #include "commands/vacuum.h" +#include "executor/instrument.h" #include "miscadmin.h" #include "optimizer/paths.h" #include "pgstat.h" @@ -381,6 +382,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, @@ -569,6 +572,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)) @@ -620,7 +626,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 records, " + UINT64_FORMAT " bytes"), + walusage.wal_records, + walusage.wal_fpw_records, + walusage.wal_bytes); ereport(LOG, (errmsg_internal("%s", buf.data))); @@ -713,6 +725,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1690,6 +1704,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_fpw_records, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --uAKRQypu60I7Lcqm-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v8 4/4] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 25 ++++++++++++++++++++++++- 1 file changed, 24 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 9726f69629..55df857ff7 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -65,6 +65,7 @@ #include "commands/dbcommands.h" #include "commands/progress.h" #include "commands/vacuum.h" +#include "executor/instrument.h" #include "miscadmin.h" #include "optimizer/paths.h" #include "pgstat.h" @@ -401,6 +402,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, @@ -622,6 +625,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)) @@ -673,7 +679,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 records, " + UINT64_FORMAT " bytes"), + walusage.wal_records, + walusage.wal_fpw_records, + walusage.wal_bytes); ereport(LOG, (errmsg_internal("%s", buf.data))); @@ -765,6 +777,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1744,6 +1758,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_fpw_records, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --DBIVS5p969aUjpLe-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v15] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) 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 --lkTb+7nhmha7W+c3-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v9 6/6] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 27e163f5b3..0a74f63856 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -409,6 +409,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, @@ -613,6 +615,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)) @@ -665,7 +670,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))); @@ -757,6 +768,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1726,6 +1739,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page writes, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_num_fpw, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --3MwIy2ne0vdjdPXF-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v10 6/6] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 24 +++++++++++++++++++++++- src/backend/commands/explain.c | 2 +- 2 files changed, 24 insertions(+), 2 deletions(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index cc7e8521a5..735087dd74 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))); @@ -758,6 +769,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1727,6 +1740,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page writes, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_num_fpw, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index b05b55979b..f7f1c3efce 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -3105,7 +3105,7 @@ show_wal_usage(ExplainState *es, const WalUsage *usage) { ExplainPropertyInteger("WAL records", NULL, usage->wal_records, es); - ExplainPropertyInteger("WAL full page records", NULL, + ExplainPropertyInteger("WAL full page writes", NULL, usage->wal_num_fpw, es); ExplainPropertyUInteger("WAL bytes", NULL, usage->wal_bytes, es); -- 2.20.1 --BXVAT5kNtrzKuDFl-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v12 4/4] Expose WAL usage counters in verbose (auto)vacuum output. @ 2020-03-19 15:08 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw) Author: Julien Rouhaud Reviewed-by: Fuji Masao Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index cc7e8521a5..735087dd74 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))); @@ -758,6 +769,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, IndexBulkDeleteResult **indstats; int i; PGRUsage ru0; + WalUsage walusage_start = pgWalUsage; + WalUsage walusage = {0, 0, 0}; Buffer vmbuffer = InvalidBuffer; BlockNumber next_unskippable_block; bool skipping_blocks; @@ -1727,6 +1740,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, "%u pages are entirely empty.\n", empty_pages), empty_pages); + + memset(&walusage, 0, sizeof(WalUsage)); + WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start); + appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page writes, " + UINT64_FORMAT " WAL bytes\n"), + walusage.wal_records, + walusage.wal_num_fpw, + walusage.wal_bytes); + appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0)); ereport(elevel, -- 2.20.1 --O3RTKUHj+75w1tg5-- ^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH v15 4/8] Row pattern recognition patch (planner). @ 2024-03-28 10:30 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Tatsuo Ishii @ 2024-03-28 10:30 UTC (permalink / raw) --- src/backend/optimizer/plan/createplan.c | 24 +++++++++++++++----- src/backend/optimizer/plan/planner.c | 3 +++ src/backend/optimizer/plan/setrefs.c | 27 ++++++++++++++++++++++- src/backend/optimizer/prep/prepjointree.c | 4 ++++ src/include/nodes/plannodes.h | 19 ++++++++++++++++ 5 files changed, 71 insertions(+), 6 deletions(-) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 5f479fc56c..fe51ad351e 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -287,9 +287,11 @@ static WindowAgg *make_windowagg(List *tlist, Index winref, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations, int frameOptions, Node *startOffset, Node *endOffset, Oid startInRangeFunc, Oid endInRangeFunc, - Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, - List *runCondition, List *qual, bool topWindow, - Plan *lefttree); + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, List *runCondition, + RPSkipTo rpSkipTo, List *patternVariable, List *patternRegexp, + List *defineClause, + List *defineInitial, + List *qual, bool topWindow, Plan *lefttree); static Group *make_group(List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations, Plan *lefttree); @@ -2699,6 +2701,11 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) wc->inRangeAsc, wc->inRangeNullsFirst, wc->runCondition, + wc->rpSkipTo, + wc->patternVariable, + wc->patternRegexp, + wc->defineClause, + wc->defineInitial, best_path->qual, best_path->topwindow, subplan); @@ -6627,8 +6634,10 @@ make_windowagg(List *tlist, Index winref, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations, int frameOptions, Node *startOffset, Node *endOffset, Oid startInRangeFunc, Oid endInRangeFunc, - Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, - List *runCondition, List *qual, bool topWindow, Plan *lefttree) + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, List *runCondition, + RPSkipTo rpSkipTo, List *patternVariable, List *patternRegexp, List *defineClause, + List *defineInitial, + List *qual, bool topWindow, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); Plan *plan = &node->plan; @@ -6654,6 +6663,11 @@ make_windowagg(List *tlist, Index winref, node->inRangeAsc = inRangeAsc; node->inRangeNullsFirst = inRangeNullsFirst; node->topWindow = topWindow; + node->rpSkipTo = rpSkipTo, + node->patternVariable = patternVariable; + node->patternRegexp = patternRegexp; + node->defineClause = defineClause; + node->defineInitial = defineInitial; plan->targetlist = tlist; plan->lefttree = lefttree; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 38d070fa00..82c370c4b4 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -869,6 +869,9 @@ subquery_planner(PlannerGlobal *glob, Query *parse, wc->runCondition = (List *) preprocess_expression(root, (Node *) wc->runCondition, EXPRKIND_TARGET); + wc->defineClause = (List *) preprocess_expression(root, + (Node *) wc->defineClause, + EXPRKIND_TARGET); } parse->limitOffset = preprocess_expression(root, parse->limitOffset, diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 42603dbc7c..f7abb2be96 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -210,7 +210,6 @@ static List *set_windowagg_runcondition_references(PlannerInfo *root, List *runcondition, Plan *plan); - /***************************************************************************** * * SUBPLAN REFERENCES @@ -2455,6 +2454,32 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset) NRM_EQUAL, NUM_EXEC_QUAL(plan)); + /* + * Modifies an expression tree in each DEFINE clause so that all Var + * nodes's varno refers to OUTER_VAR. + */ + if (IsA(plan, WindowAgg)) + { + WindowAgg *wplan = (WindowAgg *) plan; + + if (wplan->defineClause != NIL) + { + foreach(l, wplan->defineClause) + { + TargetEntry *tle = (TargetEntry *) lfirst(l); + + tle->expr = (Expr *) + fix_upper_expr(root, + (Node *) tle->expr, + subplan_itlist, + OUTER_VAR, + rtoffset, + NRM_EQUAL, + NUM_EXEC_QUAL(plan)); + } + } + } + pfree(subplan_itlist); } diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 300691cc4d..3220072a51 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -2147,6 +2147,10 @@ perform_pullup_replace_vars(PlannerInfo *root, if (wc->runCondition != NIL) wc->runCondition = (List *) pullup_replace_vars((Node *) wc->runCondition, rvcontext); + + if (wc->defineClause != NIL) + wc->defineClause = (List *) + pullup_replace_vars((Node *) wc->defineClause, rvcontext); } if (parse->onConflict) { diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 7f3db5105d..b0e50ae886 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -20,6 +20,7 @@ #include "lib/stringinfo.h" #include "nodes/bitmapset.h" #include "nodes/lockoptions.h" +#include "nodes/parsenodes.h" #include "nodes/primnodes.h" @@ -1096,6 +1097,24 @@ typedef struct WindowAgg /* nulls sort first for in_range tests? */ bool inRangeNullsFirst; + /* Row Pattern Recognition AFTER MACH SKIP clause */ + RPSkipTo rpSkipTo; /* Row Pattern Skip To type */ + + /* Row Pattern PATTERN variable name (list of String) */ + List *patternVariable; + + /* + * Row Pattern RPATTERN regular expression quantifier ('+' or ''. list of + * String) + */ + List *patternRegexp; + + /* Row Pattern DEFINE clause (list of TargetEntry) */ + List *defineClause; + + /* Row Pattern DEFINE variable initial names (list of String) */ + List *defineInitial; + /* * false for all apart from the WindowAgg that's closest to the root of * the plan -- 2.25.1 ----Next_Part(Thu_Mar_28_19_59_25_2024_076)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v15-0005-Row-pattern-recognition-patch-executor.patch" ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-03-28 10:30 UTC | newest] Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-19 15:08 [PATCH v13 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v11 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v8 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v6 3/3] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v7 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v15] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v9 6/6] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v10 6/6] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2020-03-19 15:08 [PATCH v12 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <[email protected]> 2024-03-28 10:30 [PATCH v15 4/8] Row pattern recognition patch (planner). Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox