Performance troubleshooting 5.2.1

[fitzy89]

Infos:

• Used Zammad version: 5.2.1
• Used Zammad installation type: package
• Operating system: Previously Ubuntu, now Alma Linux
• Browser + version: Edge 103.0.1264.49
• Maximum 15 concurrent users

Expected behavior:

System should be responsive with minimal delays
*

Actual behavior:

Regular white screens while loading tickets, and tickets slow to update
*

Steps to reproduce the behavior:

Refresh the page, or update a ticket.
*

It may help if I give a timeline of events here which led up to the issue, because it used to work perfectly for two years until a couple of weeks ago.

The first sign of trouble was when Office 365 stopped accepting IMAP logins, which Zammad used for several email accounts, we verified it to be an Office 365 issue, and not related to Zammad. I then setup the Microsoft 365 channel type in Zammad to import emails and this was then able to retrieve tickets once again. However the performance was terrible, every screen and everything you clicked on took 30-40 seconds to complete, I assumed it was catching up with itself and would improve after a few hours but it did not. The load average of the server was consistently above 5 and CPU was maxed out at 100% on all cores by a combination of Puma and background_worker processes, they both took it in turns to hammer the system.

I started checking logs and found the most prevalent issue was apparently some database deadlocks, every deadlock seemed to be preceded by a taskbar-related command:

2022-07-08 10:51:40.578 UTC [1043561] ERROR:  deadlock detected
2022-07-08 10:51:40.578 UTC [1043561] DETAIL:  Process 1043561 waits for ShareLock on transaction 805640; blocked by process 1048434.
        Process 1048434 waits for ShareLock on transaction 805639; blocked by process 1043561.
        Process 1043561: SELECT "taskbars".* FROM "taskbars" WHERE "taskbars"."id" = $1 LIMIT $2 FOR UPDATE
        Process 1048434: SELECT "taskbars".* FROM "taskbars" WHERE "taskbars"."id" = $1 LIMIT $2 FOR UPDATE
2022-07-08 10:51:40.578 UTC [1043561] HINT:  See server log for query details.
2022-07-08 10:51:40.578 UTC [1043561] CONTEXT:  while locking tuple (101,2) in relation "taskbars"
2022-07-08 10:51:40.578 UTC [1043561] STATEMENT:  SELECT "taskbars".* FROM "taskbars" WHERE "taskbars"."id" = $1 LIMIT $2 FOR UPDATE
2022-07-08 10:51:40.579 UTC [1043561] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2022-07-08 10:51:40.579 UTC [1043561] STATEMENT:  DEALLOCATE a1
2022-07-08 10:51:40.580 UTC [1043561] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2022-07-08 10:51:40.580 UTC [1043561] STATEMENT:  DEALLOCATE a2
2022-07-08 10:51:40.581 UTC [1043561] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2022-07-08 10:51:40.581 UTC [1043561] STATEMENT:  DEALLOCATE a3
2022-07-08 10:51:40.582 UTC [1043561] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2022-07-08 10:51:40.582 UTC [1043561] STATEMENT:  DEALLOCATE a4
2022-07-08 10:51:40.590 UTC [1043561] ERROR:  current transaction is aborted, commands ignored until end of transaction block

The above DEALLOCATE lines repeat for many pages. I tried looking up some of the process IDs but the processes did not exist by the time I was searching. The maximum database connections was, at the time set to 500, with about 430 or so in use at the busiest times, but I have since increased it to 2000.

During troubleshooting, with the high CPU usage I decided to migrate the system to a more powerful server to see if that would help, so I have now moved it to Amazon EC2, I went with a t3a.2xlarge machine type with 8vCPUs and 32gig RAM which appeared to perform the best out of several types that I tried, but it’s still not good. For the migration process I used a pre-defined AWS image from their marketplace to ensure everything was setup ok, then did a backup from the old server, restore to the new one, then reinstalled packages on the new one and it came right up and loaded without error, for the record the AWS image uses Alma Linux as the os, where my previous in-house server used Ubuntu.

CPU usage has now settled down considerably since moving to AWS to 1-3% on average, but the load times are still awful and I still see deadlock errors in the DB, much less frequently now but they still occur and are still mostly related to the taskbar. I note that there was a previous bug related to taskbar deadlocks but I understand that the fix should be built into my version 5.2.1? Search taskbars cause DeadLocks · Issue #3087 · zammad/zammad · GitHub

How can I further diagnose this issue and which logs should I be looking at? I am mostly checking the Postgresql log and Zammad’s own log at the moment, is there something else I should be looking as well? It seemed that the main trigger for all of this was the switch from IMAP accounts to Microsoft 365 and since then the performance is so incredibly bad, but I can’t find many logs to indicate why or what is causing it

[dvanzuijlekom]

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 expecting it will solve your issues, you need to understand what its effects are.

System performance very limited without obvious reason

What I’ve said before and what you seem to be ignoring: start by monitoring the basics. Start by graphing performance metrics over time, inside the OS as well as at the hypervisor level. I’m guessing your VM packs a bit of a punch, but the software just isn’t using all that power because it isn’t tuned appropriately. Adding RAM does nothing if you aren’t configuring your applications to actually use it. Giving the VM a lot of CPU’s does nothing if the applications aren’t instructed to start more…

System performance very limited without obvious reason

You may want to have a look into the tuning section: https://docs.zammad.org/en/latest/appendix/configure-env-vars.html And while you’re at it, as your configuration looks off compared to our suggestion, also have a look at our database configuration guide: https://docs.zammad.org/en/latest/appendix/configure-database-server.html Have a look into your elasticsearch log file. The reasons for elasticsearch crashing or not starting can be various. Nobodoy can help you at that point. The El…

Database deadlock

What kind of a userbase and hardware specs are you running? Did you tune the database or is this a vanilla install (besides the max_connections)? Our installbase is quite large, with a very busy helpdesk: lots of active agents and incoming e-mails. We were seeing the same sort of lock contending issues and even deadlock situations. We started monitoring and tuning PostgreSQL and now deadlocks and locking issues are significantly less and mostly gone. Giving advice on tuning parameters is dange…

I am hesitant of straight up giving you tips on where to look for tuning, as this is not appreciated by the Zammad Core Team without express warnings in place. Our setup is not yours and our tuning might cause unwanted side-effects in your environment.

[fitzy89]

Thanks for the information, I guess what I’m finding difficult is that most posts recommend to tune it manually, and they list the settings to do so but I’m struggling to find information on which values are good or bad. For example setting a value too high may be detrimental, but so is setting it too low. What I would find helpful in the documentation is if they could add some tips next to each option something like “set X value to the number of cpu cores” or “if X happens, it’s too high, lower the value”, so just some general tips on finding the optimal values for a users particular setup would be really helpful.

Just working through your recommendations, here’s the outputs, hopefully the formatting is legible:

• What is the output of this database query?
  SELECT * FROM pg_settings WHERE source != 'default';

zammad=> SELECT * FROM pg_settings WHERE source != 'default';
            name            |      setting       | unit |                       category                       |                                                       short_desc                                                        |                                                      extra_desc                                                       |  context   | vartype |       source       | min_val |  max_val   |                               enumvals                               |     boot_val      |     reset_val      | sourcefile | sourceline | pending_restart
----------------------------+--------------------+------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------+---------+------------+----------------------------------------------------------------------+-------------------+--------------------+------------+------------+-----------------
 application_name           | psql               |      | Reporting and Logging / What to Log                  | Sets the application name to be reported in statistics and logs.                                                        |                                                                                                                       | user       | string  | client             |         |            |                                                                      |                   | psql               |            |            | f
 client_encoding            | UTF8               |      | Client Connection Defaults / Locale and Formatting   | Sets the client's character set encoding.                                                                               |                                                                                                                       | user       | string  | client             |         |            |                                                                      | SQL_ASCII         | UTF8               |            |            | f
 data_checksums             | off                |      | Preset Options                                       | Shows whether data checksums are turned on for this cluster.                                                            |                                                                                                                       | internal   | bool    | override           |         |            |                                                                      | off               | off                |            |            | f
 DateStyle                  | ISO, MDY           |      | Client Connection Defaults / Locale and Formatting   | Sets the display format for date and time values.                                                                       | Also controls interpretation of ambiguous date inputs.                                                                | user       | string  | configuration file |         |            |                                                                      | ISO, MDY          | ISO, MDY           |            |            | f
 default_text_search_config | pg_catalog.english |      | Client Connection Defaults / Locale and Formatting   | Sets default text search configuration.                                                                                 |                                                                                                                       | user       | string  | configuration file |         |            |                                                                      | pg_catalog.simple | pg_catalog.english |            |            | f
 dynamic_shared_memory_type | posix              |      | Resource Usage / Memory                              | Selects the dynamic shared memory implementation used.                                                                  |                                                                                                                       | postmaster | enum    | configuration file |         |            | {posix,sysv,mmap}                                                    | posix             | posix              |            |            | f
 lc_collate                 | en_US.UTF-8        |      | Preset Options                                       | Shows the collation order locale.                                                                                       |                                                                                                                       | internal   | string  | override           |         |            |                                                                      | C                 | en_US.UTF-8        |            |            | f
 lc_ctype                   | en_US.UTF-8        |      | Preset Options                                       | Shows the character classification and case conversion locale.                                                          |                                                                                                                       | internal   | string  | override           |         |            |                                                                      | C                 | en_US.UTF-8        |            |            | f
 lc_messages                | en_US.UTF-8        |      | Client Connection Defaults / Locale and Formatting   | Sets the language in which messages are displayed.                                                                      |                                                                                                                       | superuser  | string  | configuration file |         |            |                                                                      |                   | en_US.UTF-8        |            |            | f
 lc_monetary                | en_US.UTF-8        |      | Client Connection Defaults / Locale and Formatting   | Sets the locale for formatting monetary amounts.                                                                        |                                                                                                                       | user       | string  | configuration file |         |            |                                                                      | C                 | en_US.UTF-8        |            |            | f
 lc_numeric                 | en_US.UTF-8        |      | Client Connection Defaults / Locale and Formatting   | Sets the locale for formatting numbers.                                                                                 |                                                                                                                       | user       | string  | configuration file |         |            |                                                                      | C                 | en_US.UTF-8        |            |            | f
 lc_time                    | en_US.UTF-8        |      | Client Connection Defaults / Locale and Formatting   | Sets the locale for formatting date and time values.                                                                    |                                                                                                                       | user       | string  | configuration file |         |            |                                                                      | C                 | en_US.UTF-8        |            |            | f
 log_destination            | stderr             |      | Reporting and Logging / Where to Log                 | Sets the destination for server log output.                                                                             | Valid values are combinations of "stderr", "syslog", "csvlog", and "eventlog", depending on the platform.             | sighup     | string  | configuration file |         |            |                                                                      | stderr            | stderr             |            |            | f
 log_line_prefix            | %m [%p]            |      | Reporting and Logging / What to Log                  | Controls information prefixed to each log line.                                                                         | If blank, no prefix is used.                                                                                          | sighup     | string  | configuration file |         |            |                                                                      | %m [%p]           | %m [%p]            |            |            | f
 log_rotation_age           | 1440               | min  | Reporting and Logging / Where to Log                 | Automatic log file rotation will occur after N minutes.                                                                 |                                                                                                                       | sighup     | integer | configuration file | 0       | 35791394   |                                                                      | 1440              | 1440               |            |            | f
 log_rotation_size          | 0                  | kB   | Reporting and Logging / Where to Log                 | Automatic log file rotation will occur after N kilobytes.                                                               |                                                                                                                       | sighup     | integer | configuration file | 0       | 2097151    |                                                                      | 10240             | 0                  |            |            | f
 log_timezone               | UTC                |      | Reporting and Logging / What to Log                  | Sets the time zone to use in log messages.                                                                              |                                                                                                                       | sighup     | string  | configuration file |         |            |                                                                      | GMT               | UTC                |            |            | f
 log_truncate_on_rotation   | on                 |      | Reporting and Logging / Where to Log                 | Truncate existing log files of same name during log rotation.                                                           |                                                                                                                       | sighup     | bool    | configuration file |         |            |                                                                      | off               | on                 |            |            | f
 logging_collector          | on                 |      | Reporting and Logging / Where to Log                 | Start a subprocess to capture stderr output and/or csvlogs into log files.                                              |                                                                                                                       | postmaster | bool    | configuration file |         |            |                                                                      | off               | on                 |            |            | f
 max_connections            | 2000               |      | Connections and Authentication / Connection Settings | Sets the maximum number of concurrent connections.                                                                      |                                                                                                                       | postmaster | integer | configuration file | 1       | 262143     |                                                                      | 100               | 2000               |            |            | f
 max_stack_depth            | 5120               | kB   | Resource Usage / Memory                              | Sets the maximum stack depth, in kilobytes.                                                                             |                                                                                                                       | superuser  | integer | configuration file | 100     | 2147483647 |                                                                      | 100               | 5120               |            |            | f
 max_wal_size               | 1024               | MB   | Write-Ahead Log / Checkpoints                        | Sets the WAL size that triggers a checkpoint.                                                                           |                                                                                                                       | sighup     | integer | configuration file | 2       | 2147483647 |                                                                      | 1024              | 1024               |            |            | f
 min_wal_size               | 80                 | MB   | Write-Ahead Log / Checkpoints                        | Sets the minimum size to shrink the WAL to.                                                                             |                                                                                                                       | sighup     | integer | configuration file | 2       | 2147483647 |                                                                      | 80                | 80                 |            |            | f
 server_encoding            | UTF8               |      | Preset Options                                       | Shows the server (database) character set encoding.                                                                     |                                                                                                                       | internal   | string  | override           |         |            |                                                                      | SQL_ASCII         | UTF8               |            |            | f
 shared_buffers             | 262144             | 8kB  | Resource Usage / Memory                              | Sets the number of shared memory buffers used by the server.                                                            |                                                                                                                       | postmaster | integer | configuration file | 16      | 1073741823 |                                                                      | 1024              | 262144             |            |            | f
 temp_buffers               | 32768              | 8kB  | Resource Usage / Memory                              | Sets the maximum number of temporary buffers used by each session.                                                      |                                                                                                                       | user       | integer | configuration file | 100     | 1073741823 |                                                                      | 1024              | 32768              |            |            | f
 TimeZone                   | UTC                |      | Client Connection Defaults / Locale and Formatting   | Sets the time zone for displaying and interpreting time stamps.                                                         |                                                                                                                       | user       | string  | configuration file |         |            |                                                                      | GMT               | UTC                |            |            | f
 transaction_deferrable     | off                |      | Client Connection Defaults / Statement Behavior      | Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures. |                                                                                                                       | user       | bool    | override           |         |            |                                                                      | off               | off                |            |            | f
 transaction_isolation      | read committed     |      | Client Connection Defaults / Statement Behavior      | Sets the current transaction's isolation level.                                                                         |                                                                                                                       | user       | enum    | override           |         |            | {serializable,"repeatable read","read committed","read uncommitted"} | read committed    | read committed     |            |            | f
 transaction_read_only      | off                |      | Client Connection Defaults / Statement Behavior      | Sets the current transaction's read-only status.                                                                        |                                                                                                                       | user       | bool    | override           |         |            |                                                                      | off               | off                |            |            | f
 wal_buffers                | 2048               | 8kB  | Write-Ahead Log / Settings                           | Sets the number of disk-page buffers in shared memory for WAL.                                                          |                                                                                                                       | postmaster | integer | override           | -1      | 262143     |                                                                      | -1                | 2048               |            |            | f
 wal_segment_size           | 16777216           | B    | Preset Options                                       | Shows the size of write ahead log segments.                                                                             |                                                                                                                       | internal   | integer | override           | 1048576 | 1073741824 |                                                                      | 16777216          | 16777216           |            |            | f
 work_mem                   | 10240              | kB   | Resource Usage / Memory                              | Sets the maximum memory to be used for query workspaces.                                                                | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. | user       | integer | configuration file | 64      | 2147483647 |                                                                      | 4096              | 10240              |            |            | f
(33 rows)

• What is the output of this shell command (run while the system is feeling sluggish):
  zammad run rails r 'p Delayed::Job.count'
  ** I will run this tomorrow when it’s under heavier load and edit the result in here **

• What is the output of these shell commands?
  zammad config:get WEB_CONCURRENCY
zammad config:get ZAMMAD_SESSION_JOBS_CONCURRENT
  This one is interesting as I have previously set both of these to 8 and restarted the zammad service, but the below output seems to indicate that they’re not set?

[zammad@ip-172-31-0-221 ~]$ zammad config:get WEB_CONCURRENCY
[zammad@ip-172-31-0-221 ~]$ zammad config:get ZAMMAD_SESSION_JOBS_CONCURRENT
[zammad@ip-172-31-0-221 ~]$

Thank you for your help so far, I’ll play around again with those last two config parameters and see if I can get them to take effect, not sure why they had not done last time

[dvanzuijlekom]

Re-setting the WEB_CONCURRENCY and ZAMMAD_SESSION_JOBS_CONCURRENT to the values you’ve previously set would probably help you a lot already and might be the solution to most of the sluggishness. The values of 8 sounds reasonable to me based on your hardware specs and my own trial & error experiences (tune a setting, monitor performance, tune a setting, monitor performance… etc).

For comparison, here are some stats about a few or our setups. Other than the concurrency settings, you might also want to look into PostgreSQL performance tuning, this will help in mitigating deadlock situations as well as increasing base performance. We run our VM’s on speedy SSD’s, so disk I/O is not a huge problem, you will run into performance issues if all your data lives on spinning rust.

The following contains a few examples of our Zammad instance setups, it will probably not fit your specific situation. Do not blindly copy tuning parameters without knowing what effect they will have

For your deadlock problems, you could look into PostgreSQL’s synchronous_commit tunable, but first READ and UNDERSTAND what this does and why it could be dangerous for you.

All our Production instances are on Ubuntu 20.04 and PostgreSQL 12, installed via package, we have recently upgraded from 4.x to 5.0 and we are currently running 5.2.1.

All of these systems have been rebooted just a few hours ago due to kernel upgrades, so memory usage is very low due to caches not being warmed up etc.

System 1, this instance is pretty active, we recently added some more memory, but this is not (yet) reflected in the tuning parameters:

root@helpdesk:~# diff /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf-20210802 
64,65c64
< # 20210802, DVZ: Raise connectionlimit
< max_connections = 200                 # (change requires restart)
---
> max_connections = 100                 # (change requires restart)
122,123c121
< # 20210802, DVZ: Increase to allow for more indices to be cached etc
< shared_buffers = 512MB                        # min 128kB
---
> shared_buffers = 128MB                        # min 128kB
132,133c130
< # 20211009, DVZ: Allow for more in-memory results
< work_mem = 8MB                                # min 64kB
---
> #work_mem = 4MB                               # min 64kB
201,202c198
< # 20210802, DVZ: Decrease transaction completion time
< synchronous_commit = off              # synchronization level;
---
> #synchronous_commit = on              # synchronization level;
495,496c491
< # 20210802, DVZ: Log slow queries
< log_min_duration_statement = 50               # -1 is disabled, 0 logs all statements
---
> #log_min_duration_statement = -1      # -1 is disabled, 0 logs all statements
538,540c533,534
< # 20210802, DVZ: Log waits, DDL changes and tempfiles
< log_lock_waits = on                   # log lock waits >= deadlock_timeout
< log_statement = 'ddl'                 # none, ddl, mod, all
---
> #log_lock_waits = off                 # log lock waits >= deadlock_timeout
> #log_statement = 'none'                       # none, ddl, mod, all
542c536
< log_temp_files = 0                    # log temporary files equal or larger
---
> #log_temp_files = -1                  # log temporary files equal or larger

root@helpdesk:~# grep -c ^processor /proc/cpuinfo 
4

root@helpdesk:~# free -m
              total        used        free      shared  buff/cache   available
Mem:          16009        4705        8944         164        2359       10813
Swap:          3903           0        3903

root@helpdesk:~# zammad config:get WEB_CONCURRENCY
5
root@helpdesk:~# zammad config:get ZAMMAD_SESSION_JOBS_CONCURRENT
8

System 2, this instance is barely used anymore. Due to the low volume of tickets, we used to analyze queries, hence the query logging and auto_explain module being active (which is usually a bad idea in PROD):

root@helpdesk:~# diff /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf-20210730 
64,65c64
< # 20210730, DVZ: Raise connectionlimit
< max_connections = 200                 # (change requires restart)
---
> max_connections = 100                 # (change requires restart)
122,123c121
< # 20210802, DVZ: Increase to allow for more indices to be cached etc
< shared_buffers = 512MB                        # min 128kB
---
> shared_buffers = 128MB                        # min 128kB
132,133c130
< # 20211009, DVZ: Allow for more in-memory results
< work_mem = 8MB                                # min 64kB
---
> #work_mem = 4MB                               # min 64kB
201,202c198
< # 20210802, DVZ: Decrease transaction completion time
< synchronous_commit = off              # synchronization level;
---
> #synchronous_commit = on              # synchronization level;
495,496c491
< # 20210730, DVZ: Log slow queries
< log_min_duration_statement = 50               # -1 is disabled, 0 logs all statements
---
> #log_min_duration_statement = -1      # -1 is disabled, 0 logs all statements
538,540c533,534
< # 20210730, DVZ: Log waits, DDL changes and tempfiles
< log_lock_waits = on                   # log lock waits >= deadlock_timeout
< log_statement = 'ddl'                 # none, ddl, mod, all
---
> #log_lock_waits = off                 # log lock waits >= deadlock_timeout
> #log_statement = 'none'                       # none, ddl, mod, all
542c536
< log_temp_files = 0                    # log temporary files equal or larger
---
> #log_temp_files = -1                  # log temporary files equal or larger
684,685c678
< # 20210731, DVZ: Show queryplans of slow queries
< session_preload_libraries = 'auto_explain'
---
> #session_preload_libraries = ''
758,761d750
< 
< # 20210731, DVZ: Explain ALL the things!
< auto_explain.log_min_duration = '100ms'
< auto_explain.log_analyze = true

root@helpdesk:~# grep -c ^processor /proc/cpuinfo 
2

root@helpdesk:~# free -m
              total        used        free      shared  buff/cache   available
Mem:           7957        4642        2229         168        1085        2882
Swap:          3903           0        3903

root@helpdesk:~# zammad config:get WEB_CONCURRENCY
5
root@helpdesk:~# zammad config:get ZAMMAD_SESSION_JOBS_CONCURRENT
4

System 3, one of our instances which used to be very active and under constant high load. We can probably scale this system down quite a bit.

root@helpdesk:~# diff /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf-20210730 
64,65c64
< # 20210802, DVZ: RDO#95573 Increase connectionlimit
< max_connections = 300                 # (change requires restart)
---
> max_connections = 100                 # (change requires restart)
122,123c121
< # 20210802, DVZ: RDO#95573 Increase to allow for more indices to be cached etc
< shared_buffers = 4096MB                       # min 128kB
---
> shared_buffers = 128MB                        # min 128kB
132,136c130,132
< # 20211009, DVZ: Allow for more in-memory results
< work_mem = 8MB                                # min 64kB
< # 20210824, DVZ: Advice from Nibble IT
< maintenance_work_mem = 265MB          # min 1MB
< autovacuum_work_mem = 64MB            # min 1MB, or -1 to use maintenance_work_mem
---
> #work_mem = 4MB                               # min 64kB
> #maintenance_work_mem = 64MB          # min 1MB
> #autovacuum_work_mem = -1             # min 1MB, or -1 to use maintenance_work_mem
202,203c198
< # 20210802, DVZ: RDO#95573 Decrease transaction completion time
< synchronous_commit = off              # synchronization level;
---
> #synchronous_commit = on              # synchronization level;
392,393c387
< # 20210805, DVZ: RDO#95573 Give PostgreSQL an estimate on available memory for caching
< effective_cache_size = 8GB
---
> #effective_cache_size = 4GB
497,498c491
< # 20210730, DVZ: RDO#95573 Log (very) slow queries
< log_min_duration_statement = 200      # -1 is disabled, 0 logs all statements
---
> #log_min_duration_statement = -1      # -1 is disabled, 0 logs all statements
513,515c506,508
< log_checkpoints = on
< log_connections = on
< log_disconnections = on
---
> #log_checkpoints = off
> #log_connections = off
> #log_disconnections = off
540,542c533,534
< # 20210730, DVZ: RDO#95573 Log waits, DDL changes and tempfiles
< log_lock_waits = on                   # log lock waits >= deadlock_timeout
< log_statement = 'ddl'                 # none, ddl, mod, all
---
> #log_lock_waits = off                 # log lock waits >= deadlock_timeout
> #log_statement = 'none'                       # none, ddl, mod, all
544c536
< log_temp_files = 0                    # log temporary files equal or larger
---
> #log_temp_files = -1                  # log temporary files equal or larger
586c578
< log_autovacuum_min_duration = 0               # -1 disables, 0 logs all actions and
---
> #log_autovacuum_min_duration = -1     # -1 disables, 0 logs all actions and

root@helpdesk:~# grep -c ^process /proc/cpuinfo 
16

root@helpdesk:~# free -m
              total        used        free      shared  buff/cache   available
Mem:          42142        8662       31642         467        1836       32510
Swap:          7999           0        7999

root@helpdesk:~# zammad config:get WEB_CONCURRENCY
10
root@helpdesk:~# zammad config:get ZAMMAD_SESSION_JOBS_CONCURRENT
13

[fitzy89]

Thank you for your help with this issue. After a week of testing and zero complaints from the agents it’s been amazing, so much faster now. Indeed it was those concurrency options that were to blame and once those had been re-set it now runs fast again.

I have not further analysed Postgre yet given that the above settings have resolved our issue, I may revisit that in future but for now it’s working well and I’m happy to leave it

Thank you again