Re: pgsql_tmp consuming all inodes - Mailing list pgsql-general

On Fri, 21 Nov 2014 09:54:43 -0500
"Nestor A. Diaz" <nestor@tiendalinux.com> 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.

Are you saying that the _exact_ same query is executed about one hundred
times at approximately the same time?

If this is the case, you'll experience great improvements all around if you
advise your developers that the RDBMS is not RAM, and encourage them to
use a proper MVC model where the model != the RDBMS.

I've worked with this kind of problem repeatedly, and fixing it not only
avoids resource exhaustion problems like you're seeing, but it improves the
performance of the system significantly.

> 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.
>
> And finally the query is this:
>
>  WITH x AS
>   ( SELECT event_time AS x_event_time,
>            system_time AS x_system_time,
>            id,
>            code,
>            metric,
>            event_time,
>            system_time,
>            bev.lat/1e5 AS f_lat,
>            bev.lon/1e5 AS f_lon,
>            ROUND(bev.mph*1 ,1) AS speed,
>            dev.increment AS distance_increment,
>            head,
>            age,
>            device_id,
>            ecu_total_fuel,
>            ecu_total_fuel_flag,
>            ecu_idle_fuel,
>            ecu_idle_fuel_flag,
>            (NOT (bev.lat = 0
>                  AND bev.lon = 0)
>             AND bev.age = 2
>             AND bev.source <= 8) AS valid_position,
>            eev.hdop/100.0 AS f_hdop,
>            georef_id,
>            photo_id,
>            pev.status AS photo_status,
>            grev.type_id AS georef_evtype,
>            COALESCE (
>                        (SELECT dc.ky_id
>                         FROM public.device_deviceconfiguration AS dc
>                         WHERE dc.device_id = ev.device_id
>                           AND dc.time <= ev.system_time
>                           AND dc.state = 3
>                         ORDER BY dc.time DESC LIMIT 1), '0000' ) AS ky,
>                     event_type
>    FROM public.device_basicgpsevent AS bev
>    JOIN public.device_event AS ev ON ev.device_id = 400612026000000
>    AND ev.event_time BETWEEN '2014-11-19 23:00:00-05:00' AND '2014-11-21 00:59:59.999999-05:00'
>    AND bev.event_ptr_id = ev.id
>    AND bev.age IN (1,
>                    2)
>    LEFT JOIN public.device_drivingmets AS dmev ON dmev.basicgpsevent_ptr_id = ev.id
>    LEFT JOIN public.device_georefevent AS grev ON grev.basicgpsevent_ptr_id = ev.id
>    LEFT JOIN public.device_extendedgpsevent AS eev ON eev.basicgpsevent_ptr_id = ev.id
>    LEFT JOIN public.device_distance AS dev ON dev.extendedgpsevent_ptr_id = ev.id
>    LEFT JOIN public.device_photoevent AS pev ON pev.basicgpsevent_ptr_id = ev.id
>    WHERE TRUE )
> SELECT *
> FROM x
> ORDER BY x.x_event_time ,
>          x.x_system_time ;
>
>
> >
> > Well this is coming from the kernel OOM(Out Of Memory) manager.
> >
> > For how to deal with this see here:
> >
> > http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24213
>
> Thanks for the suggestion.
>
> Slds.
>
> --
> Typed on my key64.org keyboard
>
> Nestor A Diaz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran
I need your help to succeed:
http://gamesbybill.com