Re: [GENERAL] Vacuum and state_change - Mailing list pgsql-general

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