6

My database is suddenly hanging on some queries that join many tables and I see:

temporary file size exceeds temp_file_limit (1025563kB)

A sample query and query plan can be seen here:

https://pastebin.com/wug86Wrr

http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

This query normally takes less than 100ms but hangs when hit temporary file size limit.

When I run:

SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;

I see:

Temporary files  Size of temporary files
---
22550            10651900248

How do I resolve this?


I was able to enable more of the problem. Say a user lists they can speak 2 languages - the query runs ok. Then they edit their profile to say they know 20 languages. The query ends up exceeding the temp_file_limit and hanging.

2 Answers 2

4

You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Either way, no wonder you amass a lot of temporary files.

Also note that all temp files of the same session count against the limit. Bound cursors can become a problem that way. Or multiple large queries in the same transaction.

By default the parameter temp_file_limit is set to -1, which means "no limit". So either sanitize your query or remove or extend the limit. (Only superusers can do that.)

If you cannot change either, setting more work_mem might help to keep more operations in RAM, so less temp space is needed.

Don't confuse this setting with temp_buffers, which is mostly unrelated and sets the amount of RAM that can be used for temporary objects (like in CREATE TEMP TABLE ...)

And you did not bother to mention your version of Postgres. Maybe outdated?

9
  • You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Yes, I am! The use case is a detailed user profile, where most fields many-to-many to lookup tables. I am not sure how to optimize that query, so I will create a separate question for that if that's the issue. But increasing my temp_file_limit helped (on Google Cloud SQL it was capped). work_mem was at 4GB and I increased to 64GB, but didn't notice much difference here. Google Cloud SQL is on Postgres 9.6.
    – atkayla
    Mar 24, 2019 at 14:00
  • Setting work_mem too high can have adverse effects. It very much depends on available RAM, number of concurrent sessions and the complete workload, but 4 GB is already an exceptionally high setting and 64 GB seems way out of proportion except for specialized warehouse systems. Mar 24, 2019 at 23:04
  • 1
    I was afraid to hear that. My schema is pretty simple like you stated, just joins to a bunch of small lookup tables to build a full profile. Yes, I would imagine PostgreSQL should be able to handle this. Google Cloud SQL is stuck at 9.6, so I just spun up PostgresSQL 11 on aiven.io to test that out. Do you know of any particular changes that might affect this and performance from 9.6 to 11, or are you saying it's just good in general to upgrade? Anyway, I will test out PostgreSQL 11 and the natural keys to see if there's any significant difference. I appreciate it.
    – atkayla
    Mar 25, 2019 at 2:06
  • 1
    Nothing in particular, and I have only superficially looked at your big query. There have been many improvements especially for big data. Mar 25, 2019 at 2:32
0

Maybe statistics are out of date, now causing the same query plan to run with a much worse query plan?

https://stackoverflow.com/questions/6903938/how-do-i-know-if-the-statistics-of-a-postgres-table-are-up-to-date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.