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

[Ivo Brodien]

Hi,

does nobody has a hint or better idea to do it?

cheers
Ivo

On 28.01.2009, at 15:16, Ivo Brodien wrote:

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