Skip to main content

Improving TileDrawer rendering speed

I have been playing with TileDrawer recently. After a first rapid successful test on a throw-away EC2 instance I decided to deploy one on a vm for intranet use.

The installation process is pretty straightforward, just run the script copied from the TileDrawer page as root. FYI make sure you have installed curl and python-cssutils before launching the script.

After the script completed I started browsing the map and noticed that tiles took a looong time to render.
A look at top from the server console showed that postgres was hogging the cpu. Memory was fine with no signs of swapping (it is a 1GB instance which I promptly upgraded to 2, running on server class hardware with Xeon CPU). Even after the memory upgrade tile rendering was so slow that the browser would sometime give up and show a white tile.

I decided to look into it a little further and started by using the technique I have already described in another article. The database cache looked fine though as most relationships were completely cached and database size is a measly 300MB which is no problem at all as the postgres param shared_buffers was already set to 512MB.

I then decided to enable slow queries logging (scroll down to 18.8.2) in postgresql.conf:

log_min_duration_statement = 100

I started with a threshold of 100ms, which means pg will log all queries that take more than 100ms to complete. The log quickly filled with lots of queries. Armed with patience I started indexing all the obvious fields like landuse, z_order, amenity, etc.

Eventually I used EXPLAIN on a frequent query that was well above 500ms and to my surprise found out that the planet_osm_polygon table was missing the spatial index on the way geometry. After the index was created no long queries were logged at all and tile rendering was snappy enough to not require cache seeding.

The complete list of all indexes I have created on the database planet_osm (copy and paste):

create index planet_osm_polygon_landuse_idx on planet_osm_polygon(landuse);
create index planet_osm_polygon_x_idx on planet_osm_polygon(z_order);
create index planet_osm_line_highway_idx on planet_osm_line(highway);
create index planet_osm_polygon_amenity_idx on planet_osm_polygon(amenity);
create index planet_osm_line_waterway_idx on planet_osm_line(waterway);
create index planet_osm_poly_building_idx on planet_osm_polygon(building);
create index planet_osm_poly_wayarea_idx on planet_osm_polygon(way_area);
create index planet_osm_poly_natural_idx on planet_osm_polygon("natural");
create index planet_osm_poly_buildingnn_idx on planet_osm_polygon(building) where (building is not null);
create index planet_osm_poly_way_idx on planet_osm_polygon using gist(way);


Comments

Popular posts from this blog

Indexing Apache access logs with ELK (Elasticsearch+Logstash+Kibana)

Who said that grepping Apache logs has to be boring?

The truth is that, as Enteprise applications move to the browser too, Apache access logs are a gold mine, it does not matter what your role is: developer, support or sysadmin. If you are not mining them you are most likely missing out a ton of information and, probably, making the wrong decisions.
ELK (Elasticsearch, Logstash, Kibana) is a terrific, Open Source stack for visually analyzing Apache (or nginx) logs (but also any other timestamped data).

From 0 to ZFS replication in 5m with syncoid

The ZFS filesystem has many features that once you try them you can never go back. One of the lesser known is probably the support for replicating a zfs filesystem by sending the changes over the network with zfs send/receive.
Technically the filesystem changes don't even need to be sent over a network: you could as well dump them on a removable disk, then receive  from the same removable disk.

A not so short guide to ZFS on Linux

Updated Oct 16 2013: shadow copies, memory settings and links for further learning.
Updated Nov 15 2013: shadow copies example, samba tuning.

Unless you've been living under a rock you should have by now heard many stories about how awesome ZFS is and the many ways it can help with saving your bacon.

The downside is that ZFS is not available (natively) for Linux because the CDDL license under which it is released is incompatible with the GPL. Assuming you are not interested in converting to one of the many Illumos distributions or FreeBSD this guide might serve you as a starting point if you are attracted  by ZFS features but are reluctant to try it out on production systems.

Basically in this post I note down both the tought process and the actual commands for implementing a fileserver for a small office. The fileserver will run as a virtual machine in a large ESXi host and use ZFS as the filesystem for shared data.