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.
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@xxxxxxxxx
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
