Re: pgsql_tmp consuming all inodes

On 11/21/2014 06:54 AM, Nestor A. Diaz wrote:
> On 11/20/2014 12:18 PM, Adrian Klaver wrote:
>> What query?
>>
>> How is it executed?
>
> Hi Adrian, this is one of the queries that appear to consume all
> resources, we use a CTE approach ("with") because in 9.1 _sometimes_ the
> planner perform an order by before doing the joins something that was
> killing the performance of the app.
>
> I think it could be a problem of the web app, I turned on the 'csvlog'
> to diagnose the problem and I found that at the same time the query get
> executes one hundred times aprox.

To me it does not look the query is executed one hundred times, it looks
one hundred temp files are created for the query.

>
> This is what got logged at the csv (look at the time, different seconds):
>
> 2014-11-20 09:01:18.493
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35849,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.103510"", size 484",,,,,,"
> 2014-11-20 09:01:18.496
> COT,"db140","db140",11600,"10.36.98.91:57449",546df18f.2d50,35850,"SELECT",2014-11-20
> 08:50:07 COT,38/563454,0,LOG,00000,"temporary file: path
> ""pg_tblspc/16575/pgsql_tmp/pgsql_tmp11600.34356"", size 220",,,,,,"
>
> [...]
>
> As you can see from above it creates a lots of temp files for the same
> query.

So what is the size of the dataset you are working with and what is the
size of the dataset you expect to return?

What are the hardware specs for the machine you are using, in particular
the amount of memory?

Are there other resource intensive programs running on this machine?

>

> Slds.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com