In the course of finding some way to “optimize” support for the planet on our server, here is an index which speeds things up greatly for city name lookups:
create index admin_city_names on planet_osm_line (boundary,admin_level,name) where (boundary='administrative' and admin_level='8');
On the higher-end server running osm2pgsql with a 8G cache, the speedup ratio is at least 2.1 and seems to increase with the size of the diff. Considering the very few samples we have, it’s hard to tell the effect of the cache size increase. But the fact is that this higher-end server behaves better, a speedup factor between 2 and 2.75 can reasonably be expected. This would translate to a daily server downtime of around 4h for planet updates. Details follow.
(more…)
Thomas already posted the results of the initial planet import on the higher end server (see the maposmatic-dev mailing-list): 52 hours, which corresponds to a speedup factor around 2 compared to our current server. Our first incremental diff import (with a 1.5G cache) shows a speedup factor of the same order, which means that an incremental diff import would require around 4h10 server downtime every day. Details follow.
We’re currently running another incremental diff import on the higher end server, this time with a 8G cache… Let’s hope it’s getting even better.
The main MapOSMatic.org website now include a box in the right column containing the five latest news coming from the blog. The intent is to make the blog contents more visible to MapOSMatic visitors and to include some more dynamic and fresh content on the main website.
For testing purposes, we just got an access to a server that uses the latest Intel CPUs, 16 GB of RAM and a storage using RAID10 over 8 disks. We are currently importing the planet data into a PostgreSQL database and will then be able to test :
This will allow us to tell if the issue is really on the hardware side or if we must think and work on optimizations at different levels. However, such a high-end server is only given to us for a short period of time, and if the conclusion of the experiment is that such a server is needed, then we’ll have to find a way to fund a similar server.
We have a development server running on a database holding the whole planet (as of today). Besides requiring 9h/day to keep the DB updated, the following usage issues arise on that “planet” development instance:
The “funny” thing is that both the “mapnik” and street index rendering don’t seem to be that slower, but this needs to be confirmed by a more thorough profiling. If this proves correct, we have to find out what makes PostGIS so slow for the first queries. Is this our queries ? The structure of the DB which is lacking a few indexes ? And, in that case, how come mapnik rendering + street index seem almost as fast: is this because the previous queries did put most things in cache ?
But what a great pleasure to be able to map NYC, Palo Alto, !…
Unfortunately, this will be our privilege, since we are decidedly not planning to open this to the public… for the time being, that is: with our current server (a “modest” dual core x86_64 2GHz / 2GB RAM). Donations or support for a new server are welcome !