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

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.

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