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 :(
