From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance
Date: Mon, 19 Feb 2024 18:46:19 +0100
Message-ID: <6ba1f723a0b6af4a1deedbe543c70ba2630b4e09.camel@cybertec.at> (raw)
In-Reply-To: <AM7P189MB1028AADBA8C7650918C7C44C9D512@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
References: <AM7P189MB1028AADBA8C7650918C7C44C9D512@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
On Mon, 2024-02-19 at 16:14 +0000, Lars Aksel Opsahl wrote:
> Then we start testing VACUUM and very simple SQL testing in another window.
>
> We can now show we have performance of "3343.794 ms" and not "0.123 ms", which
> is what we get when we are able to remove dead rows and run a new analyze.
>
> The problem is that as long as the master code is active, we cannot remove
> alle dead rows and that what seems to be killing the performance.
>
> With active I mean in hanging on pg_sleep and remember that this master has
> not created the test table or inserted any data in this test table it self.
>
> Is the expected behavior ?
It is not entirely clear what you are doing, but it seems like you are holding
a database transaction open, and yes, then it is expected behavior that
VACUUM cannot clean up dead rows in the table.
Make sure that your database transactions are short.
Don't use table or row locks to synchronize application threads.
What you could use to synchronize your application threads are advisory locks,
they are not tied to a database transaction.
Yours,
Laurenz Albe
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-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, Lars.Opsahl@nibio.no, pgsql-performance@lists.postgresql.org
Subject: Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance
In-Reply-To: <6ba1f723a0b6af4a1deedbe543c70ba2630b4e09.camel@cybertec.at>
* 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