[Tile-serving] [openstreetmap/osm2pgsql] High connection volume to Postgres (Discussion #1650)

[Ryan Lambert]

I recently ran into Postgres' `FATAL:  sorry, too many clients already` while using osm2pgsql-replication. This happened with my PgOSM Flex project, [this comment](https://github.com/rustprooflabs/pgosm-flex/pull/226#issuecomment-1059809664) has details.  This occurred on an instance with Postgres' default configuration and only handling the osmpgsql workload. The quick solution is to increase `max_connections`, for my case `max_connections=300` was sufficient (though 200 was not!).  The file being processed in this testing was the 17 MB Washington D.C. subregion from Geofabrik.  I'm using the lastest master branch of osm2pgsql, so I'm a bit beyond v1.6.0.

To see what was happening I watched connections first for the normal osm2pgsql import.  The `application_name` is set to `pgosm-flex` in the connection string.  I ran this query in psql and used `/watch 1` to monitor.  The default import in that project peaked at 44 connections.

```sql
SELECT application_name, COUNT(*)
    FROM pg_stat_activity
    GROUP BY application_name
;
```


```
┌──────────────────┬───────┐
│ application_name │ count │
╞══════════════════╪═══════╡
│ psql             │     1 │
│                  │     5 │
│ pgosm-flex       │    44 │
└──────────────────┴───────┘
```

Using `osm2pgsql-replication` peaked at 208 connections.

```
┌──────────────────┬───────┐
│ application_name │ count │
╞══════════════════╪═══════╡
│ psql             │     1 │
│                  │     5 │
│ pgosm-flex       │   208 │
└──────────────────┴───────┘
```

My hope is that there's a way within osm2pgsql to set a max number of concurrent connections it will open.  I don't know if that's possible, and it could be the way I'm using [deep copy](https://github.com/rustprooflabs/pgosm-flex/blob/main/flex-config/helpers.lua#L52-L57) to [stack styles](https://github.com/rustprooflabs/pgosm-flex/blob/main/flex-config/style/place.lua#L157-L197) is the culprit.
The default processing in my project includes 15 Lua styles stacked using this method.  I ran the `minimal` layerset in the project, which loads 3 Lua scripts, and it only opened 12 connections instead of 44.  The approach with deep copy was suggested with caveats that there might be negative side effects such as this.  So if this is a "me problem" just let me know!

Users on Postgres 13 or older will likely see poor performance in these cases scaling into the hundreds of connections.
[This post compares Pg 12 v 13](https://www.enterprisedb.com/blog/postgresql-tproc-c-benchmarks-postgresql-12-vs-postgresql-13-performance) with high connection volume.  I'm using Postgres 14 which luckily has seen major improvements in its handling of connections into the hundreds/thousands. I haven't seen good benchmarks on Postgres 14.0 or newer, but [this early look](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462#fn:1) gives a positive outlook. Both of those posts are using powerful servers (at least 96 cores).  Users with more common hardware would see the cliff in performance sooner.

I'm not opposed to letting that many connections be open either.  For planet updates on powerful hardware that could be desirable. 
 Though, even at that scale there's a desire to balance speed of completion version impact to other workloads on the server. 

If this is a self-created problem by my usage, I will likely bake pgBouncer into that Docker image and let that handle the volume of connections.



-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/discussions/1650
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/repo-discussions/1650 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20220306/866eec62/attachment.htm>