I am using Npgsql connection and kubernetes service used for deployment.see below post for my configuration.
Now i got an error check below
The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)
how to solve this
This is a problem on your client / npgsql side.
It seems like you set Max connection limit to 100 and are trying to open more than 100 connections. You either have to probably close the connections or reuse them.
i am always closing the connection when i open.
is there any example for reusing the connection in c# web api?
How do i increase the Max connection ?
below is my connection flow
but still i got the
The connection pool has been exhausted
error
Could you please help on this issue?
Dear @testjob_tyu,
thank you for sharing your observations about the behavior of the connection pool included in Npgsql. It is an important topic to configure and use a database connection pool appropriately in order to not overload the database.
May I first ask if you observe that when having reasonable load (i.e. number of requests) on the application or, instead, experience it even in low traffic situations? This is important to tell apart because seeing that error might have different root causes in individual scenarios.
Also, I would like to humbly ask how and in which environment your Kubernetes infrastructure is operated. Is it some cloud/SaaS offering running on shared infrastructure operated by AWS, Azure, Google, Alibaba and friends or do you run and operate it completely on your own premises?
Both those details are important to know about before going deeper.
Without aiming to be offensive in any way, let me cite some quotes from the discussion you shared with us [1].
In other words, this suggests to only use a single instance of
conn = new NpgsqlConnection(databaseConnString);
throughout your whole application but individually invoke conn.Open() and conn.Close() where you need a database connection. While I am not 100% sure about this, I believe the connection pooling mechanism is implemented through those methods.
According to [2], the default pool size (100) should be more than enough for handling quite a number of concurrent web requests reasonably, I don’t see a need to increase their values.
I also wanted to let you know that discussions about the same topic might just be happening at [3,4]. Maybe you can draw some inspirations from there, i.e. are you 100% sure you don’t have any connection leaks in your code?
It will always be specific to the application / web framework which gets used. Maybe you can share more code of yours?
You will have to add the respective settings to your database connection string, as outlined at Connection String Parameters | Npgsql Documentation. However, I strongly believe the default of 100 concurrent connections available to the database connection pool will be enough in most cases and should not be increased lightheartedly because that might cause to use too much resources on the database itself.
With kind regards,
Andreas.
[1] c# - what can be done for connection re use? - Stack Overflow
[2] Connection String Parameters | Npgsql Documentation
[3] NPGSQL connection pool issue · Issue #3829 · npgsql/npgsql · GitHub
[4] postgresql - NPGSQL connection pool issue - Stack Overflow
I have two services. One for insert the data into crate database and other one is fetching the same data (some aggregation query applied).
Inserting application is always running and it receive 600 data per minute. When the web user interface calling the fetch application, it will collect the data by fetch query. 50 query called in 10 seconds from web user interface. this is the entire situation. But some times got pool exhausted error. Not always.
Azure kubernetes.
For all other question i will share my code below.
I am using dotnet core 3.1 console application for database insert.
Code is below
public async Task InsertData(DataViewModel data, string tablename)
{
try
{
var ConnectionString = "Host=crate-internal-service;Username=crate;SSL Mode=Prefer;Database=doc"
using (NpgsqlConnection sqlCon = new NpgsqlConnection(ConnectionString))
{
sqlCon.Open();
string cmdString = $"INSERT INTO {tablename} (parameterid,deviceid,gatewayid,reading,timestamp) VALUES (@s,@d,@g,@r,@t)";
await using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmdString, sqlCon))
{
sqlCmd.Parameters.AddWithValue("s", data.parameterid);
sqlCmd.Parameters.AddWithValue("d", data.deviceid);
sqlCmd.Parameters.AddWithValue("g",data.gatewayid);
sqlCmd.Parameters.AddWithValue("r", data.reading);
sqlCmd.Parameters.AddWithValue("t", data.timestamp);
await sqlCmd.ExecuteNonQueryAsync();
}
sqlCon.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
For fetching the data uisng c# dotnet core3.1 web api application
public async Task<List<Aggregation>> Aggregated(Aggregation model,string tablename)
{
var ConnectionString = "Host=crate-internal-service;Username=crate;SSL Mode=Prefer;Database=doc"
var result = new List<Aggregation>();
try
{
using (NpgsqlConnection sqlCon = new NpgsqlConnection(SqlConnectionString))
{
sqlCon.Open();
var cmd = $"select date_trunc('{model.groupby}','Asia/Kolkata', v.timestamp) as unixtime,{model.operation}(reading) as reading from {tablename} v(parameterid,deviceid,reading,timestamp) where parameterid IN (@pt) AND timestamp>=@st AND timestamp<=@et group by (unixtime) ORDER BY unixtime ASC";
using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, sqlCon))
{
sqlCmd.Parameters.AddWithValue("pt", parameteres);
sqlCmd.Parameters.AddWithValue("st", model.starttime);
sqlCmd.Parameters.AddWithValue("et", model.endtime);
await using (var reader = await sqlCmd.ExecuteReaderAsync())
{
while (reader.HasRows && await reader.ReadAsync())
{
Aggregation item = new Aggregation
{
reading = readingValue,
};
result.Add(item);
}
}
}
sqlCon.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return result;
}
Always calling the new NpgsqlConnection(SqlConnectionString)) , open and close . When a data come and insert into the database, connect,open and close functions called every time. Same way for fetch application.
Dear @testjob_tyu,
thank you for sharing more details about your environment and application. Now, let’s temporarily assume it is not an overload situation, but an underutilization issue instead. In order to explain that way of thinking about the problem and the respective path of investigations further, let me elaborate a bit more about what we found out the other day.
You outlined that on the write path, you have a sustained insert operation going on. However, on the read path, there might be intermittent pauses between batches of requests? Now, if we assume the database connections serving those read requests will be put back in the connection pool and get stale while not being reused before the configured TCP timeout, the observed behavior like “connection pool exhausted” might be exactly the same as usually seen in overcommitment situations.
The reason why I asked about the hosting environment is that we have been able to observe nasty issues when operating long-lived TCP connections in typical cloud environments, which is a general issue and not specifically related to CrateDB. Specifically, we encountered those issues at the first time when operating Kafka on Azure but they also apply to other cloud provider’s infrastructures in one way or another.
It is related to the load balancing component, i.e. the NAT gateway typically used within cloud infrastructures to channel outbound network connections. We already assembled a comprehensive report about this topic at Networking robustness and resiliency on Azure and beyond (AWS, GCP, AliCloud) · Issue #10779 · crate/crate · GitHub, a descendant of the original RCA at Robustness and resiliency on Azure · Issue #3109 · edenhill/librdkafka · GitHub.
Now, attaching to that, in order to rule out those issues, we will probably have to adjust/enable some TCP keepalive features/threshold values on the Npgsql client settings. Maybe playing around with these connection string parameters can help:
Connection Idle Lifetime=200Tcp Keepalive=trueKeepalive=30Those resources are helpful to look up documentation about these parameters:
Please let me know about the outcome. If that doesn’t help, we will have to dig deeper.
With kind regards,
Andreas.
This is really just a wild guess, but at [1,2], I discovered the asynchronous variants OpenAsync() and CloseAsync(). Maybe using them will improve the runtime behavior right away, specifically when it comes to proper cleanup of resources.
On the other hand, [3] also has an interesting discussion about whether to favor Dispose() over Close(). In this case, you should probably also use the asynchronous variant DisposeAsync().
[1] https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L143-L151
[2] https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L683-L695
[3] c# - Why does this `using` block not release the database connection? - Stack Overflow
Okay,I will try this tcp connection problem
From the placement of your try-catch-block it seems like you are only closing the connections, when there is no exception.
You might want to move it and close the sqlCon in a finally-block.
Also, are you sure all your code is non-blocking, how many open pgsql-connections do you see in cratedb?
SELECT SUM(connections['psql']['open']) as pg_sql FROM sys.nodes limit 100;