{"action":"created","issue":{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/314","repository_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles","labels_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/314/labels{/name}","comments_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/314/comments","events_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/314/events","html_url":"https://github.com/osm2vectortiles/osm2vectortiles/issues/314","id":153254270,"number":314,"title":"Mapnik Timeouts","user":{"login":"lukasmartinelli","id":1288339,"avatar_url":"https://avatars.githubusercontent.com/u/1288339?v=3","gravatar_id":"","url":"https://api.github.com/users/lukasmartinelli","html_url":"https://github.com/lukasmartinelli","followers_url":"https://api.github.com/users/lukasmartinelli/followers","following_url":"https://api.github.com/users/lukasmartinelli/following{/other_user}","gists_url":"https://api.github.com/users/lukasmartinelli/gists{/gist_id}","starred_url":"https://api.github.com/users/lukasmartinelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukasmartinelli/subscriptions","organizations_url":"https://api.github.com/users/lukasmartinelli/orgs","repos_url":"https://api.github.com/users/lukasmartinelli/repos","events_url":"https://api.github.com/users/lukasmartinelli/events{/privacy}","received_events_url":"https://api.github.com/users/lukasmartinelli/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/labels/bug","name":"bug","color":"fc2929"},{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/labels/high-priority","name":"high-priority","color":"e11d21"},{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/labels/performance","name":"performance","color":"eb6420"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/milestones/18","html_url":"https://github.com/osm2vectortiles/osm2vectortiles/milestones/v1.6","labels_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/milestones/18/labels","id":1619633,"number":18,"title":"v1.6","description":"","creator":{"login":"manuelroth","id":1810384,"avatar_url":"https://avatars.githubusercontent.com/u/1810384?v=3","gravatar_id":"","url":"https://api.github.com/users/manuelroth","html_url":"https://github.com/manuelroth","followers_url":"https://api.github.com/users/manuelroth/followers","following_url":"https://api.github.com/users/manuelroth/following{/other_user}","gists_url":"https://api.github.com/users/manuelroth/gists{/gist_id}","starred_url":"https://api.github.com/users/manuelroth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/manuelroth/subscriptions","organizations_url":"https://api.github.com/users/manuelroth/orgs","repos_url":"https://api.github.com/users/manuelroth/repos","events_url":"https://api.github.com/users/manuelroth/events{/privacy}","received_events_url":"https://api.github.com/users/manuelroth/received_events","type":"User","site_admin":false},"open_issues":5,"closed_issues":14,"state":"open","created_at":"2016-03-03T08:46:49Z","updated_at":"2016-05-05T15:16:28Z","due_on":"2016-05-15T22:00:00Z","closed_at":null},"comments":0,"created_at":"2016-05-05T15:16:28Z","updated_at":"2016-05-05T15:46:44Z","closed_at":null,"body":"While on the PostGIS side everything is happy and shiny I now get a lot of Mapnik copy timeouts.\r\nNamely Mapnik is on 400% CPU consuming GBs of data but producing nothing and is eventually failing - while no expensive queries go onto PostGIS.\r\n\r\nOne example of such a tile is this in the middle of nowhere.\r\n\r\n![geojson_io](https://cloud.githubusercontent.com/assets/1288339/15045924/7345c27a-12dd-11e6-9615-b5bffe8cc715.png)\r\n\r\nCommand to reproduce:\r\n\r\n```bash\r\ntilelive-copy --scheme 'pyramid' --minzoom 8 --maxzoom 14 --bounds='-22.5,79.6871841545,-21.09375,79.9359182463' --timeout=60000 --slow=60000 'tmsource:///tmp/project' 'mbtiles:///usr/src/app/ab1b9bc5f65b4bbbbb71994490e8516e06abdb14.mbtiles'\r\n```\r\n\r\nInvestigated a bit by removing layers from tm2source to see what might be the problem.\r\n\r\n- [x] removing water doesn't help\r\n- [x] removing landuse doesn't help\r\n- [x] removing waterway doesn't help\r\n- [x] removing country_label, marine_label, state_label, place_label doesn't help\r\n- [x] removing water_label doesn't help\r\n- [x] removing aeroway, barrier_line, building, road doesn't help\r\n- [x] removing landuse_overlay doesn't help\r\n- [x] removing admin, poi_label, road_label, housenum_label, airport_label, rail_station_label doesn't help\r\n- [x] removing waterway_label doesn't help\r\n\r\nOkay at this point I just deleted everything except one layer the `rail_station_label`. At this point it works. So now I am adding the layers that might be the problem again as it seems it is not a single layer that is the problem.\r\n\r\nAnother crazy man's thought just switched the scheme from `pyramid` to `scanline`and then it actually starts faster (doesn't stall Mapnik at the beginning) and I see that all the z13 and z14 tiles require all about 30s of rendering time?!! until eventuall it times out as well.\r\n\r\nSo now the question is why do some tiles require 30s of rendering? Do they receive so much data that that Mapnik needs to much processing time (perhaps we forgot a where clause or something).\r\n\r\n\r\n- [x] having just water using pyramid takes 1m and 10s\r\n- [x] having just water using scanline takes 14s\r\n- [x] having just water using pyramid takes 1m and 13 s\r\n\r\nSo scanline is better for a small bbox?\r\n\r\nContinue with finding out what layer might be the problem by adding layers.\r\n\r\n- [x] adding landuse and waterway to water with scanline takes takes 1m 14\r\n\r\nSo is either landuse or waterway the problem?\r\n\r\n- [x] having just landuse using pyramid times out\r\n- [x] having just water using pyramid takes 1m and 8s\r\n- [x] having just waterway using pyramid takes 12s\r\n\r\nSo we now know that landuse is a good problem child for testing.\r\n\r\nLet's do this differently again. Let's measure how much traffic flows from PostGIS into the worker when we just render this bbox.\r\n\r\nIt is a whooping **1.7 GB** and after that it times out (since I gave a copy timeout of 60s). That means something is really fishy.\r\n\r\nLet's do this just for the layers.\r\n\r\n- [x] having just waterway takes 10MB\r\n- [x] having just water takes already 872MB\r\n- [x] having just landuse takes even more 1.893GB  after which it times out\r\n- [x] having just landuse with a timeout of 120s consumes 4.009 GB and also times out\r\n\r\nSo let's jump to some conclusions.\r\n\r\n- Copy timeouts happen when Mapnik is waiting for the copy process from PostGIS to Mapnik and this takes longer than the specified timeout (per default 60s)\r\n- The layer queries consume way too much input data aka they throw away nearly all data they receive\r\n\r\nActions.\r\n\r\n- [ ] Find out why landuse or water are consuming so much input data\r\n- [ ] Limit number of data transferred from PostGIS to Mapnik\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/comments/217191065","html_url":"https://github.com/osm2vectortiles/osm2vectortiles/issues/314#issuecomment-217191065","issue_url":"https://api.github.com/repos/osm2vectortiles/osm2vectortiles/issues/314","id":217191065,"user":{"login":"lukasmartinelli","id":1288339,"avatar_url":"https://avatars.githubusercontent.com/u/1288339?v=3","gravatar_id":"","url":"https://api.github.com/users/lukasmartinelli","html_url":"https://github.com/lukasmartinelli","followers_url":"https://api.github.com/users/lukasmartinelli/followers","following_url":"https://api.github.com/users/lukasmartinelli/following{/other_user}","gists_url":"https://api.github.com/users/lukasmartinelli/gists{/gist_id}","starred_url":"https://api.github.com/users/lukasmartinelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukasmartinelli/subscriptions","organizations_url":"https://api.github.com/users/lukasmartinelli/orgs","repos_url":"https://api.github.com/users/lukasmartinelli/repos","events_url":"https://api.github.com/users/lukasmartinelli/events{/privacy}","received_events_url":"https://api.github.com/users/lukasmartinelli/received_events","type":"User","site_admin":false},"created_at":"2016-05-05T15:46:44Z","updated_at":"2016-05-05T15:46:44Z","body":"Assumption: Grouping the landuse stuff together might be the cause\r\nActually this is not the case. When I remove the grouping it still consumes 2.023 GB and times out.\r\n\r\nAssumption: Do the layer queries have a bug?\r\nThe queries look good imho.\r\nLet's find out at which zoom level this happens by limiting the tilelive copy command to certain zoom level ranges.\r\n- z8-z9 is fast.\r\n- z8-z12 is fast\r\n- z8-z13 is slow\r\n\r\nSo the problem happens at z13.\r\n\r\nAssumption: Are there very big landuse geometries?\r\nSo it might just be that there are huge landuse geometries that always need to be transferred.\r\nFor example the query below yields a 2.3MB glacier (which is not much but there are probably bigger ones) And 2.3 MB times 5000 is 14.828265 gigabytes\r\n\r\nTesting this with query.\r\n\r\n```sql\r\nSELECT octet_length(ST_AsBinary(\"geometry\")) AS geom_byte_size,\"osm_id\",\"class\",\"type\" FROM (\r\n\t  SELECT osm_ids2mbid(osm_id, true) AS osm_id, geometry, landuse_class(type) AS class, type\r\n\t    FROM (\r\n\t    SELECT osm_id, geometry, type\r\n\t    FROM landuse_z5toz6\r\n\t    WHERE z(68247.3) BETWEEN 5 AND 6\r\n\t    UNION ALL\r\n\t    SELECT osm_id, geometry, type\r\n\t    FROM landuse_z7toz8\r\n\t    WHERE z(68247.3) BETWEEN 7 AND 8\r\n\t    UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z9\r\n\t      WHERE z(68247.3) = 9\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z10\r\n\t      WHERE z(68247.3) = 10\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z11\r\n\t      WHERE z(68247.3) = 11\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z12\r\n\t      WHERE z(68247.3) = 12\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z13toz14\r\n\t      WHERE z(68247.3) BETWEEN 13 AND 14\r\n\t    ) AS landuse_z9toz14\r\n\t    WHERE geometry && ST_SetSRID('BOX3D(-2426493.46291292 15414520.4350735,-2421448.619046099 15419565.27894032)'::box3d, 3857)\r\n\t) AS data\r\n```\r\n\r\nBut how can we make this better? \r\nSome geometry function that can clip/cut out a smaller part (aka the bounding box + buffer) from a big geometry and just return that? Like [ST_Intersection](http://postgis.net/docs/RT_ST_Intersection.html).\r\n\r\nSo if we try something like this (we need to add a buffer to ensure that the vector tile extent still works - so we just always take an entire tile as buffer - 256px).\r\n\r\n```sql\r\nST_Intersection(geometry, ST_Buffer(!bbox!), !pixel_width! * 256)) AS geometry\r\n```\r\n\r\nThe full query. It now just returns 9 bytes.\r\nBut whether that returns still useful data - have to test this with the postgis editor.\r\n\r\n```sql\r\nSELECT octet_length(ST_AsBinary(\"geometry\")) AS geom,\"osm_id\",\"class\",\"type\" FROM (\r\n\t  SELECT osm_ids2mbid(osm_id, true) AS osm_id, \r\n\t  ST_Intersection(geometry, ST_Buffer(ST_SetSRID('BOX3D(-2426493.46291292 15414520.4350735,-2421448.619046099 15419565.27894032)'::box3d, 3857), 200)) AS geometry,\r\n\t  landuse_class(type) AS class, type\r\n\t    FROM (\r\n\t    SELECT osm_id, geometry, type\r\n\t    FROM landuse_z5toz6\r\n\t    WHERE z(68247.3) BETWEEN 5 AND 6\r\n\t    UNION ALL\r\n\t    SELECT osm_id, geometry, type\r\n\t    FROM landuse_z7toz8\r\n\t    WHERE z(68247.3) BETWEEN 7 AND 8\r\n\t    UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z9\r\n\t      WHERE z(68247.3) = 9\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z10\r\n\t      WHERE z(68247.3) = 10\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z11\r\n\t      WHERE z(68247.3) = 11\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z12\r\n\t      WHERE z(68247.3) = 12\r\n\t      UNION ALL\r\n\t      SELECT osm_id, geometry, type\r\n\t      FROM landuse_z13toz14\r\n\t      WHERE z(68247.3) BETWEEN 13 AND 14\r\n\t    ) AS landuse_z9toz14\r\n\t    WHERE geometry && ST_SetSRID('BOX3D(-2426493.46291292 15414520.4350735,-2421448.619046099 15419565.27894032)'::box3d, 3857)\r\n\t) AS data\r\n```"}}