I have improve Postgresql configuration shared_buffer = 20000 MB and work_mem = 500 MB. Is this configuration fine or I need to improve more?
On Wed, Mar 6, 2019 at 7:53 PM parsis presswala <[email protected]> wrote: > No, not yet. > > On Wed, Mar 6, 2019 at 7:49 PM Yves Jacolin <[email protected]> > wrote: > >> Did you improve Postgresql configuration (shared_buffer and work_mem >> parameters) ? >> >> Y. >> >> Le mer. 6 mars 2019 à 15:15, parsis presswala <[email protected]> >> a écrit : >> >>> Hello, >>> >>> I have already created indexing on some fields. Here is my indexed >>> fields. >>> CREATE INDEX planet_osm_polygon_idx >>> ON public.planet_osm_polygon >>> USING btree >>> (waterway COLLATE pg_catalog."default", "natural" COLLATE >>> pg_catalog."default", landuse COLLATE pg_catalog."default", building >>> COLLATE pg_catalog."default", aeroway COLLATE pg_catalog."default", leisure >>> COLLATE pg_catalog."default", amenity COLLATE pg_catalog."default"); >>> >>> Even I have experiment with r4.8xlarge with 32 CPU and 244 GB RAM. But >>> got same result. >>> What should I do? >>> >>> On Wed, Mar 6, 2019 at 7:31 PM Yves Jacolin <[email protected]> >>> wrote: >>> >>>> 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 < >>>> [email protected]> 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 < >>>>> [email protected]> 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 < >>>>>> [email protected]> 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@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@asiadb LOG: could not >>>>>>> send data to client: Broken pipe >>>>>>> 2019-03-06 11:24:14.911 UTC [2592] parsis@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@asiadb LOG: could not >>>>>>> send data to client: Broken pipe >>>>>>> 2019-03-06 11:24:14.911 UTC [2617] parsis@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@asiadb FATAL: connection >>>>>>> to client lost >>>>>>> 2019-03-06 12:08:25.564 UTC [3362] parsis@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@asiadb LOG: could not >>>>>>> send data to client: Broken pipe >>>>>>> 2019-03-06 12:08:25.609 UTC [3426] parsis@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@asiadb FATAL: connection >>>>>>> to client lost >>>>>>> >>>>>>> Regards, >>>>>>> Parsis Presswala >>>>>>> >>>>>>> On Wed, Mar 6, 2019 at 6:35 PM Yves Jacolin < >>>>>>> [email protected]> 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 < >>>>>>>> [email protected]> 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 >>>>>>>>> [email protected] >>>>>>>>> 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 : [email protected] >>>>>>>> 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 : [email protected] >>>>>> 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 : [email protected] >>>> 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 : [email protected] >> http://www.camptocamp.com >> >
_______________________________________________ mapserver-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/mapserver-users
