Hi Nuno,
A late reply; this looks very interesting!
One thought on the "Replace Operation" you describe - given the sensible
aversion to VACUUM, how about doing the replace operation function at
the SQLite level rather than the file-system level? So rather than just
copying the file, you could SELECT the content from the old SQLite file
and then INSERT it to a new one. This would function the same way as a
"VACUUM", although would have a higher cost than doing it at the
file-system level.
Also, if you have any questions about the how to design this optimised
to SQLite's capabilities, I'd strongly suggest asking on the SQLite
mailing list - they're a very helpful, friendly, and knowledgeable bunch.
Cheers,
Jonathan
On 19/05/2016 11:00, Nuno Oliveira wrote:
> Hi all,
> sorry for the cross posting.
>
> We would like to add MBTiles support to GWC.
> Follows a description of the work with the main issues\limitations.
>
> I would like to have community feedback on this, by the way is there a better
> way to propose this work ?
>
> * MBtiles and SQLitle *
>
> MBtiles is a specification that describe how to store tiles in an SQLite
> database, this will allow us to store many tiles in a single SQLite
> file avoiding us file systems headaches:
> https://github.com/mapbox/mbtiles-spec/blob/master/1.1/spec.md.
>
> We can rely on GeoTools gt-mbtiles module for reading and writing MBTiles,
> this way most of the work of implementing this blobstore will be
> managing SQLite connections and SQLite files.
>
> SQLite files cannot be managed as simple files. When connections to an SQLite
> database are open we should not delete, move or switch the
> associated file. Databases files can be filled with "empty space" after
> deleting an huge amount of data or can become fragmented after
> frequent inserts, updates or delete operations.
>
> SQLite documentation warns us against putting databases files on a shared
> file system if multiple process need access to it (which is our
> case). Unless we can rely on a distributed lock mechanism SQLite databases
> files should not be used with shared stores.
>
> * VACUUM and DiskQuota *
>
> To remove the fragmented space (or the empty space), the VACUUM command needs
> to be executed. Although, performing a VACUUM command as a few
> drawbacks:
>
> - During a VACUUM twice the size of the original database file is
> required in disk.
> - During the VACUUM operation no access to the database is allowed.
> - The VACUUM operation copies the whole database which can take minutes.
>
> For these reasons the VACUUM command cannot be performed after each
> operation. When possible we will avoid creating fragmented space. For
> example, during a truncate operation we may prefer remove a whole SQLilte
> file instead of deleting part of is content. Another consequence
> of the fragmented space is that DiskQuota will not be compatible with this
> blobstore.
>
> * MBTiles Granularity *
>
> Reading and writing tiles on an SQLite database will be slower than writing
> on a file system but will allow us to avoid file system
> headaches. In order to limit the amount of contention on each single MBTiles
> file we will allow users to decide the granularity of the files
> so that instead of having a single file for each single layer we will allow
> users to have more granularity.
>
> MBTiles force us to have at least a file per layer and format. If we want to
> support more CRSs we will also need a file for each CRSs. By
> configuration it will be possible to configure the granularity of the
> database files. By default we will have a granularity per layer, crs,
> format and zoom level. As an instance something like this could be offered:
>
> <blobstore>
> <file>/path/to/{grid}/{dim}/{tileset}/{z}/{x}-{y}.sqlite</file>
> <xcount>1000</xcount>
> <ycount>1000</ycount>
> </blobstore>
>
> In this case we should include the {x}, {y} and {z} replacements in the
> template determining the file to use. In the previous example, tile
> (z,x,y)=(15,3024,1534) would be stored in a file named
> /path/to/g/mytileset/15/3000-1000.sqlite3 and tile (5,2,8) would be stored in
> a file
> named /path/to/g/mytileset/5/0-0.sqlite3.
>
> With more databases files we have more performance but we will have also more
> files to manage on the file system. In addition we can couple
> this with the in-memory cache in order to improve tile serving performance.
>
> * Connection Pooling and Performance *
>
> SQLite allow multiple readers but only allow one writer at the time which
> will block the entire database. At most only one connection should
> be open to each SQLite database, the total number of open connections is
> limited by the number of open files allowed by the OS (in linux
> this is controlled by the ulimit). A connection pool that will control the
> number of open connections and that will be responsible to manage
> the connections will be implemented.
>
> * Replace Operation *
>
> As said before, if the cache is running we cannot simply switch SQLite files,
> we need to make sure that all connections are closed. A
> replace operation will be created for this propose. The replace operation
> will first copy the new file side by side the old one, then block
> the requests to the old file, tear down the store, delete the old one, rename
> the new file to current one, reopen the new db file and start
> serving requests again. Should be almost instant. A REST entry point for this
> operation will be created (with the possibility to send the
> new file with the request).
>
> Regards,
>
------------------------------------------------------------------------------
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic
patterns at an interface-level. Reveals which users, apps, and protocols are
consuming the most bandwidth. Provides multi-vendor support for NetFlow,
J-Flow, sFlow and other flows. Make informed decisions using capacity
planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e
_______________________________________________
Geoserver-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
