Hello, I started data import from a planet file into Nominatim 3.1.0 and Postgres log contains the lines that follow. There were no deadlocks like this for importing via 2.5.1 and also at the end of the import DB size for 2.5.1 is larger by a few tens of gigabytes. Import run as: sudo -u nominatim ./utils/setup.php --osm-file /mnt/ephemeral/planet-latest.osm.pbf --all --osm2pgsql-cache 28000 Also I can see there are multiple threads for import process (several postgress connections) that I'd assume cause the deadlocks. So my questions are:
Postgres log: 2018-01-29 22:27:21 UTC [15707-73] LOG: checkpoints are occurring too frequently (29 seconds apart) 2018-01-29 22:27:21 UTC [15707-74] HINT: Consider increasing the configuration parameter "max_wal_size". Interrupt requested 2018-01-29 22:30:02 UTC [18513-1] ERROR: canceling autovacuum task 2018-01-29 22:30:02 UTC [18513-2] CONTEXT: automatic analyze of table "nominatim.public.placex" 2018-01-29 23:04:13 UTC [18699-1] nominatim@nominatim ERROR: deadlock detected 2018-01-29 23:04:13 UTC [18699-2] nominatim@nominatim DETAIL: Process 18699 waits for ShareLock on transaction 185788759; blocked by process 18698. Process 18698 waits for ShareLock on transaction 185788763; blocked by process 18699. Process 18699: update placex set indexed_status = 0 where place_id = $1 Process 18698: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:13 UTC [18699-3] nominatim@nominatim HINT: See server log for query details. 2018-01-29 23:04:13 UTC [18699-4] nominatim@nominatim CONTEXT: while locking tuple (3588611,16) in relation "placex" SQL statement "UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id" PL/pgSQL function placex_update() line 142 at SQL statement 2018-01-29 23:04:13 UTC [18699-5] nominatim@nominatim STATEMENT: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:14 UTC [18702-1] nominatim@nominatim ERROR: deadlock detected 2018-01-29 23:04:14 UTC [18702-2] nominatim@nominatim DETAIL: Process 18702 waits for ShareLock on transaction 185788759; blocked by process 18698. Process 18698 waits for ShareLock on transaction 185788758; blocked by process 18702. Process 18702: update placex set indexed_status = 0 where place_id = $1 Process 18698: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:14 UTC [18702-3] nominatim@nominatim HINT: See server log for query details. 2018-01-29 23:04:14 UTC [18702-4] nominatim@nominatim CONTEXT: while locking tuple (3588611,16) in relation "placex" SQL statement "UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id" PL/pgSQL function placex_update() line 142 at SQL statement 2018-01-29 23:04:14 UTC [18702-5] nominatim@nominatim STATEMENT: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:23 UTC [18696-1] nominatim@nominatim ERROR: deadlock detected 2018-01-29 23:04:23 UTC [18696-2] nominatim@nominatim DETAIL: Process 18696 waits for ShareLock on transaction 185789325; blocked by process 18701. Process 18701 waits for ShareLock on transaction 185789329; blocked by process 18696. Process 18696: update placex set indexed_status = 0 where place_id = $1 Process 18701: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:23 UTC [18696-3] nominatim@nominatim HINT: See server log for query details. 2018-01-29 23:04:23 UTC [18696-4] nominatim@nominatim CONTEXT: while locking tuple (1561131,10) in relation "placex" SQL statement "UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id" PL/pgSQL function placex_update() line 142 at SQL statement 2018-01-29 23:04:23 UTC [18696-5] nominatim@nominatim STATEMENT: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:50 UTC [18697-1] nominatim@nominatim ERROR: deadlock detected 2018-01-29 23:04:50 UTC [18697-2] nominatim@nominatim DETAIL: Process 18697 waits for ShareLock on transaction 185794891; blocked by process 18701. Process 18701 waits for ShareLock on transaction 185794892; blocked by process 18697. Process 18697: update placex set indexed_status = 0 where place_id = $1 Process 18701: update placex set indexed_status = 0 where place_id = $1 2018-01-29 23:04:50 UTC [18697-3] nominatim@nominatim HINT: See server log for query details. 2018-01-29 23:04:50 UTC [18697-4] nominatim@nominatim CONTEXT: while locking tuple (4485156,18) in relation "placex" SQL statement "UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id" PL/pgSQL function placex_update() line 142 at SQL statement asked 30 Jan '18, 12:48 Taras O |
During import Nominatim detects deadlocks (before it used to crash). It's possible the detection logic simply got better. If I remember correctly it does string matching. Check Nominatim 3.1 puts postcodes in different tables. https://www.openstreetmap.org/user/lonvia/diary/43143 On a planet database 10GB is less than 5% difference and expected. answered 30 Jan '18, 13:23 mtmail |