PostgreSQL unlimited growth of pgsql_tmp

[michaelschefczyk]

Dear All,

I am running synapse-1.31.0 as a docker container using a postregsql 11 server on a debian buster virtual machine. This is my only postgresql application, all my other DBs are mariadb.

Recently (still running 1.30.0), I did notice, that the host running postgresql from time to time did experience high load. Postgresql log files showed that pgsql_tmp space was used up.

My databse is < 2 GB in size. I did add a 30 GB disk just vor /var/lib/postrgesql Nevertheless, it does get filled up and CPU utilization yanks high, then. I did apply vacuum, analyze, reindex and cluster maintenance tasks as best as possible, but with no effect.

I did not find feeback in the matrix support channel. Therefore, I am asking here: Is this a known issue? Is there a systematic way to diagnose and cure it? Is there a way to go back to sqlite3? If nothing helps, I will probably need to consider my admin skills insufficient to running self hosted synapse and abandon my home server.

Regards,

Michael Schefczyk

[anoadragon453]

I'm afraid I haven't personally hit this issue before. After doing some research it looks as if setting log_temp_files to 0 (docs) will log the reason that a temp file was used to complete a query. This may help you understand which queries are causing the temporary tablespace to be used and filled.

You can also try raising the value of work_mem in order to allow operations to use system memory rather than disk for storing temporary data.

Is there a way to go back to sqlite3?

We generally don't recommend SQLite3 for production use as it has poor performance overall. PostgreSQL will serve you better if we can figure out your disk issue.

[michaelschefczyk]

Thank you very much for the response!

What I did is set work_mem to 512MB (vs. the default of just 4M in debian postgresql 11). Maybe, I should go up to 2GB since the database file is lightly below 2 GB when the postgresql server is turned off.

I also have log_temp_files on, but I find it hard to interpret the results. They are growing string of statements like below:

2021-04-16 16:19:58.166 CEST [8588] synapse@synapse LOG: temporäre Datei: Pfad »base/pgsql_tmp/pgsql_tmp8588.1«, Größe 258280326 2021-04-16 16:19:58.166 CEST [8588] synapse@synapse ANWEISUNG: WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC 2021-04-16 16:23:09.207 CEST [8588] synapse@synapse LOG: temporäre Datei: Pfad »base/pgsql_tmp/pgsql_tmp8588.2«, Größe 774840978 2021-04-16 16:23:09.207 CEST [8588] synapse@synapse ANWEISUNG: WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC 2021-04-16 16:32:42.982 CEST [8588] synapse@synapse LOG: temporäre Datei: Pfad »base/pgsql_tmp/pgsql_tmp8588.4«, Größe 1073741824 2021-04-16 16:32:42.982 CEST [8588] synapse@synapse ANWEISUNG: WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC 2021-04-16 16:32:43.022 CEST [8588] synapse@synapse LOG: temporäre Datei: Pfad »base/pgsql_tmp/pgsql_tmp8588.6«, Größe 1073741824 2021-04-16 16:32:43.022 CEST [8588] synapse@synapse ANWEISUNG: WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC 2021-04-16 16:32:43.037 CEST [8588] synapse@synapse LOG: temporäre Datei: Pfad »base/pgsql_tmp/pgsql_tmp8588.8«, Größe 177039286 2021-04-16 16:32:43.037 CEST [8588] synapse@synapse ANWEISUNG: WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC

The core content should be:

WITH RECURSIVE state(state_group) AS ( VALUES(29863::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC

How could I find out what this really means and how to keep the server from growing temp files > 10x the size of the database this way?

[michaelschefczyk]

It seems that cooling down the database server was possible using the following parameters - where a high level of work_mem (which is quite low in the default) may do just the opposite of what one may expect:

shared_buffers = 128MB work_mem = 8MB temp_file_limit = 2048MB

That does contain the database of a low-useage homeserver below 1 GB running on a Debian server with sufficient RAM (mostly running a mariadb cluster node). Then, posggresql uses about 2 GB or RAM and a very low faction of CPU.

[richvdh]

sounds like this has been resolved.