Re: When do vacuumed pages/tuples become available for reuse?

rihad <rihad@xxxxxxx> · Thu, 11 Apr 2019 20:18:32 +0400



    On 04/11/2019 08:09 PM, Jeff Janes
      wrote:

    
          On Thu, Apr 11, 2019 at 11:44 AM rihad <rihad@xxxxxxx>
            wrote:

          
                On
                  04/11/2019 07:40 PM, Jeff Janes wrote:

                
                      The disk usage doesn't reach a steady state
                        after one or two autovacs?  Or it does, but you
                        are just unhappy about the ratio between the
                        steady state size and the theoretical fully
                        packed size?
                      

                      Cheers,
                      

                      Jeff
                    
                  
                Since we dump&restore production DB daily into
                  staging environment, the difference in size (as
                  reported by psql's \l+) is 11GB in a freshly restored
                  DB as opposed to 70GB in production.

                
            Yeah, that seems like a problem.  Do you have long
              lived transactions/snapshots that are preventing vacuuming
              from removing dead tuples?  You can run a manual "vacuum
              verbose" and see how many dead but nonremovable tuples
              there were, or set log_autovacuum_min_duration to some
              non-negative value less than the autovac takes, and do the
              same.
            

    vacuum frees tuples just fine. It's just that by the time each run
    finishes many more accumulate due to table update activity, ad
    nauseum. So this unused space constantly grows. Here's a sample
    autovacuum run:

    
    2019-04-11 19:39:44.450841500 [] LOG:  automatic vacuum of table
    "foo.public.bar": index scans: 1

    2019-04-11 19:39:44.450843500   pages: 0 removed, 472095 remain, 4
    skipped due to pins, 39075 skipped frozen

    2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811
    remain, 465 are dead but not yet removable

    2019-04-11 19:39:44.450845500   buffer usage: 62407557 hits, 6984769
    misses, 116409 dirtied

    2019-04-11 19:39:44.450846500   avg read rate: 16.263 MB/s, avg
    write rate: 0.271 MB/s

    2019-04-11 19:39:44.450847500   system usage: CPU 59.05s/115.26u sec
    elapsed 3355.28 sec

    
            (Indeed, those dumps you take daily might be the source
              of those long-lived snapshots.  How long does a dump
              take?)
          
        
    The daily dumps are taken daily from the slave server as part of
    stock FreeBSD postgres port activity.

    I don't think it impacts the master server.

    
            Also, what does pg_freespace (https://www.postgresql.org/docs/current/pgfreespacemap.html)
              show about the available of space in the table?  How
              about pgstattuple (https://www.postgresql.org/docs/current/pgstattuple.html)
            

    Thanks, I'll try those. But as I said freshly restored DB is only
    11GB in size, not 70 (only public schema is used).

    
            Cheers,
            

            Jeff