[Tile-serving] [openstreetmap/osm2pgsql] Unable to import planet after repeated attempts on powerful machines (#930)

[Max Heinritz]

Hi, I've been using `osm2pgsql` with great success for city-sized imports for about a year. Thank you for the excellent project.

For the last month or so, I've been trying to get the whole planet render with no luck. I've tried perhaps half a dozen times locally and on AWS. I'm experiencing something very similar to what was reported in https://github.com/openstreetmap/osm2pgsql/issues/329, https://github.com/openstreetmap/osm2pgsql/issues/218, https://github.com/openstreetmap/osm2pgsql/issues/785.

On the client, the process gets killed (not by me) when processing relations. Here's the PostgreSQL error log:
```
:LOG: could not receive data from client: Connection reset by peer
:ERROR: unexpected EOF on client connection with an open transaction
:CONTEXT: COPY planet_osm_rels, line 610791
:STATEMENT: COPY planet_osm_rels FROM STDIN
:ERROR: unexpected EOF on client connection with an open transaction
:CONTEXT: COPY planet_osm_line, line 1
:STATEMENT: COPY planet_osm_line(osm_id,"access", ...,"way_area",way) FROM STDIN
:FATAL: terminating connection because protocol synchronization was lost
:LOG: could not send data to client: Broken pipe
:FATAL: terminating connection because protocol synchronization was lost
:LOG: could not send data to client: Broken pipe
```
Full log in this gist: https://gist.github.com/maxh/5b9f7ee51096428de862248bfb62c9a5

No other log information on the client, at least that I can find. I'm at a loss for how to proceed other than trying a different import tool or looking at source code. So I thought I'd ask for advice here first. I confirmed that a city-size import with the exact same setup works fine.

Specs:
- [t3a.2xlargem](https://aws.amazon.com/blogs/aws/now-available-amd-epyc-powered-amazon-ec2-t3a-instances/) 8 CPUs, 32 GiB + 200gb EBS mounted for planet file and flat node file
- [db.r4.2xlarge](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html) 8 CPUs, 61GB memory

`osmpgsql` params per https://www.volkerschatz.com/net/osm/osm2pgsql-usage.html
```sh
/home/ec2-user/osm2pgsql/build/osm2pgsql \
> --create --slim --keep-coastlines --multi-geometry \
> --database osm \
> --host ....rds.amazonaws.com \
> --cache 28000 \
> --number-processes 8 \
> --flat-nodes /data/nodes.bin \
> --username database_master \
> /data/extracts/planet-190429.osm.pbf
```

Database settings:
Mostly like https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks#Planet_import_on_Ubuntu_17.10_NVMe, except that AWS Aurora doesn't let you change some things like `max_wal_size`.

Answering a likely follow up question.
>Are the planet_osm_point, line, poly and roads tables created? Is there data in them?

Sometimes yes, sometimes no (for both questions).

Things I'm considering:
- If this is about TCP/IP rather local sockets as suggested in https://github.com/openstreetmap/osm2pgsql/issues/329#issuecomment-91601423, perhaps I would have more luck manually installing Postgres on an EC2 instance and using local sockets instead of network connection between compute and db?
- From https://github.com/openstreetmap/osm2pgsql/issues/329, the error seems to be on the (db) client rather than the server. Is that right? Should I focus my debugging there?
- If the issue is OOM as suggested in https://github.com/openstreetmap/osm2pgsql/issues/329#issuecomment-272726430, anything I can do other than getting a machine with more GB?
- Per `min(0.75*total_mem, 20GB)` recommendation in https://github.com/openstreetmap/osm2pgsql/issues/218#issuecomment-65121327, I've lowered cache from 28000 to 22000. I've tried with 20000 before to same result, so idk.
- Is there a way to see more error logs on the client side? (Edit: I just kicked off another attempt with `--verbose`, maybe that will be more useful?)
- Any other recommendations?

My feedback loop right is long and expensive right now, so I'm very grateful for any thoughts or replies at all! Thanks in advance!

-- 
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/930
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20190507/419338b0/attachment.html>