sqlite backend for caches

[thomas bonfort]

Hi all,

trunk version has just received a new sqlite cache backend along with
the disk and memcache ones.

the configuration is straightforward:

<cache name="sqlite" type="sqlite3">
<!-- base
absolute filesystem path where the sqlite database files
will be stored.
this directory needs to be readable and writable by the user running
apache
-->
<base>/tmp</base>

<!-- hitstats
log last access time and total number of hits for each tile
in the cache.
note that this slows the tile accesses drastically as it
requires a write
to the database for each tile access
-->
<hitstats>true</hitstats>
</cache>

The backend will create a database file for each tileset/grid
combination, and also supports dimensions.

The schema isn't set in stone yet:

create table if not exists tiles(
x integer,
y integer,
z integer,
data blob,
dim text, /*not present if tileset has no dimensions */
ctime datetime,
atime datetime,
hitcount integer default 0,
primary key(x,y,z,dim)
)

From my limited testing, the performance accessing tiles this way is
very good, although not as fast as the disk cache.

regards,

thomas

[Stephen Woodbridge]

Hi Thomas,

I would consider putting the logs in a separate table so that the tiles
are write once and read mostly while the hits stats are mostly write.
This might speed things up a lot, but I have not tested that is the case.

-Steve

[Michael Smith]

Hi Thomas,

I've just changed my cache over to sqlite backend and am now getting

geocache_seed -c /etc/geocache/geocache.xml -t osm -g gg -z 0,2 -n2

seeding level 1sqlite backend failed on set: SQL logic error or missing databasesqlite backend failed on set: SQL logic error or missing databasesqlite backend failed on set: SQL logic error or missing database

seeded 1 metatiles at 0.0836718 tiles/sec

I can see the sqlite database being created when apache restarts

I have 

 <grid name="gg">

            <metadata>

              <title>GoogleMapsCompatible</title>

               <WellKnownScaleSet>urn:ogc:def:wkss:OGC:1.0:GoogleMapsCompatible</WellKnownScaleSet>

            </metadata>

            <extent>-20037508.3427892480 -20037508.3427892480 20037508.3427892480 20037508.3427892480</extent>

            <srs>EPSG:900913</srs>

            <srsalias>EPSG:3857</srsalias>

            <size>256 256</size>

            <resolutions> 156543.0339280410 78271.51696402048 39135.75848201023 19567.87924100512 9783.939620502561 4891.969810251280 2445.984905125640 1222.992452562820 611.4962262814100 305.7481131407048 152.8740565703525 76.43702828517624 38.21851414258813 19.10925707129406 9.554628535647032 4.777314267823516 2.388657133911758 1.194328566955879 0.5971642834779395 </resolutions>

         </grid>

<cache name="sqlite" type="sqlite3">

  <base>/osm_cachedb</base>

  <hitstats>false</hitstats>

</cache>

<tileset name="osm">

      <metadata>

         <title>osm mapserver</title>

         <abstract>see http://mapserver-utils.googlecode.com</abstract>

      </metadata>

      <source>osm</source>

      <cache>sqlite</cache>

      <format>PNG</format>

      <grid>gg</grid>

      <metatile>10 10</metatile>

      <expires>1</expires>

      <metabuffer>10</metabuffer>

   </tileset>

Mike

Michael Smith

US Army Corps of Engineers

[thomas bonfort]

Mike,
Could you check the permissions and ownership on the sqlite file that
was created, as I suspect that the problem is coming from that. As a
temporary fix, chown/chmod it back to something that you and/or the
apache-user can read and write.

> --
> You received this message because you are subscribed to the Google Groups
> "mod-geocache" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/mod-geocache/-/IDSr-DlLYAwJ.
> To post to this group, send email to mod-ge...@googlegroups.com.
> To unsubscribe from this group, send email to
> mod-geocache...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/mod-geocache?hl=en.
>

[Michael Smith]

Thomas,

Same error after changing both owner and permissions.

seeding level 1sqlite backend failed on set: SQL logic error or missing
databasesqlite backend failed on set: SQL logic error or missing
databasesqlite backend failed on set: SQL logic error or missing database

--
Mike

Michael Smith
Remote Sensing/GIS Center

US Army Corps of Engineers

[thomas bonfort]

even with a chmod 777 (on the directories leading to the .db files also) ?

[Michael Smith]

That was it, the directory permissions.

Seems to be working now.

Thanks!

--
Mike

Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers

[Michael Smith]

Thomas,

I'm also noticing that while the cache is seeding, I'm seeing

error: sqlite backend failed on get: database is locked

When accessing tiles that are already generated. Does this mean that the
sqlite backend will be unavailable during the tile writes?

--
Mike

Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers

[thomas bonfort]

mod_geocache waits up to 3 seconds for the sqlite backend to become
available in case it is locked by a write operation.
In case you are seeding it seems that this isn't sufficient when other
processes are requesting tiles.
I'm a bit reluctant to set this 3 second default to anything higher,
so you can either:
- hack a higher value into the code ( sqlite3_busy_timeout(handle,3000); )
- put up with these transient errors that should really only occur
when the backend has a seeding operation going on
- convince me to add the timeout as a configuration parameter.

regards,
thomas

On Mon, Jul 25, 2011 at 00:43, Michael Smith