[Geocoding] 50% of the time thrashing

[Sarah Hoffmann]

Hi,

On Mon, Nov 04, 2013 at 01:08:48PM +0000, Simon Nuttall wrote:
> Our nominatim service at:
> 
>  http://nominatim.cyclestreets.net/
> 
> has been running since July.
> 
> But according to the munin charts it seems to spend about half a day
> everyday thrashing the disk, and during that time the service is too
> slow for use in a production environment. Presumably this is doing a
> daily update?

Do you have a link to the munin graphs?

It is quite possible that postgresql and osm2pgsql are fighting for
memory during the update and that this is what is thrashing the disk.
Normally I would recommend to use smaller diffs but that is probably
not an option in this case. Try reducing the cache memory used by
osm2pgsql. If you are running Nominatim 2.1 then that is as simple as
adding --osm2pgsql-cache=500 (for 500MB cache) as a parameter for
you update.php script. If you run an older version, this needs to be
hacked into the script.

Another helpful feature in this case might be osm2pgsql's flat node 
cache. This option caches the nodes in a simple file on disk instead
of the DB which saves around 60GB (for the planet) and is normally
faster to access. This requires Nominatim 2.1 as well and unfortunately
a reimport of your database. Add the Osm2pgsql_Flatnode_File option
to your local.php settings and make it point to the file to save
the nodes to. Plan at least 20GB for it.

> I've not yet tried the auto vacuuming suggestions following my
> previous query about the growth of the use of disk space - mainly
> because I'm not familiar with the workings of Postgres.
>
> It might help if I knew which commands to run in Postgres to vacuum
> the indexes and if that may also help with the thrashing problem?

You need to run a 'vacuum analyse <table name>' on the table to which
the index belongs, 'vacuum analyse search_name' in this case.

I doubt that the growth of the indexes has much to do with it unless
you are very tight on memory. It is more likely that postgres' config
needs more tweaking.

Sarah