[MASSMAIL] Please recommend postgresql.conf improvements for osm2pgsql loading Europe - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | [MASSMAIL] Please recommend postgresql.conf improvements for osm2pgsql loading Europe |
| Date | |
| Msg-id | CAADeyWibmmfvBhJS2P2mPUOLx57WJ0h+qM5xBQPqFehh-Sna4g@mail.gmail.com Whole thread Raw |
| Responses |
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
|
| List | pgsql-general |
Good evening,
I use the following postgresql.conf in my Dockerfile
( the full version at https://stackoverflow.com/a/78243530/165071 ),
when loading a 28 GByte large europe-latest.osm.pbf
into PostgreSQL 16 with PostGIS extension:
echo "shared_buffers = 1GB" >> $PGDATA/postgresql.conf && \
echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \
echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \
echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \
echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \
echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \
echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \
echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \
echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \
echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \
echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \
And after/during osm2pgsql is loading the data into the database,
And after/during osm2pgsql is loading the data into the database,
the following messages are displayed by PostgreSQL:
2024-03-29T14:50:19.6909027Z 2024-03-29 14:50:19 Processed 3355283586 nodes in 432s (7m 12s) - 7767k/s
2024-03-29T14:50:19.6909478Z 2024-03-29 14:50:19 Processed 406646166 ways in 2720s (45m 20s) - 150k/s
2024-03-29T14:50:19.6909785Z 2024-03-29 14:50:19 Processed 7265827 relations in 672s (11m 12s) - 11k/s
2024-03-29T14:50:20.0292733Z [0m [91m2024-03-29 14:50:20 node cache: stored: 3355283586(100.00%), storage efficiency: 56.37% (dense blocks: 268264, sparse nodes: 1877553733), hit rate: 100.00%
2024-03-29T14:50:20.0293430Z 2024-03-29 14:50:20 Clustering table 'planet_osm_line' by geometry...
2024-03-29T14:50:20.0293787Z 2024-03-29 14:50:20 Clustering table 'planet_osm_point' by geometry...
2024-03-29T14:50:20.1056438Z [0m [91m2024-03-29 14:50:20 Clustering table 'planet_osm_polygon' by geometry...
2024-03-29T14:50:20.1056819Z 2024-03-29 14:50:20 Clustering table 'planet_osm_roads' by geometry...
2024-03-29T15:15:27.3834184Z [0m [91m2024-03-29 15:15:24 Creating geometry index on table 'planet_osm_roads'...
2024-03-29T15:15:56.7850293Z [0m2024-03-29 15:15:56.784 UTC [344] LOG: skipping vacuum of "planet_osm_roads" --- lock not available
2024-03-29T15:16:56.7864755Z 2024-03-29 15:16:56.785 UTC [347] LOG: skipping vacuum of "planet_osm_roads" --- lock not available
2024-03-29T15:17:56.7891503Z 2024-03-29 15:17:56.788 UTC [350] LOG: skipping vacuum of "planet_osm_roads" --- lock not available
2024-03-29T15:18:16.5830638Z [91m2024-03-29 15:18:16 Analyzing table 'planet_osm_roads'...
2024-03-29T15:18:18.1485666Z [0m [91m2024-03-29 15:18:18 All postprocessing on table 'planet_osm_roads' done in 1678s (27m 58s).
2024-03-29T15:22:17.1463669Z [0m [91m2024-03-29 15:22:17 Creating geometry index on table 'planet_osm_point'...
2024-03-29T15:22:56.8860848Z [0m2024-03-29 15:22:56.885 UTC [365] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:23:56.8890669Z 2024-03-29 15:23:56.888 UTC [368] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:24:56.9521355Z 2024-03-29 15:24:56.951 UTC [371] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:25:56.9166127Z 2024-03-29 15:25:56.915 UTC [374] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:26:57.4606148Z 2024-03-29 15:26:57.460 UTC [377] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:27:56.9555832Z 2024-03-29 15:27:56.955 UTC [380] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:28:56.9718398Z 2024-03-29 15:28:56.971 UTC [383] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:29:56.9912768Z 2024-03-29 15:29:56.990 UTC [386] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:30:57.1482580Z 2024-03-29 15:30:57.147 UTC [389] LOG: skipping vacuum of "planet_osm_point" --- lock not available
2024-03-29T15:31:01.2674499Z [91m2024-03-29 15:31:01 Analyzing table 'planet_osm_point'...
2024-03-29T15:31:02.3310950Z [0m [91m2024-03-29 15:31:02 All postprocessing on table 'planet_osm_point' done in 2442s (40m 42s).
2024-03-29T15:41:13.1544481Z [0m [91m2024-03-29 15:41:13 Creating geometry index on table 'planet_osm_line'...
2024-03-29T15:41:57.2434689Z [0m2024-03-29 15:41:57.242 UTC [422] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:42:57.2519273Z 2024-03-29 15:42:57.251 UTC [425] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:43:57.2626514Z 2024-03-29 15:43:57.262 UTC [428] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:44:57.2531401Z 2024-03-29 15:44:57.252 UTC [431] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:45:57.2515330Z 2024-03-29 15:45:57.251 UTC [434] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:46:35.3182906Z 2024-03-29 15:46:35.317 UTC [28] LOG: checkpoint starting: time
2024-03-29T15:46:57.2509511Z 2024-03-29 15:46:57.250 UTC [437] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:47:58.3610817Z 2024-03-29 15:47:58.360 UTC [440] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:48:57.2897759Z 2024-03-29 15:48:57.289 UTC [443] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:49:57.9056709Z 2024-03-29 15:49:57.905 UTC [446] LOG: skipping vacuum of "planet_osm_line" --- lock not available
2024-03-29T15:49:59.6678449Z [91m2024-03-29 15:49:59 Analyzing table 'planet_osm_line'...
2024-03-29T15:50:09.6412981Z [0m [91m2024-03-29 15:50:09 All postprocessing on table 'planet_osm_line' done in 3589s (59m 49s).
2024-03-29T15:54:38.1250032Z [0m [91m2024-03-29 15:54:38 Creating geometry index on table 'planet_osm_polygon'...
2024-03-29T15:54:57.5225406Z [0m2024-03-29 15:54:57.522 UTC [461] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T15:55:57.4751735Z 2024-03-29 15:55:57.474 UTC [464] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T15:56:57.4952251Z 2024-03-29 15:56:57.494 UTC [467] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T15:57:57.6161474Z 2024-03-29 15:57:57.615 UTC [470] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T15:58:57.5342965Z 2024-03-29 15:58:57.533 UTC [473] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T15:59:57.5535374Z 2024-03-29 15:59:57.553 UTC [476] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:00:57.5727969Z 2024-03-29 16:00:57.572 UTC [479] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:01:57.5920798Z 2024-03-29 16:01:57.591 UTC [482] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:02:18.0375710Z 2024-03-29 16:02:18.036 UTC [449] LOG: automatic vacuum of table "osm_database.public.planet_osm_line": index scans: 0
2024-03-29T16:02:18.0376138Z pages: 0 removed, 4766720 remain, 4766720 scanned (100.00% of total)
2024-03-29T16:02:18.0376345Z tuples: 0 removed, 118178562 remain, 0 are dead but not yet removable
2024-03-29T16:02:18.0378017Z removable cutoff: 18260, which was 20 XIDs old when operation ended
2024-03-29T16:02:18.0378243Z frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
2024-03-29T16:02:18.0378408Z index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
2024-03-29T16:02:18.0382989Z index "planet_osm_line_way_idx": pages: 577427 in total, 0 newly deleted, 0 currently deleted, 0 reusable
2024-03-29T16:02:18.0383168Z avg read rate: 61.347 MB/s, avg write rate: 0.013 MB/s
2024-03-29T16:02:18.0383306Z buffer usage: 4766334 hits, 5344899 misses, 1175 dirtied
2024-03-29T16:02:18.0383451Z WAL usage: 2 records, 0 full page images, 417 bytes
2024-03-29T16:02:18.0383600Z system usage: CPU: user: 10.94 s, system: 19.27 s, elapsed: 680.67 s
2024-03-29T16:02:57.6121504Z 2024-03-29 16:02:57.611 UTC [485] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:03:57.8261200Z 2024-03-29 16:03:57.825 UTC [488] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:04:57.6506943Z 2024-03-29 16:04:57.650 UTC [491] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:05:57.6702697Z 2024-03-29 16:05:57.669 UTC [494] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:06:57.6731793Z 2024-03-29 16:06:57.672 UTC [497] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:07:57.6929376Z 2024-03-29 16:07:57.692 UTC [500] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:08:57.7118933Z 2024-03-29 16:08:57.711 UTC [503] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:09:27.9176268Z 2024-03-29 16:09:27.917 UTC [461] LOG: automatic vacuum of table "osm_database.pg_toast.pg_toast_3528119": index scans: 0
2024-03-29T16:09:27.9177696Z pages: 0 removed, 1122287 remain, 1122287 scanned (100.00% of total)
2024-03-29T16:09:27.9178122Z tuples: 0 removed, 5101444 remain, 0 are dead but not yet removable
2024-03-29T16:09:27.9178409Z removable cutoff: 18279, which was 1 XIDs old when operation ended
2024-03-29T16:09:27.9178607Z new relfrozenxid: 18263, which is 3 XIDs ahead of previous value
2024-03-29T16:09:27.9180150Z frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
2024-03-29T16:09:27.9180404Z index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
2024-03-29T16:09:27.9180572Z avg read rate: 7.433 MB/s, avg write rate: 10.076 MB/s
2024-03-29T16:09:27.9180728Z buffer usage: 1416563 hits, 828117 misses, 1122600 dirtied
2024-03-29T16:09:27.9182167Z WAL usage: 1122288 records, 35 full page images, 66502016 bytes
2024-03-29T16:09:27.9182354Z system usage: CPU: user: 4.05 s, system: 7.38 s, elapsed: 870.39 s
2024-03-29T16:09:57.7317530Z 2024-03-29 16:09:57.731 UTC [506] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:10:57.7507241Z 2024-03-29 16:10:57.750 UTC [509] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:11:57.7701269Z 2024-03-29 16:11:57.769 UTC [512] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:12:57.7790805Z 2024-03-29 16:12:57.778 UTC [515] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:13:57.7991903Z 2024-03-29 16:13:57.798 UTC [518] LOG: skipping vacuum of "planet_osm_polygon" --- lock not available
2024-03-29T16:14:03.8618112Z [91m2024-03-29 16:14:03 Analyzing table 'planet_osm_polygon'...
2024-03-29T16:14:04.5346120Z [0m [91m2024-03-29 16:14:04 All postprocessing on table 'planet_osm_polygon' done in 5024s (1h 23m 44s).
2024-03-29T16:14:04.5612319Z [0m [91m2024-03-29 16:14:04 osm2pgsql took 8849s (2h 27m 29s) overall.
2024-03-29T16:14:07.3275177Z [0m [91m+ rm -f /data/map.osm.pbf
2024-03-29T16:14:07.3309732Z [0m [91m+ pg_ctl stop
2024-03-29T16:14:07.3321751Z [0m2024-03-29 16:14:07.331 UTC [27] LOG: received fast shutdown request
2024-03-29T16:14:07.3369440Z waiting for server to shut down....2024-03-29 16:14:07.336 UTC [27] LOG: aborting any active transactions
2024-03-29T16:14:07.3378562Z 2024-03-29 16:14:07.337 UTC [27] LOG: background worker "logical replication launcher" (PID 33) exited with exit code 1
2024-03-29T16:14:07.4013492Z 2024-03-29 16:14:07.400 UTC [28] LOG: checkpoint complete: wrote 24 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1652.023 s, sync=0.034 s, total=1652.084 s; sync files=227, longest=0.007 s, average=0.001 s; distance=721 kB, estimate=9467 kB; lsn=0/6519650, redo lsn=0/1FCCA88
2024-03-29T16:14:07.4014432Z 2024-03-29 16:14:07.400 UTC [28] LOG: shutting down
2024-03-29T16:14:07.4048807Z 2024-03-29 16:14:07.404 UTC [28] LOG: checkpoint starting: shutdown immediate
2024-03-29T16:14:20.1091481Z ............2024-03-29 16:14:20.108 UTC [28] LOG: checkpoint complete: wrote 101114 buffers (77.1%); 0 WAL file(s) added, 0 removed, 5 recycled; write=0.366 s, sync=12.305 s, total=12.708 s; sync files=19, longest=3.837 s, average=0.648 s; distance=70963 kB, estimate=70963 kB; lsn=0/65196C8, redo lsn=0/65196C8
2024-03-29T16:14:20.1897519Z 2024-03-29 16:14:20.189 UTC [27] LOG: database system is shut down
2024-03-29T16:14:20.2497852Z done
2024-03-29T16:14:20.2498193Z server stopped
Is anybody please able to spot any improvements I could
apply to the postgresql.conf config values at the top of my mail,
Is anybody please able to spot any improvements I could
apply to the postgresql.conf config values at the top of my mail,
that could reduce the loading time of almost 2 hours?
Thank you for any hints
Alex
pgsql-general by date: