PostGIS vs. Shapefile

[Daniele Margotti]

In /var/log I only have a 0-byte pgsql file (but in my postgresql.conf I
didn't set any debug option...).

Inspecting top on the server (which normally is 100% idle), I can see
that during elaboration of the map (/cgi-bin/mapserv?mode=browse)
involved processes are "mapserv" and "postmaster", that use 100% CPU
(sharing it in various %) for about 2 seconds.

During query, the process "postmaster" monopolizes CPU with 99.9% load,
for many seconds, during which sometimes appear "mapserv" and "httpd"
processes: such situation starts when I click with the mouse and ends
when the page is completely transmitted to the browser (I can see the
page slowly composing, as if I had a 14.4 Kbps connection, instead of
being LAN-connected!).

With shapefile query, the page is instantaneously shown.

        Daniele


-----Original Message-----
From: Umberto Nicoletti [mailto:unicoletti at prometeo.it]
Sent: Friday, February 11, 2005 9:29 AM
To: Daniele Margotti
Cc: Mapserver ML
Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile


They could be in /var/log then.
Try running top on the server to see if there is load during the request
and what process is causing it.

Please make sure you understand how to index data in postgis. They have
rather good documentation on how to speed up mapserver queries.

Regards,
Umberto

On Fri, 2005-02-11 at 09:16 +0100, Daniele Margotti wrote:
> My connection string is:
>
> DATA "the_geom from my_table01"
> CONNECTION "user=postgres dbname=my_database host=localhost port=5432"

> CONNECTIONTYPE postgis
>
> Where can I find logs?
> I installed PostgreSQL via RPMs, which did put the program in
> /var/lib/pgsql:
>
> # ls -al /var/lib/pgsql/data/
> totale 52
> drwx------    6 postgres postgres     4096 10 feb 16:37 .
> drwx------    4 postgres postgres     4096 10 feb 17:02 ..
> drwx------    5 postgres postgres     4096 10 feb 12:38 base
> drwx------    2 postgres postgres     4096 11 feb 09:09 global
> drwx------    2 postgres postgres     4096  8 feb 15:57 pg_clog
> -rw-------    1 postgres postgres     2572 10 feb 10:13 pg_hba.conf
> -rw-------    1 postgres postgres     1441  8 feb 15:58 pg_ident.conf
> -rw-------    1 postgres postgres        4  8 feb 15:57 PG_VERSION
> drwx------    2 postgres postgres     4096 10 feb 13:09 pg_xlog
> -rw-------    1 postgres postgres     5238  9 feb 14:03
postgresql.conf
> -rw-------    1 postgres postgres       32 10 feb 15:17
postmaster.opts
> -rw-------    1 postgres postgres       45 10 feb 15:17 postmaster.pid
>
> In pg_clog and pg_xlog I have no text logs, only data files that are
> not comprehensible.
>
>         Daniele
>
>
>
> -----Original Message-----
> From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU]
> On Behalf Of Umberto Nicoletti
> Sent: Friday, February 11, 2005 8:29 AM
> To: MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>
>
> Ciao Daniele,
> Could be a name resolution problem. Are you using an ip address or a
> host name to specify the postgres connection string? If you are using
> a name please make sure that it can be resolved correctly by
> registering it in dns and/or in the /etc/hosts file.
>
> Did you try localhost in the connection string?
> Also have a look at the log files in /var/lib/postgres/data.
>
> HTH,
> Umberto
>
> On Thu, 2005-02-10 at 17:01 +0100, Daniele Margotti wrote:
> > Yes, Mapserver is in the same computer of PostGIS: a Linux Red Hat
> > 9. Computer is rather fast: I can dump a 33000-row table (14 Mb
> > dumped to
> > file) in about 3 seconds.
> >
> > And also when I connect from a Windows XP (via Lan) using ODBC
> > drivers, data are read very fast.
> >
> > Only Mapserver is slow...
> >
> >         Daniele
> >
> >
> > >-----Original Message-----
> > >From: UMN MapServer Users List
> > >[mailto:MAPSERVER-USERS at LISTS.UMN.EDU]
> > On Behalf Of Lyndon Tiu
> > >Sent: Thursday, February 10, 2005 4:36 PM
> > >To: MAPSERVER-USERS at LISTS.UMN.EDU
> > >Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
> > >
> > >
> > >On February 10, 2005 02:03 am, Daniele Margotti wrote:
> > >> Hi all,
> > >> I have made some spatial queries (with cgi-bin Mapserver 4.2.3)
> > >> alternately with data stored in shapefiles and PostGIS (imported
> > >> from
> >
> > >> these shapefiles).
> > >>
> > >> My query (304 elements on 5 layers) takes 5 seconds (elaboration,

> > >> and
> >
> > >> then output on browser) if data are read from shapefiles, and
> > >> about
> > 50
> > >> seconds if data are read from PostGIS.
> > >>
> > >> The query is the same: I simply press "reload" on my browser
> > >> (after
>
> > >> manually changed .map file and query template files) to be sure
> > >> of it...
> > >>
> > >> Is it normal?
> > >>
> > >
> > >Is Postgis in the same computer as mapserver?
> > >
> > >--
> > >Lyndon Tiu
> > >
> --
> Umberto Nicoletti <unicoletti at prometeo.it>
> Prometeo SRL
--
Umberto Nicoletti <unicoletti at prometeo.it>
Prometeo SRL