2

I'm currently using the default connection pool in sequelize, which is as follows: 

const defaultPoolingConfig = {
  max: 5,
  min: 0,
  idle: 10000,
  acquire: 10000,
  evict: 10000,
  handleDisconnects: true
};

Of late, I'm getting these errors ResourceRequest timed out which are due to the above DB configuration. According to some answers the max pool should be set to 5, but those who have faced the above, Resource timeout, error have suggested to increase the pool size to 30, along with increasing the acquire time.

I need to know what must be the optimum value of max pool size for a web-app.

Edit: 1.Lets say I have 200 concurrent users, and I have 20 concurrent queries. Then what should be the values?
2.My database is provided by GCP, with the following configuration: vCPUs 1
Memory 3.75 GB SSD storage 10 GB

  1. I'm adding some graphs for CPU utilization, Read / write operations per second and transactions per second. Transactions per second CPU Utilization Read / write operations

  2. My workload resources are as follows:

    resources: limits: cpu: 500m memory: 600Mi requests: cpu: 200m memory: 500Mi

farhan
  • 59
  • 1
  • 1
  • 6
  • 3
    This is a number that is specific to your web application, your load profile, the number of concurrent users and the way the database is accessed. It's impossible to give you a number for that. –  Feb 10 '20 at 09:27
  • @a_horse_with_no_name I have edited the question with some more details, what would your answer be now? – farhan Feb 10 '20 at 09:37
  • Usually such flat lines on usage graphs are indicative of being resource-constrained, but the figure is quite low for SSDs. What's your application? Something that is user-facing (and should thus have peaks and throughs related to the number of users over time), or do you have long-running processes making very regular queries at all times? – jcaron Feb 10 '20 at 10:17
  • @jcaron I've just checked another graph for active connections, and th avg no of connections is around 8, whereas my default pool allows for only 5 connections, so does it mean that if I increase the max pool_size to 10, this problem would be solved? – farhan Feb 10 '20 at 10:18
  • The application is a nodejs based microservice with relatively lesser traffic and yes there are a few long running processes making regular queries – farhan Feb 10 '20 at 10:24
  • If you have an avg no of connections higher than your maximum, that means you have some other process or pool at play here. As detailed below, it all depends on what is going on. Increasing the pool size may just delay the issue or move it from one location to another. You need to check what you server is doing. Start by looking at `pg_stat_activity` to understand what is going on. But increasing the pool size a bit shouldn't hurt (even if it does not actually fix the issue). – jcaron Feb 10 '20 at 10:26
  • @jcaron I just ran `pg_stat_activity `, and at any time it returns me 30 rows on average. Of these 30 queries, there's just one or two which are in active state, and the rest are "idle". Is that a problem? And, I dont see any other unwanted queries / processes / pools – farhan Feb 10 '20 at 11:01
  • 30? Did you already bump your max connections and restart your server? Check the various dates in the `pg_stat_activity` output to see if the connections are actually being used or if they are stale connections. – jcaron Feb 10 '20 at 11:07
  • @jcaron actually this server has around 7 databases. And yes there are 30 and at some instances around 40. Most of these are in / go into idle state, and just one or two are active. I think you mean idle state by stale. edit: all these are of one minute or less, after which the output of `pg_stat_activity ` changes – farhan Feb 10 '20 at 11:13
  • @jcaron ok, the database in question returns me just 10 rows on average for `pg_state_activity`. This is still worrying if I have 5 as my max pool, right? Note: most of the connections (7 out of 10 on avg) are idle – farhan Feb 10 '20 at 11:27

1 Answers1

3

The number of concurrent connections should be large enough for the number of concurrent running queries or transactions you may have.

If you have a lower limit, then new queries/transactions will have to wait for an available connection.

You may want to monitor currently running queries (see pg_stat_activity for instance) to detect such issues.

However, your database server must be able to handle the number of connections. If you are using a server provided by a third party, it may have set limits. If you are using your own server, then it needs to be configured properly.

Note that to handle more connections, your database server will need more processes and more RAM. Also, if they are long running queries (as opposed to transactions), then you are most probably resource-constrained on the server (often I/O-bound), and adding more queries running at the same time usually won't help with overall performance. You may want to look at configuration of your DB server (buffers etc.), and of course, if you haven't already done so, optimise your queries (make sure they all use indexes). The other pg_stat_* views and EXPLAIN are your friends here.

If you have long-running transactions with lots of idle time, then more concurrent connections may help, though you may have to wonder why you have such long-running transactions.

To summarise, your next steps should be to:

  • Check the immediate state of your database server using pg_stat_activity and friends.

  • If you don't already have that, set up monitoring of I/O, CPU, memory, swap, postgresql statistics over time. This will give you a clearer picture of what is going on on your server. If you don't have that, you're just running blind.

  • If you have long-running transactions, check that you always correctly release transactions/connections, including when errors occur. This is a pretty common issue with node.js-based web servers. Make sure you use try .. catch blocks wherever needed.

  • If there are any long-running queries, check that they are properly optimised (using indexes). If not, do your utmost to optimise them. This will be the single most useful step you can take if that's were the issue is.

  • If they are properly optimised and you have enough spare resources (RAM, I/O...), then you can consider raising the number of connections. Otherwise it's just pointless.

Edit

Since you are not operating the database yourself, you won't necessarily have all the visibility you could have on resource usage.

However, you can still:

  • Check pg_stat_activity. This alone will tell you a lot of things.
  • Check for connections/transactions that are kept around when they shouldn't
  • Check queries are properly optimised

GCP has a default maximum concurrent connections limit set to 100 for instances with 3.75 GiB of RAM. So you could indeed increase the size of your pool. But if any of the above issues are present, you are just delaying or moving the issue a bit further, so start by checking those and fixing them if relevant.

jcaron
  • 17,302
  • 6
  • 32
  • 46
  • Thanks for the information, I have added some more information about my configuration in the question. Do you think I'm resource constrained for running let's say, a pool of 30 connections? – farhan Feb 10 '20 at 09:54
  • @farhan You have to check your resource monitoring. A server config without a workload does not tell us much (and vice-versa). – jcaron Feb 10 '20 at 09:58
  • I've added some graphs for my DB server and workload related config too. – farhan Feb 10 '20 at 10:10