Autovacuum of independent tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Autovacuum of independent tables

Michael Holzman
Hi,

I have two applications A and B. A runs SELECT statements only and only on tableA. B actively updates tableB, A never looks into tableB. B has nothing to do with tableA.

Still, if A is inside a long running transaction, autovacuum does not handle tableB. Why is it so?

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Pavel Stehule
Hi

út 8. 9. 2020 v 9:32 odesílatel Michael Holzman <[hidden email]> napsal:
Hi,

I have two applications A and B. A runs SELECT statements only and only on tableA. B actively updates tableB, A never looks into tableB. B has nothing to do with tableA.

Still, if A is inside a long running transaction, autovacuum does not handle tableB. Why is it so?

autovacuum does cleaning of changes related to finished transactions. It does nothing if possible dead tuples are assigned to open transactions.

Regards

Pavel
 

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman

On Tue, Sep 8, 2020 at 10:46 AM Pavel Stehule wrote:

autovacuum does cleaning of changes related to finished transactions. It does nothing if possible dead tuples are assigned to open transactions.

This is the point.
Autovacuum does not clean dead tuples of closed transactions in tableB while there is an open transaction on tableA.
But the tables have nothing in common. They are handled by separate applications and there are no transactions that touch both tables simultaneously.
Why does autovacuum create an artificial dependency on the tables?

--
Regards,
    Michael Holzman
 
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Paquier-2
On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote:
> Autovacuum does not clean dead tuples of closed transactions in tableB
> while there is an open transaction on tableA.
> But the tables have nothing in common. They are handled by separate
> applications and there are no transactions that touch both tables
> simultaneously.
> Why does autovacuum create an artificial dependency on the tables?

This is called MVCC, which applies to a session as a whole.  The point
here is that even if your application knows that only tableA is used
by a given transaction, Postgres cannot know that, as it could be
possible that data from tableB is needed in this same transaction, so
old versions of the rows from tableB matching with the snapshot hold
by this long-running transaction still have to be around.
--
Michael

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

Re: Autovacuum of independent tables

Michael Holzman


On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote:

This is called MVCC, which applies to a session as a whole.  The point
here is that even if your application knows that only tableA is used
by a given transaction, Postgres cannot know that, as it could be
possible that data from tableB is needed in this same transaction, so
old versions of the rows from tableB matching with the snapshot hold
by this long-running transaction still have to be around.

 Yes, I thought so. I just hoped there may be a workaround decoupling the tables.
Thanks.

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Pavel Stehule


út 8. 9. 2020 v 10:42 odesílatel Michael Holzman <[hidden email]> napsal:


On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote:

This is called MVCC, which applies to a session as a whole.  The point
here is that even if your application knows that only tableA is used
by a given transaction, Postgres cannot know that, as it could be
possible that data from tableB is needed in this same transaction, so
old versions of the rows from tableB matching with the snapshot hold
by this long-running transaction still have to be around.

 Yes, I thought so. I just hoped there may be a workaround decoupling the tables.
Thanks.

You can try to reduce length of transactions, if possible.

Regards

Pavel


 

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman


On Tue, Sep 8, 2020 at 12:28 PM Pavel Stehule wrote:

You can try to reduce length of transactions, if possible.


This is the problem. A and B were developed for Oracle where SELECT does not open a transaction. We moved them to PG and now we have to very accurately add COMMITs without breaking the flow. It is quite a complex thing. I hoped we can avoid that.

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Ravi__Krishna

This is the problem. A and B were developed for Oracle where SELECT does not open a transaction. We moved them to PG
and now we have to very accurately add COMMITs without breaking the flow. It is quite a complex thing. I hoped we can
avoid that.

 

Interesting. Are you telling the Oracle version of the code had no intermittent COMMIT and relied on one
final COMMIT at the end. Even in Oracle developers must have planned for commit since a long running
open transaction can lead to “snapshot too old” error.

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman


On Tue, Sep 8, 2020 at 1:52 PM Ravi Krishna wrote:
 

Interesting. Are you telling the Oracle version of the code had no intermittent COMMIT and relied on one
final COMMIT at the end. Even in Oracle developers must have planned for commit since a long running
open transaction can lead to “snapshot too old” error.

Yes, I am saying just that. With one important clarification: there were no transactions as SELECT does not open them and the application does not change anything on that connection.
So, no 'snapshot too old' and no COMMITs.

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Ravi__Krishna

>>Interesting. Are you telling the Oracle version of the code had no

>>intermittent COMMIT and relied on one final COMMIT at the end. Even

>>in Oracle developers must have planned for commit since a long running

>>open transaction can lead to “snapshot too old” error.

>Yes, I am saying just that. With one important clarification: there were

>no transactions as SELECT does not open them and the application does not

>change anything on that connection. So, no 'snapshot too old' and no COMMITs.

 

It's been a while since I worked with Oracle as a developer.  But my understanding

is that even a read-only transaction, like the one you described above, requires

a point in time consistent image of the database. This would imply that if your

transaction runs for a long time and meanwhile other DML sessions change lot of blocks,

resulting in undo tablespace getting totally turned over, then Oracle can no longer

gurantee PIT consistent view of the database to your session and barf out with

snapshot-too-old error.

 

I have no way of confirming this and I am writing this based on my limited experience

with oracle.  So I may be wrong.

 

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Ravi__Krishna

This is assuming other sessions change the same block your session is trying to read.

=== 

It's been a while since I worked with Oracle as a developer.  But my understanding

is that even a read-only transaction, like the one you described above, requires

a point in time consistent image of the database. This would imply that if your

transaction runs for a long time and meanwhile other DML sessions change lot of blocks,

resulting in undo tablespace getting totally turned over, then Oracle can no longer

gurantee PIT consistent view of the database to your session and barf out with

snapshot-too-old error.

 

I have no way of confirming this and I am writing this based on my limited experience

with oracle.  So I may be wrong.


 

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Magnus Hagander-2
In reply to this post by Michael Holzman
(Please don't drop the mailinglist from CC, as others are likely interested in the responses)

On Tue, Sep 8, 2020 at 3:06 PM Michael Holzman <[hidden email]> wrote:


On Tue, Sep 8, 2020 at 3:03 PM Magnus Hagander wrote:
A PostgreSQL SELECT does *not* open a transaction past the end of the statement, if it's run independently on a connection.
This sounds like you are using a client on PostgreSQL that uses an "autocommit off" mode, since that's the only case where you'd need to add COMMITs (or ROLLBACKs) to close a transaction after a SELECT. 

Yes, this is correct. We do not use autocommit. Everything is controlled explicitly. We run quite complex multi-statement multi-table transactions and cannot work with "autocommit on".

That is not what autocommit means.

Whether you have autocommit on or off, you can *always* control things explicitly. And you can certainly run "multi-statement transactions" in autocommit on -- in fact, it's what most people do since it's the default configuration of the system (and I don't see why multi-table would even be relevant).

Autocommit on/off only controls what happens when you *don't* control things explicitly.

 
Therefore, this is what we have

> psql
psql (11.2)
Type "help" for help.

pg-11.2 rw => COMMIT;
WARNING:  25P01: there is no transaction in progress
LOCATION:  EndTransactionBlock, xact.c:3675
COMMIT
Time: 0.745 ms
pg-11.2 rw => select 2*2;
 ?column?
----------
        4
(1 row)

Time: 0.347 ms
pg-11.2 rw => COMMIT;
COMMIT
Time: 0.525 ms

The first COMMIT (immediately after connect) fails as there is no transaction.
The second one works as even this SELECT opened one. We have a transaction (and a snapshot) when no table is touched!

So just to be clear,  here is how PostgreSQL behaves by default:

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT
postgres=# select 2*2;
 ?column?
----------
        4
(1 row)

postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT



But yes, if you explicitly ask that a query shall keep a transaction open across multiple statements, by turning off autocommit, it will. 

In fact, *PostgreSQL* will always behave that way. The *psql client* will behave differently depending on how you configure it, and the same will of course apply to any other client that you have. In the example above, psql.


You cannot both have a transaction existing and not existing at the same time. You do have to separate the idea of transactions from snapshots though, as they can differ quite a bit depending on isolation levels.


> And how much a running transaction blocks autovacuum is also dependent on what isolation level you're running it in. In the default isolation level, a snapshot is taken for each individual select, so does not block vacuuming past the end of the individual select. Higher isolation levels will.

> We use default isolation mode and we proved that SELECTs block autovacuum. As soon as we added COMMITs after SELECTS in several places (not all as we still have not fixed all the code), autovacuum started working properly in the fixed flows.


As I said yes, a running SELECT will, because of the snapshot. An open transaction will not, past the individual select, because a new snapshot is taken for each SELECT.

If you have an open transaction that runs regular selects but as separate queries then it will not block autovacuum, unless it also does something else. 

Of course if it's a big query that runs the whole time it will, but then there would also not be a way to "add commits" into the middle of it, so clearly that's not what's going on here.

//Magnus
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman


On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:


Whether you have autocommit on or off, you can *always* control things explicitly. And you can certainly run "multi-statement transactions" in autocommit on -- in fact, it's what most people do since it's the default configuration of the system (and I don't see why multi-table would even be relevant).

Autocommit on/off only controls what happens when you *don't* control things explicitly.
I know that we can control things explicitly with "autocommit on". But we would need to add "BEGIN" statements to the code which is an even bigger change than adding COMMITs. We considered it and found that the development cost is too high.

It seems I was not clear enough. I do not complain. I have been a PG fan since 2000 when I worked with it for the first time. I just wanted to understand it deeper and, fortunately, find a work around that would simplify our current development.

Thanks to all.

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Magnus Hagander-2


On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman <[hidden email]> wrote:


On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:


Whether you have autocommit on or off, you can *always* control things explicitly. And you can certainly run "multi-statement transactions" in autocommit on -- in fact, it's what most people do since it's the default configuration of the system (and I don't see why multi-table would even be relevant).

Autocommit on/off only controls what happens when you *don't* control things explicitly.
I know that we can control things explicitly with "autocommit on". But we would need to add "BEGIN" statements to the code which is an even bigger change than adding COMMITs. We considered it and found that the development cost is too high.

It seems I was not clear enough. I do not complain. I have been a PG fan since 2000 when I worked with it for the first time. I just wanted to understand it deeper and, fortunately, find a work around that would simplify our current development.


Oh sure, but there is clearly *something* going on, so we should try to figure that out. Because a transaction running multiple independent selects with the defaults settings will not actually block autovacuum. So clearly there is something else going on -- something else must be non-default, or it's something that the driver layer does.

To show that, something as simple as the following, with autovacuum logging enabled:

session 1:
CREATE TABLE test AS SELECT * FROM generate_series(1,10000);

session 2:
begin;
SELECT count(*) FROM test;
\watch 1

session 1:
delete from test;


In this case, you will see autovacuum firing just fine, even though there is an open transaction that queries the table test. As you're running you can use a third session to see that session 2 flips between "active" and "idle in transaction". The log output in my case was:

2020-09-08 16:13:12.271 CEST [26753] LOG:  automatic vacuum of table "postgres.public.test": index scans: 0
pages: 0 removed, 45 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 56 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 241585
buffer usage: 112 hits, 4 misses, 5 dirtied
avg read rate: 0.006 MB/s, avg write rate: 0.008 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 5.01 s

It is failing to *truncate* the table, but the general autovacuum is running.

Are you by any chance specifically referring to the truncation step?

However, if you change the session 2 to select from a *different* table, the truncation also works, so I'm guessing that's not it?

//Magnus

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Tom Lane-2
Magnus Hagander <[hidden email]> writes:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.

I don't think the OP is claiming that autovacuum is blocked, only that
it's failing to remove recently-dead rows that he thinks could be removed.

The reason that's not so is that whether or not transaction A *has*
touched table B is irrelevant.  It *could* read table B at any moment,
for all autovacuum knows.  Therefore we cannot remove rows that should
still be visible to A's snapshot.

There are some approximations involved in figuring out which rows are
potentially still visible to someone.  So perhaps this is a situation
where an approximation is being used and tighter analysis would have
shown that indeed a row could be removed.  But we haven't seen any
evidence of that so far.  The basic fact that A's snapshot is limiting
removal of rows from a table it has not touched is not a bug.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Magnus Hagander-2
On Tue, Sep 8, 2020 at 4:38 PM Tom Lane <[hidden email]> wrote:
Magnus Hagander <[hidden email]> writes:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.

I don't think the OP is claiming that autovacuum is blocked, only that
it's failing to remove recently-dead rows that he thinks could be removed.

The reason that's not so is that whether or not transaction A *has*
touched table B is irrelevant.  It *could* read table B at any moment,
for all autovacuum knows.  Therefore we cannot remove rows that should
still be visible to A's snapshot.

There are some approximations involved in figuring out which rows are
potentially still visible to someone.  So perhaps this is a situation
where an approximation is being used and tighter analysis would have
shown that indeed a row could be removed.  But we haven't seen any
evidence of that so far.  The basic fact that A's snapshot is limiting
removal of rows from a table it has not touched is not a bug.

Right. But in the default isolation level, the snapshot of A gets reset between each SELECT, and does not persist to the end of the transaction. So adding COMMIT between each select shouldn't change that part, should it? That is, it's the snapshot age that decides it, not the transaction age.

I feel there is still some piece of information missing there, that could explain the problem better...

//Magnus

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Ron-2
In reply to this post by Michael Paquier-2


On 9/8/20 3:27 AM, Michael Paquier wrote:

> On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote:
>> Autovacuum does not clean dead tuples of closed transactions in tableB
>> while there is an open transaction on tableA.
>> But the tables have nothing in common. They are handled by separate
>> applications and there are no transactions that touch both tables
>> simultaneously.
>> Why does autovacuum create an artificial dependency on the tables?
> This is called MVCC, which applies to a session as a whole.  The point
> here is that even if your application knows that only tableA is used
> by a given transaction, Postgres cannot know that, as it could be
> possible that data from tableB is needed in this same transaction, so
> old versions of the rows from tableB matching with the snapshot hold
> by this long-running transaction still have to be around.

Too bad the START TRANSACTION statement doesn't have a RESERVING clause
where you can enumerate the tables you'll be using.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman
In reply to this post by Tom Lane-2


On Tue, Sep 8, 2020 at 5:38 PM Tom Lane wrote:
Magnus Haganderwrites:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.

I don't think the OP is claiming that autovacuum is blocked, only that
it's failing to remove recently-dead rows that he thinks could be removed.
Yes, this is exactly what happens.

The reason that's not so is that whether or not transaction A *has*
touched table B is irrelevant.  It *could* read table B at any moment,
for all autovacuum knows.  Therefore we cannot remove rows that should
still be visible to A's snapshot.

There are some approximations involved in figuring out which rows are
potentially still visible to someone.  So perhaps this is a situation
where an approximation is being used and tighter analysis would have
shown that indeed a row could be removed.  But we haven't seen any
evidence of that so far.  The basic fact that A's snapshot is limiting
removal of rows from a table it has not touched is not a bug.
It's obviously not a bug. I was just surprised when I figured that out. It's also quite complex to explain to my colleagues. Actually, this is the main reason I started this thread: I tried to explain to someone and felt that I miss something.


--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Michael Holzman
In reply to this post by Magnus Hagander-2


On Tue, Sep 8, 2020 at 5:47 PM Magnus Hagander  wrote:

I feel there is still some piece of information missing there, that could explain the problem better...
I gave all the information I have (without real application and table names, of course).
Both applications are C++ demons working with PG via ODBC on RHEL. We use default ODBC settings.

--
Regards,
    Michael Holzman
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum of independent tables

Tom Lane-2
In reply to this post by Magnus Hagander-2
Magnus Hagander <[hidden email]> writes:
> On Tue, Sep 8, 2020 at 4:38 PM Tom Lane <[hidden email]> wrote:
>> The reason that's not so is that whether or not transaction A *has*
>> touched table B is irrelevant.  It *could* read table B at any moment,
>> for all autovacuum knows.  Therefore we cannot remove rows that should
>> still be visible to A's snapshot.

> Right. But in the default isolation level, the snapshot of A gets reset
> between each SELECT, and does not persist to the end of the transaction.

Well, we don't know what isolation level the OP is using.  We also don't
know what PG version he's using.  From memory, it hasn't been that long
since we fixed things so that an idle read-committed transaction
advertises no xmin.  It's also possible that the transaction isn't really
idle between statements (eg, if it's holding open cursors, or the like).

                        regards, tom lane


12