From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Konstantin Mikhailov <ekimka@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg 9.1 brings host machine down
Date: Wed, 06 Jun 2012 15:25:29 +0300
Message-ID: <4FCF4C39.6000506@gmail.com> (raw)
In-Reply-To: <CABDd6w_aGZJYn5UF_gGduCY-S1ijXx5m=mF80ePCMQSyowge6g@mail.gmail.com>
References: <CABDd6w_aGZJYn5UF_gGduCY-S1ijXx5m=mF80ePCMQSyowge6g@mail.gmail.com>
Hello.
Seen this already.
It looks like cross join + sort. Badly configured ORM tools like
Hibernate with multiple one-to-many relationships fetched with 'join'
strategy may produce such result.
Unfortunately I don't know if it's possible to protect from such a case
at server side.
Best regards, Vitalii Tymchyshyn
06.06.12 15:05, Konstantin Mikhailov написав(ла):
> 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 :(
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, ekimka@gmail.com
Subject: Re: pg 9.1 brings host machine down
In-Reply-To: <4FCF4C39.6000506@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox