RE: pg_upgrade —link does it remove table bloat - Mailing list pgsql-general

@Adrian Klaver,
I was concerned with the 1.4 value of tbloat and wastedbytes value, then again the last autovacuum was at 2020-02-13
02:25:22.533372-05and I took this snapshot at 3:44PMEST.  So it may be ok, what do you think?
 


current_database | schemaname |                 tablename                 | tbloat | wastedbytes |
iname                    | ibloat | wastedibytes
 
notimportant         | public              | members                                   |    1.4   |  3080314880  |
members_cobrid                     |    0.2 |            0
 





notimportant=# select * from pg_stat_user_tables where relname = 'members';
-[ RECORD 1 ]-------+------------------------------
relid               | 2045245
schemaname          | public
relname             | members
seq_scan            | 55065
seq_tup_read        | 201069350222
idx_scan            | 5349501175
idx_tup_fetch       | 7201402647
n_tup_ins           | 910616
n_tup_upd           | 46730942
n_tup_del           | 1
n_tup_hot_upd       | 41845682
n_live_tup          | 18262438
n_dead_tup          | 14740
n_mod_since_analyze | 2476
last_vacuum         | 2019-10-13 01:01:40.587534-04
last_autovacuum     | 2020-02-13 02:25:22.533372-05
last_analyze        | 2019-10-13 01:01:41.916929-04
last_autoanalyze    | 2020-02-13 13:44:46.273096-05
vacuum_count        | 15
autovacuum_count    | 92
analyze_count       | 15
autoanalyze_count   | 243

Jason Ralph

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, February 13, 2020 3:19 PM
To: Jason Ralph <jralph@affinitysolutions.com>; Michael Lewis <mlewis@entrata.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade —link does it remove table bloat

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@entrata.com>, 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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki
>
.postgresql.org%2Fwiki%2FShow_database_bloat&data=01%7C01%7Cjralph%40affinitysolutions.com%7C6ead29e3b0bf4238f50e08d7b0c1fd0b%7Cfbf1a257f1104fc19456b0ca3038c6f0%7C1&sdata=d8kSilB5eqJGWujd7tzlX5xaRhm5Z335G34MAO6%2BHOY%3D&reserved=0
itstill 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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fbucardo.org%2Fcheck_postgres%2Fcheck_postgres.pl.html%23bloat&data=01%7C01%7Cjralph%40affinitysolutions.com%7C6ead29e3b0bf4238f50e08d7b0c1fd0b%7Cfbf1a257f1104fc19456b0ca3038c6f0%7C1&sdata=XugOdnmx%2BcElhEKTPqL30cjIKEDUyYHl8WvD75r82G8%3D&reserved=0

"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@entrata.com>
> *Sent:* Thursday, February 13, 2020 1:02 PM
> *To:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Cc:* Jason Ralph <jralph@affinitysolutions.com>;
> pgsql-general@lists.postgresql.org
> *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@aklaver.com
This message contains confidential information and is intended only for the individual named. If you are not the named
addresseeyou should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if
youhave received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be
guaranteedto 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
contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a
hard-copyversion.