Re: Vacuum and state_change

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> 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.”

By temporary tables I mean just regular table not tables created by "create temporary table" . I should have been more precise. We call them temporary since we do drop them after all is said and done. Maybe we should change the way we call them

>
>>> 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.

9.5 but considering I can track what auto vacuum does I was thinking to use that as a reason to the upgrade advantage

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