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

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