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

I recently imported the nominatim with full planet and queries are undesirable performance. The first query, before the cache is created, takes longer than 20 seconds and sometimes I see this error:

Internal Server Error
Nominatim has encountered an internal error while accessing the database. This may happen because the database is broken or because of a bug in the software. If you think it is a bug, feel free to report it over on Github. Please include the URL that caused the problem and the complete error details below.
Message: Could not lookup place
SQL Error: DB Error: unknown error
Details:
SELECT     osm_type,    osm_id,    class,    type,    admin_level,    rank_search,    rank_address,    min(place_id) AS place_id,    min(parent_place_id) AS parent_place_id,    -1 as housenumber,    country_code,get_address_by_language(place_id,-1,ARRAY['short_name','name','brand','official_name','ref','type']) AS langaddress,    get_name_by_language(name,ARRAY['short_name','name','brand','official_name','ref','type']) AS placename,    get_name_by_language(name, ARRAY['ref']) AS ref,    avg(ST_X(centroid)) AS lon,     avg(ST_Y(centroid)) AS lat,     COALESCE(importance,0.75-(rank_search::float/40)) AS importance, (SELECT max(ai_p.importance * (ai_p.rank_address + 2))   FROM place_addressline ai_s, placex ai_p   WHERE ai_s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)     AND ai_p.place_id = ai_s.address_place_id      AND ai_s.isaddress      AND ai_p.importance is not null) AS addressimportance,    (extratags->'place') AS extra_place  FROM placex WHERE place_id in (92672355,196842012,4488269,196732908,48310082,160137,91687842,70711723,83690356,77366185,74053027,92460750,82583385,113120861,177719652,86034442,82755806,76866792,83301006,92789547)    AND (        placex.rank_address between 0 and 30     OR (extratags->'place') = 'city'       )     AND linked_place_id is null  GROUP BY      osm_type,      osm_id,      class,      type,      admin_level,      rank_search,      rank_address,      housenumber,     country_code,      importance,      langaddress,      placename,      ref,      extratags->'place'  [nativecode=ERROR:  canceling statement due to statement timeout
CONTEXT:  PL/pgSQL function get_addressdata(bigint,integer) line 103 at FOR over SELECT rows
PL/pgSQL function get_address_by_language(bigint,integer,text[]) line 12 at FOR over SELECT rows]


Due to the delay of the queries I thought the problem was the lack of some index and then I executed:

nohup ./utils/setup.php --create-search-indices --ignore-errors &

Output:

2018-08-13 20:59:56 == Create Search indices
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
ERROR:  relation "idx_word_word_id" already exists
ERROR:  relation "idx_search_name_nameaddress_vector" already exists
ERROR:  relation "idx_search_name_name_vector" already exists
ERROR:  relation "idx_search_name_centroid" already exists
ERROR:  relation "idx_place_addressline_address_place_id" already exists
ERROR:  relation "idx_placex_rank_address" already exists
ERROR:  relation "idx_placex_pendingsector" already exists
ERROR:  relation "idx_placex_parent_place_id" already exists
ERROR:  relation "idx_placex_reverse_geometry" already exists
ERROR:  relation "idx_location_area_country_place_id" already exists
ERROR:  relation "idx_osmline_parent_place_id" already exists
ERROR:  relation "idx_search_name_country_centroid" already exists
NOTICE:  index "place_id_idx" does not exist, skipping
ERROR:  relation "idx_place_osm_unique" already exists
ERROR:  relation "idx_postcode_id" already exists
ERROR:  relation "idx_postcode_postcode" already exists
Summary of warnings:


2018-08-14 02:23:14 == Setup finished.

What does this line mean? NOTICE: index "place_id_idx" does not exist, skipping


After that, I turn on the postgresql log and called the search api with these parameters:

http://nominatim.dev/nominatim/search.php?format=jsonv2&addressdetails=1&q=cancun

Logs:

2018-08-14 14:30:49 UTC [2489-1] www-data@nominatim LOG:  duration: 16092.775 ms  statement: SELECT     osm_type,    osm_id,    class,    type,    admin_level,    rank_search,    rank_address,    min(place_id) AS place_id,    min(parent_place_id) AS parent_place_id,    -1 as housenumber,    country_code,get_address_by_language(place_id,-1,ARRAY['short_name:en-en','name:en-en','short_name:en','name:en','short_name','name','brand','official_name:en-en','official_name:en','official_name','ref','type']) AS langaddress,    get_name_by_language(name,ARRAY['short_name:en-en','name:en-en','short_name:en','name:en','short_name','name','brand','official_name:en-en','official_name:en','official_name','ref','type']) AS placename,    get_name_by_language(name, ARRAY['ref']) AS ref,    avg(ST_X(centroid)) AS lon,     avg(ST_Y(centroid)) AS lat,     COALESCE(importance,0.75-(rank_search::float/40)) AS importance, (SELECT max(ai_p.importance * (ai_p.rank_address + 2))   FROM place_addressline ai_s, placex ai_p   WHERE ai_s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)     AND ai_p.place_id = ai_s.address_place_id      AND ai_s.isaddress      AND ai_p.importance is not null) AS addressimportance,    (extratags->'place') AS extra_place  FROM placex WHERE place_id in (197441826,111603036,182595459,139694930,144830394,118512715,74049435,99932703,159450111,141912308,112119380,80303695,80305679,112647794,80522782,115827503,128389572,149585949,74675442,150225528)    AND (        placex.rank_address between 0 and 30     OR (extratags->'place') = 'city'       )     AND linked_place_id is null  GROUP BY      osm_type,      osm_id,      class,      type,      admin_level,      rank_search,      rank_address,      housenumber,     country_code,      importance,      langaddress,      placename,      ref,      extratags->'place'

2018-08-14 14:31:35 UTC [2490-1] ERROR:  function transliteration(text) does not exist at character 23
2018-08-14 14:31:35 UTC [2490-2] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2018-08-14 14:31:35 UTC [2490-3] QUERY:  SELECT gettokenstring(transliteration(name))
2018-08-14 14:31:35 UTC [2490-4] CONTEXT:  PL/pgSQL function public.make_standard_name(text) line 5 at assignment
    automatic analyze of table "nominatim.public.placex"

Looking at the logs I thought some functions might be missing, and execute this:

nohup ./utils/setup.php --create-functions --ignore-errors &

2018-08-14 14:48:04 == Create Functions
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
NOTICE:  drop cascades to function get_addressdata(bigint,integer)
NOTICE:  drop cascades to function get_wikipedia_match(hstore,character varying)
Summary of warnings:


2018-08-14 14:48:04 == Setup finished.


Some info about my installation:

Nominatim version 3.1.0

Ec2 t2.2xlarge with T2 Unlimited | vCPU 8 | 32GB RAM | SSD 870 GB gp2 2610/3000 IOPS

 Postgresql configurations:
- shared_buffers = 2GB
- maintenance_work_mem = 10GB
- work_mem = 50MB
- effective_cache_size = 24GB
- synchronous_commit = off
- checkpoint_timeout = 10min
- checkpoint_completion_target = 0.9
- fsync = on
- full_page_writes = on
- random_page_cost = 1.1
- wal_buffers = 16MB
- max_worker_processes = 8

df -h

Filesystem      Size  Used Avail Use% Mounted on
udev             16G     0   16G   0% /dev
tmpfs           3.2G  9.5M  3.2G   1% /run
/dev/xvda1      844G  674G  170G  80% /
tmpfs            16G  4.0K   16G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs            16G     0   16G   0% /sys/fs/cgroup
/dev/loop0       13M   13M     0 100% /snap/amazon-ssm-agent/295
/dev/loop1       87M   87M     0 100% /snap/core/4917
/dev/loop2       87M   87M     0 100% /snap/core/4830
/dev/loop3       87M   87M     0 100% /snap/core/5145
tmpfs           3.2G     0  3.2G   0% /run/user/1000

free -h 

              total        used        free      shared  buff/cache   available
Mem:            31G        171M         29G        115M        1.5G         30G
Swap:            0B          0B          0B

nominatim=# \d search_name
                Table "public.search_name"
       Column       |          Type           | Modifiers
--------------------+-------------------------+-----------
 place_id           | bigint                  |
 importance         | double precision        |
 search_rank        | smallint                |
 address_rank       | smallint                |
 name_vector        | integer[]               |
 nameaddress_vector | integer[]               |
 country_code       | character varying(2)    |
 centroid           | geometry(Geometry,4326) |
Indexes:
    "idx_search_name_centroid" gist (centroid)
    "idx_search_name_name_vector" gin (name_vector) WITH (fastupdate=off)
    "idx_search_name_nameaddress_vector" gin (nameaddress_vector) WITH (fastupdate=off)
    "idx_search_name_place_id" btree (place_id)

nominatim=# \d placex
                   Table "public.placex"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
 place_id        | bigint                      | not null
 parent_place_id | bigint                      |
 linked_place_id | bigint                      |
 importance      | double precision            |
 indexed_date    | timestamp without time zone |
 geometry_sector | integer                     |
 rank_address    | smallint                    |
 rank_search     | smallint                    |
 partition       | smallint                    |
 indexed_status  | smallint                    |
 osm_id          | bigint                      | not null
 osm_type        | character(1)                | not null
 class           | text                        | not null
 type            | text                        | not null
 name            | hstore                      |
 admin_level     | smallint                    |
 address         | hstore                      |
 extratags       | hstore                      |
 geometry        | geometry(Geometry,4326)     | not null
 wikipedia       | text                        |
 country_code    | character varying(2)        |
 housenumber     | text                        |
 postcode        | text                        |
 centroid        | geometry(Geometry,4326)     |
Indexes:
    "idx_place_id" UNIQUE, btree (place_id)
    "idx_placex_adminname" btree (make_standard_name(name -> 'name'::text), rank_search) WHERE osm_type = 'N'::bpchar AND rank_search < 26
    "idx_placex_geometry" gist (geometry)
    "idx_placex_linked_place_id" btree (linked_place_id) WHERE linked_place_id IS NOT NULL
    "idx_placex_osmid" btree (osm_type, osm_id)
    "idx_placex_parent_place_id" btree (parent_place_id) WHERE parent_place_id IS NOT NULL
    "idx_placex_pendingsector" btree (rank_search, geometry_sector) WHERE indexed_status > 0
    "idx_placex_rank_address" btree (rank_address)
    "idx_placex_rank_search" btree (rank_search)
    "idx_placex_reverse_geometry" gist (geometry) WHERE rank_search <> 28 AND (name IS NOT NULL OR housenumber IS NOT NULL) AND (class <> ALL (ARRAY['waterway'::text, 'railway'::text, 'tunnel'::text, 'bridge'::text, 'man_made'::text]))
Triggers:
    placex_before_delete AFTER DELETE ON placex FOR EACH ROW EXECUTE PROCEDURE placex_delete()
    placex_before_insert BEFORE INSERT ON placex FOR EACH ROW EXECUTE PROCEDURE placex_insert()
    placex_before_update BEFORE UPDATE ON placex FOR EACH ROW EXECUTE PROCEDURE placex_update()


I did not come up with a solution with no command executed

What else can I do to try to solve this?

asked 14 Aug '18, 18:43

gnosis7's gravatar image

gnosis7
19336
accept rate: 0%

Running the ./utils/setup.php --index command seems to stuck in:

Starting rank 4 Done 0 in 0 @ 0.000000 per second - FINISHED

(14 Aug '18, 18:49) gnosis7

Overall the output and setup looks good, nothing catches my eye immediately.

You can ignore the "function transliteration(text) does not exist" warning. That happens during postgresql vacuum process and not user query. The function does exists because it's used during data import and every/most user queries. More details in https://github.com/openstreetmap/Nominatim/issues/1097

Did the initial import happen with the Nominatim-3.1 release (https://www.nominatim.org/release-history/) or did you upgrade the software in the meantime? Is the current installed version 3.1 or latest master? I'm not recommending a version, it just helps to figure out which indices should exist because that keeps changing.

Try running test queries with 'EXPLAIN', see a similar discussion at https://github.com/openstreetmap/Nominatim/issues/1023#issuecomment-384441294

It might be easier to move the discussion to https://github.com/openstreetmap/Nominatim/issues/

permanent link

answered 14 Aug '18, 21:24

mtmail's gravatar image

mtmail
4.8k1574
accept rate: 27%

Mtmail, thanks for the help! My installation comes from this link: https://nominatim.org/release/Nominatim-3.1.0.tar.bz2 Regarding the tests of queries, I'll send in github.

(14 Aug '18, 22:09) gnosis7

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
×193
×147
×33
×28

question asked: 14 Aug '18, 18:43

question was seen: 4,270 times

last updated: 15 Aug '18, 00:21

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