> On Jun 9, 2017, at 3:52 PM, Adrian Klaver <adrian.kla...@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 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
