Increase Tiles Caching Speed (TileStache)

Question

I am serving vector tiles using TileStache, I have everything set up like I want. My data is stored in Postgres and I am using the VecTiles provider to serve GeoJSON tiles.

I want to cache all my tiles to make the tiles serve faster. I am using tilestache-seed.py to seed my cache. I am running tilestache-seed on several machines. Tilestache-seed worked really well uptill zoom level 13, but after that it is taking way too long to cache the tiles. Just for Zoom Level 16 I have 5023772 tiles to cache, and I am only getting 100k-200k tiles per day on each machine.

How can I make my tiles cache faster? Is there a way to fine tune tilestache-seed.py and make it seed faster?

Update: I have tried building spatial indexes on my tables (on the geometry column and the columns used for filtering data through the where clause) and I still haven't seen a significant increase in tiling speed. At this rate only Zoom 17 will take me a month and this time will only increase exponentially as I move towards Zoom 21

Update 2: I tried making materialized views as well and there is no discernible change in performance, so optimizing the database is not working. I think I will need to optimize the tilestache-seed.py itself, or devise a new way to cache the tiles.

Hardware Info I am running the caching processes on 8 different PCs, one of which is an i7 with 32gb ram and another is an i3 with 4gb ram but they both give me almost the same caching speed (approximately 100k tiles per day)

Answer 1

I would say that for zoom greater than 15, if you split your area of interest into smaller areas(Bounding box), you will be able to cache them in much less time by running multiple processes on a single machine.

For example, you are running zoom 16 (having 50,000,00 tiles) on a machine and according to your average tile-caching speed, this process will complete in about 40-50 days. Lets say you split these tiles in to two and run them simultaneously on the machine then you will be able to cache them in 20-25 days because tilestache seeding process uses only about 30 percent of your processor for a single tile caching process and I know this because i have the same issue once and up to some extant this solved my problem.

It won't effect the tile-caching speed if you are running a single process on a machine or multiple processes but the CPU usage will be increased.

I hope this will help you.

Answer 2

By default shp2pgsql does NOT create indexes. You need to pass -I to make it generate a spatial index. http://postgis.net/docs/manual-1.3/ch04.html#id435762

Check if your table has an index by running \d tablename in psql. In the list of indexes should be a line with "gist" (unless you picked a different index) and your geometry column name.

You can add one after the fact as well, see http://postgis.net/docs/manual-1.3/ch03.html#id434676 (don't let the note about lossiness scare you):

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometrycolumn] );

Since you probably also use non-spatial columns in your queries, you usually want to create indexes for each column that is used for lookup. If for example you have a query like SELECT * FROM roads WHERE priority = 3; then priority is used and adding a index for it will significantly speed-up things:

CREATE INDEX idx_roads_priority ON roads(priority);.

Answer 3

Another thing to try if you're using a standard query is creating a materialized view from the query, and building your tiles from that: http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

What this will do is make you a table that stores the query (so you could potentially update it in the future). Make sure you have spatial indices on the child MVs and then you'll be as fast as possible.

What might be happening is that you have a spatial index, but then you are selecting only some of the data, which means you're not using the spatial index any more...