Re: Autovacuum, dead tuples and bloat

Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> · Thu, 20 Jun 2024 20:09:35 +0300



    Στις 20/6/24 19:46, ο/η Shenavai,
      Manuel έγραψε:

    
        Hi everyone,
         
        we can see in our
            database, that the DB is 200GB of size, with 99% bloat.
            After vacuum full the DB decreases to 2GB.
            
        DB total size: 200GB
        DB bloat: 198 GB
        DB non-bloat: 2GB
         
        We further see, that
            during bulk updates (i.e. a long running transaction), the
            DB is still growing, i.e. the size of the DB growth by +20GB
            after the bulk updates.
         
        My assumption is, that
            after an autovacuum, the 99% bloat should be available for
            usage again. But the DB size would stay at 200GB. In our
            case, I would only expect a growth of the DB, if the
            bulk-updates exceed the current DB size (i.e. 220 GB).
         
        How could I verify my
            assumption?
         
        I think of two
            possibilities:
        
          My assumption is wrong and for some reason
              the dead tuples are not cleaned so that the space cannot
              be reused
          The bulk-update indeed exceeds the current DB
              size. (Then the growth is expected).
        
         
    Your only assumption should be the official manual, and other
      material such as books, articles from reputable sources, even
      reading the source as a last resort could be considered.
    For starters : do you have autovacuum enabled ? If not, then
      enable this. 

    
    Then monitor for vacuum via pg_stat_user_tables, locate the
      tables that you would expect vacuum to have happened but did not,
      then consider autovacuum tuning.
    Watch the logs for lines such as :
    <N> dead row versions cannot be removed yet, oldest xmin: <some xid>
    those are held from being marked as removed, due to being visible by long running transactions. Monitor for those transactions.
    

    You have to monitor (if this is the case) about autovacuum being killed and not allowed to do its job.

    
        Can you help me to
            verify these assumptions? Are there any statistics available
            that could help me with my verification?
         
        Thanks in advance &
        Best regards,
        Manuel
      
    
    -- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)