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