NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

As a new user I have multiple questions, I will try to break it up by topic.

Part 1

I am a new user of OSM and started building a tool to locate customers and group them by criteria. I had made some great progress on my project and then came in last Monday to find the OSM tile server very slow. Not knowing how common this is I started putting together my own server. I stumbled across this thread: https://help.openstreetmap.org/questions/65043/osm-slowdown which initially gives me the impression it is an unusual situation, but either way I happen to have a decent server available that isn't doing anything at the moment and so figured I could set it up anyway.

Server Specs:

  1. Dell R620
  2. Perc H700
  3. Dual X5680 @ 3.33GHz (6 core / 12 thread per processor)
  4. 192GB DDR3 RAM
  5. 4X 146GB 10K drives for OS
  6. 1 256GB PCIe m.2 SSD for /var
  7. 100GB Fiber Internet

I started setting it up last week and had an issue where it slowed way down which I discussed on StackOverflow here: https://stackoverflow.com/questions/51625815/osm2pgsql-extremely-slow-on-import-on-server-with-192gb-ram

The issue initially looked to be related to a HDD that went bad in a RAID setup and I swapped it out for the SSD and using the shell script you see there I was able to import Alabama in about 300 seconds at a rate of between 300-400k points/second during the first command, then running the second command to --append it slowed way down again to 1k points/second. So while I did have an issue with the HDD, it doesn't seem to be the only bottleneck.

Is it better for me to import all of North America using the --create vs --append multiple states? Is there supposed to be that dramatic of a difference between them?

I opened the database using a GUI (Navicat) and opened the planet_osm_nodes table which opened quickly with 1000 records. I then asked for the last page to get an idea how many records were in it. It took over a minute to pull back page 416,338. Using iotop I noticed something unusual, it was WRITING to the disk drive at a speed of around 120MB/sec to give me the last page. Is this because of how my GUI is requesting those pages The select statement would be of the type SELECT * FROM "public"."planet_osm_nodes" LIMIT 1000 OFFSET 416337000? It just seems very odd to have it writing during a select statement.

I was visiting with a DBA from Micron over the weekend and talking about the import process and the topic of disabling foreign keys and indexes came up as a way to speed up an import into an empty DB. Does the Osm2pgsql script already do this? He was talking about the Postgresql ROLLBACK feature that lets you reverse commits. While this is a great idea during updates, for an initial DB load, it isn't really important for Postgresql to be keeping track of how to rollback, is there options to disable that within the import script rather than tweaking the server config?

This is a brand new installation of Ubuntu 16.04 without any data anyone needs to worry about preserving. With that in mind, what is the fastest method we could devise of importing these records. What is the postgresql.conf file supposed to look like for a server with those stats? Here is what I came up with, but I am sure it could be much better:

data_directory = '/var/lib/postgresql/9.5/main'
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.5-main.pid'
port = 5432
max_connections = 300
unix_socket_directories = '/var/run/postgresql'
ssl = true
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 14GB
work_mem = 1GB
maintenance_work_mem = 8GB
temp_buffers = 8GB
dynamic_shared_memory_type = sysv
max_files_per_process = 1000
effective_io_concurrency = 500
max_worker_processes = 8
checkpoint_timeout = 1h
max_wal_size = 5GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1          effective_cache_size = 25GB
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_timezone = 'Navajo'
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
autovacuum = off
datestyle = 'iso, mdy'
timezone = 'Navajo'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

asked 07 Aug '18, 18:46

AlanHalls's gravatar image

AlanHalls
16445
accept rate: 0%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×196
×165
×28

question asked: 07 Aug '18, 18:46

question was seen: 3,069 times

last updated: 07 Aug '18, 18:46

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum