CODEWITHSUNDEEP
postgresqlshared-memory
Andrew
Andrew

Reputation: 836

PostgreSQL V12 create temp table runs out of shared memory, can I create on disk?

Running on RHEL 7 PostgreSQL Version 12 System has 28G Memory, and 12G shared Memory The DB uses over 6T on disk Some rows have around 300 million rows.

Moved my DB from version 9 to version 12 and am running tests on the new DB. We have a process that generates summary data in a temporary table and then we query the temporary table for different things, and then we delete the temporary table - much faster than running very similar queries multiple times is why this was done.

They query is similar to this: 

CREATE TEMPORARY TABLE
        XXX
        AS
        SELECT
        COUNT(t.id) AS count,
        t.tagged AS tagged,
        t.tag_state AS tag_state,
        t.error AS error,
        td.duplicate AS duplicate
        FROM
        ttt t
        INNER JOIN tweet_data td ON (td.tweet_id = t.id)
        GROUP BY
        t.tagged,
        t.tag_state,
        t.error,
        td.duplicate;

Note that this works fine on V9, but, I have not watched it very carefully on V9 to see what it does. On V12, shared memory usage grows slowly and then after about 15 minutes it kicks into high gear, grows to about 12G and then tries to make it bigger and failes:

The error is: ERROR: could not resize shared memory segment "/PostgreSQL.868719775" to 2147483648 bytes: No space left on device

On a whim, we ran just the select statement without creating the temporary table and it also failed while shared memory was increasing, but, the error message said that it was killed by admin.

I am currently running vacuum against the DB to see if that helps.

The largest concern is that this does work with V9, but fails on V12. I also know that they query engine is very different and new in V12 compared to V9.

I had some crazy hope that running vacuum in stages would make a difference. The data was migrated using pg_upgrade.

vacuumdb -U postgres -p 5431 --all --analyze-in-stages

I don't know if the temporary table is created or not, but, after running vacuum, we ran the full query again creating the temp table and it also failed.

Any thoughts? Is my only choice to try more shared memory?

Upvotes: 0

Views: 729

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

These shared memory segments are used for communication between worker processes with parallel query.

PostgreSQL seems to be tight on resources, and while the error is a symptom rather than the cause of the problem, you can improve the situation by disabling parallel query for this statement:

SET max_parallel_workers_per_gather = 0;

Then your query will take more time, but use less resources, which might be enough to get rid of the problem.

In the long run, you should review your configuration, which might be too generous with memory or the number of connections, but I cannot diagnose that from here.

Upvotes: 1

Related Questions