if you have millions of files in data/pgsql_tmp it means that you're
using temporary tables (very) heavily .. or you've a huge sorting
activity (of large tables) and that the sort happens on disk (you can
verify that with an EXPLAIN ANALYZE of the query, you'll see something
like "external disk merge").
What you can do is either raise work_mem (be careful that it takes more
space to sort in memory than on disk), or add more RAM (and raise
work_mem too). By the way, it is generally a good idea to monitor that
directory to get and idea of how much concurrent sorting is happening on
your database.
In some extreme case you can also create a dedicated tablespace and add
that tablespace to temp_tablespaces.**
**
On 06/06/2012 14:05, Konstantin Mikhailov wrote:
> I'm faced with a problem running postgres 9.1.3 which seems to
> nobody else see before. Tried to search and only one relevant
> post fond (about millions of files in pgsql_tmp).
>
> Sympthoms:
>
> Some postgres process size is getting abnormally big compared
> to other postgres processes. Top shows the 'normal' pg processed
> is about VIRT 120m, RES ~30m and SHR ~30m. That one
> is about 6500m, 3.4g, 30m corresp. Total RAM avail - 8g.
> When one more such a process appears the host going into
> deep swap and pg restart can help only (actually the stop
> won't even stop such a process - after shutdown it still alive
> and can be only killed).
>
> base/pgsql_tmp contains millions of files. In this situation stop
> and dirty restart is possible - the normal startup is impossible
> either. Read somewhere that it tries to delete (a millions
> files) from that directory. I can't even imagine when it finish
> the deletion so i'm simple move that folder outside the base
> - then start can succeed.
>
> on ubuntu 11.10,12.04 x64. cpu intel core Q9650 3GHz.
> 8G RAM.
>
> Does anybody see that behaviour or maybe have some glue how to
> handle it.
>
> PS: the my preliminary conclusion: some sql is produces
> a lot of files in the temporary table spaces - very quickly.
> When sql is finished postgres tries to cleanup the folder
> reading all contents of the folder and removing the files
> one by one. It does the removal slow (watched the folder
> by `find pgsql_tmp | wc -l') but process still consumes the
> RAM. Next such sql will be a killer :(
>
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.