PG Errors when opening tickets

[astrugatch]

Infos:

• Used Zammad version: 5.3.1-1674057058.5f657f26.bionic
• Used Zammad installation type: package
• Operating system: Ubuntu
• Browser + version: Chrome

We have recently been getting errors like these:

Error ID 5lb-5xBa: Please contact your administrator
When users try to open tickets.

Logs show this seems to be an error with PG, but don’t seem to indicate the cause.

E, [2023-01-20T08:25:52.432316 #849-19992440] ERROR -- : Error ID G0gZ0dZ2: PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block

I’m not enough of a database expert to determine the issue. Any assistance would be appreciated.

[astrugatch]

I think my error is due to our PG Connections setting. It was still at default (500 I think?). I followed the docs here: Configure Database server — Zammad documentation and increased the number of connections.

Will continue to monitor.

[dvanzuijlekom]

The default for PostgreSQL’s max_connections is usually set at 100 connections. If you’re hitting that, you should see a specific line in your logs. I have written a bit about this in the following post:

Performance problems with 30 employees

If you’re hitting the PostgreSQL max_connections limit, you should see this in your PostgreSQL logs, e.g. something like: FATAL: remaining connection slots are reserved for non-replication superuser connections So inspect your database logs for errors like this. Blindly raising max_connections to an arbitrary high number is a bit of a dumb idea. Here is a pretty good write-up on the subject. I would start looking for I/O bottlenecks, e.g. disk or database. The data you’ve supplied doesn’t me…

[astrugatch]

I’m not seeing the errors in the Zammad UI anymore, but I am still seeing the following error in PG’s logs:

2023-01-20 15:14:28.572 EST [2580] zammad@zammad ERROR: current transaction is aborted, commands ignored until end of transaction block

Thoughts on the cause?

[dvanzuijlekom]

Yeah, check this. You may want to change the way PostgreSQL logs as well, as you’re probably missing log information.

Performance troubleshooting 5.2.1

Regarding the database deadlocks, you might need to look into PostgreSQL tuning. I have written a bit about this in earlier posts, as we were facing the same sort of issues. This was on a Zammad 4.x system though, but PostgreSQL database tuning and best practices don’t change too much in 5.x. We are currently running 5.1.x and will soon be upgrading to 5.2.x with the same tuning in place. Please do read the comments by Zammad Core Team members about not just blindly copying parameters/advice and…

[MrGeneration]

Restart postgresql and Zammad to ensure PGSQL versions are fitting in runtime.
The issue should disappear after that.