strange performance problem - Mailing list pgsql-general
From | Linos |
---|---|
Subject | strange performance problem |
Date | |
Msg-id | 49A7E4D9.2010303@linos.es Whole thread Raw |
Responses |
Re: strange performance problem
|
List | pgsql-general |
Hello i have the same table with the same data in my development machine and in a small server in production. The table is this: Tabla «modelo_subfamilia» Columna | Tipo | Modificadores ---------------+-----------------------+--------------- nombre | character varying(40) | not null subfamilia_id | character(4) | not null id_familia | character(4) | not null hasta | character(4) | not null foto | bytea | id_seccion | integer | id_categoria | integer | Índices: «modelo_subfamilia_pkey» PRIMARY KEY, btree (subfamilia_id) «uq_hasta_index_modsubfam» UNIQUE, btree (hasta) «nombre_index_modsubfam» btree (nombre) the column "foto" has a toast table, aside from the fact that it have in the server three triggers they are exactly the same, with the same data too, my development machine has version 8.3.6 (linux kernel 2.6.28) and production server has version 8.3.3 (linux kernel 2.6.26), the settings in postgresql.conf are nearly the same except for work_men (24 server, 36 development machine) and effective_cache_size (1024 server, 1536 development machine), they have the same sysctl settings and limits too, and the same mount options for the ext3 filesystem that have the data, i have a single sata disk (wd velociraptor) in my development machine and the server it is using a linux software raid10 with 4 sata disks. I have detected that a simple query from the application i am developing in QT it is really fast in my machine and takes too much time in production server, i am logging the queries that spend more than 500ms so i have this in the log. 2009-02-27 13:51:15 CET 127.0.0.1LOG: duración: 4231.045 ms sentencia: SELECT "nombre", "subfamilia_id", "id_familia", "hasta", "foto", "id_seccion", "id_categoria" FROM "modelo_subfamilia" so i have been testing in my machine and in the server the same query to see the difference. EXPLAIN ANALYZE: -development: Seq Scan on modelo_subfamilia (cost=0.00..11.68 rows=368 width=73) (actual time=0.010..0.092 rows=368 loops=1) Total runtime: 0.174 ms -server: Seq Scan on modelo_subfamilia (cost=0.00..6.10 rows=368 width=69) (actual time=0.008..0.158 rows=368 loops=1) Total runtime: 0.289 ms PSQL with \timing: -development: Time: 72,441 ms -server: Time: 78,762 ms but if i load it from QT or from pgadmin i get more than 4 seconds in server and ~100ms in develoment machime, if i try the query without the "foto" column i get 2ms in development and 30ms in server so the difference its there anyway but not in psql commandline it seems to be only when accessing from a graphical front end, and with the complete query with foto column included i get the postgresql process to eat 90% of the cpu for the complete 4 seconds that it gets to send me the result so it not seems to be a problem with the cpu usage from the graphic libs (no QT or WxWindows), how could i debug this problem?, where should i begin to search? Thanks. Regards, Miguel Angel.
pgsql-general by date: