[OSM-dev] osm2pgsql slim mode, postgis, and hard disk spindles

[Frederik Ramm]

Hi,

> I do have this setup (2 physical disks) 
> My first attempts were to move around postgis data to try to increase
> performance but without much result. (and introducing admin complexity). I'm
> now runing a conventionnal setup at postgis side, but have moved the datas
> on to a RAID 0 stripping linux software raid (without any fs block or
> fs/raid block tweaks) and every things has speed up a lot both on initial
> and hourly imports. 

That is supported by my own experiments. I loaded the 20090819 planet, 
tarred the PostGIS partition so that I could quickly re-create the 
scenario, and then applied the 20090819-20090820 daily diff with 
osm2pgsql --slim in a number of different configurations:

"plain" (nothing special, all on one hd)..... 270 minutes
"plain" with -C4000.......................... 262 minutes
with pg_xlog on other hd..................... 266 minutes
with all indexes on other hd................. 210 minutes
with the "slim mode" tables on other hd ..... 220 minutes
"crossed" (normal tables on disk 1, their
indexes on disk 2, slim tables on disk 2,
their indexes on disk 1)..................... 213 minutes
like "plain" but on a RAID-0 md device....... 191 minutes
like "plain" but on a RAID-1 md device....... 281 minutes

I have not tried combinations of these; it is to be expected that the 
-C4000 will speed up the RAID-0 value a little but that's as good as it 
gets. Since the process is still disk bound, adding a third hd to the 
RAID-0 array could again improve things.

I haven't thoroughly tested read performance; I guess that the RAID-1 
should give slightly faster reads than RAID-0.

I feel a bit like someone who has spent days to code something in 
assembler only to find that writing it in C and having the compiler 
produce machine code yields something more effective ;-)

Bye
Frederik