Re: [GENERAL] Re: [GENERAL] Help on ṕerformance - Mailing list pgsql-general

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@gmail.com>

On Mon, Sep 30, 2013 at 10:03 AM, Carlos Eduardo Sotelo Pinto
<carlos.sotelo.pinto@gmail.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@twitter

    GNU Linux Admin | PHP Senior Web Developer

    Mobil: RPC (Claro)+51, 958194614 | Mov: +51, 959980794

    GTalk: carlos.sotelo.pinto@gmail.com | Skype: csotelop

    MSN: carlos.sotelo.pinto@gmail.com | Yahoo: csotelop

    GNULinux RU #379182 | GNULinux RM #277661

GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B