August 5, 2024
Summary: in this tutorial, you will learn how to check transaction commit ratio in PostgreSQL.
Table of Contents
Introduction
Commit ratio is the ratio between committed transactions (xact_commit) and rolled back transactions (xact_rollback). It indicates the amount of successful operations. Changes made by transactions may be aborted (rollback) or committed. Single queries that have failed outside the transactions are also accounted as rollbacks. So, in general, using the commit ratio we can estimate the amount of errors in a particular database.
Values closer to 100 mean that your database is healthy and has very few errors. If the commit ratio is below 90, it is a good idea to investigate further by enabling additional logs and analyzing them to build a list of the most common errors.
Example
First, create a database named bench:
CREATE DATABASE bench;
Second, we need to set up the pgbench sample database by executing pgbench with the -i (initialize) option:
$ pgbench -i -s 50 bench
creating tables...
5000000 of 5000000 tuples (100%) done (elapsed 5.33 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
Third, run a benchmark test with pgbench:
$ pgbench -c 10 -j 2 -t 10000 bench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average: 4.176 ms
tps = 2394.718707 (including connections establishing)
tps = 2394.874350 (excluding connections establishing)
Fourth, let’s check the number of transactions in the sample database that have been committed or rolled back:
SELECT xact_commit, xact_rollback
FROM pg_stat_database WHERE datname = 'bench';
xact_commit | xact_rollback
-------------+---------------
100000 | 0
(1 row)
Obviously, the transaction commit ratio is 100%.
Query the pg_stat_database view
You can see database-level statistics in the view pg_stat_database, which has a xact_rollback column. You should pay attention to this column, because it will tell you whether your database has many rollbacks, which will be potential risks in your application.
Commit ratio is the similar to cache hit ratio, but in addition it shows the amount of successful operations. It’s well known that changes made by transactions may be aborted (rollback) or commited. If rollback isn’t properly handled by an application, it can be considered as an error. Also, single queries that have failed outside the transactions are also accounted as rollbacks. So, in general, using commit ratio we can estimate amount of errors in a particular database. For commit ratio, xact_commit and xact_rollback values are used, and using queries like following it’s possible to calculate commit ratio. Here is an example which shows per-database results:
SELECT datname, 100 * xact_commit / (xact_commit + xact_rollback) as commit_ratio
FROM pg_stat_database WHERE (xact_commit + xact_rollback) > 0;
Result values may vary between 1 to 100. Values that are closer to 100 mean that you database has very few errors. In case when commit ratio is below 90, a good idea is to configure proper logging and check out logs for errors, built a list of most often errors and begin to eliminate them step by step.