[PATCH] vacuumlo: print the number of large objects going to be removed
Hello,
If tables has a lot of rows with large objects (>1_000_000) that
removed throughout the day, it would be useful to know how many
LOs going to be removed.
First patch - print the number of large objects going to be removed,
second patch - print how many LOs removed in percent.
Can anyone please review.
Please cc, I am not subscribed to the list.
Regards,
TimurFrom 8465774c33b200c1a531465acaef85d2d261bb26 Mon Sep 17 00:00:00 2001
From: Timur Birsh
Date: Wed, 12 Jun 2019 04:13:29 +
Subject: [PATCH 1/2] vacuumlo: print the number of large objects going to be
removed
If tables has a lot of rows with large objects (>1_000_000) that
removed throughout the day, it would be useful to know how many
LOs going to be removed.
---
contrib/vacuumlo/vacuumlo.c | 20
1 file changed, 20 insertions(+)
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 73c06a043e..beade1c9c0 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -64,6 +64,7 @@ vacuumlo(const char *database, const struct _param *param)
PGresult *res,
*res2;
char buf[BUFSIZE];
+ long to_delete = 0;
long matched;
long deleted;
int i;
@@ -276,6 +277,25 @@ vacuumlo(const char *database, const struct _param *param)
}
PQclear(res);
+ if (param->verbose)
+ {
+ snprintf(buf, BUFSIZE, "SELECT count(*) FROM vacuum_l");
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ fprintf(stderr, "Failed to get number of large objects "
+ "going to be removed:\n");
+ fprintf(stderr, "%s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ to_delete = strtol(PQgetvalue(res, 0, 0), NULL, 10);
+ PQclear(res);
+ fprintf(stdout, "%ld large objects will be removed\n",
+to_delete);
+ }
+
/*
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
*
--
2.17.1
From 4be55b5b5e566faad441824910b4e49c6b5f5879 Mon Sep 17 00:00:00 2001
From: Timur Birsh
Date: Wed, 12 Jun 2019 05:56:44 +
Subject: [PATCH 2/2] vacuumlo: print how many LOs removed in percent
---
contrib/vacuumlo/vacuumlo.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index beade1c9c0..5d629264f1 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -359,7 +359,8 @@ vacuumlo(const char *database, const struct _param *param)
if (param->verbose)
{
-fprintf(stdout, "\rRemoving lo %6u ", lo);
+fprintf(stdout, "\rRemoving lo %6u\t(%3.f%%)", lo,
+ ((float) deleted / (float) to_delete) * 100);
fflush(stdout);
}
--
2.17.1
Re: [PATCH] vacuumlo: print the number of large objects going to be removed
12.06.2019, 14:31, "Timur Birsh" : > Please cc, I am not subscribed to the list. I have subscribed to the mailing list, there is no need to cc me. Thank you.
Re: [PATCH] vacuumlo: print the number of large objects going to be removed
Hello Michael,
13.06.2019, 12:11, "Michael Paquier" :
> Welcome. Nice to see that you have subscribed to the lists.
Thank you for your explanations!
> Now, if you want to get review for your patch, you should register it
> in what we call the commit fest app, which is here:
> https://commitfest.postgresql.org/23/
Done. Please see https://commitfest.postgresql.org/23/2148/
> Commit fests happen every two months for a duration of one month, and
> the next one which will begin the development cycle of v13 begins on
> the 1st of July. As a basic rule, it is expected that for one patch
> submitted, you should review another patch of equal difficulty to keep
> some balance in the force.
Ok.
> Regarding the patch, there is an argument to be made for reporting a
> rate as well as the actual numbers of deleted and to-delete items.
>
> + if (param->verbose)
> + {
> + snprintf(buf, BUFSIZE, "SELECT count(*) FROM vacuum_l");
> + res = PQexec(conn, buf);
> That part is costly.
Just to be sure, a new command line argument needs to be added for
reporting the numbers? Should it implies --verbose argument?
Thanks,
Timur
Re: [PATCH] vacuumlo: print the number of large objects going to be removed
13.06.2019, 13:57, "Michael Paquier" :
> On Thu, Jun 13, 2019 at 01:25:38PM +0600, Timur Birsh wrote:
>> Just to be sure, a new command line argument needs to be added for
>> reporting the numbers? Should it implies --verbose argument?
>
> Nope. I mean that running a SELECT count(*) can be costly for many
> items.
Understood, thanks.
I found a way to get the number of LOs that will be removed without
the SELECT count(*) - PQcmdTuples(). Please find attached patch v2.
I fixed some indentation in the variable declaration blocks.
There is a database with tables that have a lot of tuples with large objects:
# select count(*) from pg_largeobject_metadata;
count
--
44707424
(1 row)
An application that uses this database from time to time deletes and adds a lot
of rows, it happens that more than 10,000,000 orphaned LOs remain in the
database. Removing such a number of items takes a long time.
I guess, it would be helpful to know how many LOs going to be removed and
report deleted percentage.
Thanks,
TimurFrom 2350a42ec46ab511020060b3f4427848628b0ecb Mon Sep 17 00:00:00 2001
From: Timur Birsh
Date: Fri, 14 Jun 2019 04:31:28 +
Subject: [PATCH] Report the number of large objects going to be removed
---
contrib/vacuumlo/vacuumlo.c | 29 +++--
1 file changed, 19 insertions(+), 10 deletions(-)
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 73c06a043e..77e91ff7ad 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -60,17 +60,18 @@ static void usage(const char *progname);
static int
vacuumlo(const char *database, const struct _param *param)
{
- PGconn *conn;
- PGresult *res,
- *res2;
+ PGconn *conn;
+ PGresult *res,
+ *res2;
char buf[BUFSIZE];
+ long to_delete = 0;
long matched;
long deleted;
- int i;
+ int i;
bool new_pass;
bool success = true;
- static bool have_password = false;
- static char password[100];
+ static bool have_password = false;
+ static char password[100];
/* Note: password can be carried over from a previous call */
if (param->pg_prompt == TRI_YES && !have_password)
@@ -171,6 +172,7 @@ vacuumlo(const char *database, const struct _param *param)
PQfinish(conn);
return -1;
}
+ to_delete = strtol(PQcmdTuples(res), NULL, 10);
PQclear(res);
/*
@@ -221,9 +223,10 @@ vacuumlo(const char *database, const struct _param *param)
for (i = 0; i < PQntuples(res); i++)
{
- char *schema,
- *table,
- *field;
+ char *schema,
+ *table,
+ *field;
+ long excluded;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
@@ -268,7 +271,9 @@ vacuumlo(const char *database, const struct _param *param)
PQfreemem(field);
return -1;
}
+ excluded = strtol(PQcmdTuples(res2), NULL, 10);
PQclear(res2);
+ to_delete -= excluded;
PQfreemem(schema);
PQfreemem(table);
@@ -276,6 +281,9 @@ vacuumlo(const char *database, const struct _param *param)
}
PQclear(res);
+ if (param->verbose)
+ fprintf(stdout, "%ld large objects will be removed\n", to_delete);
+
/*
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
*
@@ -339,7 +347,8 @@ vacuumlo(const char *database, const struct _param *param)
if (param->verbose)
{
-fprintf(stdout, "\rRemoving lo %6u ", lo);
+fprintf(stdout, "\rRemoving lo %6u\t(%3.f%%)", lo,
+ ((float) deleted / (float) to_delete) * 100);
fflush(stdout);
}
--
2.17.1
