What MapGuide does during database schema discovery?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

What MapGuide does during database schema discovery?

Gabriele Monfardini
Hi all,

I had a PostgreSQL/Postgis database with some schemas and many tables.
I'm connecting to it using MapGuide 2.5.2, OGR provider and a recompiled
gdal/ogr library to support PostgreSQL.

All is working fine except for one thing.
The first time that a map that use this db is opened it takes tens of
seconds to appear.
After the first time, the same map is opened in 3-4 seconds as it should.

Investigating this issue the problem seems to be in schema discovery since
purging cached schema information from maestro triggers again the slow
start.

Analyzing database log I've found that MapGuide seems to try to evaluate
the extent of each geometry table in the database (brutally querying SELECT
Extent(the_geom) on all of them).

And we happen to have a few very complicated views, defined from several
other views, which are used in a totally unrelated project and never
in MapGuide. Querying the extent of those views eats a lot of seconds.

Tuning the views is not really an option since evaluating the extent is
somewhat unexpected on them.

Thus my questions are the following:

   1. What stats is MapGuide collecting on all tables? Are there anything
   configurable?
   2. Are there any way to manually exclude problematic tables?
   In PostGIS <2 I can drop the rows related to those tables from
   geometry_columns, since MapGuide use geometry_columns, but in PostGIS 2+
   this is no more possible.
   I've seen also that another possible workaround is to set the SRID for
   problematic tables to undefined, but again this is not always possible.
   Also I've not understood why the SRID does make this difference...
   3. Why is extent needed? And if really needed why don't use
   ST_Estimated_Extent (or ST_EstimatedExtent in PostGIS 2.1+) to have a
   quicker response from the database?


Best regards,

Gabriele Monfardini
_______________________________________________
mapguide-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapguide-internals
Reply | Threaded
Open this post in threaded view
|

Re: What MapGuide does during database schema discovery?

zspitzer
surely the native FDO provider is going to perform far better than OGR?


On Wed, Mar 19, 2014 at 9:54 PM, Gabriele Monfardini
<[hidden email]>wrote:

> Hi all,
>
> I had a PostgreSQL/Postgis database with some schemas and many tables.
> I'm connecting to it using MapGuide 2.5.2, OGR provider and a recompiled
> gdal/ogr library to support PostgreSQL.
>
> All is working fine except for one thing.
> The first time that a map that use this db is opened it takes tens of
> seconds to appear.
> After the first time, the same map is opened in 3-4 seconds as it should.
>
> Investigating this issue the problem seems to be in schema discovery since
> purging cached schema information from maestro triggers again the slow
> start.
>
> Analyzing database log I've found that MapGuide seems to try to evaluate
> the extent of each geometry table in the database (brutally querying SELECT
> Extent(the_geom) on all of them).
>
> And we happen to have a few very complicated views, defined from several
> other views, which are used in a totally unrelated project and never
> in MapGuide. Querying the extent of those views eats a lot of seconds.
>
> Tuning the views is not really an option since evaluating the extent is
> somewhat unexpected on them.
>
> Thus my questions are the following:
>
>    1. What stats is MapGuide collecting on all tables? Are there anything
>    configurable?
>    2. Are there any way to manually exclude problematic tables?
>    In PostGIS <2 I can drop the rows related to those tables from
>    geometry_columns, since MapGuide use geometry_columns, but in PostGIS 2+
>    this is no more possible.
>    I've seen also that another possible workaround is to set the SRID for
>    problematic tables to undefined, but again this is not always possible.
>    Also I've not understood why the SRID does make this difference...
>    3. Why is extent needed? And if really needed why don't use
>    ST_Estimated_Extent (or ST_EstimatedExtent in PostGIS 2.1+) to have a
>    quicker response from the database?
>
>
> Best regards,
>
> Gabriele Monfardini
> _______________________________________________
> mapguide-internals mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapguide-internals
>



--
Zac Spitzer
+61 405 847 168
_______________________________________________
mapguide-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapguide-internals
Reply | Threaded
Open this post in threaded view
|

Re: What MapGuide does during database schema discovery?

Gabriele Monfardini
Hi Zac,


> surely the native FDO provider is going to perform far better than OGR?
>

from my stress tests, the performance is similar and acceptable for both.

I had some problems with native FDO PostgreSQL/PostGIS provider in a map
with custom simbols (I'm currently trying to produce a useful test case to
file a possible bug).

A minor "problem" is also to change the name of schema and table in each
layer that use the provider, which is annoying if you have several hundreds
of layers.

BTW, do you think that changing provider will change schema discovery
strategy?

Regards,

Gabriele
_______________________________________________
mapguide-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapguide-internals
Reply | Threaded
Open this post in threaded view
|

Re: What MapGuide does during database schema discovery?

zspitzer
let's not even talk about FDO class naming conventions, I'm way too scarred
by
that experience

OGR is a generic provider, whereas the specific providers should use the
most
database optimised approach to extracts extents, I'm pretty certain the
PostgreSQL/PostGIS provider is going to be better that the reliable brute
force

it's easy enough to test this, just create a feature source and see how
long it
take to initialise




On Wed, Mar 19, 2014 at 10:56 PM, Gabriele Monfardini
<[hidden email]>wrote:

> Hi Zac,
>
>
> > surely the native FDO provider is going to perform far better than OGR?
> >
>
> from my stress tests, the performance is similar and acceptable for both.
>
> I had some problems with native FDO PostgreSQL/PostGIS provider in a map
> with custom simbols (I'm currently trying to produce a useful test case to
> file a possible bug).
>
> A minor "problem" is also to change the name of schema and table in each
> layer that use the provider, which is annoying if you have several hundreds
> of layers.
>
> BTW, do you think that changing provider will change schema discovery
> strategy?
>
> Regards,
>
> Gabriele
> _______________________________________________
> mapguide-internals mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapguide-internals
>



--
Zac Spitzer
+61 405 847 168
_______________________________________________
mapguide-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapguide-internals
Reply | Threaded
Open this post in threaded view
|

Re: What MapGuide does during database schema discovery?

Gabriele Monfardini
Hi all,

I would like to report some findings about this problem (first map loading
is slow since MapGuide does not proceeds until it has queried the extent of
each table and view of the feature source).

Having a look at the code of the FDO OGR provider, the "culprit" seems to
be the following line (OgrProvider.cpp, line 664, in function FdoByteArray*
OgrSpatialContextReader::GetExtent())

/*OGRErr err =*/
m_connection->GetOGRDataSource()->GetLayer(m_nIndex)->GetExtent(&e,
> TRUE);


In this line GetExtent from libgdal is called.

From OGR documentation the second parameter is a boolean called bForce

Returns the extent (MBR) of the data in the layer. If bForce is FALSE, and
> it would be expensive to establish the extent then OGRERR_FAILURE will be
> returned indicating that the extent isn't know. If bForce is TRUE then some
> implementations will actually scan the entire layer once to compute the MBR
> of all the features in the layer.


This seems to be the case in my situation, that evaluating the extent is
expensive (and also there are a lot of tables to be investigated).

Note that it does not seem to be any check to detect an OGRERR_FAILURE.

I've tried to change the parameter with FALSE.
This change does not yield any benefit since libgdal performs the same
queries.
Moreover I'm a bit concerned that no check is performed to detect an OGR
failure, which seems to be more likely since it leaves the OGR driver the
option to return a failure if evaluating the extent is deemed to be too
expensive.

I've also made a more radical change, commenting out the call to libgdal
and returning an hard coded extent (a square POLYGON((0 0,0 50,50 50,50 0,0
0)), which is far away from real extent of my layers)

The interesting part is that apparently all works correctly inside MapGuide
(and first map loading is very quick now), so probably the extent is not
used a lot (or at least is not used in my particular workflow).

I'm not familiar with MapGuide code, may anyone point me to the code where
the extent is collected?
If this info is not used in some workflow maybe we can try to avoid to
collect it at all, since a feature source may have an unpredictable high
number of spatial relations.

For example (I used this to perform some tests) this info is collected
during FeatureService->GetSpatialContexts.

While having the Coordinate Reference System of each layer is useful (and
quick), also the extent is evaluated which is very slow.

Patching OGRProvider to return an hard coded extent speed up a lot
FeatureService->GetSpatialContexts and the returned CRS are still correct.

In my setup current collecting of extent is too slow to be acceptable, and
I'm seeking help to try to solve this problem in MapGuide code.

Any comments or help, before filing a ticket?

Regards,

Gabriele Monfardini
_______________________________________________
mapguide-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapguide-internals