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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
