[PATCH] vacuumlo: print the number of large objects going to be removed

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[PATCH] vacuumlo: print the number of large objects going to be removed

Timur Birsh
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,
Timur

0001-vacuumlo-print-the-number-of-large-objects-going-to-.patch (2K) Download Attachment
0002-vacuumlo-print-how-many-LOs-removed-in-percent.patch (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Timur Birsh
12.06.2019, 14:31, "Timur Birsh" <[hidden email]>:
> 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.


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Michael Paquier-2
Hi,

On Thu, Jun 13, 2019 at 10:49:46AM +0600, Timur Birsh wrote:
> 12.06.2019, 14:31, "Timur Birsh" <[hidden email]>:
>> Please cc, I am not subscribed to the list.
>
> I have subscribed to the mailing list, there is no need to cc me.

Welcome.  Nice to see that you have subscribed to the lists.

Please note that we have some guidelines regarding the way patches are
submitted:
https://wiki.postgresql.org/wiki/Submitting_a_Patch
Based on what I can see with your patch, things are in good shape on
this side.

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/

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.

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.

Thanks!
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Timur Birsh
Hello Michael,

13.06.2019, 12:11, "Michael Paquier" <[hidden email]>:
> 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


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Michael Paquier-2
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.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Timur Birsh
13.06.2019, 13:57, "Michael Paquier" <[hidden email]>:
> 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,
Timur

0001-Report-the-number-of-large-objects-going-to-be-remov.patch (3K) Download Attachment