PostgreSQL informs about high rollback count and low commit ratio

Question
1

Hi,

we noticed that the PrivacyIDEA database on our PostgreSQL server has an unusual high rollback count, compared to the committed transactions.

Furthermore, the commit ratio is around 68 where it should be higher that 90. Every other application running on this server has 99 or higher.

SQL query for commit ration:
SELECT 
  datname, 100 * xact_commit / (xact_commit + xact_rollback) as commit_ratio,
  xact_commit,
  xact_rollback
FROM pg_stat_database WHERE (xact_commit + xact_rollback) > 0;

commit_ratio: 68
xact_commit: 8682393
xact_rollback: 3933269

Is this a general problem or by design? Does this affect the data integrity over the long term?

I can’t find anything about this in the privacyidea.log-file…

Kind regards
notc

2

Hi @notc and sorry for the late reply.
We use SQLAlchemy for the database abstraction and it emits a rollback whenever a connection to the db is closed:
https://docs.sqlalchemy.org/en/14/faq/connections.html#why-does-sqlalchemy-issue-so-many-rollbacks
Does the high rollback count have a negative impact on the performance in Your case?

3

Hi @plettich,
okay, thank you for the information. We are just plan to monitor more aspects of our PostgreSQL database and the commit to rollback ratio is one of the metrics.

PrivacyIDEA is not running that smoothly with around 150 users in our case but I wouldn’t say it comes from the high rollback count. It’s pretty hard to diagnose where the delay is coming from exactly but probably many users requesting push tokens/authentications at the same time.

Kind regards,
notc

4

Push can cause a lot of performance problems depeding on how it is used.

E.g. if you are using push_wait or if you have high loads of pending authentication, the application (not smartphone app) will poll the privacyIDEA server.

So you probably need to look into your push configuration more deeply.