Re: Vacuum and state_change

On 06/09/2017 01:31 PM, armand pirvu wrote:

>>
>> Are these large tables?

> I would say yes
>
> select count(*) from csischema.tf_purchased_badge;
> 9380749
>
> select count(*) from csischema.tf_purchases_person;
> 19902172
>
> select count(*) from csischema.tf_demographic_response_person;
> 80868561
>
> select count(*) from csischema.tf_transaction_item_person;
> 3281084
>
> Interesting enough two completed

So the two 'smaller' tables which would make sense.

>
> relname | seq_scan | seq_tup_read | idx_scan |
> idx_tup_fetch | n_tup_ins | n_tup_upd | n_live_tup | n_dead_tup |
> n_mod_since_analyze | last_vacuum | last_autovacuum |
> autovacuum_count
> ----------------------------+----------+--------------+----------+---------------+-----------+-----------+------------+------------+---------------------+-------------+-------------------------------+------------------
> tf_transaction_item_person | 160 | 0 | 476810 |
> 1946119 | 2526 | 473678 | 3226110 | 0 |
> 116097 | | 2017-06-09 11:15:24.701997-05 | 2
> tf_purchased_badge | 358 | 1551142438 | 2108331 |
> 7020502 | 5498 | 1243746 | 9747336 | 107560 |
> 115888 | | 2017-06-09 15:09:16.624363-05 | 1
>
>
> I did notice though that checkpoints seem a bit too often aka below 5
> min from start to end

You probably should take a look at:

https://www.postgresql.org/docs/9.6/static/wal-configuration.html

and

https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM

> These tables suffer quite some data changes IIRC but that comes via some
> temp tables which reside in a temp schema and some previous messages
> from the log suggest that it might have ran into ladder locking in
> early stages, aka tmp table locked from vacuum and any further
> processing waiting for it and causing some other waits on those largish
> tables

Did you do a manual VACUUM of the temporary tables?

If not see below.

>
> Considering the temp ones are only for load and yes some processing goes
> in there , I am thinking disabling auto vacuum for the temp tables . Or
> should I disable auto vacuum all together and run say as a bath job on a
> weekend night ?

I don't think temporary tables are the problem as far as autovacuum goes:

https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM

"Temporary tables cannot be accessed by autovacuum. Therefore,
appropriate vacuum and analyze operations should be performed via
session SQL commands."

>
>> If you are on Postgres 9.6:
>>
>> https://www.postgresql.org/docs/9.6/static/progress-reporting.html
>>
>
> Aside that there are vacuum improvements and such, any other strong
> compelling reason to upgrade to 9.6 ?

That would depend on what version you are on now. If it is out of
support then there would be a reason to upgrade, not necessarily to 9.6
though.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com