[Qgis-developer] Fwd: Re: [Geopackage] Geopackage on a shared network drive

[Even Rouault]

Le lundi 03 octobre 2016 11:49:52, Even Rouault a écrit :
> Le lundi 03 octobre 2016 10:33:25, Neumann, Andreas a écrit :
> > Hi Even,
> > 
> > Thank you for sharing this finding!
> > 
> > In that case I think that QGIS should offer a flag to open Geopackages
> > in read-only mode.
> 
> <probably_ignore_this_and_skip_to_last_paragraph>
> I'm not sure we need an explicit flag for read-only mode (in the GUI I
> mean). This could bring some complications: for example should that be
> saved with the layer in a project file? But if so, then you'd be stuck to
> read-only later if you want to edit a layer, etc.
> 
> Read-only mode could be the default mode for opening, and re-open in read-
> write mode when this is needed. This is something I've done recently, but
> limited to MapInfo .tab, since opening .tab files in read-write mode
> prevented concurrent opening of the file in QGIS & MapInfo. I didn't want
> to mess with other formats, but fundamentally I don't see any reason why
> this couldn't be generalized to other formats (I'm not sure why QGIS has
> historically opened in read-write mode as first try. Probably because this
> was easier to implement)
> 
> For GPKG, there's just one subtelty though. I've fixed recently #15351 and
> to avoid deadlocks between readers&writers, the first to open a GPKG needs
> to turn on write ahead log (WAL) journalisation, so the workflow would be
> :
> 
> - open in read only
> - check if journalisation is WAL
>    - if so, done
>    - if not, re-open in read-write, turn on WAL, re-open in read-only
> 
> And similar on closing to revert to the default DELETE journalisation mode.
> </probably_ignore_this_and_skip_to_last_paragraph>
> 
> Hum, with further thinking, I don't undertstand why my suggestion in the
> discussion thread on the geopackage ML of changing the file permission to
> read- only made things faster. Because, for feature iterators, QGIS uses a
> dedicated OGR connection in read-only mode and does not use the provider
> connection that might be in read-write mode. Unless the reporter still
> uses GDAL 1.11, which used to open in read-write mode regardless of what
> the user specified (was fixed in GDAL 2.0). That must be it. I'll check
> back with the reporter. So probably all the above is not needed after
> all...

I have had several follow-up exchanges with Árni Geirsson and the conclusions 
I drew from his test results are :

- the apparent cause for performance degradation is that there exists a file 
handle opened in read/write mode on the GeoPackage. An existing file handle in 
read/write mode negatively impacts performance on read-only connections. And 
that is true when this R/W file handle is in the same process than the R/O file 
handle, or in other process (we tested ogr2ogr performance when reading a 
remote GPKG file, while QGIS opened on this GPKG)

Does such behaviour of files on network shares on Windows ring a bell to 
someone ? I tried (quickly) to find if this was a documented behaviour but 
didn't manage to find.
So my above suggested changes between 
<probably_ignore_this_and_skip_to_last_paragraph> would appear to be worth 
(except that I went a bit too fast in my explanations and you don't want to 
turn WAL on files on network shares, but this is something I had already 
implemented per #15351)

- VSI_CACHE in the GeoPackage case (when enabled with SQLITE_USE_OGR_VFS=YES) 
does not seem to bring any performance improvement. And I'd wondering if this 
wouldn't be the case now for shapefile since (contrary to what I said above), 
both shapefile and mapinfo are now opened by default in read-only mode. Or 
perhaps VSI_CACHE has still effect since in sqlite case access are made on a 
sqlite page granularity (1024 bytes) whereas for shapefile that might be just a 
few bytes. But clearly there's something happening at Windows OS level when a 
R/W connection exists.

==> It would be cool if someone could test, with QGIS 2.16.3, shapefile 
performance on network share, by removing/commenting the set VSI_CACHE=YES 
environment variable of qgis.bat. 


> 
> > Even - since you are "Mr. Geopackage" in the QGIS project anyway - can
> > you please make sure that this can be covered in the upcoming Geopackage
> > improvements? Would a "read-only" mode also be useful for other data
> > formats? I opened a ticket at http://hub.qgis.org/issues/15652 and
> > assigned it to you. I have no idea how much effort this enhancement
> > would mean - can you please do an estimate and send me a quote for that?
> > Maybe we could still include it in the upcoming Geopackage improvements
> > financed by the Swiss QGIS user group.
> > 
> > -------------------
> > 
> > This also leads to a follow up question: many OGR data formats have
> > "open options". As far as I know, these options are not exposed to the
> > User in QGIS, other than the encoding - or did I miss something? Should
> > we introduce a "GUI" way in QGIS 3.x to expose these "open options" in
> > the "add vector layer" dialogue?
> 
> Open options cannot be specified through QGIS currently indeed. There are
> not so many vector drivers that expose them, mostly those who are of the
> database type (with some redundancy with the way configuring the
> connection is already offered in the GUI). Some notable exceptions in the
> regular file category are CSV and GML.
> One downside of open options is that they can be rather esoteric and hard
> to understand for the average user, so I guess they should be presented in
> a expandable tab.
> 
> GDAL offers the possibility to auto-discover which open options are
> available for a format, but that doesn't solve the issue with
> internationalization in QGIS. Although I think I discussed with someone
> about this and one possibility mentionned was to dump, at QGIS build time,
> the strings to translate from GDAL in a QGIS resource file. I didn't look
> how internationalization worked in QGIS.
> 
> Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com