[postgis-users] Possible bug in postgis RC3 / proj4 withlargedatasets
strk at refractions.net
strk at refractions.net
Mon Mar 14 00:18:54 PST 2005
On Sun, Mar 13, 2005 at 11:00:27PM +1300, Bryce Watkins wrote:
> Hi strk, the plot thickens - I tried creating a new BSD/Posgresql/Postgis
> install (5.3/8.01/RC3) to test out cvs version (thought I'd test what
> shouldn't work first). Ran the same pgsql2shp dump that failed on our prod
> server and this time it worked on my new dev server.
>
> There are currently 2 differences that I can see. My dev server has had no
> changes to postgresql.conf and also when I run "select
> postgis_full_version()" I get POSTGIS="1.0.0RC3" GEOS="2.0.1" PROJ="Rel.
> 4.4.9, 29 Oct 2004" USE_STATS DBPROC="0.2.0" RELPROC="0.2.0"
> But running the same on prod server I get POSTGIS="1.0.0RC3" GEOS="2.0.1"
> PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS DBPROC="0.1.0" RELPROC="0.2.0"
> (needs proc upgrade)
>
> What is DBPROC?
DB-stored procedures, the plpgsql/sql ones, which are stored 'verbatim'
in pgsql system tables. Those ones can't be upgraded by simply replacing
old postgis shared object with the new one, so if the version of the
installed ones is different from version of released ones (RELPROC)
you need a procedures upgrade (dump/reload).
--strk;
>
> Cheers,
> Bryce.
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> strk at refractions.net
> Sent: Saturday, 12 March 2005 6:43 a.m.
> To: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] Possible bug in postgis RC3 / proj4
> withlargedatasets
>
> On Fri, Mar 11, 2005 at 11:14:54PM +1300, Bryce Watkins wrote:
> > Ok it was successful without the where clause, but upon watching the
> > process utilisation it did continue to consume a lot of memory - but
> > it finished before it ran out.
>
> Bryce, I've made both transform() early relese internally-allocated memory.
> Might be the case that a longer-lived memory context is used in your
> specific query. Please try current CVS and let me know if it fixes your
> problem.
>
> --strk;
>
> >
> > The server has 2Gb ram and I have the following settings:
> > shared_buffers = 45000 # min 16, at least max_connections*2, 8KB
> > each
> > work_mem = 10240 # min 64, size in KB
> >
> > I have tried reducing these values but then it just fails sooner.
> >
> > I have included a text file with a bit of a process log as it runs.
> >
> > Also FYI:
> > Explain SELECT distinct geometrytype(point) from address_geom where
> > NOT geometrytype(point) is null;
> >
> > QUERY PLAN
> > Unique (cost=272467.60..279964.39 rows=1499359 width=25)
> > -> Sort (cost=272467.60..276216.00 rows=1499359 width=25)
> > Sort Key:
> > geometrytype(transform(translate(crs_street_address.shape,
> > 160::double precision, 0::double precision, 0::double precision), 27200))
> > -> Hash Join (cost=1750.10..97787.90 rows=1499359 width=25)
> > Hash Cond: ("outer".rna_id = "inner".id)
> > -> Seq Scan on crs_street_address (cost=0.00..47308.63
> > rows=1499359 width=29)
> > Filter: (NOT
> > (geometrytype(transform(translate(shape,
> > 160::double precision, 0::double precision, 0::double precision),
> > 27200)) IS
> > NULL))
> > -> Hash (cost=1579.68..1579.68 rows=68168 width=4)
> > -> Seq Scan on crs_road_name (cost=0.00..1579.68
> > rows=68168 width=4)
> >
> > Cheers,
> > Bryce.
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> > strk at refractions.net
> > Sent: Friday, 11 March 2005 8:06 p.m.
> > To: 'PostGIS Users Discussion'
> > Subject: Re: [postgis-users] Possible bug in postgis RC3 / proj4
> > withlargedata sets
> >
> > On Fri, Mar 11, 2005 at 01:58:53PM +1300, Bryce Watkins wrote:
> > > Yep that query did indeed directly cause the error to occur.
> >
> > Do you still get it removing the WHERE clause ?
> > Can you monitor process size while running ?
> > --strk;
> >
> > >
> > > Cheers,
> > > Bryce.
> > >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net
> > > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> > > strk at refractions.net
> > > Sent: Thursday, 10 March 2005 2:40 a.m.
> > > To: postgis-users at postgis.refractions.net
> > > Subject: Re: [postgis-users] Possible bug in postgis RC3 / proj4
> > > with largedata sets
> > >
> > > Try defining VERBOSE to 3 on top of pgsql2shp.c it will print
> > > queries as they are executed.
> > >
> > > I have checked the getGeometryType() function, and the two queries
> > > it execute both do release allocated memory before continuing.
> > >
> > > The error message should be generated from the following query:
> > >
> > > SELECT distinct geometrytype(the_geom) from table where
> > > NOT geometrytype(the_geom) is null;
> > >
> > > Try that directly with the monitor.
> > >
> > > --strk;
> > >
> > > On Wed, Mar 09, 2005 at 11:17:35PM +1300, Bryce Watkins wrote:
> > > > Hi folks,
> > > >
> > > > We have a problem when running pgsql2shp on a large dataset via
> > > > the following query (as a view):
> > > >
> > > > SELECT crs_street_address.house_number AS house_no,
> > > > crs_street_address.status, crs_road_name."type",
> > > > crs_road_name.name, crs_road_name."location",
> > > > transform(translate(crs_street_address.shape,
> > > > 160::double precision, 0::double precision, 0::double precision),
> > > > 27200) AS point
> > > > FROM crs_street_address
> > > > JOIN crs_road_name ON crs_street_address.rna_id =
> > > > crs_road_name.id;
> > > >
> > > > QUERY PLAN
> > > > Merge Join (cost=0.00..83997.59 rows=1506893 width=86)
> > > > Merge Cond: ("outer".id = "inner".rna_id)
> > > > -> Index Scan using crs_road_name_id_idx on crs_road_name
> > > > (cost=0.00..1854.48 rows=68168 width=51)
> > > > -> Index Scan using crs_street_address_rna_id_idx on
> > > > crs_street_address
> > > > (cost=0.00..55602.24 rows=1506893 width=43)
> > > >
> > > > The job runs for quite some time, but eventually will output the
> > > following:
> > > > Initializing... GeometryType: ERROR: out of memory
> > > > DETAIL: Failed on request of size 63.
> > > > CONTEXT: SQL function "get_proj4_from_srid" statement 1 PL/pgSQL
> > > > function "transform" line 2 at return
> > > >
> > > > This is running on FreeBSD 5.3 with 2Gb ram and as I watch memory
> > > > utilisation, it is not using any swap at all. So it doesn't
> > > > appear to be a traditional memory leak. Also we have other querys
> > > > that work find on a smaller number of rows.
> > > >
> > > > Any suggestions appreciated.
> > > >
> > > > Bryce.
> > >
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> > === Without where clause - after approx 30 secs.
> >
> > last pid: 76929; load averages: 0.72, 0.61, 0.43
> up 8+00:45:17 22:46:51
> > 43 processes: 2 running, 41 sleeping
> > CPU states: 24.9% user, 0.0% nice, 0.2% system, 0.1% interrupt,
> > 74.8% idle
> > Mem: 148M Active, 996M Inact, 131M Wired, 87M Cache, 112M Buf, 454M
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 102 0 473M 271M CPU1 1 11:27 87.60% 87.60%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> > 502 root 96 0 5664K 1848K select 3 0:27 0.00% 0.00% nmbd
> >
> >
> >
> > === Without where clause - shortly before succesfull completion.
> >
> > last pid: 76929; load averages: 0.97, 0.75, 0.51
> up 8+00:47:34 22:49:08
> > 43 processes: 2 running, 41 sleeping
> > CPU states: 24.7% user, 0.0% nice, 0.3% system, 0.0% interrupt,
> > 75.0% idle
> > Mem: 375M Active, 996M Inact, 181M Wired, 87M Cache, 112M Buf, 177M
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 106 0 705M 498M CPU0 0 13:44 99.02% 99.02%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> > 502 root 96 0 5664K 1848K select 0 0:27 0.00% 0.00% nmbd
> > 435 root 96 0 3636K 2164K select 1 0:16 0.00% 0.00%
> sendmail
> >
> >
> >
> > === Without where clause - shortly after succesfull completion.
> >
> > last pid: 76929; load averages: 0.95, 0.65, 0.43
> up 8+00:43:37 22:45:11
> > 43 processes: 1 running, 42 sleeping
> > CPU states: 20.9% user, 0.0% nice, 6.4% system, 0.5% interrupt,
> > 72.2% idle
> > Mem: 67M Active, 996M Inact, 121M Wired, 87M Cache, 112M Buf, 545M
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 4 0 382M 190M sbwait 0 10:46 98.97% 98.97%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> >
> >
> >
> >
> > === With where clause - after 30 secs
> >
> > last pid: 76910; load averages: 0.48, 0.35, 0.19
> up 8+00:33:30 22:35:04
> > 43 processes: 2 running, 41 sleeping
> > CPU states: 25.0% user, 0.0% nice, 0.1% system, 0.1% interrupt,
> > 74.8% idle
> > Mem: 135M Active, 1151M Inact, 123M Wired, 99M Cache, 112M Buf, 308M
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 102 0 464M 258M CPU2 2 3:37 79.93% 79.93%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> > 502 root 96 0 5664K 1848K select 1 0:27 0.00% 0.00% nmbd
> > 435 root 96 0 3636K 2164K select 0 0:16 0.00% 0.00%
> sendmail
> > 530 root 96 0 1284K 680K select 1 0:04 0.00% 0.00% moused
> > 68330 root 96 0 5592K 4404K select 2 0:04 0.00% 0.00% cupsd
> >
> >
> >
> > === With where clause - just before crash
> >
> > last pid: 76910; load averages: 0.99, 0.71, 0.38
> up 8+00:37:31 22:39:05
> > 43 processes: 2 running, 41 sleeping
> > CPU states: 24.5% user, 0.0% nice, 0.8% system, 0.0% interrupt,
> > 74.7% idle
> > Mem: 550M Active, 996M Inact, 168M Wired, 99M Cache, 112M Buf, 3232K
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 107 0 881M 674M CPU0 0 7:38 99.02% 99.02%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> > 502 root 96 0 5664K 1848K select 2 0:27 0.00% 0.00% nmbd
> > 435 root 96 0 3636K 2164K select 0 0:16 0.00% 0.00%
> sendmail
> >
> >
> > === With where clause - just after crash
> >
> > last pid: 76913; load averages: 0.39, 0.59, 0.35
> up 8+00:38:31 22:40:05
> > 43 processes: 1 running, 42 sleeping
> > CPU states: 0.0% user, 0.0% nice, 0.3% system, 0.1% interrupt,
> > 99.6% idle
> > Mem: 67M Active, 996M Inact, 122M Wired, 87M Cache, 112M Buf, 545M
> > Free
> > Swap: 4095M Total, 284K Used, 4094M Free
> >
> > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU
> COMMAND
> > 76889 pgsql 4 0 382M 190M sbwait 0 7:45 6.30% 6.30%
> postgres
> > 577 root 96 0 95780K 94344K select 3 15:09 0.00% 0.00% Xorg
> > 502 root 96 0 5664K 1848K select 3 0:27 0.00% 0.00% nmbd
> > 435 root 96 0 3636K 2164K select 3 0:16 0.00% 0.00%
> sendmail
> > 530 root 96 0 1284K 680K select 1 0:04 0.00% 0.00% moused
> > 68330 root 96 0 5592K 4404K select 2 0:04 0.00% 0.00% cupsd
> >
>
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list