[postgis-users] Possible bug in postgis RC3 / proj4 withlargedata sets
strk at refractions.net
strk at refractions.net
Fri Mar 11 09:42:53 PST 2005
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
More information about the postgis-users
mailing list