Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-06 Thread Seth G via MapServer-users 
Hi Rob,

Are you using the same MapServer machine to make the queries?
If the MapServer version, and dependencies are all identical then the generated 
SQL queries should also be identical.
You wrote about "hitting the web page" - but if the requested extents are 
different then it is likely the client requests are different. Are you using 
WMS services? What do these requests look like?
Also you mention 4 different layers, so I'd try testing them one by one - are 
they all generating different SQL and response times?

Seth

--
web:https://geographika.net & https://mapserverstudio.net
twitter: @geographika

On Tue, Feb 6, 2024, at 8:53 PM, Rob Dennett via MapServer-users wrote:
> Both DBs are hosted on AWS Aurora.  The old one is serverless v1 and the new 
> one is serverless v2.  We were thinking that perhaps the geom column for my 
> table needed a spatial index even though the old db doesn't have one.  Is 
> that possible?  Also, would it make sense that the same query run on both 
> should take the same amount of time (roughly)?
> 
> Thanks,
> Rob
> 
> 
> 
> 
> ___
> MapServer-users mailing list
> MapServer-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-06 Thread Rahkonen Jukka via MapServer-users 
Hi,

If spatial index is needed depends on the data. It is certainly needed when the 
table is large (million features or so) and requests typically cover a small 
area. If for example 1000 features gets selected it means that the spatial 
index can filter out 999 thousand features. If the table is small like one 
thousand features, then it is not so expensive to read everything without 
spatial filter.

You are giving us little by little more information. I do not believe that the 
PostgreSQL 13 vs version 11 is the main thing at all. However, I do not have 
databases hosted on AWS and I can not make tests in the similar environment 
that you have.

-Jukka Rahkonen-

Lähettäjä: MapServer-users  Puolesta 
Rob Dennett via MapServer-users
Lähetetty: tiistai 6. helmikuuta 2024 21.53
Vastaanottaja: mapserver-users@lists.osgeo.org
Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?

Both DBs are hosted on AWS Aurora.  The old one is serverless v1 and the new 
one is serverless v2.  We were thinking that perhaps the geom column for my 
table needed a spatial index even though the old db doesn't have one.  Is that 
possible?  Also, would it make sense that the same query run on both should 
take the same amount of time (roughly)?

Thanks,
Rob
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-06 Thread Rob Dennett via MapServer-users 
Both DBs are hosted on AWS Aurora.  The old one is serverless v1 and the new 
one is serverless v2.  We were thinking that perhaps the geom column for my 
table needed a spatial index even though the old db doesn't have one.  Is that 
possible?  Also, would it make sense that the same query run on both should 
take the same amount of time (roughly)?

Thanks,
Rob
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] CGI variable "map" fails to validate

2024-02-06 Thread Rahkonen Jukka via MapServer-users 
Hi,

This one that is slightly edited from the MS4W config file works for me

MS_MAP_PATTERN 
"^(C:)?\/ms4w_data\/((?!\.{2})[_A-Za-z0-9\-\.]+\/{1})*([_A-Za-z0-9\-\.]+\.(map))$"

I am not a regexp expert but it seems like a slash must be escaped with a 
backslash.

-Jukka Rahkonen-

Lähettäjä: MapServer-users  Puolesta 
Lernout, Matthew via MapServer-users
Lähetetty: tiistai 6. helmikuuta 2024 21.13
Vastaanottaja: mapserver-users@lists.osgeo.org
Aihe: [MapServer-users] CGI variable "map" fails to validate

Hi,

Having trouble making requests since updating to 8.0.1 from 7.6.2.
Mapserver is running in IIS using FastCGI and the configuration steps from: 
https://mapserver.org/installation/iis.html

A new config file was added to the filesystem and server Environment variables 
as per documented requirements added since 7.6.3, but having trouble validating 
any map arguments. All requests return the error "CGI variable "map" fails to 
validate" even though regexp should pass based on expression tests.

Here is the full config file in use:

CONFIG
  ENV
MS_MAP_PATTERN "\.map$"
PROJ_LIB "F:/MapServer/bin/proj7/SHARE"
CURL_CA_BUNDLE "F:\MapServer\bin\curl\curl-ca-bundle.crt"
  END

  PLUGINS
"mssql" "F:/MapServer/bin/ms/plugins/mssql2008/msplugin_mssql2008.dll"
"oci" "F:/MapServer/bin/ms/plugins/oci/msplugin_oracle.dll"
  END
END

The current MS_MAP_PATTERN is just a simplified test to confirm 8.0.1 working 
before I update to the real regexp, and my understanding is it should map 
anything that ends in .map? But every map argument I throw in (including 
verified working arguments for 7.6.2) result in a failure to validate. Are 
there other crucial configurations missing that are necessary to pass 
validation?

Thanks,
Matt
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-06 Thread Rahkonen Jukka via MapServer-users 
Hi,

I made a test with a PostGIS on my own laptop. My versions are:
"POSTGIS=""3.4.1 3.4.1"" [EXTENSION] PGSQL=""160"" GEOS=""3.12.1-CAPI-1.18.1"" 
SFCGAL=""SFCGAL 1.5.0, CGAL 5.6, BOOST 1.78.0"" PROJ=""8.2.1

The polygon layer has 215000 lake polygons. My BBOX select 73 polygons. It 
takes 66 milliseconds.
My query and the execution plan are as follows:

select * from jarvinemo
where "geom" &&
ST_GeomFromText(
'POLYGON (( 314728.6874003611 6936494.124854623, 314728.6874003611 
6946067.332484153, 327290.0638853506 6946067.332484153, 327290.0638853506 
6936494.124854623, 314728.6874003611 6936494.124854623 ))');

"Bitmap Heap Scan on jarvinemo  (cost=4.58..155.40 rows=39 width=588) (actual 
time=0.048..0.076 rows=73 loops=1)"
"  Recheck Cond: (geom && 
'0103000100050059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"
"  Heap Blocks: exact=64"
"  ->  Bitmap Index Scan on jarvinemo_geom_geom_idx  (cost=0.00..4.58 rows=39 
width=0) (actual time=0.039..0.039 rows=73 loops=1)"
"Index Cond: (geom && 
'0103000100050059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"
"Planning Time: 0.142 ms"
"Execution Time: 0.106 ms"

I can see that the bbox (POLYGON) in your query is big, something like 
1500-2500 kilometres wide, and covers almost the whole EXTENT of your data. 
That means that the spatial filter is ineffective because it does not filter 
out anything. Have a try with a small polygon as a filter and compare the 
execution plan with the one that my database makes. That helps you to find out 
if the spatial index kicks in at some time. Of course, that does not help when 
the map covers the whole area. Then all the data must be read. But spending 5 
seconds (the old db) or 15 seconds (the new db) for that as you have written 
before feels very slow. I wonder if the slowness comes from the connection to 
the database rather than from the database itself. It is some hosted database, 
cartodb perhaps by some logs that you have showed. Have you made any tests with 
a local db?

-Jukka Rahkonen-


Lähettäjä: MapServer-users  Puolesta 
Rob Dennett via MapServer-users
Lähetetty: tiistai 6. helmikuuta 2024 21.02
Vastaanottaja: mapserver-users@lists.osgeo.org
Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?


So, I ran explain analyze, and got different results when run against the old 
and new db.  I ran VACUUM on the table (which ran in less than ½ a second) and 
checked the query plans again.  They're now the same except for the time 
estimates and against the new db they're still much slower.

We ran across a StackExchange article about performance degradation after 
upgrading to postgres 13 and postgis 3.4 
(https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11)
  and they mention a change in the way postgis works, but since these queries 
are generated by mapserver, I am not sure what I can do on my end.  Is there a 
version of MapServer that's recommended for Postgres 13/PostGIS 3?
[https://cdn.sstatic.net/Sites/dba/Img/apple-touch-i...@2.png?v=246e2cb2439c]
Why is my spatial query slower in Postgres 13 than in Postgres 
11?
Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc 
(Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 
11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled ...
dba.stackexchange.com



From: Travis Kirstine 
mailto:traviskirst...@gmail.com>>
Sent: Monday, February 5, 2024 10:50 AM
To: Rob Dennett mailto:rob.denn...@twdb.texas.gov>>
Cc: mapserver-users@lists.osgeo.org 
mailto:mapserver-users@lists.osgeo.org>>
Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?


External: Beware of links/attachments.

Not sure I understand the issue with the polygons.   The polygon value in the 
select statement will change based on the client's view extent, for example if 
the users moves the map a new request with a different polygon / bbox would be 
issued to mapserver and through to postgres. The EXTENT value defined in the 
mapfile defines the extent of the map or layer coverage.

I would try running EXPLAIN ANALYZE on the same query on both versions of 
Postgres and see what the differences are.

explain analyze select

[MapServer-users] CGI variable "map" fails to validate

2024-02-06 Thread Lernout, Matthew via MapServer-users 
Hi,

Having trouble making requests since updating to 8.0.1 from 7.6.2.
Mapserver is running in IIS using FastCGI and the configuration steps from: 
https://mapserver.org/installation/iis.html

A new config file was added to the filesystem and server Environment variables 
as per documented requirements added since 7.6.3, but having trouble validating 
any map arguments. All requests return the error "CGI variable "map" fails to 
validate" even though regexp should pass based on expression tests.

Here is the full config file in use:

CONFIG
  ENV
MS_MAP_PATTERN "\.map$"
PROJ_LIB "F:/MapServer/bin/proj7/SHARE"
CURL_CA_BUNDLE "F:\MapServer\bin\curl\curl-ca-bundle.crt"
  END

  PLUGINS
"mssql" "F:/MapServer/bin/ms/plugins/mssql2008/msplugin_mssql2008.dll"
"oci" "F:/MapServer/bin/ms/plugins/oci/msplugin_oracle.dll"
  END
END

The current MS_MAP_PATTERN is just a simplified test to confirm 8.0.1 working 
before I update to the real regexp, and my understanding is it should map 
anything that ends in .map? But every map argument I throw in (including 
verified working arguments for 7.6.2) result in a failure to validate. Are 
there other crucial configurations missing that are necessary to pass 
validation?

Thanks,
Matt
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

2024-02-06 Thread Rob Dennett via MapServer-users 

So, I ran explain analyze, and got different results when run against the old 
and new db.  I ran VACUUM on the table (which ran in less than ½ a second) and 
checked the query plans again.  They're now the same except for the time 
estimates and against the new db they're still much slower.

We ran across a StackExchange article about performance degradation after 
upgrading to postgres 13 and postgis 3.4 
(https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11)
  and they mention a change in the way postgis works, but since these queries 
are generated by mapserver, I am not sure what I can do on my end.  Is there a 
version of MapServer that's recommended for Postgres 13/PostGIS 3?
[https://cdn.sstatic.net/Sites/dba/Img/apple-touch-i...@2.png?v=246e2cb2439c]
Why is my spatial query slower in Postgres 13 than in Postgres 
11?
Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc 
(Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 
11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled ...
dba.stackexchange.com



From: Travis Kirstine 
Sent: Monday, February 5, 2024 10:50 AM
To: Rob Dennett 
Cc: mapserver-users@lists.osgeo.org 
Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in 
Postgres 11?


External: Beware of links/attachments.


Not sure I understand the issue with the polygons.   The polygon value in the 
select statement will change based on the client's view extent, for example if 
the users moves the map a new request with a different polygon / bbox would be 
issued to mapserver and through to postgres. The EXTENT value defined in the 
mapfile defines the extent of the map or layer coverage.

I would try running EXPLAIN ANALYZE on the same query on both versions of 
Postgres and see what the differences are.

explain analyze select 
"sourceid"::text,"featuretyp"::text,"cartodb_id"::text,"name"::text,"sourcetype"::text,"drawingord"::text,"isnew"::text,ST_AsBinary(("geom"),'NDR')
 as geom,"cartodb_id"::text from the_table_in_question where "geom" && 
ST_GeomFromText('POLYGON((-12520996.7293382 2507134.52775378,-12520996.7293382 
3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 
2507134.52775378,-12520996.7293382 
2507134.52775378))',find_srid('','iswp_sourcefeatures2022','geom')) and 
("featuretyp"::text = 'polygon')







___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [MapServer-users] Security and LDAP with mapservet

2024-02-06 Thread Bjørn Ove Grøtan via MapServer-users 
 G. Völkl via MapServer-users:
> Hello,
> 
> I want to create some WMS Services which can be only accessed by some Special 
> Users.
> 
> Is it   possible to Secure wms Services? Is it possible to use LDAP.
> 
> Best regards Gerhard 
> Von meinem iPhone gesendet

You can secure Mapserver WMS using whatever webserver you are exposing them 
through, be it NGINX og Apache (0).

I know Apache supports ldap (1) and I would guess NGINX does to.
If you expose Mapserver WMS through mapscript, you can handle authn/authz 
inside your mapscript-code.

(0) https://httpd.apache.org/docs/2.4/howto/auth.html
(1) https://httpd.apache.org/docs/2.4/mod/mod_ldap.html

-- 
BEst regards
 
Bjørn Ove Grøtan
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

[MapServer-users] Security and LDAP with mapservet

2024-02-06 Thread G . Völkl via MapServer-users 
Hello,

I want to create some WMS Services which can be only accessed by some Special 
Users.

Is it   possible to Secure wms Services? Is it possible to use LDAP.

Best regards Gerhard 
Von meinem iPhone gesendet
___
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users