Testing osm2pgsql PR #2134 for reducing conections

rustprooflabs / pgosm-flex

PgOSM Flex provides high quality OpenStreetMap datasets in PostGIS (Postgres) using the osm2pgsql Flex output.

MIT License

101 stars 20 forks source link

Testing osm2pgsql PR #2134 for reducing conections #376

Closed rustprooflabs closed 9 months ago

rustprooflabs commented 9 months ago

This PR started by testing https://github.com/osm2pgsql-dev/osm2pgsql/pull/2134. Those results were encouraging and is now this is a PR addressing a couple other minor improvements.

Uses latest osm2pgsql, significantly reducing connection volume to Postgres https://github.com/osm2pgsql-dev/osm2pgsql/pull/2134
Remove extra -d from osm2pgsql-replication - required to work with https://github.com/osm2pgsql-dev/osm2pgsql/pull/2115
Add OSM2PGSQL_REPO to Dockerfile for easier testing
Adjust GitHub URL for change to /osm2pgsql-dev/ (from /openstreetmap/

rustprooflabs commented 9 months ago

Connections before -- 41 idle!

Connections after -- 3 idle.

rustprooflabs commented 9 months ago

Remove extra -d from osm2pgsql-replication command after more strict option checking implemented https://github.com/osm2pgsql-dev/osm2pgsql/pull/2115

Sets repo/branch back to production setting. Leaving the new repo URL.

rustprooflabs commented 9 months ago

Adding a few more notes about connection volume with replication. The initial load had a connection pattern similar to a single load w/out replication (sustained 41 idle connections). Where things could get really nasty connection-wise was the replication update steps.

Before the patch the default PgOSM Flex layerset would consume 207 connections for replication updates. It's a relatively short time, but compared to Postgres' default max_connections=100 that can be shocking.

After the change to osm2pgsql, the total connection load drops to 17 (-92% diff)