Fiona: Writing to GeoPackage is very slow

[culebron]

Working in GeoPandas I found an issue with GeoPackage format. It seems that it's both GeoPandas' and lower level's fault. Can performance be improved at Fiona's level?

I have SSD. OS is Ubuntu 17.04, Fiona version 1.7.9.post1. I installed it as recommended in #400 :

sudo pip3 install -I fiona --no-binary fiona

Here's my test suite for a proof: https://github.com/culebron/geodata Run python3.6 few.py and python3.6 multiple.py to compare. few.py opens a file with a lot of data, but only 2.7K records as GeoDataFrame. It writes them into GeoJSON and GPKG. In this case, GPKG driver outperforms GeoJSON. multiple.py creates a 100K records dataframe and then saves it to GeoJSON and GPKG. Here, GPKG is incredibly slow.

My results with default GeoPandas:

$ python3.6 few.py 
writing 2.7K records to geojson 36.283805477003625
writing 2.7K records to gpkg    20.792497718997765
$ python3.6 multiple.py 
100%|████████████████████████████████████████████████████████| 100000/100000 [00:03<00:00, 29996.25it/s]
writing 100K records to geojson 61.62079200500011
writing 100K records to gpkg    260.4413645050008

And notice that in case of multiple.py, the resulting GeoPackage file is only 9 megs. Which is times smaller than the file produced by few.py.

As I understand, the problem is that Fiona opens a session in Sqlite and creates a lock file, and it takes some time. And inspecting the code, I see GeoPandas naively writes everything 1 record at a time, which means Sqlite honestly locks it, then writes, then unlocks:

https://github.com/geopandas/geopandas/blob/master/geopandas/io/file.py#L107

with fiona.drivers():
    with fiona.open(filename, 'w', driver=driver, crs=df.crs,
                    schema=schema, **kwargs) as colxn:
        for feature in df.iterfeatures():
            colxn.write(feature)

I tried batching records:

with fiona.drivers():
    with fiona.open(filename, 'w', driver=driver, crs=df.crs,
                    schema=schema, **kwargs) as colxn:
        buf = []
        for feature in df.iterfeatures():
            buf.append(feature)
            if len(buf) > 9999:
                colxn.writerecords(buf)
                buf = []

        colxn.writerecords(buf)

$ python3.6 multiple.py 
100%|████████████████████████████████████████████████████████| 100000/100000 [00:03<00:00, 31041.45it/s]
writing 100K records to gpkg    91.03069832099573

91 seconds instead of 260. But I still see that Sqlite locks and unlocks the db many times on the way, and actual writing speed is still slow.

Can this be improved?

You may try opening the resulting test.geojson in QGIS and saving it to GeoPackage from there, you'll see it takes mere seconds.

[sgillies]

@culebron I'm copying some text over from https://github.com/geopandas/geopandas/issues/557:

I think the main speed issue is explained in the performance hints section of the OGR SQLite format docs:

The default OGR behavior is to COMMIT a transaction every 200 inserted rows. This value is surely too low for SQLite; and closing too much frequently the current transaction causes severe performance degradation.

It's my understanding that the GPKG driver is based on the SQLite driver and has the same performance issue. The docs are vague on how to increase the transaction size.

The only thing to be done in your Fiona usage is to eliminate the use of a buffer and just do this:

    colxn.writerecords(df.iterfeatures())

There's already a buffer on the OGR side and an extra Python one accomplishes nothing. I still think the SQLite transaction size is the major bottleneck.

[micahcochran]

It looks like 'ogr2ogr' uses a '-gt' parameter to group transaction. The variable is psOptions->nGroupTransactions, which this code even defaults to 20,000 transactions. ogr2ogr's command line library is taking care of those details of when to commit.
https://github.com/OSGeo/gdal/blob/2.2/gdal/apps/ogr2ogr_lib.cpp

I couldn't find anything in Fiona dealing with transactions, so it probably is using the default value.

[sgillies]

@micahcochran thanks for the pointer! Seems like we (speaking for the project) need to prioritize the use of OGR dataset transactions for GPKG at least. This function and the corresponding commit function appear to be the keys:

http://www.gdal.org/gdal_8h.html#a57e354633ef531d521b674b4e5321369

[rouault]

The doc of the SQLite driver was out-dated / unclear. Fixed per https://trac.osgeo.org/gdal/changeset/40435.

[snorfalorpagus]

Are transactions something that should be exposed to the user in Fiona, or something that is handled behind the scenes?

[sgillies]

@snorfalorpagus now that's a good question! For a start, we should start using transactions within writerecords so we can control the transaction size (the default size is too small).

Here's an example of starting a transaction (in C): https://github.com/OSGeo/gdal/blob/trunk/gdal/apps/ogr2ogr_lib.cpp#L4271. Here's how the transactions are committed: https://github.com/OSGeo/gdal/blob/trunk/gdal/apps/ogr2ogr_lib.cpp#L4342-L4346.

[sgillies]

Closed via #491. GeoPackage writing with performance as good as ogr2ogr will be a fiona 1.8.0 feature (more about the plan at https://github.com/Toblerity/Fiona/issues/396). I'm going to try to get a 1.8a1 release out very soon.

[sid-kap]

@sgillies you said in #491 "We've omitted GPKG support", but then you still said it's taken care of in that issue.
I'm confused, do we have GPKG support for transactions or not?

[sgillies]

@sid-kap yes, Fiona will write geopackages with larger transactions. My comment about omitting GPKG applied only to the GDAL builds on our CI servers.

[sid-kap]

That makes sense, thanks! Is GPKG supported in the 1.8a1 release or only on HEAD?

[snorfalorpagus]

That makes sense, thanks! Is GPKG supported in the 1.8a1 release or only on HEAD?

GPKG has been supported for a long time (since 2014 - 9b41007048bc529e2b2f8d37ee9d931d3aa49526). Since 1.8a1 writing GPKG files will be quicker because of the transaction handling in #491.

To use GPKG from Fiona you need to have GDAL compiled with support for it.

[sid-kap]

Got it, thanks!

[kbevers]

Is there a work-around for this for those of us still stuck on 1.7.x? Maybe by setting an environment variable that controls how GDAL behaves behind the scenes?

[sgillies]

@kbevers I'm sorry, but there is no workaround.

[kbevers]

@sgillies That's what I feared. No worries though, I can work around it for now and improve my code once 1.8 is available. That is going to be a fun day :-)