[Geocoding] Nominatim SQL script indexing speed

[Gergely Hanti]

"I have seen stuff like that on other OSM related imports and my explanation
was that when you start indexing your index is empty. Your index grows

with the amount of data you already indexed so your working set grows
and thus your working speed decreases.
Flo"

Hello,

I think Teemu was speaking about the "loaddata" phase of the
installation, which I think should not be considered as "normal"
indexing.

For me the data-processing speed was anything but consequent. For the
Europe extract it was 5-7 MB/m for the first 5 hours. Then it slowed
down drastically (1,5-3 MB/m) for 22 hours. Then it became very fast
(15-22 MB/m) for 7 hours. Unfortunately after that, at cca. 70% the
process has run out of memory. (so I am not sure if it was a
"standard" run till the crash)

This was the result with PostGIS 1.3.3. After this I had updated to
1.5.2. (I had also raised the pg's shared_buffers and
effective_cache_size a bit)  Then the process was like 3x slower.

I am using Debian, PostgreSQL 8.3.12, PostGIS 1.3.3/1.5.2, with a two
months old osm2pgqsl version. In a dedicated System x3650 M2 with 24GB
Ram

I've set Debian's SHMMAX to 8GB = 8*1024*1024*1024 = 8589934592, and
SHMALL to SHMMAX/4096 = 2097152

107,108c107
< shared_buffers = 6GB
---
> shared_buffers = 32MB
115,116c114,115
< work_mem = 256MB
< maintenance_work_mem = 256MB
---
> #work_mem = 1MB
> #maintenance_work_mem = 16MB
121c120
< max_fsm_pages = 1592000
---
> max_fsm_pages = 204800
153c152
< fsync = off
---
> #fsync = on
163c162
< wal_buffers = 24MB
---
> #wal_buffers = 64kB
172c171
< checkpoint_segments = 20
---
> #checkpoint_segments = 3
174c173
< checkpoint_completion_target = 0.9
---
> #checkpoint_completion_target = 0.5
205c204
< random_page_cost = 1.5
---
> #random_page_cost = 4.0
209c208
< effective_cache_size = 20GB
---
> #effective_cache_size = 128MB
383c381
< autovacuum = off
---
> #autovacuum = on

What do you suggest? Should I downgrade the PostGIS? What should I
change in the config? (Not considering the "out of memory issue",
because it is not related probably)

Regards,
Gergő
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/geocoding/attachments/20110224/1bb09715/attachment.html>