Re: [GENERAL] 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.klaver@aklaver.com