From 8663a1a0c40142764d759bf375f3f7192d7c16e2 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 19 Dec 2018 20:15:39 +0000 Subject: [PATCH v1 4/4] Add --min-relation-size option to vacuumdb. --- doc/src/sgml/ref/vacuumdb.sgml | 21 +++++++++++++++++++++ src/bin/scripts/t/100_vacuumdb.pl | 9 ++++++++- src/bin/scripts/vacuumdb.c | 33 ++++++++++++++++++++++++++++++--- 3 files changed, 59 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 94e8aac268..0fbccf310e 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -178,6 +178,27 @@ PostgreSQL documentation + + + + + Only execute the vacuum or analyze commands on tables with a total size + of at least size megabytes. + + + Note that this option cannot be used in conjunction with the + option. + + + + This option is available for servers running PostgreSQL 8.1 and later. + If specified on a server running an older version of PostgreSQL, this + option is silently ignored. + + + + + diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 45f79b244e..982195817e 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -3,7 +3,7 @@ use warnings; use PostgresNode; use TestLib; -use Test::More tests => 30; +use Test::More tests => 33; program_help_ok('vacuumdb'); program_version_ok('vacuumdb'); @@ -75,3 +75,10 @@ $node->issues_sql_like( [ 'vacuumdb', '--min-mxid-age=123456789', 'postgres' ], qr/AND greatest\(mxid_age\(c.relminmxid\), mxid_age\(t.relminmxid\)\) >= 123456789/, 'vacuumdb --min-mxid-age'); +$node->command_fails( + [qw|vacuumdb -Zt funcidx --min-relation-size 8192|], + 'specified table with --min-relation-size option'); +$node->issues_sql_like( + [ 'vacuumdb', '--min-relation-size=8192', 'postgres' ], + qr/AND pg_total_relation_size\(c.oid\) >= 8589934592/, + 'vacuumdb --min-relation-size'); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index a2622fab24..243126bbeb 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -43,6 +43,7 @@ typedef struct vacuumingOptions bool skip_locked; int min_xid_age; int min_mxid_age; + long min_rel_size_bytes; } vacuumingOptions; @@ -116,6 +117,7 @@ main(int argc, char *argv[]) {"skip-locked", no_argument, NULL, 4}, {"min-xid-age", required_argument, NULL, 5}, {"min-mxid-age", required_argument, NULL, 6}, + {"min-relation-size", required_argument, NULL, 7}, {NULL, 0, NULL, 0} }; @@ -240,6 +242,15 @@ main(int argc, char *argv[]) exit(1); } break; + case 7: + vacopts.min_rel_size_bytes = (long) atoi(optarg) * 1024L * 1024L; + if (vacopts.min_rel_size_bytes <= 0) + { + fprintf(stderr, _("%s: minimum relation size must be at least 1\n"), + progname); + exit(1); + } + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); @@ -288,6 +299,13 @@ main(int argc, char *argv[]) exit(1); } + if (tables.head != NULL && vacopts.min_rel_size_bytes != 0) + { + fprintf(stderr, _("%s: cannot vacuum specific tables in conjunction with \"%s\"\n"), + progname, "--min-relation-size"); + exit(1); + } + setup_cancel_handler(); /* Avoid opening extra connections. */ @@ -386,6 +404,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, bool parallel = concurrentCons > 1; bool filter_by_xid_age; bool filter_by_mxid_age; + bool filter_by_rel_size; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", @@ -416,20 +435,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, /* * Before 7.2, age() did not exist. Before 9.5, mxid_age() did not exist. + * Before 8.1, pg_total_relation_size() did not exist. */ filter_by_xid_age = vacopts->min_xid_age != 0 && PQserverVersion(conn) >= 70200; filter_by_mxid_age = vacopts->min_mxid_age != 0 && PQserverVersion(conn) >= 90500; + filter_by_rel_size = vacopts->min_rel_size_bytes != 0 && PQserverVersion(conn) >= 80100; /* * If a table list is not provided and we're using multiple connections, * prepare the list of tables by querying the catalogs. * * If a table list is not provided and we're using one connection, prepare - * the list of tables by querying the catalogs only if --min-xid-age or - * --min-mxid-age is specified. + * the list of tables by querying the catalogs only if --min-xid-age, + * --min-mxid-age, or --min-relation-size is specified. */ if ((parallel && (!tables || !tables->head)) || - (!parallel && (filter_by_xid_age || filter_by_mxid_age))) + (!parallel && (filter_by_xid_age || filter_by_mxid_age || filter_by_rel_size))) { PQExpBufferData buf; PQExpBufferData catalog_query; @@ -456,6 +477,11 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, " AND greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) >= %d\n", vacopts->min_mxid_age); + if (filter_by_rel_size) + appendPQExpBuffer(&catalog_query, + " AND pg_total_relation_size(c.oid) >= %ld\n", + vacopts->min_rel_size_bytes); + appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;"); res = executeQuery(conn, catalog_query.data, progname, echo); termPQExpBuffer(&catalog_query); @@ -1087,6 +1113,7 @@ help(const char *progname) printf(_(" -F, --freeze freeze row transaction information\n")); printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n")); printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n")); + printf(_(" --min-relation-size=SIZE miminum size of tables to vacuum, in megabytes\n")); printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" --skip-locked skip relations that cannot be immediately locked\n")); -- 2.16.5