[mapserver-users] Error While Generating Tiles

Yves Jacolin yves.jacolin at camptocamp.com
Wed Mar 6 06:01:04 PST 2019 

You probablty needs to create index(es) on leisure, landuse and amenity.
Probably both of all but you need to do some test.

Second, you probably needs to improve PostgreSQL configuration to let
PostgreSQL use your 61 GB of RAM :) shared_buffers and work_mem are good
parameters to look for.

Y.

Le mer. 6 mars 2019 à 14:56, parsis presswala <parsispresswala at gmail.com> a
écrit :

> Hello,
>
> Yes I am using OSM data.
> I have imported it using osm2pgsql. Database: asiadb and tables
> are planet_osm_line, planet_osm_point, planet_osm_polygon, planet_osm_roads.
> I have imported data of whole planet using 40 GB .osm.pbf file.
> First I have only imported data of Asia and created map using map-server,
> which is working fine but as soon as import data of whole world, this
> problem occurred.
> I have 61 GB RAM and 8 Core processor as i am working on Amazon Elastic
> Compute Cloud (Amazon EC2) type r4.2xlarge.
>
> Here is my mapfile.
>  http://13.126.201.198/asiamap.map
>
> I have run EXPLAIN <slow request> and got following result:
> "Seq Scan on planet_osm_polygon  (cost=0.00..18878787.88 rows=36617920
> width=277)"
> "  Filter: ((leisure IS NOT NULL) OR (landuse IS NOT NULL) OR (amenity IS
> NOT NULL))"
>
>
> On Wed, Mar 6, 2019 at 7:04 PM Yves Jacolin <yves.jacolin at camptocamp.com>
> wrote:
>
>> Some questions that can help you :)
>>
>> is it OSM data? How do you import them?
>> What is the extent of the data? Could you check if spatial indexes exist?
>> Could you run EXPLAIN <your slow request> and analyze it?
>>
>> Y.
>>
>> Le mer. 6 mars 2019 à 14:29, parsis presswala <parsispresswala at gmail.com>
>> a écrit :
>>
>>> Hello,
>>>
>>> Here is a log of PostgreSQL. I consider that PostgreSQL takes very long
>>> time to execute query on polygon. What should I do fot it?
>>>
>>> 2019-03-06 11:23:49.620 UTC [2302] parsis at asiadb STATEMENT:  select
>>> "highway"::text,"name"::text,"reflen"::text,"ref"::text,ST_AsBinary(ST_Force2D("way"),'NDR')
>>> as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way,
>>> highway, name, ref, oneway, CHAR_LENGTH(ref) AS reflen FROM planet_osm_line
>>> WHERE highway IN ('motorway', 'trunk','motorway_link','trunk_link') AND
>>> (name IS NOT NULL OR ref IS NOT NULL)) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-9473834.25396072
>>> 2853929.66787328,-9473834.25396072 3446928.13330971,-8880835.78852428
>>> 3446928.13330971,-8880835.78852428 2853929.66787328,-9473834.25396072
>>> 2853929.66787328))',900913)
>>> 2019-03-06 11:24:14.911 UTC [2592] parsis at asiadb LOG:  could not send
>>> data to client: Broken pipe
>>> 2019-03-06 11:24:14.911 UTC [2592] parsis at asiadb STATEMENT:  select
>>> "stylegroup"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as
>>> geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way, CASE
>>> WHEN leisure in
>>> ('park','garden','playground','golf_course','sports_centre','pitch','stadium','nature_reserve','common')
>>> THEN 'leisuree' WHEN landuse
>>> IN('park','forest','grass','farmyard','farm','farmland','wood','meadow','village_green','recreation_ground')
>>> THEN 'landusee' WHEN amenity in
>>> ('university','school','college','library','fuel','parking','cinema','theatre','place_of_worship','hospital')
>>> THEN 'amenityy' END AS stylegroup FROM planet_osm_polygon where (leisure is
>>> not null) or (landuse is not null) or (amenity is not null)) AS data) as
>>> nullidq where "way" && ST_GeomFromText('POLYGON((8116389.75422228
>>> -1276192.28146022,8116389.75422228 3467795.44203122,12860377.4777137
>>> 3467795.44203122,12860377.4777137 -1276192.28146022,8116389.75422228
>>> -1276192.28146022))',900913)
>>> 2019-03-06 11:24:14.911 UTC [2617] parsis at asiadb LOG:  could not send
>>> data to client: Broken pipe
>>> 2019-03-06 11:24:14.911 UTC [2617] parsis at asiadb STATEMENT:  select
>>> "stylegroup"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as
>>> geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way, CASE
>>> WHEN leisure in
>>> ('park','garden','playground','golf_course','sports_centre','pitch','stadium','nature_reserve','common')
>>> THEN 'leisuree' WHEN landuse
>>> IN('park','forest','grass','farmyard','farm','farmland','wood','meadow','village_green','recreation_ground')
>>> THEN 'landusee' WHEN amenity in
>>> ('university','school','college','library','fuel','parking','cinema','theatre','place_of_worship','hospital')
>>> THEN 'amenityy' END AS stylegroup FROM planet_osm_polygon where (leisure is
>>> not null) or (landuse is not null) or (amenity is not null)) AS data) as
>>> nullidq where "way" && ST_GeomFromText('POLYGON((8116389.75422228
>>> -1276192.28146022,8116389.75422228 3467795.44203122,12860377.4777137
>>> 3467795.44203122,12860377.4777137 -1276192.28146022,8116389.75422228
>>> -1276192.28146022))',900913)
>>> 2019-03-06 11:24:14.911 UTC [2592] parsis at asiadb FATAL:  connection to
>>> client lost
>>> 2019-03-06 12:08:25.564 UTC [3362] parsis at asiadb STATEMENT:  select
>>> ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *,
>>> NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN
>>> ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin',
>>> 'reservoir')) ) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-20228294.8225998
>>> -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297
>>> 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998
>>> -20228294.8225998))',900913)
>>> 2019-03-06 12:08:25.564 UTC [3260] FATAL:  terminating connection due to
>>> administrator command
>>> 2019-03-06 12:08:25.564 UTC [3260] STATEMENT:  select
>>> ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *,
>>> NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN
>>> ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin',
>>> 'reservoir')) ) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-20228294.8225998
>>> -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297
>>> 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998
>>> -20228294.8225998))',900913)
>>> 2019-03-06 12:08:25.564 UTC [3261] FATAL:  terminating connection due to
>>> administrator command
>>> 2019-03-06 12:08:25.564 UTC [3261] STATEMENT:  select
>>> ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *,
>>> NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN
>>> ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin',
>>> 'reservoir')) ) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-20228294.8225998
>>> -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297
>>> 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998
>>> -20228294.8225998))',900913)
>>> 2019-03-06 12:08:25.564 UTC [3363] FATAL:  terminating connection due to
>>> administrator command
>>> 2019-03-06 12:08:25.564 UTC [3363] STATEMENT:  select
>>> ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *,
>>> NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN
>>> ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin',
>>> 'reservoir')) ) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-20228294.8225998
>>> -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297
>>> 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998
>>> -20228294.8225998))',900913)
>>> 2019-03-06 12:08:25.570 UTC [1535] LOG:  worker process: parallel worker
>>> for PID 3255 (PID 3261) exited with exit code 1
>>> 2019-03-06 12:08:25.570 UTC [1535] LOG:  worker process: parallel worker
>>> for PID 3362 (PID 3363) exited with exit code 1
>>> 2019-03-06 12:08:25.570 UTC [1535] LOG:  worker process: parallel worker
>>> for PID 3255 (PID 3260) exited with exit code 1
>>> 2019-03-06 12:08:25.609 UTC [3426] parsis at asiadb LOG:  could not send
>>> data to client: Broken pipe
>>> 2019-03-06 12:08:25.609 UTC [3426] parsis at asiadb STATEMENT:  select
>>> "admin_level"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as
>>> geom,"nullid"::text from (select *, NULL as nullid from (select * from
>>> planet_osm_line where admin_level IN('2') and boundary IN
>>> ('administrative') and way NOT IN (SELECT way FROM planet_osm_line where
>>> admin_level IN('2') and boundary like 'administrative' and (tags->'dispute'
>>> = 'yes' or tags->'ISO3166-1' IN ('IN','CN','PK') or way IN(select way from
>>> in_cn_ignored_border)))) AS data) as nullidq where "way" &&
>>> ST_GeomFromText('POLYGON((-20228294.8225998
>>> -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297
>>> 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998
>>> -20228294.8225998))',900913)
>>> 2019-03-06 12:08:25.609 UTC [3426] parsis at asiadb FATAL:  connection to
>>> client lost
>>>
>>> Regards,
>>> Parsis Presswala
>>>
>>> On Wed, Mar 6, 2019 at 6:35 PM Yves Jacolin <yves.jacolin at camptocamp.com>
>>> wrote:
>>>
>>>> Hello,
>>>>
>>>> Did you try to curl the request and see what happens: timeout, etc. If
>>>> so, try to get some log (mapserver or postgresql log) and see where is the
>>>> bottleneck.
>>>>
>>>> Y.
>>>>
>>>> Le mer. 6 mars 2019 à 13:58, parsis presswala <
>>>> parsispresswala at gmail.com> a écrit :
>>>>
>>>>> Hello,
>>>>> I am getting following error while generating tiles. I can't get the
>>>>> reason of this error. I an using mapcache to generate tiles.
>>>>>
>>>>> failed to seed tile z10,x645,y510:
>>>>> curl failed to request url
>>>>> http://13.126.201.198/cgi-bin/mapserv?VERSION=1.1.1&REQUEST=GetMap&SERVICE=WMS&STYLES=&BBOX=5201998.739765%2c-81328.655306%2c5795150.079258%2c511822.684187&WIDTH=3880&HEIGHT=3880&FORMAT=image%2fpng&SRS=EPSG%3a3857&LAYERS=osm_new_admin%2cosm_new_adminocean%2cosm_new_landusages%2cosm_new_water%2cbuildings%2caeroway%2cosm_new_waterways%2cosm_new_railways%2cplanet_osm_roads%2cplanet_osm_roads_line%2cosm_new_railways1%2ccountry_line%2ccountry_line_claim%2cstate_line%2cmotorway_label%2cmainroad_label%2cminorroad_label%2cosm_new_railways_symbol%2cplace-country%2cplace-state%2cplace-city%2cplace-town%2cplace-Suburb%2cplace-village%2cplace-locality%2carea_label%2caerodrome_label%2cwaterway_label%2cwaterarea_label%2cpoi_pointlabel&MAP=%2fhome%2fubuntu%2fworldmap%2fasiamap.map
>>>>> : Operation timed out after 600000 milliseconds with 0 bytes received
>>>>>
>>>>> aborting seed as 100.0% of the last 1000 requests failed
>>>>>
>>>>> seeded 225 tiles, now at z10 x660 y510
>>>>> seeded 2 metatiles (450 total tiles, 450 non-empty tiles) in 1201.8
>>>>> seconds at 0.4 tiles/sec (0.4 non-empty tiles/sec)
>>>>>
>>>>> If anyone could help me with this, it would be greatly appreciated.
>>>>>
>>>>> Regards,
>>>>> Parsis Presswala
>>>>> _______________________________________________
>>>>> mapserver-users mailing list
>>>>> mapserver-users at lists.osgeo.org
>>>>> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>>>>
>>>>
>>>>
>>>> --
>>>> Yves Jacolin
>>>> Training and support manager - Team Manager
>>>> Camptocamp
>>>>
>>>> Tel (France) : +33 4 58 48 20 43
>>>> Tel (Switzerland) : +41 21 619 10 43
>>>> Mob. : +33 6 18 75 42 21
>>>>
>>>> email : yves.jacolin at camptocamp.com
>>>> http://www.camptocamp.com
>>>>
>>>
>>
>> --
>> Yves Jacolin
>> Training and support manager - Team Manager
>> Camptocamp
>>
>> Tel (France) : +33 4 58 48 20 43
>> Tel (Switzerland) : +41 21 619 10 43
>> Mob. : +33 6 18 75 42 21
>>
>> email : yves.jacolin at camptocamp.com
>> http://www.camptocamp.com
>>
>

-- 
Yves Jacolin
Training and support manager - Team Manager
Camptocamp

Tel (France) : +33 4 58 48 20 43
Tel (Switzerland) : +41 21 619 10 43
Mob. : +33 6 18 75 42 21

email : yves.jacolin at camptocamp.com
http://www.camptocamp.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190306/45fd9be1/attachment-0001.html>

More information about the mapserver-users mailing list