Hi, We have installed Nominatim 2.2.0 on centos We have uploaded\imported Netherlands Map. and initiated daily Auto updates using command
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 aseerel4c26 ♦ |
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 :
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:
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) :
answered 09 Aug '14, 14:45 Vincent de P... ♦ 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
|
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/ answered 09 Aug '14, 12:30 SimonPoole ♦ 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');
(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... ♦
|
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 ?
Hi Vincent,
[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
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:
and then ran the following commands:
Freed up a lot of space