[mapserver-users] Error While Generating Tiles
Erik Gustafson
gustafson.erik at gmail.com
Thu Mar 14 00:25:54 PDT 2019
Hi,
did you create the spatial index on the geometry column (which I wrongly
called "geom" when it was named "way")?
Example:
CREATE INDEX planter_osm_polygon_sidx on planet_osm_polygon USING GIST(way);
If it still slow, please send:
explain analyze verbose <query>
and the query itself.
I'm expecting a spatial index to speed up the query. But you should also
consider splitting the mapserver layer into more than one, since in the
smaller scales you read a lot of features from the database but then throws
them away when they don't match any class expression. Better to have more
layers with various MINSCALEDENOM/MAXSCALEDENOM and filter the data in the
sql directly. I think the query comes from the layer "area_label".
On Thu, Mar 14, 2019 at 7:16 AM parsis presswala <parsispresswala at gmail.com>
wrote:
> Hello, I am getting following result by explain <query>. It has been
> running for an hour. Can you help me with this?
> *Bitmap Heap Scan on planet_osm_polygon (cost=182016.93..13405835.58
> rows=16599 width=138)*
> * Recheck Cond: (name IS NOT NULL)*
> * Filter: (((leisure = ANY ('{park,nature_reserve}'::text[])) OR
> (tourism IS NOT NULL) OR (historic IS NOT NULL)) AND (way &&
> '010300002031BF0D000100000005000000E8C60D48207933C1E5BD415EE1174A41E8C60D48207933C1FD8D6E9DBD245F41A17A94B809754A41FD8D6E9DBD245F41A17A94B809754A41E5BD415EE1174A41E8C60D48207933C1E5BD415EE1174A41'::geometry))*
> * -> Bitmap Index Scan on planet_osm_polygon_name_index
> (cost=0.00..182012.78 rows=9749894 width=0)*
> * Index Cond: (name IS NOT NULL)*
> *(5 rows)*
>
>
> On Wed, Mar 13, 2019 at 10:55 PM parsis presswala <
> parsispresswala at gmail.com> wrote:
>
>> Thanks, I'll check it out.
>>
>> On Wed, 13 Mar 2019 at 8:53 PM, Erik Gustafson <gustafson.erik at gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> seems like you're missing a spatial index on the geometry column.
>>> CREATE INDEX planet_osm_polygon_sidx ON planet_osm_polygon USING
>>> GIST(geom);
>>>
>>> If that doesn't help, do: explain analyze verbose <query>; and send the
>>> results.
>>>
>>>
>>>
>>> On Wed, Mar 6, 2019 at 2:56 PM parsis presswala <
>>> parsispresswala at gmail.com> wrote:
>>>
>>>> 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
>>>>>
>>>> _______________________________________________
>>>> mapserver-users mailing list
>>>> mapserver-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>>>
>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190314/0f4f1e29/attachment-0001.html>
More information about the mapserver-users
mailing list