All we need is an easy explanation of the problem, so here it is.
While working on performance improvements on our PostgreSQL database (Ubuntu Focal, PostgreSQL 13.3), I created a "temporary" tablespace on a fast local NVME drive. This works well and both temporary tables and temp_files created by large queries end up there, taking a lot of load off the main data drive.
However, we use server side CURSORS a lot, and it appears that temp_files created by those always end up on the main data volume, causing a lot more I/O than we really want there.
2022-03-13 00:59:51.692 UTC 1350170 [email protected] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp1350170.54", size 564228392
2022-03-13 00:59:51.692 UTC 1350170 [email protected] STATEMENT: FETCH FORWARD 5569 FROM "xx"
I’ve done about as much work_mem tuning as I can, but many of our queries are large (temp_files are often over 1GB). Our biggest bottleneck is I/O on the data drive. So, if I can get those temp_files onto the separate local NVME drive instead, that’d do wonders.
I’ve seen a few places suggest replacing the pgsql_tmp dir from ~/main/base/pgsql_tmp with a symlink to the other drive, but also warnings that that isn’t necessarily safe.
Is this a safe way to do this, or is there another, better way? Is there some way to tell PostgreSQL to put temp_files generated by CURSORS onto the temporary drive?
Thanks,
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
If these cursors are declared WITH HOLD, then I think you are running into this bit from src/backend/storage/file/fd.c:
* BUT: if the temp file is slated to outlive the current transaction,
* force it into the database's default tablespace, so that it will not
* pose a threat to possible tablespace drop attempts.
*/
if (numTempTableSpaces > 0 && !interXact)
{
Perhaps this should be documented in user-space, not just the source-code comments.
I don’t see that there is anything you can do about it, other than remove the !interXact test and compile your own binaries.
I might find symlinking ~/main/base/pgsql_tmp to be preferable to that. Did the people warning against it say why?
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0