Thread: Gratuitous use of savepoint considered silly, if not harmful
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.consulting@comcast.net p: 312.241.7800
On Fri, Apr 13, 2018 at 2:32 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
As Forest Gump once said "Stupid is as stupid does". 😁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.consulting@comcast.net
p: 312.241.7800
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!