Re: [GENERAL] Help on ṕerformance

Thanks to all

I have fix that refactoring the function

BEGIN
arr := regexp_split_to_array(_imeis, E'\\s+');
RETURN QUERY
SELECT
gpstracking_device_tracks.imei,
gpstracking_device_tracks.device_id,
gpstracking_device_tracks.date_time_process,
gpstracking_device_tracks.latitude,
gpstracking_device_tracks.longitude,
gpstracking_device_tracks.course,
gpstracking_device_tracks.speed,
gpstracking_device_tracks.mileage,
gpstracking_device_tracks.gps_signal,
gpstracking_device_tracks.gsm_signal,
gpstracking_device_tracks.alarm_status,
gpstracking_device_tracks.gps_status,
gpstracking_device_tracks.vehicle_status,
gpstracking_device_tracks.alarm_over_speed,
gpstracking_device_tracks.other,
gpstracking_device_tracks.address
FROM (
SELECT
gpstracking_device_tracks.imei,
gpstracking_device_tracks.device_id,
gpstracking_device_tracks.date_time_process,
gpstracking_device_tracks.latitude,
gpstracking_device_tracks.longitude,
gpstracking_device_tracks.course,
gpstracking_device_tracks.speed,
gpstracking_device_tracks.mileage,
gpstracking_device_tracks.gps_signal,
gpstracking_device_tracks.gsm_signal,
gpstracking_device_tracks.alarm_status,
gpstracking_device_tracks.gps_status,
gpstracking_device_tracks.vehicle_status,
gpstracking_device_tracks.alarm_over_speed,
gpstracking_device_tracks.other,
gpstracking_device_tracks.address,
ROW_NUMBER() OVER(PARTITION BY gpstracking_device_tracks.imei ORDER BY
gpstracking_device_tracks.date_time_process DESC) as rnumber
FROM gpstracking_device_tracks
WHERE gpstracking_device_tracks.imei = ANY(arr)
AND gpstracking_device_tracks.date_time_process >= date_trunc('hour',
now())
AND gpstracking_device_tracks.date_time_process <= NOW()
) AS gpstracking_device_tracks
WHERE gpstracking_device_tracks.rnumber = 1;
END;

2013/10/2 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Mon, Sep 30, 2013 at 10:03 AM, Carlos Eduardo Sotelo Pinto
> <carlos(dot)sotelo(dot)pinto(at)gmail(dot)com> wrote:
> >
> > I need a help on postgresql performance
> >
> > I have configurate my postgresql files for tunning my server, however it
> is
> > slow and cpu resources are highter than 120%
> >
> > I have no idea on how to solve this issue, I was trying to search more
> infor
> > on google but is not enough, I also have try autovacum sentences and
> reindex
> > db, but it continues beeing slow
> >
> > My app is a gps listener that insert more than 6000 records per minutes
> > using a tcp server developed on python twisted, where there is no
> problems,
> > the problem is when I try to follow the gps devices on a map on a
> relatime,
> > I am doing queries each 6 seconds to my database from my django app, for
> > request last position using a stored procedure, but the query get slow on
> > more than 50 devices and cpu start to using more than 120% of its
> resources
> >
> > Django App connect the postgres database directly, and tcp listener
> server
> > for teh devices connect database on threaded way using pgbouncer, I have
> not
> > using my django web app on pgbouncer caause I dont want to crash gps
> devices
> > connection on the pgbouncer
> >
> > I hoe you could help on get a better performance
> >
> > I am attaching my store procedure, my conf files and my cpu, memory
> > information
> >
> > **Stored procedure**
> >
> > CREATE OR REPLACE FUNCTION gps_get_live_location (
> > _imeis varchar(8)
> > )
> > RETURNS TABLE (
> > imei varchar,
> > device_id integer,
> > date_time_process timestamp with time zone,
> > latitude double precision,
> > longitude double precision,
> > course smallint,
> > speed smallint,
> > mileage integer,
> > gps_signal smallint,
> > gsm_signal smallint,
> > alarm_status boolean,
> > gsm_status boolean,
> > vehicle_status boolean,
> > alarm_over_speed boolean,
> > other text,
> > address varchar
> > ) AS $func$
> > DECLARE
> > arr varchar[];
> > BEGIN
> > arr := regexp_split_to_array(_imeis, E'\\s+');
> > FOR i IN 1..array_length(arr, 1) LOOP
> > RETURN QUERY
> > SELECT
> > gpstracking_device_tracks.imei,
> > gpstracking_device_tracks.device_id,
> > gpstracking_device_tracks.date_time_process,
> > gpstracking_device_tracks.latitude,
> > gpstracking_device_tracks.longitude,
> > gpstracking_device_tracks.course,
> > gpstracking_device_tracks.speed,
> > gpstracking_device_tracks.mileage,
> > gpstracking_device_tracks.gps_signal,
> > gpstracking_device_tracks.gsm_signal,
> > gpstracking_device_tracks.alarm_status,
> > gpstracking_device_tracks.gps_status,
> > gpstracking_device_tracks.vehicle_status,
> > gpstracking_device_tracks.alarm_over_speed,
> > gpstracking_device_tracks.other,
> > gpstracking_device_tracks.address
> > FROM gpstracking_device_tracks
> > WHERE gpstracking_device_tracks.imei = arr[i]::VARCHAR
> > AND gpstracking_device_tracks.date_time_process >= date_trunc('hour',
> > now())
> > AND gpstracking_device_tracks.date_time_process <= NOW()
> > ORDER BY gpstracking_device_tracks.date_time_process DESC
> > LIMIT 1;
> > END LOOP;
> > RETURN;
> > END;
> > $func$
> > LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
>
>
> Why are you doing this in a loop? What's the point of the LIMIT 1?
> You can almost certainly refactor this procedure into a vanilla query.
>
> merlin
>

--
Carlos Eduardo Sotelo Pinto | http://carlossotelo.com | csotelo(at)twitter
GNU Linux Admin | PHP Senior Web Developer
Mobil: RPC (Claro)+51, 958194614 | Mov: +51, 959980794
GTalk: carlos(dot)sotelo(dot)pinto(at)gmail(dot)com | Skype: csotelop
MSN: carlos(dot)sotelo(dot)pinto(at)gmail(dot)com | Yahoo: csotelop
GNULinux RU #379182 | GNULinux RM #277661
GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B