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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: When do vacuumed pages/tuples become available for reuse?
Дата
Msg-id CAMkU=1z+-o0F5PSsjhN8P98+YrYgg7odTZ+WZvocBObY-LnZLA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: When do vacuumed pages/tuples become available for reuse?  (rihad <rihad@mail.ru>)
Ответы Re: When do vacuumed pages/tuples become available for reuse?
Список pgsql-general
Дерево обсуждения
On Thu, Apr 11, 2019 at 11:44 AM rihad <rihad@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.

(Indeed, those dumps you take daily might be the source of those long-lived snapshots.  How long does a dump take?)

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)

Cheers,

Jeff

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: When do vacuumed pages/tuples become available for reuse?
Следующее
От: rihad
Дата:
Сообщение: Re: When do vacuumed pages/tuples become available for reuse?