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

Hi,

We have installed Nominatim 2.2.0 on centos

We have uploaded\imported Netherlands Map.

and initiated daily Auto updates using command

"/utils/update.php --import-osmosis-all --no-npi"

Now, every day Postgres database size growing rapidly , even though only imported NL maps.

How can I decrease or limit the size of Nominatim database to not growing gradually.

Thanks

asked 05 Aug '14, 14:29

PrakashThakor's gravatar image

PrakashThakor
6335
accept rate: 0%

edited 05 Aug '14, 14:45

aseerel4c26's gravatar image

aseerel4c26 ♦
32.6k18248554

What version of PG is this ? Did you leave the autovacuum settings at their default ? Does check_postgres.pl --action=bloat --db=nominatim show a lot of bloat ?

(05 Aug '14, 17:39) Vincent de P... ♦

Hi Vincent,

  1. I have PGSQL 9.3
  2. Yes , I did not change in autovaccum settings and its default.
  3. "check_postgres.pl --action=bloat --db=nominatim" shown following result

[root@nominatim-ams check_postgres-2.21.0]# ./check_postgres.pl --action=bloat -db=nominatim POSTGRES_BLOAT CRITICAL: DB "nominatim" (db nominatim) index planet_osm_ways_nodes rows:? pages:1737024 shouldbe:502394 (3.5X) wasted bytes:10114088960 (9 GB) | planet_osm_ways_nodes=10114088960B public.place_addressline=1059995648B idx_search_name_nameaddress_vector=1001832448B idx_placex_pendingsector=114614272B public.search_name=110084096B public.location_area_large_117=45719552B public.placex=7045120B idx_location_area_large_117_geometry=1089536B idx_location_area_large_117_place_id=0B idx_place_addressline_address_place_id=0B idx_place_addressline_place_id=0B idx_search_name_centroid=0B idx_search_name_name_vector=0B idx_search_name_place_id=0B public.planet_osm_ways=0B

(09 Aug '14, 11:07) PrakashThakor

I was able to reduce the bloating by reindexing the affected tables, first I ran above check_postgres script to find out which tables/indexes exactly where affected:

  • POSTGRES_BLOAT CRITICAL: DB "nominatim" (db nominatim) index idx_search_name_nameaddress_vector rows:? pages:5571228 shouldbe:1788574 (3.1X) wasted bytes:30987501568 (28 GB) * (db nominatim) table public.place_addressline rows:299672640 pages:3052511 shouldbe:2200240 (1.4X) wasted size:6981804032 (6 GB) | [...]

and then ran the following commands:

  • psql nominatim -c "reindex index idx_search_name_nameaddress_vector;"
  • psql nominatim -c "reindex table public.place_addressline;"

Freed up a lot of space

(29 Jul '16, 12:34) jot

Are you updating from the global minutely diffs? If yes, while I don't know the exact workings of nominatim, this will cause additional objects to be added outside of the area of your original extract.

There are numerous ways around this problem, one is to consume diffs that are specific to your region as, for example, provided by GeoFabrik http://download.geofabrik.de/europe/netherlands-updates/

permanent link

answered 09 Aug '14, 12:30

SimonPoole's gravatar image

SimonPoole ♦
44.7k13326701
accept rate: 18%

edited 09 Aug '14, 12:31

Hi SimonPools,

Mostly I kept default setting of Nominatim , should I change replication URL to http://download.geofabrik.de/europe/netherlands-updates/

//Replication settings

@define('CONST_Replication_Url', 'http://planet.openstreetmap.org/replication/minute');

    @define('CONST_Replication_MaxInterval', '40000');

    @define('CONST_Replication_Update_Interval', '172800');  // How often upstream publishes diffs

    @define('CONST_Replication_Recheck_Interval', '900'); // How long to sleep if no update found yet
(09 Aug '14, 12:52) PrakashThakor
1

Yes, you should change the URL. You might also want to redo your import from scratch to get rid of the unwanted world data.

(09 Aug '14, 14:31) Vincent de P... ♦

Hi Vincent,

But I did initial import of only Netherland maps using netherlands-latest.osm.pbf and not the entire planet maps , AS we only required Netherlands Maps.

Do I need to still import from scratch.

(09 Aug '14, 14:38) PrakashThakor
1

Consuming worldwide diffs adds worldwide data to your db, regardless of what region the initial db contained. That's certainly what causes the rapid db growth.

You don't have to remove that data, but it's probably a good idea.

(09 Aug '14, 14:49) Vincent de P... ♦

As SimonPoole says, making sure you get only the local diffs is the most important step. That said, it's strange that you got all the way to a 9G/3.5x bloat. Check that autovacuum is working as expected :

psql nominatim -c "select relname,last_autovacuum from pg_stat_user_tables;"

You should see recent-ish dates for your updated tables. Check your logs for errors if not.

It's often a good idea to set your vacuuming more aggressively in postgresql.conf:

autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 5000
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 10ms

Also, at any time you can issue a manual full vacuum to regain space (note that some % of bloat is normal, and that doing a "full" vacuum will block other db queries) :

vacuumdb -fz nominatim
permanent link

answered 09 Aug '14, 14:45

Vincent%20de%20Phily's gravatar image

Vincent de P... ♦
17.3k18152249
accept rate: 19%

Hi Vincent I just ran query to find most space consuming tables on Nominatim db.

and found tables consumes most of the space in Indexing

for example plane_osm_ways occupies 14 GB of index


Table Name ; Table_size ; Indexs_size, Total Size


""public"."planet_osm_ways"";"4914 MB";"14 GB";"19 GB"

""public"."planet_osm_nodes"";"10 GB";"5773 MB";"16 GB"

""public"."placex"";"5609 MB";"10 GB";"16 GB"

""public"."place"";"5416 MB";"2152 MB";"7568 MB"

""public"."place_addressline"";"3687 MB";"3512 MB";"7200 MB"

""public"."new_query_log"";"6537 MB";"341 MB";"6879 MB"

(09 Aug '14, 15:03) PrakashThakor

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:

×689
×118
×30

question asked: 05 Aug '14, 14:29

question was seen: 7,571 times

last updated: 29 Jul '16, 12:36

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