[OSM-dev] Tuning PostgreSQL for on-the-fly SVG map rendering?

[Ivo Brodien]

Hello,

for a project I want to render SVG maps on-the-fly using Geoserver and  
a PostGIS data store. I know there is Cairo but don't need it to be  
that perfect. The files should be as small as possible while still  
nice enough to see recognize something.

As already mentioned on this list I managed to import the "planet_osm"  
file into my PostgreSQL DB.

The machine is:

CPU: 	2.66 GHz Intel Dual Core
RAM: 	3.2 GB RAM
OS: 		Ubuntu 8.10
DB:		PostgreSQL 8.3

In order to get not all the features (rows) for each query for the  
different zoom levels I thought of adding a maxlevel & minlevel column  
to each row in each table and creating an index on these columns.

So I could do something like: "SELECT * FROM osm_roads WHERE way in  
BBOX and maxlevel =< 18 and minlevel >= 17"

The problem is, that I am not an expert for databases especially big  
ones like the "osm" DB, so I would like to ask for some help in tuning  
the databese:

The first thing that causes headaches is adding the new columns:

ALTER TABLE planet2_line ADD COLUMN maxlevel integer NOT NULL DEFAULT 0;
ALTER TABLE planet2_line ADD COLUMN minlevel integer NOT NULL DEFAULT  
100;

This statement however never returns - at least not in reasonable time.

If I do:

ALTER TABLE planet2_line ADD COLUMN maxlevel integer;
ALTER TABLE planet2_line ADD COLUMN minlevel integer;

it works. So I guess writing the default value takes so much time.

I want to use a default value, because afterwards I want to add the  
correct values by statements like these, so there have to be the  
initial values for comparing:

--line
UPDATE planet2_line SET maxlevel=max(17,planet2_line.maxlevel),  
minlevel=min(15,planet2_line.minlevel) WHERE highway =  
'secondary_link' and not (tunnel='yes' or tunnel='true');
UPDATE planet2_line SET maxlevel=max(18,planet2_line.maxlevel),  
minlevel=min(17,planet2_line.minlevel) WHERE highway =  
'secondary_link' and not (tunnel='yes' or tunnel='true');

--point
UPDATE planet2_point SET maxlevel=max(8,planet2_point.maxlevel),  
minlevel=min(6,planet2_point.minlevel) WHERE place = 'city';
UPDATE planet2_point SET maxlevel=max(10,planet2_point.maxlevel),  
minlevel=min(8,planet2_point.minlevel) WHERE place = 'city';

-- polygon
UPDATE planet2_polygon SET maxlevel=max(18,planet2_polygon.maxlevel),  
minlevel=min(12,planet2_polygon.minlevel) WHERE aeroway = 'apron';
UPDATE planet2_polygon SET maxlevel=max(18,planet2_polygon.maxlevel),  
minlevel=min(13,planet2_polygon.minlevel) WHERE 'natural' = 'beach';

Running these UPDATE statement needs a lot of time as well, but I  
don't care that much, because I only have to do it once.

The maxlevel and minlevel values come from the osm.xml file which is  
included in the Mapnik repository.


So the big question now is:

How do I tune my DB Server for more performance? The server does not  
need to do more work than running the DB and there will be only one  
client connected.

What could be good values for the Memory in postgresql.conf?

At the moment I changed these values:

# - Memory -

shared_buffers = 500MB			# min 128kB or max_connections*16kB
temp_buffers = 8MB			# min 800kB
work_mem = 10MB				# min 64kB

I probably have to use different tunings for doing the level setting  
task and the rendering task? But what would they look like?

Thanks for any little help or advice!

Ivo