Обсуждение: creating an index with tablespace on a dedicated filesystem (postgresql 8.1.5)
Hello, we use tablespace feature to keep indexes on a dedicated raid array (for performance). What I've noticed is that when the index were created, based on the i/o activity it seemed that postgres was creating the index file somewhere on the array where the default table space is hosted and the data is stored. Then, when the index was completed, it moved index file to array for indexes. If my observation is accurate, and it really works this way, then it's definitely point for improvement imho... -- Vlad
Vlad <marchenko@gmail.com> writes: > we use tablespace feature to keep indexes on a dedicated raid array > (for performance). What I've noticed is that when the index were > created, based on the i/o activity it seemed that postgres was > creating the index file somewhere on the array where the default table > space is hosted and the data is stored. Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/. If you have a mind to, you can replace that subdirectory with a symlink to a (suitably secure) directory elsewhere. regards, tom lane
so I guess for performance sake it makes sense do that under $TABLESPACEPATH/yourdb/pgsql_tmp/ in case $TABLESPACEPATH for the object is being created is set to a non-default value > > Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/. If you have a > mind to, you can replace that subdirectory with a symlink to a (suitably > secure) directory elsewhere. > -- Vlad
Temp file space (Re: creating an index with tablespace on a dedicated filesystem)
От
Glen Parker
Дата:
Tom Lane wrote: > Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/. If you have a > mind to, you can replace that subdirectory with a symlink to a (suitably > secure) directory elsewhere. Tom, is it safe to assume that the contents of the temp directory do not need to persist across Postgres restarts? I may move ours to an alternate volume, but the answer to this question will have an impact on my backup strategy. -Glen
Glen Parker <glenebob@nwlink.com> writes: > Tom, is it safe to assume that the contents of the temp directory do not > need to persist across Postgres restarts? Yup, they're only temp files (in fact, I think there is code somewhere that actually runs around and cleans out the temp directories during a restart). regards, tom lane
Tom, which means that earlier suggestion you've made - to host temp dir on a separate hd linked to temp dir under data tree it's not quite a straight solution .... I assume that link will be erased by postgres every time it finishes operating with temp directory. > Yup, they're only temp files (in fact, I think there is code somewhere > that actually runs around and cleans out the temp directories during a > restart). > -- vlad
Vlad wrote: > which means that earlier suggestion you've made - to host temp dir on > a separate hd linked to temp dir under data tree it's not quite a > straight solution .... I assume that link will be erased by postgres > every time it finishes operating with temp directory. Why would the postmaster delete the entire temp dir/link at any point? -Glen
Vlad <marchenko@gmail.com> writes: > which means that earlier suggestion you've made - to host temp dir on > a separate hd linked to temp dir under data tree it's not quite a > straight solution .... I assume that link will be erased by postgres > every time it finishes operating with temp directory. No, it just deletes the contents of the directory, it has no occasion to destroy a symlink. It is true that you'd have to set the symlink up manually any time you created a database, but after that it should be a non-issue. regards, tom lane