On Fri, Apr 13, 2018 at 2:32 PM, Jerry Sievers <gsiever...@comcast.net> wrote:
> So I'm (was) puzzled here when a big warehouse system just upgraded to > 9.6 which I knew does only a few 100k *real* transactions/day was > wrapping txid_current() so fast, in turn causing some big, nasty tablesl > to age and then require painful long-running vacuums... > > Got the brilliant idea to full statement log for just 20 minutes or so > and then do some digging. > > OMG my DW team's Pentaho/Kettle driver gizmo emulates single-line > autocommit inserts using savepoints. > > It racked up ~1.8M txids meanwhile actually doing only ~900 > transactions. > > Symptomatic of this also is that your pg_stat_database.(xact_commit + > xact_rollback) counter over time will be wildly smaller then the > advancement of txid_current() perhaps helping in the confusion. > > This was csvlogged and field #8 is command-tag which we're pulling out > > and summarizing as seen below. > > tmp$ grep ^2018 $log-file | grep silly_etl_user | cut -d, -f8 | sort | > uniq -c | sort -k1,1bnr -k2 > > 1880283 "INSERT" > 1879931 "RELEASE" > 1879931 "SAVEPOINT" > 314838 "SELECT" > 314298 "UPDATE" > 2681 "idle" > 2677 "authentication" > 1967 "SET" > 898 "COMMIT" > 897 "BEGIN" > 160 "DELETE" > 83 "TRUNCATE TABLE" > 6 "DROP TABLE" > 2 "CREATE INDEX" > 2 "CREATE TABLE AS" > > Anyway, I felt this was worth sharing :-) > > Thx > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 > > *As Forest Gump once said "Stupid is as stupid does". *😁 -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
