[postgis-users] ST_Intersects(geom, geom) Memory issue

Paul Ramsey pramsey at opengeo.org
Fri Feb 3 08:20:24 PST 2012


Something in this chain of changes is probably the cause...
P.

On Fri, Feb 3, 2012 at 2:44 AM, Andreas Forø Tollefsen
<andreasft at gmail.com> wrote:
> Hi again...
>
> Downgraded to revision 8001 and the query works.
>
> Query returned successfully: 162082 rows affected, 114992 ms execution time.
>
> Andreas
>
> 2012/2/3 Andreas Forø Tollefsen <andreasft at gmail.com>
>>
>> Hi again,
>>
>> I have a issue with the server crashing when running a simple query i have
>> been using together with previous versions of postgis on the same system.
>> The query intersects two tables with geometry. While i never have had
>> problems with this query before, it is now terminates my postgresql server.
>> It used to take ~5 min to complete, but now it terminates after 2 minutes.
>> When running the query i can see using top that memory rises to 96 %
>> before terminating the postgreql service.
>>
>> My setup is Intel(R) Xeon(R) CPU E31270 @ 3.40GHz with 12GB of ram
>> running:
>>                                                     version
>>
>> ----------------------------------------------------------------------------------------------------------------
>>  PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
>> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
>> (1 row)
>>
>> postgis_full_version
>>
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------
>>  POSTGIS="2.0.0alpha4SVN" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23
>> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
>> USE_STATS
>> (1 row)
>> shared_buffers = 4GB                    # min 128kB
>> temp_buffers = 64MB                     # min 800kB
>> work_mem = 64MB                 # min 64kB
>> maintenance_work_mem = 512MB            # min 1MB
>>
>> Running the query:
>> DROP TABLE IF EXISTS geoepr_cell;
>> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO
>> geoepr_cell
>> FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND
>> a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.geom)
>> AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid, g.startyear,
>> g.endyear, g.cowgroup, a.gridyear ORDER BY gid ;
>> terminates the server with the below messages. I have used the
>> query previously without problems, but after updating to latest subversion
>> it do not work anymore. I am suspecting there is some memory issue.
>> I tried reducing the shared_buffers and disabling memory overcommit
>> (bottom: http://www.postgresql.org/docs/9.1/static/kernel-resources.html).
>>
>> Any help on this would be very much welcome.
>>
>> All the best,
>> Andreas
>>
>> Log:
>>
>> 2012-02-03 09:27:01 CET DETAIL:  The postmaster has commanded this server
>> process to roll back the current transaction and exit, because another
>> server process exited abnormally and possibly corrupted shared memory.
>> 2012-02-03 09:27:01 CET HINT:  In a moment you should be able to reconnect
>> to the database and repeat your command.
>> 2012-02-03 09:27:02 CET LOG:  all server processes terminated;
>> reinitializing
>> 2012-02-03 09:27:03 CET LOG:  database system was interrupted; last known
>> up at 2012-02-03 09:25:37 CET
>> 2012-02-03 09:27:04 CET LOG:  database system was not properly shut down;
>> automatic recovery in progress
>> 2012-02-03 09:27:04 CET LOG:  consistent recovery state reached at
>> 7F/E90EF588
>> 2012-02-03 09:27:04 CET LOG:  record with zero length at 7F/E90EF588
>> 2012-02-03 09:27:04 CET LOG:  redo is not required
>> 2012-02-03 09:27:04 CET LOG:  database system is ready to accept
>> connections
>> 2012-02-03 09:27:04 CET LOG:  autovacuum launcher started
>> 2012-02-03 09:30:07 CET LOG:  incomplete startup packet
>> 2012-02-03 09:30:08 CET LOG:  received fast shutdown request
>> 2012-02-03 09:30:08 CET LOG:  aborting any active transactions
>> 2012-02-03 09:30:09 CET LOG:  autovacuum launcher shutting down
>> 2012-02-03 09:30:09 CET FATAL:  terminating connection due to
>> administrator command
>> 2012-02-03 09:30:09 CET FATAL:  terminating connection due to
>> administrator command
>> 2012-02-03 09:30:09 CET STATEMENT:  DROP TABLE IF EXISTS geoepr_cell;
>>         SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO
>> geoepr_cell
>>         FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid
>> AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell,
>> g.geom)
>>         AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid,
>> g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ;
>> 2012-02-03 09:31:09 CET LOG:  received immediate shutdown request
>> 2012-02-03 09:31:09 CET WARNING:  terminating connection because of crash
>> of another server process
>> 2012-02-03 09:31:09 CET DETAIL:  The postmaster has commanded this server
>> process to roll back the current transaction and exit, because another
>> server process exited abnormally and possibly corrupted shared memory.
>> 2012-02-03 09:31:09 CET HINT:  In a moment you should be able to reconnect
>> to the database and repeat your command.
>>
>
>
>
> _______________________________________________
> 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