From: senor <frio_cervesa@hotmail.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: txid_current vs xact_commit stats
Thread-Topic: txid_current vs xact_commit stats
Thread-Index: AQHbGfd5n9TFXded/E6foDj9BgTKdQ==
Date: Wed, 9 Oct 2024 04:22:41 +0000
Message-ID:
 <SJ0P221MB06894F3D6D38DE5C61E82B30F77F2@SJ0P221MB0689.NAMP221.PROD.OUTLOOK.COM>
Accept-Language: en-US
Content-Language: en-US
msip_labels:
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: SJ0P221MB0689:EE_|CO6P221MB0712:EE_
x-ms-office365-filtering-correlation-id: a96504d5-a898-441a-6a9a-08dce81a03f4
x-microsoft-antispam:
 BCL:0;ARA:14566002|8060799006|19110799003|8062599003|15030799003|461199028|15080799006|7092599003|3412199025|440099028|102099032|3430499032;
x-microsoft-antispam-message-info:
 2tjNtJH+laVEU9HHpN7UO/PIiN+lp1zPlWRggx17kJ7UEEmdgiaoYg7v1gInUVnk4oe+lZ3ZAt+lVWarj4uPhnMDo3Ov+LUL4VhEhKuv/gbfq5cOtTMw3r+r9806PSQYEp3aEGML8/GD7wDbmF69UCClpQoYEaOMEqS/75ZMxusNTzp/qqaDEVboCVIsyMAsX5kp9AhPw0wUzm09XdktjgKhsw6K74zKldjiFy6PlkO6g4gofTJ0PXhJ/UdPG2Bv9/ZaAMfIAlUptL6SBSXbwELtx1yJDzyGGxE/NttjKiD5RbGYtcePlt0YDIpX/iLGv8GZO6kI2X1YRnxOHjOxCyCBRR8EPeyGTkBVXEB5Ua3MYiM6G6+4SvVA+3JPqbRUPavabvPp7KZPG/+ho0ax5rLjxPN7epoe8In+yrLkigimXvM5uQQP0c3YHGKlZZWUnf7wY8nsnVu/x5lA76p3BNG4m+/YpxzXDfD+Nr7T1UvOIEB6sqO46/C1V+8OQQMsPwcQAtW8+w++uJggVBwTGOJJxNEk31YG7nFE1pZ1QdxXK9/kz9nCVzynMeX6A9jbdSRcCchCT0liFdIYA1GIIqQZUE+R9ch/fzUEFYttwBWRwCSE+4S6UsVy0ZxNoVucn56YsgVkiTwFJ+s++65mmJnxDuxOqQ+i6eF8I63cwzEm70VaPIo4MGnJ6A/EQh6B0fKAKgMjQW9dGRd3v2lWQRP5U2By2L0onTXmpwxRf3JhkPU/JsIfxWCuTInzkOMr+OwWoSSF8MyHL7ugcExyIQ==
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-8859-1?Q?ZXr+hP5LjJu1CfYIc1aln+YJnLnZVpIgEqqvhhE4ljX9jgSjIwSK/eVJv+?=
 =?iso-8859-1?Q?r7bxQZIg7ulT/z2YV8PemWLKMcac0V4PXRULu1iPquGcUxWGl45xYSz01T?=
 =?iso-8859-1?Q?i0ONcr6wUq33I4IgWv3EUZl5Mi+X4p4of4vImsGGboIMMdMOdSB2Gcmv5f?=
 =?iso-8859-1?Q?+GPrS3w4cdE4b3Dh0QAH8g5JkNJ10IHZRsnuV89r/S53NuR7kUUhayYrjy?=
 =?iso-8859-1?Q?W/Uxigo2rwz0Wn5T4F4AH4x54E2fMqJq9Gr+pyx/YbXGgE0fp3OhyQ+qWe?=
 =?iso-8859-1?Q?wA2ff6vr9gQ8bht0cV1brKERWG24jl40ArD253IZ6nj23oWaa6Eug8f4in?=
 =?iso-8859-1?Q?f7wvcP5J4370Try6smAEArkYtgBYgxo8CwwGWtaUdc9MG+vq+MgrMnPAq1?=
 =?iso-8859-1?Q?GP5aCY/cqLb4fqyfDAAoewp61ZaMwAljCtEDMi9KEPaBq5MSv+zKpXEwR+?=
 =?iso-8859-1?Q?N0/wNvbdTew3o3q2/op5CPvwT9BSOYWelfp7hRA2IRC2XdU+Ar8GOQIZBS?=
 =?iso-8859-1?Q?pV5x+erzeMSC2u2RzxoWT3xh6G6XeSSC3s3okOz6lOx/L5S8RgubpdTswx?=
 =?iso-8859-1?Q?VvMHEoTopERtdhSt1KvedTNIyl7ns3dq2cvFV9KykgD8qWc4Lg2SQn9LW+?=
 =?iso-8859-1?Q?pT0xVXdks3Op2eZwNCaOQasgL/txEjrw2FziLJ3ngUgs1heMyPk3Bb3an5?=
 =?iso-8859-1?Q?P3uLzvqD8AP1w1MNwh159Ll4bjhsZkcuCYQauNa3Lq5qH6PE6Y7bS2aVUv?=
 =?iso-8859-1?Q?h4tQnP5t/R+Qo7Q3UTuoudlbIOhpx96Sx93/5rc+BnddI116qstVYMHIlt?=
 =?iso-8859-1?Q?4WpwjZ9/RAFGjswhxflTLT2LUWiz5Ffj32yYYZIBpqWaaBy3LpSeAu+6ob?=
 =?iso-8859-1?Q?kd16x4u8QNsTn9RJVxHXVNqCN4KFi2nLAnURaLdiw3oWtyAPM3sLMBi563?=
 =?iso-8859-1?Q?pKG73PzamhmCyBBByW0QYK/RJEczftSiI0Bm9crYnDNmvjGGM/V5mQ3lTM?=
 =?iso-8859-1?Q?O2NdTDg1J45u7akvNq5o1OFqU6hU4IUU+Hi/pDAB3/I7N2LxWDESZ1hv9I?=
 =?iso-8859-1?Q?FnbTdReJuTBO3cOXH/uW9hIZN2eLy30v011YAYSY2ab+l+7tkRf+pPYEo8?=
 =?iso-8859-1?Q?g3iSvP23w0fN9AtOeWPUjzyo077hX39i4VjS9PWA51CBkygbS01Z0rqIXR?=
 =?iso-8859-1?Q?OMBzpM0+bh5Qny5PMxrFAmwVX4X5slti5nhV1ksfTpC/lOJAoHIGw3dmJH?=
 =?iso-8859-1?Q?EtO+JRcCuxKkOIF5sLCWNKFgD67CwnuNkxIKi99qQ=3D?=
Content-Type: multipart/alternative;
	boundary="_000_SJ0P221MB06894F3D6D38DE5C61E82B30F77F2SJ0P221MB0689NAMP_"
MIME-Version: 1.0
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-f5d03.templateTenant
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: SJ0P221MB0689.NAMP221.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: a96504d5-a898-441a-6a9a-08dce81a03f4
X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Oct 2024 04:22:41.2369
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO6P221MB0712
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/SJ0P221MB06894F3D6D38DE5C61E82B30F77F2%40SJ0P221MB0689.NAMP221.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_SJ0P221MB06894F3D6D38DE5C61E82B30F77F2SJ0P221MB0689NAMP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

HI All;

I was under the impression that all transactions must end with a commit or =
a rollback but watching stats doesn't support this. Web searches tend to re=
turn info on what a transaction is or the ratio of commits to rollbacks. I =
found nothing contradicting what I think I know.

I've sampled pg_stat_database.xact_commit, pg_stat_database.xact_rollback a=
nd txid_current() at intervals on a few independent clusters and see that c=
ommits increase anywhere from 50% to 300% of the rate of transaction increa=
se. Rollback remains very near zero for all clusters. Each cluster tends to=
 stay consistently within a range (i.e. 120-130% or 50-70%).

I've seen strange issues with the stats collector missing updates and causi=
ng problems with autovacuum but that wouldn't explain more commits than tra=
nsactions. All clusters receive many inserts (~10-100) in single transactio=
ns but AFAIK this still counts as a single commit. Many other tables are cr=
eated while processing the inserted data and much of that is done within tr=
ansaction blocks. I'm not aware of anything very sophisticated in the appli=
cation handling this but I could be wrong.

I'm probably missing something fundamental. I know what I know but I'm not =
a DBA.
PG version 11 & 12 on Linux

Any hints and references appreciated.
Thanks,
Senor


--_000_SJ0P221MB06894F3D6D38DE5C61E82B30F77F2SJ0P221MB0689NAMP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
HI All;</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I was under the impression that all transactions must end with a commit or =
a rollback but watching stats doesn't support this. Web searches tend to re=
turn info on what a transaction is or the ratio of commits to rollbacks. I =
found nothing contradicting what
 I think I know.</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I've sampled pg_stat_database.xact_commit, pg_stat_database.xact_rollback a=
nd txid_current() at intervals on a few independent clusters and see that c=
ommits increase anywhere from 50% to 300% of the rate of transaction increa=
se. Rollback remains very near zero
 for all clusters. Each cluster tends to stay consistently within a range (=
i.e. 120-130% or 50-70%).&nbsp;</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I've seen strange issues with the stats collector missing updates and causi=
ng problems with autovacuum but that wouldn't explain more commits than tra=
nsactions. All clusters receive many inserts (~10-100) in single transactio=
ns but AFAIK this still counts as
 a single commit. Many other tables are created while processing the insert=
ed data and much of that is done within transaction blocks. I'm not aware o=
f anything very sophisticated in the application handling this but I could =
be wrong.</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I'm probably missing something fundamental. I know what I know but I'm not =
a DBA.</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
PG version 11 &amp; 12 on Linux</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Any hints and references appreciated.</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thanks,</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Senor</div>
<div class=3D"elementToProof" style=3D"font-family: Calibri, Helvetica, san=
s-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
</body>
</html>

--_000_SJ0P221MB06894F3D6D38DE5C61E82B30F77F2SJ0P221MB0689NAMP_--