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

[Karl Wettin]

So Paul Norman and I ran a bit of tracing of this via IRC. It seems as PostgreSQL (9.1.11) has no problems, that it's something with renderd/mapnik. I ended up decided to install a new VM with PostgreSQL 9.3 and running tile rendering on that same machine. I'll get back with results in a couple of days. The whole plan with separation of rendering and database is that I wanted to be able to upgrade and fiddle with database or rendering without affecting the other. Giving that up and hoping to get things working :-)


		karl

On 16 Feb 2014, at 22:05, Karl Wettin <karl.wettin at kodapan.se> wrote:

> 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