[Tile-serving] [openstreetmap/osm2pgsql] Why so slow, even after upgrading to full SSD system and doubling RAM to 32Gb? (#883)

[Sarah Hoffmann]

I can't believe your system doesn't use swap. Let's have a look at your RAM use in the second case. You have 32GB RAM total. 16GB are allocated to postgresql shared buffers. This memory is blocked by postgresql and cannot be used for anything else. That leaves 16GB for the OS and osm2pgsql. However, you have asked osm2pgsql to use 24GB for the node cache. So at least 8GB of this have to come out of swap memory. In addition to that the flat node file has to be loaded into memory for lookups. Given that we have already established that all memory is used, you likely end up with a disk access on almost every lookup.

For a system with 32GB RAM and SSDs, I generally recommend a postgres configuration as follows:
```
shared_buffers = 2GB
maintenance_work_mem = 10GB
```
and during the initial import:
```
fsync = off
synchronous_commit = off
full_page_writes = off
```
(reenable these immediately after the import has finished or you might find yourself importing again because of a database corruption)

With only 32GB RAM, the node cache won't fit into RAM anyway, so you might as well disable it almost completely and leave the space for buffering the flatnode file. Use a `-C 10` or similar.

Numbers from a run I did at the beginning of the year on a 32GB RAM machine with slow SSD:

```
Processing: Node(4245103k 1453.8k/s) Way(459833k 9.29k/s) Relation(5455360 253.74/s)  parse time: 73927s
Node stats: total(4245103652), max(5302726531) in 2920s
Way stats: total(459833994), max(548925998) in 49507s
Relation stats: total(5455389), max(7838217) in 21500s

Pending ways took 21422s at a rate of 14482.89/s

Osm2pgsql took 118211s overall
```

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/883#issuecomment-443539333
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20181202/dda8a86f/attachment.html>