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

[Rob Dennett]

Resending this since it was too large the first time

Thanks to everyone for their help so far.

There are only 3 things that changed when we upgraded.  One, the Postgres version, two, the PostGIS version and three, we went from Aurora serverless v1 to serverless v2.  MapServer itself runs on Amazon ECS which in this case has 8 Fargate instances all running an identical version of MapServer in a Docker container.  These instances are load balanced.  The table in question has 3479 rows.  I am not clear how to determine how many features it has.  Is it one per row?

My URLs look like

https://mapserver.mydomain.org/?map=/path/to/my/mapfile.map

I actually made two copies of the original map file and changed the config for where the MS_ERRORFILE should be written and added DEBUG 2.

One of those copies had the CONNECTION string set to point at the old db.

The extent listed in the map section of the .map file is

  EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184

It does seem to zoom in a little as it loads, but I am not sure where it's getting the polygon information in the queries it's generating.

I hit the two URLs again, and this time got a debug file from the .mapfile pointed at the old db that is 226 lines long and one from the one pointed at the new db that is almost 24,000 lines long.  The map file for the new db has  thousands of lines like this (as did the .map file pointed at the old db the 1st time I ran it):

[Tue Feb  6 20:37:34 2024].128829 msPostGISLayerNextShape called.
[Tue Feb  6 20:37:34 2024].128833 msPostGISReadShape called.
[Tue Feb  6 20:37:34 2024].128838 msPostGISLayerNextShape called.
[Tue Feb  6 20:37:34 2024].128842 msPostGISReadShape called.
[Tue Feb  6 20:37:34 2024].129474 msPostGISLayerNextShape called.
[Tue Feb  6 20:37:34 2024].129480 msPostGISReadShape called.


The queries are the same for the new db as the were the last time I loaded the page although they occur in a different order for each.   However, the .map file pointed at the old db had different values for the polygons and all the queries returned 0 rows, even though the map rendered was fine.  To be clear, running either set in pgAdmin is much slower against the new db compared to the old.  Additionally, the set of old queries which all return 0 results took less than 1 sec on the old db and about 15 on the new.  Additinonally, I tried adding an index in the new db, but while it seems slightly faster, it still takes twice as long as the old db does without a spatial index.  Here's what I ran:

CREATE INDEX idx_the_table_in_question_geom ON the_table_in_question USING gist (geom);





Thanks,

Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240207/e2c67003/attachment-0001.htm>