0

I went into this issue on purpose by scaling up a dockerized Python application 50x using this command docker-compose up --scale app=50 app:

This app connects to a single PostgreSQL database (also as a Docker container).

As monitored by pgAdmin4, the server activity is as follow:

postgresql activity as monitored by pgadmin4

I've read that PostgreSQL allows a number of maximum concurrent connections of 100 by default. This should be fine because my app is "only" scaled by a factor of x50.

But I have plenty of such messages from Python:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already

plus this one sometimes:

psycopg2.errors.DiskFull: could not resize shared memory segment "/PostgreSQL.1997913688" to 196736 bytes: No space left on device

The computer on which I'm running this application has 256 GB of RAM and 2x 1TB almost empty hard drives. I've also noticed that the RAM is only used at ~30%-40% max when the app is actively writing data to the database.

I can then change the max_connections parameters but I went through a nice article on this topic, and it seems tricky to set up correctly, so I prefer not to touch the PostgreSQL default's configuration (unless absolutely needed of course): https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/

But prior to changing anything in the config, or studying more about pgBouncer which may be better suited here(?), I need to understand why when scaling up my app by a factor of "only" 50x, the PostgreSQL max_connections value, which is 100, is overtaken?
Any hints on this?

Version: I am using postgis/postgis:13-3.1 docker image for the database.

4
  • What does scaling your app by a factor of x50 mean? You should be using pgBouncer, yes. Commented Sep 19, 2021 at 9:16
  • docker-compose up --scale app=50 app Main post modified accordingly. Thanks.
    – s.k
    Commented Sep 19, 2021 at 9:40
  • 1
    Since Postgres won't create any connections to itself, it's likely that your application creates more than one simultaneous connection.
    – mustaccio
    Commented Sep 19, 2021 at 11:28
  • Hmmm, I see. This is possible indeed, I have to explore it more in details; I may have simple SELECT queries somewhere to fetch column headers... Thanks for the hint.
    – s.k
    Commented Sep 19, 2021 at 12:20

1 Answer 1

1

You should look at pg_stat_activity to see what the connections are doing (or have done recently, in the case of idle connections), plus what user and database they logged on to, and what the IP address they come from and what application_name they report. There is a good chance that this will immediately let you know why your app is making more than one connection per app-server. Since you haven't told us anything about your application, what more can we tell you? In general it is not surprising that an app server would have more than one connection. But it seems you are surprised by it, but we don't know why you find it surprising. Was your app carefully designed to not use more than one connection?

could not resize shared memory segment "/PostgreSQL.1997913688" to 196736 bytes: No space left on device

I think the device being referenced here is not some physical device, like RAM or disk. It is a virtual "device" (well, so are RAM and disks, but this is even more virtual than them). I would think you have parallel workers or something trying to use more shared memory than your kernel is configured to allow.

I can then change the max_connections parameters but I went through a nice article on this topic, and it seems tricky to set up correctly, so I prefer not to touch the PostgreSQL default's configuration (unless absolutely needed of course)

I think you have too much respect for the default. There is no reason not to change the default, except that in this case, there is no reason to change it. It sounds like the whole point of this scaling exercise was to see what problems it would cause. And you found a problem: that it uses more connections than you thought it would. So go fix that problem, rather than papering over it by increasing max_connections. You could fix the problem by increasing max_connections. But you could also fix the problem by scaling to only 40 rather than 50. But in either case, what would have been the point?

Same goes for pgbouncer. If you want to study pgbouncer, that's fine. But is that what the point of this exercise was, to give yourself an excuse to go do that?

1
  • I like this kind of answers, thanks a lot, you made me watch my Python code more carefully and think(!) more in depth its relation to the database, and indeed, I found a psycopg2 connection which was not properly closed. I was having plenty of "idle" state "COMMIT". Now that this point is fixed the red curve ('idle' connections) on the graph is totally flat. I also increased the shm_size from 512mb to 8gb in my Compose services coz I noticed that /dev/shm is regularly increasing (up to 1GB) in the database container (I don't really know why; the pgdata volume is in bind mount...)
    – s.k
    Commented Sep 20, 2021 at 9:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.