Nominatim PgSQL database size growing gradually

Question

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

Answer 1

4

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 ♦
44.7k●13●326●701
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... ♦

Answer 2

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

Nominatim PgSQL database size growing gradually

Follow this question

Related questions