Cache configuration and settings

Topics: General
Sep 16, 2012 at 11:09 PM

Using the control for a while not and see how it works, very cool and very useful.

On the cache side I have a question as to how the delete older than call is expected to work on a secondary (or primary) cache that is using a MSSQL database because the table schema does not contain a date for the tile entry ?

With the cache configuration for delayed flush, would it be possible to expose the timer as a property that is being used to time the frequency of cache page writes into the database. The reason is that a cache that is on an SSD is one extreme of write capability, while the typical (90's legacy) LAN latency and SAN backed DB could be limited to under 10 writes per second. The current setup seems to be around 500ms ? Either that or provide an exposed flush cache method in the interface (missing from current hot build).

For the cache flush, being able to flush a single provider and not the whole cache would be useful (can work around it by calling the DB directly) because some providers, weather data for example, have a date within the URL that is the date specific to the returned tiles. Flushing a whole cache to make sure a single provider is refreshed would seem to make the secondary cache property susceptable to individual users flushing the cache for the whole. The case i have is a map provider that limits the dailt tile request to 300 tiles per day, while weather satellite data is daily refreshed or dated. Flushing the 300 tiles rather than building on the 300 each day for the sake of the other provider seems to be a simple solution of FlushCache(dbid) method ?

Within the Import gmdb option and a primary cache setting for MSSQL the import process does not appear to load the tiles into the database during the method call or loads them to memory cache and flushes on the delayed sequencing ? If the import has duplicate tiles does this throw the import process out ?

Last one, the current hot build appears to have changed the ArcGIS provider base from the release build, which makes none of the previous providers work (? Is there a change log as to what has altered since the release build ?

Sep 16, 2012 at 11:25 PM

Within the ArcGIS provider base the GetTileUsingHttp appears to return null for a valid URL


Sep 16, 2012 at 11:51 PM

Also get an error infrequently with MSSQL as a secondary cache provider when set :

SQLite error (17): statement aborts at 29: [INSERT INTO main.Tiles(X, Y, Zoom, Type, CacheTime) VALUES(@p1, @p2, @p3, @p4, @p5)] database schema has changed

This does not occur for all tile inserts and tiles are written into the database (over 35,000) and the schema within the MSSQL database was changed to make the tile field a varbinary(max) from an image field type because this field type will not be supported by MSSQL in the future and should in theory have no impact on the code. I'm presuming here that the SQLite base is used as the link through to MSSQL provider and may be ending up with the wrong schema for the local primary SQLite cache ? Out of a few thousand tile writes the error occured 3 times.

Sep 17, 2012 at 3:37 PM
  • to 'save' ssd you can set map.CacheLocation to other drive
  • for other providers you can manually using sql management tool delete/import unneeded tiles
Sep 17, 2012 at 5:13 PM

The secondary cache schema could do with the timestamp column that is in the sqlite database as this would allow for manual cache management options in a LAN where some provider tiles have limited lifespans (weather data for example) while retaining all the benefits of the secondary cache for other permanent map tile sources.

The SSD comment was in relation to having the cache database in MSSQL on SSD, which would be able to keep up with (I/O capacity of over 30,000 per second and 500MB/sec) direct saving of the tiles as they are retrieved rather than on a background lazy writer thread.

Sep 17, 2012 at 10:07 PM

There also seems to be a separate bug with the secondary / primary cache storing tiles, which have not been retrieved from the WMS provider but scaled by the control. When zooming in the tiles are graphically scaled ahead of the tile retirval process and under some conditions these scaled tiles are stored into the cache and subsequently stay, creating some issues that require either a complete tile purge or..... a function to purge / refresh the active tiles within the current view pane, which also refreshes the tiles within the cache (forces a write). This would allow part of a tile management scheme to remain within the control without having to resort to creating separate SQLitecode to move tiles around between cache files, in this instance from separate SQLite caches to the MSQL secondary :

    Dim sqConn As New System.Data.SQLite.SQLiteConnection
    sqConn.ConnectionString = "Data Source=Y:\" & ThisDatabase & ";"

    Dim sqCmd As New SQLite.SQLiteCommand
    sqCmd.CommandText = "SELECT Tiles.Type,Tiles.Zoom,Tiles.X,Tiles.Y,TilesData.Tile,Tiles.CacheTime FROM Tiles INNER JOIN TilesData ON ="
    sqCmd.CommandType = CommandType.Text
    sqCmd.Connection = sqConn

    Dim sqRead As System.Data.SQLite.SQLiteDataReader
    Dim SPin As SqlCommand = myDBmap.dbProcedures("usp_GMapNETcache_add")
    Dim RowCount As Integer

    sqRead = sqCmd.ExecuteReader()

    While sqRead.Read
      RowCount = RowCount + 1
      tMapTile.Rows.Add(sqRead(0), sqRead(1), sqRead(2), sqRead(3), sqRead(4))
      If RowCount = 100 Then
        SPin.Parameters("@tMapTile").Value = tMapTile
        RowCount = 0
      End If
    End While
    If RowCount > 0 Then
      SPin.Parameters("@tMapTile").Value = tMapTile
    End If

Currently have 148,026 tiles within the MSSQL cache (2GB)m which seems to be performing quite well....

Sep 18, 2012 at 7:50 AM

under NONE conditions these scaled tiles are stored into the cache

Sep 18, 2012 at 8:20 AM


Have also experienced this, with zoomed tiles being stored in the cache. Had to get round it by creating another option for FORCE titles to be downloaded and writen to cache on maps i discovered the problem.

Not sure if it was caused by a slow connection, where the tile was zoomed, but the actual tile not recieved in time, resulting in the zoom image to be saved to cache ?


Sep 18, 2012 at 8:33 AM
Edited Sep 18, 2012 at 8:35 AM

it may somehow stuck on screen, but they never get into the cache

p.s. i mean disk cache ;}

Sep 19, 2012 at 12:52 PM

They do appear to be getting saved into the cache as I can turn the mode to cache only and see the scaled vs non scaled tiles on screen and that is after a re-start so that it is not from the memory cache (database, both primary SQLite and MSSQL secondary).

A function to refresh tiles, based on the active map screen window and a coupled to re-writing the tiles into the cache would be very useful, prevent a full cache wipe or having to implement a workaround by separate delete into the database and then re-start the app (because until the cache fall from memory it will not get put back into the database).

This would be a very method to have, allows a double check on the images in the cache (have google updated the satellite view) while allowing other time based WMS providers to be refreshed on demand (area of interest only).

Sep 19, 2012 at 1:03 PM

As a thought it may be when the mode is being switched between cache only and server and cache. This would allow the map to be moved around and scaled tiles to be displayed in cache only mode, which when the access mode is turned back on to server and cache if the memory cache does not keep track of which tiles are scaled and written to disk ?

Sep 19, 2012 at 1:52 PM

jesus..., it's programming, keep it simple ;D

Sep 19, 2012 at 8:55 PM