Strapi v4 + Postgres + Render.com = Error "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 4.1.3
  • Operating System: MacOS
  • Database: PostgreSQL 13
  • Node Version: 16
  • NPM Version:
  • Yarn Version:

I am often running in the following error. I’m hosting on www.render.com.

KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?    at Client_PG.acquireConnection (/opt/render/project/src/node_modules/knex/lib/client.js:295:26)    at async Runner.ensureConnection (/opt/render/project/src/node_modules/knex/lib/execution/runner.js:259:28)    at async Runner.run (/opt/render/project/src/node_modules/knex/lib/execution/runner.js:30:19)    at async PostgresqlSchemaInspector.getForeignKeys (/opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:205:22)    at async /opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:127:29    at async Promise.all (index 32)    at async PostgresqlSchemaInspector.getSchema (/opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:123:21)    at async Object.syncSchema (/opt/render/project/src/node_modules/@strapi/database/lib/schema/index.js:51:24)    at async Strapi.bootstrap (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:380:5)    at async Strapi.load (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:408:5)    at async Strapi.start (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:161:9)

My node Strapi service is running on 2 CPU and 4GB RAM.

My Postgres database is running on 2 CPU and 4GB RAM.

I’m using the following settings in /config/env/production/database.js

const { parse } = require("pg-connection-string");

module.exports = ({ env }) => {
  const { host, port, database, user, password } = parse(env("DATABASE_URL"));

  return {
    connection: {
      client: "postgres",
      connection: {
        host,
        port,
        database,
        user,
        password,
        ssl: {
          rejectUnauthorized: false,
        },
      },
      debug: true,
      pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 30000000,
        createTimeoutMillis: 30000000,
        acquireTimeoutMillis: 30000000,
        propagateCreateError: false,
      },
    },
  };
};

Has anyone found good settings to use on Render.com to avoid running in this error?

3 Likes
2

Same problem here!

1 Like
3

I moved to Railway.app

4

@gvocale I have the exact same issue…

Did you find out how to solve it?

Or do you have a workaround?
The only thing helping me right now is redoing the deploy again and again until it is (randomly?) successful…

5

No solution…

We switched from PostgreSQL to MariaDB out of frustration. We don’t get anymore the Knex: Timeout acquiring a connection, but we get some more descriptive errors.

One bug we figure out is sometimes columns name were too long (more then 64 characters), like described into this bug Tracking Issue - Database table, column, index, and foreign keys are too long · Issue #13117 · strapi/strapi · GitHub. So for now we manually shorten the collectionName as described here Error: ER_TOO_LONG_IDENT | Too long column names hit character restriction in database · Issue #12101 · strapi/strapi · GitHub

Another bug we’re always running into is ER_DUP_KEYNAME: Duplicate key name when running automatic migrations on production. So far we’ve been able to solve it by manually purging the strapi_content_types_schema key from the strapi_core_store_settings table as suggested here Discord

6

Thank you for your reply!

Sorry to hear you couldnt fix it. Did you try to keep icreasing the server specs? It feels like it gets “better” when I switch to more CPU/RAM but its not “fixed” for me, just less often.

Right now I am thinking about increasing my server specs on render to the highest plan just for deployment and then switch to a less expensive one after. But this does not feel too good :smiley: …

When you have MariaDB now, you are not longer at render.com?

7

Right now we’re running Strapi on a Render Node Pro Plan (4GB RAM, CPU 2), and MariaDB on a Render Docker Standard Plan (2GB RAM, CPU 1).

I think when we were on Render Hosted PostgreSQL I tried go up to 8GB RAM, but would still run in the Knex issues… I found some post saying they’ve tried with higher spec’d machines, but the problem was still happening, so probably unrelated.

Also you can’t downgrade I think, at least for the database. So no chance to try a more powerful database machine, run the automatic migration, then take it to a lower spec.

We’re still with render.com with MariaDB (Dockerized). No more Knex issue, but still the deploy fails often with the ER_DUP_KEYNAME: Duplicate key error. In fact we’ve been delaying launching our strapi powered frontend few weeks just because of all these deployment issues… Haven’t figure out all these quirks yet sadly.

8

Did you resolve that issue? We’re deciding where to move our stack right now and this thread scares me about Render. I’ve been using it for pure UI deployments and it works like a charm.

Thanks,

9

I cannot recommend it. See my explanation here: Strapi Postgres Connection Pool breaks connection - #20 by N_H

10

Along with connection and pool, add acquireConnectionTimeout, and give this a big value. This worked for me. We had to take Strapi support inputs to resolve this! In my case, I noted that the error would occur after 60 seconds. I tried multiple connection pool configurations and the issue repeated until adding acquireConnectionTimeout. This configuration is not a part of pool, but, needs to be put separately. i.e.

module.exports = ({ env }) => ({
connection: {
client: ‘postgres’,
connection: {
host: env(‘DATABASE_HOST’, ‘127.0.0.1’),
port: env.int(‘DATABASE_PORT’, 5432),
database: env(‘DATABASE_NAME’, ‘strapi’),
user: env(‘DATABASE_USERNAME’, ‘strapi’),
password: env(‘DATABASE_PASSWORD’, ‘strapi’),
schema: env(‘DATABASE_SCHEMA’, ‘public’), // Not required
ssl: env(‘DATABASE_SSL’, false)
},
acquireConnectionTimeout: 1000000,
pool: {
min: 0,
max: 5,
acquireTimeoutMillis: 300000,
createTimeoutMillis: 300000,
destroyTimeoutMillis: 300000,
idleTimeoutMillis: 30000,
reapIntervalMillis:1000,
createRetryIntervalMillis: 2000
},
debug: false,
},
});

11

Switching to Railway.app literally solved all my problems in minutes.

Also, it’s promising to be cheaper than Render. So that’s a bonus. And Render had a history of random outages in the Frankfurt region that weren’t logged properly in the status dashboard. Incident reports were missing or said services were down for a few minutes, when it was hours instead. So maybe the Render problems are a sign to abandon and move. I’m kinda sad to be leaving Render, I loved being able to choose which region my data lives in.

12

Actually, I might have been wrong. Strapi v4.2.3 deploys on Railway.app easily, but it still has HUGE performance issues.

I use Strapi as the backend for a Next.js app.

V3 on Render worked quite well and static page generation on Next.js never had a problem.
V4 on Render just fails. I’m currently dealing with Render support, but basically their answer is: “A lot of people have this problem, Strapi is extremely resource hungry, not much we can do when your app crashed because it runs out of memory.”

I can deploy v4 on Railway.app, and run the backend just fine. (see my post above).

BUT…

When deploying the Next.js frontend on Vercel, any static page generation crashes Strapi (on Railway.app) when Next.js collects the page data from getStaticProps().

The requests for pretty much any api endpoint with population of fields creates a huge spike in memory use and the requests time out, or often the server crashes.

Screen Shot 2022-07-19 at 14.26.36
Screen Shot 2022-07-19 at 14.26.361724×702 130 KB

I changed all the getStaticProps calls in the Next.js frontend to getServerSideProps effectively disabling static page generation and instead using SSR to load one page at a time (and cache it).

Only one call I need to do on build. I’m generating an RSS feed for a podcast. To generate that I need to do ONE API call to an endpoint containing just 8 items in the collection with ONE dynamic zone that needs to be populated.

Running that single query spikes memory use to over 1GB and effectively halts execution of the request on Railway.com.

I found that population depth had a huge impact on performance. It feels like it loads the whole collection of possibly linked relations into memory first, only to select the the one that was actually linked for that record. That’s just a feeling, I could verify that. It’s like it gets stuck in some sort of infinite loop once population goes deeper than a level or so.

To fix it, I now select as few fields/relations as possible, which helps. but still trying to figure out how to specify which components of a dynamic zone to populate. (But that’s another story).

Hope that helps someone, until Strapi gets a bit more performant.

13

I have the same issue when i apply this command npm run develop to start the strapi my cpu is going high to 175% when i check the htop the mysqld used my cpu load when i stop the command cpu load came down.
this message come
Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Please help if you know what is the issue

14

Here’s the solution that worked fine for me and I have noticed that this fixed others similar problems that you have mentioned too. I just updated the version from 4.1.* to 4.2.2 and everything is now working fine. I am wondering why there is no post about this fix in their version release. This might’ve been a small patch and strapi developers might’ve just thought that this is’t needed, this gave me a hard time to figure out what exactly is happening. Yeah , its true there is a lot happening but this version update fix is kinda abstract and we need to know more on what exaclty gone wrong and what did they fix. The error could be on the db connectivity or in their memory, cause my local server is working fine its when i connect the server’s db like from aws things go bad with this error. End of the day a update fixed the issue. Happy coding.

15

This really helped me so much !!! Upgrading strapi from version 4.1 to 4.2.2 worked !
They should reallly include it in the docs

16

I ended up creating self hosting solution after encountering this issue… if anyone interested you can follow details at Strapi (Headless CMS), Google Cloud Run, and PostgreSQL | by Ganesh, Mohan | Jan, 2023 | Medium

17

Thank you @shrikant for sharing. As I did not understand the consequences of these settings, I asked chatGPT to explain the settings (I hope it is telling the truth). Some of the values are defaults and do not change anything. Others have consequences, and I’m trying to understand them so that I don’t create new problems after adjusting.
Can you tell me more about why you set the values?

This is what chatgpt told me:

In the provided configuration code, several Knex.js connection and pool settings are set. Here’s an explanation of the parameters from acquireConnectionTimeout and down:

  1. acquireConnectionTimeout: The time (in milliseconds) to wait before raising an error when a connection cannot be acquired from the pool. Default value: 60000 (60 seconds). In your example, it is set to 1000000 ms (1000 seconds).
  2. pool: An object containing the connection pool settings.
  • min: The minimum number of connections in the pool. Default value: 2. In your example, it is set to 0.
  • max: The maximum number of connections in the pool. Default value: 10. In your example, it is set to 5.
  • acquireTimeoutMillis: The time (in milliseconds) to wait for a connection to be acquired from the pool before raising an error. Default value: 60000 (60 seconds). In your example, it is set to 300000 ms (300 seconds).
  • createTimeoutMillis: The time (in milliseconds) to wait for a connection to be created before raising an error. Default value: 30000 (30 seconds). In your example, it is set to 300000 ms (300 seconds).
  • destroyTimeoutMillis: The time (in milliseconds) to wait for a connection to be destroyed before raising an error. Default value: 5000 (5 seconds). In your example, it is set to 300000 ms (300 seconds).
  • idleTimeoutMillis: The time (in milliseconds) after which idle connections will be released. Default value: 30000 (30 seconds). In your example, it is set to 30000 ms (30 seconds).
  • reapIntervalMillis: The time (in milliseconds) between pool reaping events, during which idle connections are released. Default value: 1000 (1 second). In your example, it is set to 1000 ms (1 second).
  • createRetryIntervalMillis: The time (in milliseconds) between connection creation retry attempts. Default value: 200. In your example, it is set to 2000 ms (2 seconds).
  1. debug: A boolean indicating whether to enable query debugging. If set to true, Knex.js will log every executed query. Default value: false. In your example, it is set to false.

The provided configuration sets custom values for many of the Knex.js pool settings. Keep in mind that setting extreme values for these settings may affect the performance and stability of your application. Make sure to test and monitor your application to find the optimal configuration.

18

Let me try to explain my issue, and what this configuration resolved. I am using multiple environments - dev, staging, production. In dev, the content isn’t much populated, whereas in staging and production, the content is populated. During development, when I changed existing collection types, it worked fine. However, during deployment to staging and production, I was getting timeout. This was because, migrations that occur automatically during starting the Strapi app take time when there’s considerable content in the tables. My website has 850+ pages. I noted that this timeout issue was only because of the quantum of data. Removing content caused the start to work normally. So, we raised an issue with Strapi support and ended up increasing acquireConnectionTimeout. Before raising the ticket, “acquireConnectionTimeout” was not mentioned in the docs, and was later added.

I pasted the entire object just so that someone else might try it. The only change I really wanted was to increase “acquireConnectionTimeout” for this issue. I changed the max pool to 5 because postgres instance I was using had a max limit of 5.

Same config may or may not resolve your issue. You can refer to knex documentation since Strapi uses knex.

19

Thanks @shrikant . I upgraded to the database configuration that was defined in 4.6.2 and I see that the configuration can now be set in .env . I’m still trying to find out why I get the error. My system is slightly different from yours as I don’t use many tables. I have 40 cron jobs that process data (crawl websites, use public APIs, etc.). All of the jobs query a table to check if they can start or not. This is where I run out of resources.

20

Hey guys, I was encountering this issue too but finally solved it. If any of you need further help, I wrote an article about it on Medium, here is the link: