[OSM-dev] Postgis Plugin: out of memory for query result#

[Karl Wettin]

Hi all,

we're running a switch2osm-package Ubuntu serving tiles. The PostGIS is located on another machine and communicates with the tile server via TCP.

For a couple of months now we've been running against a Sweden-only database without any problems. When now installed planet on a new PostGIS-VM and get these sort of errors when requesting tiles in densely mapped areas on zoom levels that are less than 15 or so:

Feb 16 21:11:51 modtile2 renderd[1353]:    reason: Postgis Plugin: out of memory for query result#012#012in executeQuery Full sql was: 'SELECT ST_AsBinary("way") AS geom,"feature" FROM (select way, religion,#012coalesce (aeroway, amenity, landuse, leisure, military, "natural", power, tourism, highway) as feature from (#012select way,#012('aeroway_' || (case when aeroway in ('apron', 'aerodrome') then aeroway else null end)) as aeroway,#012('amenity_' || (case when amenity in ('parking', 'university', 'college', 'school', 'hospital', 'kindergarten', 'grave_yard') then amenity else null end)) as amenity,#012('landuse_' || (case when landuse in ('quarry', 'vineyard', 'orchard', 'cemetery', 'grave_yard', 'residential', 'garages', 'field', 'meadow', 'grass', 'allotments', 'forest', 'farmyard', 'farm', 'farmland', 'recreation_ground', 'conservation', 'village_green', 'retail', 'industrial', 'railway', 'commercial', 'brownfield', 'landfill', 'greenfield', 'construction') then landuse else null end)) as landuse,#012('leisure_' || (case when leisure in ('swimming_pool', 'playground', 'park', 'recreation_ground', 'common', 'garden', 'golf_course', 'picnic_table') then leisure else null end)) as leisure,#012('military_' || (case when military in ('barracks', 'danger_area') then military else null end)) as military,#012('natural_' || (case when "natural" in ('field','beach','desert','heath','mud','grassland','wood','sand','scrub') then "natural" else null end)) as "natural",#012('power_' || (case when power in ('station','sub_station','generator') then power else null end)) as power,#012('tourism_' || (case when  tourism in ('attraction', 'camp_site', 'caravan_site', 'picnic_site', 'zoo') then tourism else null end)) as tourism,#012('highway_' || (case when highway in ('services', 'rest_area') then highway else null end)) as highway,#012case when religion in ('christian','jewish') then religion else 'INT-generic'::text end as religion#012       from planet_osm_polygon#012       where landuse is not null#012          or leisure

My question is, what process is it that runs out of memory? PostgreSQL? Mapnik? Renderd? My guess is that PostgreSQL manage just fine, passes down a result set with a couple of hundred MB data to Renderd which is not able to allocate this amount of data. Or perhaps it's Mapnik that can't handle the data passed down from Renderd.

The PostgreSQL VM has some 16GB of RAM and plenty of shared- and work memory for the processes, the tile rendering VM has 8GB RAM.  

Most of the time this error get caught and ignored by Renderd, but every fifth time or so it causes Renderd to simply die on us.

After upping shmax to 1GB it works a bit better. After upping it to 4GB we see these in the renderd log:

Feb 16 22:01:16 modtile2 renderd[3065]: DEBUG: Connection 0, fd 12 closed, now 9 left
Feb 16 22:01:38 modtile2 renderd[3065]: ERROR: failed to render TILE osm_planet 13 4208-4215 2688-2695
Feb 16 22:01:38 modtile2 renderd[3065]:    reason: std::bad_alloc
Feb 16 22:01:38 modtile2 renderd[3065]: DEBUG: DONE TILE osm_planet 13 4208-4215 2688-2695 in 22.315 seconds

What's up here?

Could it simply be that we really really should be running the PostgreSQL on the same machine as the tile renderer? If so, what is the reason?



			karl