vacuum question

From: Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: vacuum question
Date: 2018-09-30 11:32:27
Message-ID: CAKkG4_nNguKrEx7x_6iV2vq+Fn+jiEJYbdhsnVZprmuXOgWUFw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a table with a really small number of rows, usually about 1500,
sometimes may be up to 5000. The usage pattern of that table is such that
rows are inserted and kept for a while, mostly seconds or minutes but
theoretically up to 1 year. After that they are deleted. No updates, just
insert, delete. The table is pretty actively written, sometimes >100
transactions per second.

Although the table is frequently auto-vacuumed, its size is growing over
time. The size of the table corresponds to the remaining number of pages in
the autovacuum log.

pages: 0 removed, 30069 remain, 0 skipped due to pins, 29985 skipped
frozen
tuples: 136 removed, 1643 remain, 15 are dead but not yet removable
buffer usage: 44327 hits, 0 misses, 21 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s
system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec

What does "29985 skipped frozen" mean? I presume these skipped buffers is
the table bloat.

My way to get rid of the bloat is to cluster the table. That takes less
than half a second and the next autovacuum log then looks like this:

pages: 0 removed, 3493 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable
buffer usage: 9425 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec

For a number of autovacuum cycles "skipped frozen" remains 0 until
eventually:

pages: 0 removed, 4822 remain, 0 skipped due to pins, 4433 skipped frozen
tuples: 227 removed, 2609 remain, 13 are dead but not yet removable
buffer usage: 5269 hits, 0 misses, 315 dirtied
avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s
system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec

Is there anything I can do to prevent that bloat from accumulating in the
first place?

Another thing that seems odd is the number of dead but not removable tuples
in the record in the middle. Sifting through the log, I can see numbers up
to 80000. One of our replicas is configured with hot_standby_feedback. Can
that be the culprit?

This is 9.6.10.

Thanks,
Torsten

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2018-09-30 11:53:20 Re: vacuum question
Previous Message Peter J. Holzer 2018-09-30 09:50:29 Re: How to maintain the csv log files in pg_log directory only for past 30 days