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

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian.klaver@aklaver.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
andsome previous messages from the log suggest that it might have ran into  ladder locking in early stages, aka tmp
tablelocked 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
vacuumfor 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
performedvia 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,
notnecessarily 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.klaver@aklaver.com