Reference update times of photon from Nominatim

[hannesaddec]

We observe larger update times for 2 languages only as we try to process all EU langauges than reported in the documentation:

Setup: Nominatim version,3.5.1, machine size: 4 cores, 28 Gb RAM), SSD DISK, global extent
Setup: Photon version, 0.3.4, , machine size: 4 cores 14 Gb. RAM). SSD DISK
Command: java -jar /opt/data/photon/photon-0.3.4.jar -nominatim-import -host 10.0.1.17 -port 5432 -database nominatim -user nominatim -password XXXXXX -languages en,es
However this lead to only 25% CPU load with 200 documents/sec processed over 3 days and producing a 26GB index. JAVA/Nominatim/Photon uses only on thread each. We never reached/peaked at RAM/CPU.

Any further advice besides to upgrade Nominatim to 3.6 and photon 0.3.4 ?

[lonvia]

Make sure you have tuned Postgresql as per https://nominatim.org/release-docs/latest/admin/Installation/#tuning-the-postgresql-database. How is the load on the Nominatim machine?

[alfmarcua]

We have tried in a single machine with 4 cores, 48GB RAM, SDD DISK, global extent and results were almost the same, 90 hours which is more than 3 days. Load on nominatim database was also low. Java process took 1 core and it was not using all the RAM available by far. Java process take more than 40% of the total time which is about 36 hours. Hence we are not sure how could we speed up the process in order to take less than those 36 hours. Could have we miss some Java additional configuration?

[lonvia]

What does the following SQL EXPLAIN say in your Nominatim database:

EXPLAIN SELECT * FROM placex p, place_addressline pa
WHERE p.place_id = pa.address_place_id and pa.place_id = 12345678
and pa.cached_rank_address > 4 and pa.address_place_id != 12345678 and pa.isaddress
ORDER BY rank_address desc, fromarea desc, distance asc, rank_search desc;

[alfmarcua]

Those queries, although tiny (1 ms on average), take the most of the time (about 38% of the total time).

Gather Merge  (cost=12552.31..12622.92 rows=614 width=549)
  Workers Planned: 1
  ->  Sort  (cost=11552.30..11553.83 rows=614 width=549)
        Sort Key: p.rank_address DESC, pa.fromarea DESC, pa.distance, p.rank_search DESC
        ->  Nested Loop  (cost=45.16..11523.86 rows=614 width=549)
              ->  Parallel Bitmap Heap Scan on place_addressline pa  (cost=44.59..6251.14 rows=614 width=28)
                    Recheck Cond: (place_id = 12345678)
                    Filter: (isaddress AND (cached_rank_address > 4) AND (address_place_id <> 12345678))
                    ->  Bitmap Index Scan on idx_place_addressline_place_id  (cost=0.00..44.33 rows=1567 width=0)
                          Index Cond: (place_id = 12345678)
              ->  Index Scan using idx_place_id on placex p  (cost=0.57..8.59 rows=1 width=521)
                    Index Cond: (place_id = pa.address_place_id)

[alfmarcua]

Query analysis

Here it is a query analysis of an interval of 93.5 minutes during an import process.

Data logging

Time to start recording all queries: 2021-03-06 21:37:25

Time to finish recording all queries: 2021-03-06 23:10:55

Time elapsed: 93.5 minutes

During that time 12214704 queries have been recorded

Data summary

## `summarise()` has grouped output by 'operation'. You can override using the `.groups` argument.

operation	sqlKeyword	avg	totalSecs	totalMins	totalHours	totalPct
execute fetch	SELECT	1552.2419231	20.179145	0.3363191	0.0056053	0.36
execute	SELECT	1.0192581	1778.564639	29.6427440	0.4940457	31.70
execute	ROLLBACK	0.0175172	30.566667	0.5094444	0.0084907	0.54
execute	BEGIN	0.0032470	5.665857	0.0944309	0.0015738	0.10
bind	SELECT	0.2099698	366.387825	6.1064638	0.1017744	6.53
bind	ROLLBACK	0.0046953	8.193050	0.1365508	0.0022758	0.15
bind	BEGIN	0.0065285	11.391967	0.1898661	0.0031644	0.20
bind	NA	0.0067176	11.721876	0.1953646	0.0032561	0.21

Time by SQL operation:

operation	avg	total
execute fetch	1552.2419231	0.3363191
execute	0.3466747	30.2466194
bind	0.0569778	6.6282453

Time by QUERY

sqlClause	nOcurr	totalSecs	totalPct
BEGIN	1744955	17.05782	0.30
ROLLBACK	1744955	38.75972	0.69
SELECT p.place_id, p.name, p.class, p.type, p.rank_address FROM placex p WHERE p.place_id = $1	606172	407.82855	7.27
SELECT p.place_id, p.name, p.class, p.type, p.rank_address FROM placex p, place_addressline pa WHERE p.place_id = pa.address_place_id and pa.place_id = $1 and pa.cached_rank_address > 4 and pa.address_place_id != $2 and pa.isaddress order by rank_address desc,fromarea desc, distance asc,rank_search desc	1138786	1737.12391	30.96
SELECT place_id, osm_type, osm_id, class, type, name, housenumber, postcode, address, extratags, ST_Envelope(geometry) AS bbox, parent_place_id, linked_place_id, rank_address, rank_search, importance, country_code, centroid FROM placex WHERE linked_place_id IS NULL AND centroid IS NOT NULL ORDER BY geometry_sector	6	20.17914	0.36
NA	872478	11.72188	0.21

[lonvia]

Looks like the statistics are off. It shouldn't be doing a heap scan on place_addressline. This is what the explain looks like for me:

 Sort  (cost=6452.10..6453.33 rows=494 width=567)
   Sort Key: p.rank_address DESC, pa.fromarea DESC, pa.distance, p.rank_search DESC
   ->  Nested Loop  (cost=1.14..6430.00 rows=494 width=567)
         ->  Index Scan using idx_place_addressline_place_id on place_addressline pa  (cost=0.57..2187.77 rows=494 width=28)
               Index Cond: (place_id = 12345678)
               Filter: (isaddress AND (cached_rank_address > 4) AND (address_place_id <> 12345678))
         ->  Index Scan using idx_place_id on placex p  (cost=0.57..8.59 rows=1 width=539)
               Index Cond: (place_id = pa.address_place_id)

Try running a VACUUM ANALYSE on place_addressline. If that doesn't help set random_page_cost = 1.0 in the postgresql configuration.

[enriquezubiria]

@lonvia , please keep in mind that the numbers about average and total query time that my colleague Alfonso has sent to you show only a limited window of time while we have had enabled full logging on our database.

[enriquezubiria]

Sarah, thanks for your help. That is exactly what we were doing. In fact, Alfonso has run the EXPLAIN while a VACUUM ANALYZE was being run. I'll rerun the EXPLAIN when the vacuum command finishes and I'll send you the results. Thanks again, Enrique Z. El mar., 9 mar. 2021 20:17, Sarah Hoffmann ***@***.***> escribió:

…

Looks like the statistics are off. It shouldn't be doing a heap scan on place_addressline. This is what the explain looks like for me: Sort (cost=6452.10..6453.33 rows=494 width=567) Sort Key: p.rank_address DESC, pa.fromarea DESC, pa.distance, p.rank_search DESC -> Nested Loop (cost=1.14..6430.00 rows=494 width=567) -> Index Scan using idx_place_addressline_place_id on place_addressline pa (cost=0.57..2187.77 rows=494 width=28) Index Cond: (place_id = 12345678) Filter: (isaddress AND (cached_rank_address > 4) AND (address_place_id <> 12345678)) -> Index Scan using idx_place_id on placex p (cost=0.57..8.59 rows=1 width=539) Index Cond: (place_id = pa.address_place_id) Try running a VACUUM ANALYSE on place_addressline. If that doesn't help set random_page_cost = 1.0 in the postgresql configuration. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#534 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALB2CIIST6LUEFLUYAPTGVLTCZX4FANCNFSM4WVGLFBQ> .

[lonvia]

No, they are not correlated. What is likely correlated is the distribution of the number of rows per place_id and is_address. And what is definitely correlated is the number of rows per address_place_id and cached_rank_address.

[otbutz]

We could check if a planer hint would improve the estimation: https://www.postgresql.org/docs/10/sql-createstatistics.html (available since PostgreSQL 10)

[lonvia]

We'd have to confirm first that the query planner and that particular query is indeed the root cause of the slow export.

[alfmarcua]

According to the information we were handling with @hannesaddec, the estimated time for uploading nominatim to photon was about 19 hours for a planet export with a 6-core machine with 64GB RAM and SSD, while you have obtained about 7-8 hours in the last migration, see #547.

Is there any misunderstanding from our side in this information?

[alfmarcua]

I'm out of ideas. I'd need to see the setup for myself.

Thanks a lot for your kind support, Sarah.

We have deployed a specific environment which is prepared to check the setup in case it is possible for you, as we are still getting long times for the export (even we are using the latest version which also includes a remarkable performance improvement #547).

If you see this appropriate, we can provide you with more details to access this environment.