On 2/13/20 11:07 AM, Jason Ralph wrote:
>There is more than one type of statistics though. Stats on the
distribution of data is easily recreated with analyze table_name or
analyzing the whole >database. What about the stats on how many rows
have been inserted or updated since the last (auto)vacuum and that will
be used to trigger autovacuum? >Are those set back to zero by an
upgrade? I would assume usage counts like how many times an index scan
has been done would be reset, but if the >numbers in pg_stat_user_tables
like n_tup_upd or n_tup_del are zero'd out during an upgrade, than it
would seem like a manual vacuum would always be a >good idea to ensure a
table wasn't 99% of the way to needing one and then the stats got reset
by upgrading.
I agree @Michael Lewis <mailto:mlewis@xxxxxxxxxxx>, thank you for this
comment.
I am thinking a vacuum full is what I am going to need. Or pg_dump /
pg_restore. I have tuned auto vacuum after the upgrade to be aggressive,
it finishes fine after a couple hours on a large table, statistics look
good on the pg_stat_user_tables. However, when I run the bloat check
from the wiki https://wiki.postgresql.org/wiki/Show_database_bloat it
still shows bloat. Thinking it may be left over from before the
pg_upgrade and auto vacuum tuning.
What values are you getting?
The script you are using comes from this:
https://bucardo.org/check_postgres/check_postgres.pl.html#bloat
"If you want to output the bloat ratio instead (how many times larger
the relation is compared to how large it should be),..."
So I'm pretty sure bloat is where tbloat > 1.0.
Best,
*Jason Ralph*
*From:* Michael Lewis <mlewis@xxxxxxxxxxx>
*Sent:* Thursday, February 13, 2020 1:02 PM
*To:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
*Cc:* Jason Ralph <jralph@xxxxxxxxxxxxxxxxxxxxx>;
pgsql-general@xxxxxxxxxxxxxxxxxxxx
*Subject:* Re: pg_upgrade —link does it remove table bloat
There is more than one type of statistics though. Stats on the
distribution of data is easily recreated with analyze table_name or
analyzing the whole database. What about the stats on how many rows have
been inserted or updated since the last (auto)vacuum and that will be
used to trigger autovacuum? Are those set back to zero by an upgrade? I
would assume usage counts like how many times an index scan has been
done would be reset, but if the numbers in pg_stat_user_tables like
n_tup_upd or n_tup_del are zero'd out during an upgrade, than it would
seem like a manual vacuum would always be a good idea to ensure a table
wasn't 99% of the way to needing one and then the stats got reset by
upgrading.
This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system. E-mail transmission cannot be
guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or
contain viruses. The sender therefore does not accept liability for any
errors or omissions in the contents of this message, which arise as a
result of e-mail transmission. If verification is required please
request a hard-copy version.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
