High connection volume to Postgres #1650
Replies: 4 comments 3 replies
-
|
The number of connections osm2pgsql uses correlates with the number of tables you are creating, I believe it uses one connection per table and then some. Something like that. This was never a problem with the The workaround is, as you mention, to increase |
Beta Was this translation helpful? Give feedback.
-
|
I started digging into this with the plan of adding pgbouncer, but didn't get very far. My first step was to understand what the connection load looked like on Postgres. Monitoring With the higher count of tables I use, this results in a sustained 40 idle connections. These connections are used at the very beginning and in the final steps of processing.
This chart doesn't show that each of those idle connection starts by running a handful of (typically) fast queries. The data for the chart was sampled every 5 seconds, but these queries all run in a fraction of a second so don't get captured. The first set of queries on each of the 40 connections all run within ~2 seconds. Here are the related Postgres logs from one PID at the startup (00:58:15). After these initial queries only 1 connection is used to load data in to Postgres, while the other 40 (in my case) connections are held open. The way these connections are held open prevents pgbouncer from doing its job. The default Closing the connection after the initial drop table / create table / etc and opening a new connection for the post-processing would be best from the Postgres perspective. That would allow pgbouncer to cycle the connections in the pool. If there is a trick to getting pgbouncer to work with this, instructions in the docs would be helpful. I'd be happy to work up a PR with the instructions if someone can point me in the right direction. |
Beta Was this translation helpful? Give feedback.
-
|
Exciting news, pgbouncer recently merged support for prepared statements. PR pgbouncer/pgbouncer#845, the changes to documentation includes:
I originally was blocked trying to use pgbouncer by prepared statements in transaction mode. This should remove that roadblock. I'll hopefully find time to do some testing with that again before long. |
Beta Was this translation helpful? Give feedback.
-
|
As far as I can recall the number of connections is (3 + number of tables) for import and (3 + (1 + number of threads) * number of tables) for updates. So another workaround for updates is to reduce the number of threads (default is 4). |
Beta Was this translation helpful? Give feedback.
-
|
I have added this to the documentation https://osm2pgsql.org/doc/manual.html#number-of-connections |
Beta Was this translation helpful? Give feedback.
-
|
Great, thank you both! There are 41 tables, that math checks out for both import and update. I'll add pgBouncer in to absorb the connections. I'll make some notes in our docs to link to that section in the osm2pgsql docs too, that's very helpful. If there's ever an effort to reduce the count of connections I'm happy to help with testing! 👍 |
Beta Was this translation helpful? Give feedback.
-
|
Although the high number of connection is not ideal, for a typical flex style only one connection will be used per thread at once, so there aren't parallelism issues. |
Beta Was this translation helpful? Give feedback.
-
I recently ran into Postgres'
FATAL: sorry, too many clients alreadywhile using osm2pgsql-replication. This happened with my PgOSM Flex project, this comment has details. This occurred on an instance with Postgres' default configuration and only handling the osmpgsql workload. The quick solution is to increasemax_connections, for my casemax_connections=300was 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_nameis set topgosm-flexin the connection string. I ran this query in psql and used/watch 1to monitor. The default import in that project peaked at 44 connections.Using
osm2pgsql-replicationpeaked at 208 connections.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 to stack styles is the culprit.
The default processing in my project includes 15 Lua styles stacked using this method. I ran the
minimallayerset 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 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 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.
Beta Was this translation helpful? Give feedback.
All reactions