Re: pgsql_tmp consuming all inodes

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