0

I need help to understand the cause of this error and how to fix it.

I have a server running PostgreSQL 9.3. The cluster has around 1.000 databases. I use pgBouncer for pooling connections, and I set up a pool_size of only 1 connection per database. The total amount of active connections at any time is ~80. This is because most of databases have almost no activity at all.

My application load has been increasing slowly during the last months, and in the last couple of days I started to see the error showing up intermitently in the postgres log file. The time the error is logged corresponds with brief moments where the load is a bit higher than usual. Notice the error happens when postgres tries to fork a new process for connection and even for an autovacuum process:

2020-05-07 07:16:16 -03  LOG:  main could not fork new process for connection: Cannot allocate memory
2020-05-07 07:16:17 -03  LOG:  could not fork autovacuum worker process

These are some of my PostgreSQL settings:

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 2GB
maintenance_work_mem = 1GB
work_mem = 288MB
wal_buffers = 8MB
checkpoint_segments = 16

The server has 64GB of total RAM, 16 CPU cores, and it is running CentOS 7. My stack is: Nginx, uWSGI, Redis, pgBouncer and PostgreSQL. It's all installed in the same server, so resources must be shared between the elements of the stack.Redis is set to use no more than 30GB of RAM. Nginx ~8GB of RAM. uWSGI uses ~10GB of RAM. PostgreSQL uses ~8GB of RAM.

I'm no an expert at PostgreSQL. I've been reading the documentation regarding memory consumption, and considering my settings, my best guess is that I need to set higher values for shared_buffers and effective_cache_size. I got to that conclusion because those settings have been there for a long time (when my server had much less resources). But in the last few years, I've been adding resources to my server, but at the same time it has become busier. So I think maybe shared_buffers should be at least of 8gb, what do you think about that?

Also, for what I've read, I think I should make effective_cache_size higher than shared_buffers (considering the amount of RAM the server has available). Am I right?

One more thing: what do you think about my work_mem setting? I'm planning to reduce it (I'm not sure why it is set to 288MB). I know that the vast majority of the queries are very simple and fast. This is because all the databases correspond to simple blog applications, where most of the queries are SELECTs and the UPDATEs involve changing some value at an specific row, nothing complex. So, what do you think? I plan to reduce work_mem, am I in the right direction?

Thank you very much in advance! Warm regards, Lisandro.

  • Where exactly do you get the error message? In the log file? On the client? During what operation? – Laurenz Albe May 7 at 11:22
  • @LaurenzAlbe sorry I forgot to mention. I've edited my question adding the following: My application load has been increasing slowly during the last months, and in the last couple of days I started to see the error showing up intermitently in the postgres log file. The time the error is logged corresponds with brief moments where the load is a bit higher than usual. Notice the error happens when postgres tries to fork a new process for connection and even for an autovacuum process. – Lisandro May 7 at 15:38
  • NB I hope you're planning to upgrade soon. You are 6 major versions behind and support for 9.3 ended over a year ago already. – Colin 't Hart May 8 at 5:46
  • If anything, you need to reduce the amount of memory Postgres (and other applications) are reserving for themselves: the error message says that Postgres couldn't fork a new process which basically means the OS is out of memory to run any more processes. – Colin 't Hart May 8 at 5:47
0

I think that your machine is overloaded. You don't say what your max_connection setting is, but based on what you say I would assume it to be something around 1000.

As long as the number of active sessions is around 80, your memory should not get exhausted, but I assume that there are spikes of more active sessions, and your RAM is exhausted.

I am surprised that your machine runs well with 80 active sessions, since you have only 16 cores (you didn't say anything about the storage).

1000 databases seem a lot; I assume that each of them is small.

Since you have so many databases, I recommend that you move some to other clusters on different machines. That is an easy way to distribute the load.

Using a lower value for max_connections would reduce the danger of overload and memory exhaustion.

| improve this answer | |
  • Thanks, you've been very helpful. Yes, 1000 databases is a lot, but most of them are totally inactive. I have max_connections = 300, but considering I use pgBouncer for pooling, do you think it would make sense to set max_connections to a much lower value, say, 20, and let pgBouncer do the magic? What do you think? If I don't misunderstand, pgBouncer can multiplex thousands of connections over a small number of real postgresql connections, right? In my case, queries are simple, and connections are used only for milliseconds and then released. – Lisandro May 7 at 22:11
  • Yes, normally that is possible. But you can only have a connection pool per database and user, so with 1000 databases you need at least 1000 pools, and you probably cannot have a low max_connections setting. – Laurenz Albe May 8 at 6:03
  • I've just realised that while I was doing some tests. I see. Maybe changing to schemas would be the proper solution, but it's not possible right now. So I guess the only option I have left is to separate postgres to its own server and give it a lot of resources so it can handle hundreds of connections. Regardless most of the databases don't have any activity, they could have it eventually, and in that scenario postgres would need to be able to open that many connections. – Lisandro May 8 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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