[Tile-serving] [openstreetmap/osm2pgsql] Enable Postgres connection polling or query connection limit (#885)

[Fabrício Zimmerer Murta]

The default connection amount (--number-processes) of the tool currently seems to be too high for a default server configuration. Besides, not always a huge number of simultaneous connections works better than just a few.

The result now is that in a default postgresql server setup with, say, 100 max connections limit, which can be less depending on the system ([see max_connection here](https://www.postgresql.org/docs/10/runtime-config-connection.html)), is being exceeded to import, for example the [Canada data obtained from geofabrik.de](https://download.geofabrik.de/north-america.html) exceeds by an unknown amount (it does not report before failing) with PostgreSQL 10, like this:

```
node cache: stored: 311544523(100.00%), storage efficiency: 57.40% (dense blocks: 27056, sparse nodes: 160538556), hit rate: 100.00%
Osm2pgsql failed due to ERROR: Connection to database failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections
```

`osm2pgsql` could either pool the connections to the database, throttling it as soon as the first gets denied, use a smaller amount of connections, or get the current max_connections from the database and use no more than that (or use below it by a threshold to avoid conflict by other running applications), to a minimum of one (*1*) connection to the database.

The maximum connections set up to a database can be queried via the `show max_connections` command within the database. An example output from the client would be:

```
# show max_connections;
 max_connections
-----------------
 100
(1 row)
```
(works at least for postfix 9 an 10 versions -- mine is 10.6, and [this one is probably 9.1](https://stackoverflow.com/questions/8288823/query-a-parameter-postgresql-conf-setting-like-max-connections)

In this example, albeit the server had a limit for 100 connections, the application failed with the connection limit error message above. Setting `--number-processes` to 20, for example, worked fine.

As a quick fix for this, I'd say a number of 20 by default would be reasonable for most set-ups, while allowing a generous level of parallel queries running at once. Using the max connections amount available is not always true and should be analyzed in a case-by-case, so I'd say setting a very high connection amount is not good, by the server's defaults alone. 

If the developer team things 20 is way too few, the choice for 100 would at least be likely to work in default postgres installations.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/885
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20181207/6f149e97/attachment.html>