13

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)

5
+100

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.

  • Thats sounds like the best thing to do so far, I will check try this out and see what happens. – Hasan Mustafa May 17 '16 at 5:55
  • This is the best solution I've found so far, though its not ideal ( I would have liked to finetune the tilestache-seed.py ) it works well enough. – Hasan Mustafa May 17 '16 at 9:30
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);.

  • I used the plug-in "PostGIS Shapefile and DBF loader" in Postgres, it created an index: CREATE INDEX scale_geom_idx ON scale USING gist(geom). , automatically when I imported my shapefiles. Should I look to make additional indexes? – Hasan Mustafa Apr 27 '16 at 9:34
  • Do you have a lot of rows? Is your vector tile generation dependant on other attributes (eg subselections of the data)? – bugmenot123 Apr 27 '16 at 9:40
  • Yes to both, I have alot of rows in some of the tables, My POI table has around 975k rows and my roads shapefile was 8.5gb before importing into Postgres. I am using queries to filter data based on zoom levels: "10":"SELECT wkb_geometry AS geometry,priority,name,route_num FROM roads WHERE priority IN (5,4,3)" this is a query I am using to return roads on zoom level 10. – Hasan Mustafa Apr 27 '16 at 9:43
  • Then create an index on each column you use in a WHERE clause. You can also create multi-column indexes if needed. – bugmenot123 Apr 27 '16 at 9:48
  • How would I go about doing that, on what basis should I make the indexes? – Hasan Mustafa Apr 27 '16 at 9:59
1

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...

  • I have 11 different tables that I am querying to make my tiles, does that mean I'll have to make 11 materialized views? And my queries change based on Zoom Levels as well. – Hasan Mustafa May 11 '16 at 5:23
  • Well if it's not fast enough, perhaps making views of the slowest select statements will be able to improve it. Note that you can make a MV of any select statement, including from multiple tables if you need. – Alex Leith May 11 '16 at 6:01
  • So if I make a single MV based of all my queries will that work? – Hasan Mustafa May 11 '16 at 6:05
  • You can't do that. Make one for your slowest query, maybe for a single zoom level, and see if it makes i faster. – Alex Leith May 11 '16 at 6:08
  • 1
    Well if that's the case then optimising the database wont help. Look deeper. – Alex Leith May 12 '16 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.