Search Postgresql Archives

Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Michael Lewis <mlewis@xxxxxxxxxxx> wrote on 06/23/2020 04:44:51 PM:
> Long running transactions are the common one that I see. You might 
> be dealing with replication slots or prepared transactions. 
> Basically, if some process might see that "old truth", then it can't
> be vacuumed away yet.

Thanks, those links you provided were informative.

Our application doesn't use prepared transactions, so that one is easy to 
eliminate.

Our database does use replication, but there's only one replication slot 
and the xmin and catalog_xmin columns are blank. I presume the only 
replication slot that exists is the one that the other replica is in fact 
using. I *think* this means replication isn't the issue, but it's hard to 
say since the xmin column is blank (and all the reading I've done doesn't 
even mention that the xmin on replication slots can be null).

That brings us to transactions. I looked for any long-running idle 
transaction (transactions that have been idle for more than 15 or 5 
minutes), but found none.

I tried:

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

But the backend_xmin for all of the rows returned is exactly the same, and 
that xmin is greater than the oldest xmin reported in the autovacuum logs. 
It does seem odd that the backend_xmin value isn't changing though. Is 
that normal?

So, for replication slots, I'm seeing a null xmin value, which the 
articles do not comment on how that should be interpreted. And for 
transactions, all of the transaction xmins are the same, which also seems 
odd and not what the articles suggested. I know the transactions 
themselves are coming and going because I can see the pids changing, but 
the xmins are always the same. Strange.

Having to check the logs to see what the oldest xmin is painful, and 
requires that a vacuum or autovacuum has been executed. Is there another 
way to check what the oldest xmin is on the dead tuples of a table?

Regards,

Jim Hurne







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux