[OSM-dev] osm2pgsql slow on update import

Sven Geggus lists at fuchsschwanzdomain.de
Thu Apr 28 10:55:25 BST 2011


Markus Wagner <markus at mwagner.info> wrote:

> After vacuuming and fiddling with those values I am much closer to
> realtime. Currently at approx ~120% of realtime. So I think, there is
> hope, once I get faster disks.

I'm reopening this thread because I have a very simular Problem and I
suspect that this could be an issue of the particular Software versions
and/or psql configuration.

I'm using Debian squeeze (64 Bit) with postgresql 9.0.3/postgis 1.5.2
(homebrew backport) and I've got a Problem with very slow updates as well.

Disk speed can not be the Problem as I'm using a 2-Disk lvm on SSD here.

The initial import is very fast (with pbf) and still fast (with .osm.bz2).
It is just updates which are very slow here.

Two differences to the standard setup are a hstore column and that my
database is using lat/long instead of sperical mercator.

osm2pgsql is a very recent version from svn (25689) including a few non
invasive minor changes to the hstore support which I added myself. Of course
these can not be the cause of the problem ;)

The Machine I'm using is quite fast (8 cores) and should have enough RAM
(32GB).

Here is what postgresql.conf looks like:

--cut--
data_directory = '/var/lib/postgresql/9.0/main'
hba_file = '/etc/postgresql/9.0/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.0/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.0-main.pid'
listen_addresses = '*'
port = 5432
max_connections = 80
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 7680MB
work_mem = 192MB
maintenance_work_mem = 1GB
fsync = off
synchronous_commit = off
wal_buffers = 8MB
checkpoint_segments = 16
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
default_statistics_target = 50
constraint_exclusion = on
log_line_prefix = '%t '
autovacuum = off
datestyle = 'iso, dmy'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'C'
lc_time = 'de_DE.UTF-8'
default_text_search_config = 'pg_catalog.german'
--cut--

I still suspect that there is some setting here which I should change to
speed this up. For some strange reason it would currently be fastest to use
complete reimports of the planet rather that updates :(

Regards

Sven

-- 
"We don't know the OS that God uses, but the Vatican uses Linux"
                               (Sister Judith Zoebelein, Vatican Webmaster)

/me is giggls at ircnet, http://sven.gegg.us/ on the Web



More information about the dev mailing list