On 04/11/2019 08:09 PM, Jeff Janes wrote:
On Thu, Apr 11, 2019 at 11:44 AM rihad <ri...@mail.ru
<mailto:ri...@mail.ru>> 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
