Summary

A sudden spike in Confluence database server's disk utilization, dropping the total available storage, can lead to the disk space running out of space and to an outage. Simultaneously, multiple temp files are created within the server, which contributes to the decrease in available storage.

Environment

• Confluence On-Prem 7.19.0
• PostgreSQL 11
  
  

Diagnosis

• Rapid usage of disk usage by the database;
• Multiple temporary files are being created by the database;
• The following entry can be seen in the atlassian-confluence.log files:

2023-03-20 19:21:01,271 ERROR [http-nio-8080-exec-46 url: /display/XXXXX/XXXXX, /pages/viewpage.action; user: admin] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp15718.0.sharedfileset/i654of131174.p0.0": No space left on device

Cause

In a PostgreSQL database, there is a setting called work_mem. This setting specifies the amount of memory used by internal sort operations and hash tables before writing to temporary disk files.

By default, the work_mem setting is 4 MB; however, for complex queries, 4 MB is not enough memory. As a result, the database will write data into temporary files and, depending on the operation, can lead to multiple temporary files being created. Those many temp files may be a sign that the database work_mem parameter may be insufficient to handle the requests in the environment, as having a small enough work_mem can cause operations to spill over from work_mem (memory) to disk (which in turn can lead to disk overuse), affecting the performance of the application.

Solution

To prevent the creation of multiple temporary files on disk and, therefore, to improve the performance and free disk storage, the work_mem value can be increased on the database directly:

• Tuning Your PostgreSQL Server