Varying performance with parallel inserts
Toxantron opened this issue · 13 comments
Since switching our application from devart DotConnect to npgsql we noticed a change in response times or more precisely variation of response times. Overall performance is really good, just not "deterministic".
We reproduced this in an isolated environment with a single entity that defines 5 columns each of type double, integer and string.
public class DummyEntity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public virtual long Id { get; set; }
public virtual double Float1 { get; set; }
public virtual string Name1 { get; set; }
public virtual int Number1 { get; set; }
...
public virtual double Float5 { get; set; }
public virtual string Name5 { get; set; }
public virtual int Number5 { get; set; }
}For our test setup we start 20 threads, each running 200 loops of inserts after an initial database creation and insert run for the JIT compiler. The Thread.Sleep(1) is used to enforce a context switch between the threads:
var times = new List<double>();
const int threads = 20;
const int loops = 200;
var connString = new NpgsqlConnectionStringBuilder
{
Username = "postgres",
Password = "postgres",
Host = "localhost",
Port = 5432,
PersistSecurityInfo = true,
Database = "NpgsqlTest",
Enlist = false,
NoResetOnClose = true
}.ToString();
for (var i = 0; i < threads; i++)
{
counter.AddCount();
ThreadPool.QueueUserWorkItem(delegate (object state)
{
var threadIndex = (int)state;
var watch = new Stopwatch();
for (int j = 0; j < loops; j++)
{
watch.Restart();
//using (var conn = new NpgsqlConnection(connString))
//{
// conn.Open();
// var cmdText =
// "INSERT INTO testmodel.\"DummyEntity\" (\"Float1\", \"Float2\", \"Float3\", \"Float4\", \"Float5\"," +
// "\"Number1\", \"Number2\", \"Number3\", \"Number4\", \"Number5\"" +
// ") VALUES (@p1,@p1,@p1,@p1,@p1,@p2,@p2,@p2,@p2,@p2)";
// using (var cmd = new NpgsqlCommand(cmdText, conn))
// {
// cmd.Parameters.AddWithValue("p1", (threadIndex + j) / (double)j);
// cmd.Parameters.AddWithValue("p2", threadIndex * j);
// cmd.ExecuteNonQuery();
// }
//}
using (var context = new TestModelContext(connString))
{
var entity = new DummyEntity();
entity.Float1 = entity.Float2 = entity.Float3 = entity.Float5 = (threadIndex + j) / (double)j;
entity.Name1 = entity.Name2 = entity.Name3 = entity.Name4 = entity.Name5 = "Bob";
entity.Number1 = entity.Number2 = entity.Number3 = entity.Number4 = entity.Number5 = threadIndex * j;
context.DummyEntities.Add(entity);
context.SaveChanges();
}
watch.Stop();
lock (times)
times.Add(watch.ElapsedMilliseconds);
Thread.Sleep(1);
}
}, i);
Thread.Sleep(1);
}Execution time ranges from 1ms to >300ms, averaging around 15ms. There are understandable longer times in the beginning while the pool is resizing, but also larger clumps spread through the test. We already disabled Enlist and ResetOnClose. We (think we) isolated the cause in the connection pool. We just don't understand why, with at most 20 parallel accesses and a maximum pool size of 100 (default) keeps running into situations where it is waiting for a connection.
Performing the same operation task with plain npgsql sees execution times from 0ms to 83ms, with an average of ~0,6ms. However all longer execution times are at the beginning, while connections are added to the pool and after that are stable between 0ms and 1ms.
Update: We also saw big delays with plain npgsql in follow up tests. Maybe the issue has nothing to do with EntityFramework, but is amplified by the EF execution overhead.
Because the application is used in the field of automation this huge variation in execution times creates visible hick-ups in the machine. We would be grateful for any pointers or how we could assist improving this.
Thanks for raising and all the relevant info - it seems you have a pretty good understanding of the various factors here. I'm generally very interested in Npgsql perf (and also EF).
We also saw big delays with plain npgsql in follow up tests
This is the part that interests me most - and it's also somewhat unlikely that merely using EF introduces large variation/jitter (as opposed to a fixed overhead). The best way forward here would be to:
- Write a benchmark showing significant variation with pure Npgsql, no EF. This would cut out EF as a potential source for the problem.
- Ideally use BenchmarkDotNet rather than a home-grown Stopwatch benchmark. It takes care of stuff like warmup and auto-calculating the number of iterations needed to reach reasonable certainty, etc. It also calculates stddev, max, min, and probably some other good statistics to give us an idea of variation/jitter
- Have you experimented against different servers? Are you able to see this when running against PostgreSQL on localhost?
I will isolate this in a benchmark repo here on Github and get back to you. I am happy to further investigate this behavior together and discuss alternative approaches for the connection pool.
Great, looking forward to seeing what you come up with!
As we prepare the repository I have a first theory:
- The pool creates connections on demand, which is quite expensive.
- From the pool it reuses free connections randomly and tracks their last usage
- Connections might be flagged unused by "bad luck" of not being chose for a while
- They are closed and reopened shortly after, which causes some requests to run significantly longer even at constant load
Solution: Set high number of minimum connections and after an initial ramp up, it should run smoothly. However I would prefer a smarter connection pooling algorithm.
Rough sketch:
- The pool determines optimal number of connections not on demand and timeout but based on request frequency and duration
- The pool should always have
required_connections + 1and if all connections are assigned it creates a new one. This minimizes cases where all connections are used up and a request pays the cost of a new connection - Spikes in requests are detected early and connections are prepared based on projected growth
- Connections are not removed based on their age but estimated necessary amount
Thanks for the time spent on this and for your comments! See npgsql/npgsql#2813 for a similar discussion on changing the pruning algorithm. /cc @NinoFloris.
First off, does this mean that if you disable pruning altogether (e.g. by setting Connection Pruning Interval to a very high number), all your issues disappear? This is just to validate that the issue is with pruning only.
A quick overview of how pruning currently works may help. The pool samples every few seconds (based on Connection Pruning Interval) and sees how many idle connectors there. If, over a period of time, idle connectors are constantly detected, and the pool is over Min Pool Size, we randomly close the surplus connections. Some of the comments you made don't seem to correspond with this (at least the way I understood them), so it could be useful to have a clear, concrete scenario and what you're seeing. Under more or less constant load connections shouldn't be getting closed/opened.
Connections might be flagged unused by "bad luck" of not being chose for a while
We don't track specific connections, only the total number of idle connections - so it doesn't matter if a given connection is chosen or not.
They are closed and reopened shortly after, which causes some requests to run significantly longer even at constant load
As written above, under constant load connections should not get closed/reopened. Having a more precise scenario description could be useful here.
They are closed and reopened shortly after, which causes some requests to run significantly longer even at constant load
Physical reopen should only happen if there are idle connections over a long period of time (leading to closing), and after this an uptick in the load (leading to opening). Constant load should not produced this.
The pool determines optimal number of connections not on demand and timeout but based on request frequency and duration
Request frequency and duration doesn't seem very relevant: i.e. load is the same if connections are requested rarely but held for a long time, or if the are requested very frequently and held for short times. In both cases the same number of physical connections is needed. This is why we sample how many actual connections are idle over time.
The pool should always have required_connections + 1 and if all connections are assigned it creates a new one. This minimizes cases where all connections are used up and a request pays the cost of a new connection
This may be a good idea - we could have a small margin above what we detect the user actually needs (based on idle sampling). The downside would be slight over-use of connections. /cc @NinoFloris
Spikes in requests are detected early and connections are prepared based on projected growth
That's a bit vague :) What does it mean exactly?
Connections are not removed based on their age but estimated necessary amount
This is more or less already the case. We don't track age on specific connections, but sample over time (as explained above) and close random idle connections.
First of all I have to admit that I did not take a close look at the pool and its current implementation, but tried to find an explanation for the behavior I am seeing. So I apologize for any false claims I made.
As written above, under constant load connections should not get closed/reopened. Having a more precise scenario description could be useful here.
Our scenario is a machine control system communicating with individual cells of an assembly line. This can be anything from 1 to a few hundred cells and their manufacturing speed ranges from multiple parts per second to minutes or hours for a single part. To make sure we maintain response times below 10ms we have system benchmarks stress testing the system with hundreds of cell interactions per second. The stress test causes relatively constant load on multiple threads while it is running. Since the machine only proceeds after an interaction was complete we saw it getting clogged up and threw put declining after switching to npgsql. Measuring individual response times yielded higher response times after boot (which is understandable and acceptable) but also individual responses at runtime jumping from 2ms to 300ms. We then narrowed it down to retrieving a connection from the pool as the only non-constant(within the limits of .NET) execution time.
Request frequency and duration doesn't seem very relevant: i.e. load is the same if connections are requested rarely but held for a long time, or if the are requested very frequently and held for short times. In both cases the same number of physical connections is needed. This is why we sample how many actual connections are idle over time.
The idea was to establish an equation, more or less connections = frequency * duration to determine the need proactively instead of opening on demand and pruning idle connections. An observatory approach is easier and more flexible, but "blindly" approaches the ideal amount of connections more or less by "create & destroy" until a stable state is achieved.
This may be a good idea - we could have a small margin above what we detect the user actually needs (based on idle sampling). The downside would be slight over-use of connections. /cc @NinoFloris
At least there was something useful in my proposal. :-)
That's a bit vague :) What does it mean exactly?
Note sure if it applies universally but the idea was: If we can calculate connections from frequency (defined as new_connection / period) and duration we can estimate total connections as a multiple of new connections in a fragment of the period as n * (new_connections/(period/n)*duration. That way we can resize the pool to the necessary size before it builds up to that size. Spikes would be defined as a threshold of deviation from the average requirement. Seeing the pool size as a function over time with a small margin on top spikes are identified when projected need exceeds the margin whereas slow increase in demand within the threshold would be covered by the "margin logic".
But maybe I just overcomplicate this and should simple set a big minimum with long pruning interval.
But maybe I just overcomplicate this and should simple set a big minimum with long pruning interval.
That's definitely possible - the simplest may be to decide on a minimum number of connections you always want open, and set Min Pool Size to that. If you know you'll never (or almost never) need more than that, then pruning won't ever do anything. Very simple, and will provide you with 100% predictable timing. Just make sure you don't keep too many idle connections open across many machines running Npgsql, otherwise you'll overload your PostgreSQL with no reason (this is exactly what pruning is meant to reduce).
In fact, you can even do a loop at the start of your program to open that exact number of connections, to make sure everything is ready up-front, rather than having Npgsql open those lazily as they are needed. This would provide even more predictability.
The idea was to establish an equation, more or less connections = frequency * duration to determine the need proactively instead of opening on demand and pruning idle connections. An observatory approach is easier and more flexible, but "blindly" approaches the ideal amount of connections more or less by "create & destroy" until a stable state is achieved.
That's absolutely something you can do - and use that to calculate the value to Min Pool Size for your specific application. However, this isn't something that Npgsql itself can do, because it knows nothing about your application's needs in advance.
At least there was something useful in my proposal. :-)
Your comments were useful in any case, even if some things weren't relevant to the pruning implementation! It's always good to get feedback and ideas from users.
Note sure if it applies universally but the idea was: If we can calculate connections from frequency (defined as new_connection / period) and duration we can estimate total connections as a multiple of new connections in a fragment of the period as n * (new_connections/(period/n)*duration. That way we can resize the pool to the necessary size before it builds up to that size. Spikes would be defined as a threshold of deviation from the average requirement. Seeing the pool size as a function over time with a small margin on top spikes are identified when projected need exceeds the margin whereas slow increase in demand within the threshold would be covered by the "margin logic".
This sounds like it goes again in the direction of calculating your needs (offline), and setting your pool parameters accordingly - which is totally reasonably. As I wrote above, just be aware to not needlessly create too many idle connections (i.e. monitor your PostgreSQL instance carefully to see resource usage), and keep in mind that if you do spike beyond your Min Pool Size, the pruning behavior you're seeing will kick in.
At this point I think it's OK to close this issue, as there's nothing actionable on the Npgsql side - I'll transfer your margin suggestion to npgsql/npgsql#2813. However, feel free to continue posting here with questions/ideas.
Thank you for your reply. While calculating minimum connections up front is possible, I would prefer to avoid those in our application, especially since the machine can "grow" at runtime by reconfiguration or response times and thereby load differs between variants manufactured on the same line. Think of building a small car and a luxury SUV on the same machine. I would prefer if connection pooling would take care of that.
But I read through npgsql/npgsql#2813 and it seems to be the same problem. Without over-complicating the pooling, I want to add a few suggestions for the benefit of all to avoid having multiple mostly identical solutions in applications.
- Add a margin option to the configuration and connection string
- Consider the margin for pruning, but also restore the margin when it gets "used up". Having a buffer of n-connections on top of the current load would drastically reduce the impact of spikes. If a margin connection is used, open a new one to maintain the margin
- Open the configured amount of minimum connections on start or in parallel to the first connection. Running a fast parallel loop in the beginning to fill the pool seems "dirty"
Especially the margin seems like an easy but powerful addition to the current algorithm.
I'm not against modifying the pruning algorithm, but we'd need a very clear, concrete proposal. I'm not sure on what basis Npgsql could calculate the minimum connections you require, except by looking at how many idle connections you typically have in the recent past, which is what it already does... But again, am always open to suggestions.
Consider the margin for pruning, but also restore the margin when it gets "used up". Having a buffer of n-connections on top of the current load would drastically reduce the impact of spikes. If a margin connection is used, open a new one to maintain the margin
Can you provide a more precise definition of what that means? I was referring to a margin over what we calculate to be the need based on idle connection sampling. To be concrete:
- Based on sampling the pool over the last hour, Npgsql sees that your application on average had 5 connections idle.
- The way things currently work, Npgsql prunes 5 idle connections, with the idea that you're now left with exactly what you need.
- A margin here would mean that we only prune 4 or 3, leaving an extra 1-2 connections on top of what we think you need.
Open the configured amount of minimum connections on start or in parallel to the first connection. Running a fast parallel loop in the beginning to fill the pool seems "dirty"
The earliest we can open connections is when you open the first connection (since before that we don't even have a connection string). Doing this on the first connection attempt may be good for some people, but not for everyone, as it could create a longer delay. There are also some possible complications around error handling (what happens if some/all of the connection attempts fail). If it's important for your application to have everything open up-front, I don't think there's anything dirty with a startup loop to do this - it's quite standard to acquire all resources eagerly at startup.
I'm not against modifying the pruning algorithm, but we'd need a very clear, concrete proposal. I'm not sure on what basis Npgsql could calculate the minimum connections you require, except by looking at how many idle connections you typically have in the recent past, which is what it already does... But again, am always open to suggestions.
What I had in mind was measuring frequency and duration of connections and their product (with some constant) yields the amount of necessary connections. Past values could also be combined with the current pool size. Now if either frequency or duration increases, the pool could be increased accordingly before a new request is forced to open another connection. It just assumes a direct correlation between the Frequency * Duration and PoolSize.
Can you provide a more precise definition of what that means?
Let's assume we have a new config value MarginSize = 2. The first feature would be, as you stated above, in each pruning interval to detect of 10 connections, 5 were idle so we prune 3, leaving us 5 used and 2 margin. What I thought of additionally was if a connection is requested and we detect that our pool now has less connections idle connections than MarginSize and preferably MinPoolSize we open enough connections to meet both values, e.g. MinPoolSize until Used + MarginSize > MinPoolSize, when we keep Used + MarginSize.
However both proposals address the same issue: The only way to open a new connection is through a request that can not get another pooled connection, so the damage is already done. As far as I understand we never open more connections than were necessary at least once. So to benefit from the margin or MinPoolSize, I first need to run into the problem of too few connections.
I am looking for a solution where increasing the pool upon request is an exception, not the default. Compare this to the .NET ThreadPool that at any time maintains enough Threads so that the ~500ms of creating a new one rarely shows up in user code. The only exception being Thread.Sleep() or dead locks within pool threads. I am fully aware the two are not fully comparable, just an example.
The earliest we can open connections is when you open the first connection (since before that we don't even have a connection string). Doing this on the first connection attempt may be good for some people, but not for everyone, as it could create a longer delay. There are also some possible complications around error handling (what happens if some/all of the connection attempts fail).
Understandable and valid concerns. What if opening additional connections based on the requirements above is done in parallel after the first connection was successfully opened, but before returning to the caller? Same goes for opening additional connections when the pool is fully used up to restore the margin. While returning a connection to the caller a replacement is created on another thread for the same connection string. As connections obviously have a state the could be Creating and if there is another request while the pool is increasing the caller can reserve one of the Creating connections and still save some time, while simultaneously opening another one. A little bit like this "Pay it forward" you have for coffee. Instead of paying Xms for opening you pay (X-TimeSinceLastRequest)ms.
If it's important for your application to have everything open up-front, I don't think there's anything dirty with a startup loop to do this - it's quite standard to acquire all resources eagerly at startup.
One problem is, that it's not a simple loop, but rather a loop of multiple threads that need to block their connection to make sure I actually create what I need and not receive the same connection n times. And as I said I can't tell for sure how many connections I need at runtime either. The same code runs on several machines which also change their load based on mechanical configuration and produced variant. While I could add the afore mentioned loop with a few config values, I still think its better located within Npgsql, at least in the form of public void PreparePool(string connectionString). I also think it would be reasonable to expect that Npgsql tries to provide MinPoolSize without having to request that amount of connections. That is not meant as criticism, but rather arguing the new behavior might be more intuitive to all users.
Proactively creating connections could also improve the performance for applications that are not even aware of that issue. I can think of a range of scenarios from a sudden burst in requests to a sine wave shaped distribution where this behavior would be benefical. How many users actually take their time to think about their connection pool and response time distribution, they simply use the defaults and as soon as it works move on to the next topic.
I've reread the entire thread and done my best to understand what you're looking for.
If I'm interpreting you correctly, you want the pool to increase its capacity faster than the application requests connections, in order to reduce (ideally eliminate) the number of times a request has to actually wait for a physical connection to be opened. For example, if there is currently 1 idle connection in the pool, and the application requests a connection, we shouldn't just return that idle connection: we should also prospectively open a new physical connection - in the background - so that we continue to maintain extra capacity for the next time a request is made.
In an example implementation, a user could specify that they want the pool to always maintain X idle connections. As load ramps up, the pool will do its best at open time to continuously open more connections than the user requested (in the background), to maintain that extra capacity. Pruning would still reduce extra capacity as load decreases, but would also maintain X idle connections. A bit spike load could still exhaust the extra capacity faster than new connections can be open, so this would not eliminate waiting fully. Is this a fair description of what you'd want?
In effect, this is a tradeoff between between latency for Open, and minimizing the number of unused PostgreSQL connections kept open by the application. I can see this being useful to a lot of people - latency is indeed important, and I suspect many would be willing to pay for that with some extra unused capacity.
Understandable and valid concerns. What if opening additional connections based on the requirements above is done in parallel after the first connection was successfully opened, but before returning to the caller?
That's in line with what I wrote above, but assuming that we don't wait for the additional connections to finish opening before returning to the user. In other words, physical opening of additional connections should happen in the background, and continue in parallel after Open has returned to the user, and the initial Open wouldn't be any slower than it is today.
Regarding my suggestion to manually open many connections at application startup (this is for today, before any of the above is implemented):
If it's important for your application to have everything open up-front, I don't think there's anything dirty with a startup loop to do this - it's quite standard to acquire all resources eagerly at startup.
One problem is, that it's not a simple loop, but rather a loop of multiple threads that need to block their connection to make sure I actually create what I need and not receive the same connection n times.
Well, it could be a simple loop that opens N connections, and than another loop that closes them - that would give you an immediately initial capacity of N idle connections in the pool. The disadvantage is that is that it would happen in serial, so if application startup time is important you could have N Tasks each open 1 connection, wait for a signal, and then close them all. It's a bit of coding but nothing very complicated.
I also think it would be reasonable to expect that Npgsql tries to provide MinPoolSize without having to request that amount of connections.
That's not unreasonable (and wasn't understood as negative criticism :))... and I agree that if we go towards providing extra capacity in the background as above, it would make sense to do this as well.
Proactively creating connections could also improve the performance for applications that are not even aware of that issue. I can think of a range of scenarios from a sudden burst in requests to a sine wave shaped distribution where this behavior would be benefical. How many users actually take their time to think about their connection pool and response time distribution, they simply use the defaults and as soon as it works move on to the next topic.
I generally agree - as long as we also understand that a sudden burst would still make your application stall if the extra capacity isn't there.
If the above is more or less what you want, let me know and I'll open an issue with a short description on https://github.com/npgsql/npgsql for this. Just be aware that while I agree this is valuable, it's unlikely to be done soon because of so much other work I have at the moment (including inside the pool).
If I'm interpreting you correctly, you want the pool to increase its capacity faster than the application requests connections, in order to reduce (ideally eliminate) the number of times a request has to actually wait for a physical connection to be opened. For example, if there is currently 1 idle connection in the pool, and the application requests a connection, we shouldn't just return that idle connection: we should also preemptively open a new physical connection - in the background - so that we continue to maintain extra capacity for the next time a request is made.
Yes, that's it! Perfectly summarized, although in all fairness that is not what I expected when I opened this thread. It somehow evolved over time.
In an example implementation, a user could specify that they want the pool to always maintain X idle connections. As load ramps up, the pool will do its best at open time to continuously open more connections than the user requested (in the background), to maintain that extra capacity. Pruning would still reduce extra capacity as load decreases, but would also maintain X idle connections. A bit spike load could still exhaust the extra capacity faster than new connections can be open, so this would not eliminate waiting fully. Is this a fair description of what you'd want?
Yes exactly. And with the new config value for X each user can decide how much resources he is willing to reserve in order to reduce the risk of waiting. Asides of capacity consideration I guess defining X comes down to expected slope and time to open a connection
In effect, this is a tradeoff between between latency for Open, and minimizing the number of unused PostgreSQL connections kept open by the application. I can see this being useful to a lot of people - latency is indeed important, and I suspect many would be willing to pay for that with some extra unused capacity.
In many cases hardware is cheap and users are impatient, so yes, I agree. I imagine it could actually reduce unused capacity in some applications that currently use big values for MinPoolSize and PruningIntervall to avoid the cost of opening connections.
Well, it could be a simple loop that opens N connections, and than another loop that closes them - that would give you an immediately initial capacity of N idle connections in the pool. The disadvantage is that is that it would happen in serial, so if application startup time is important you could have N Tasks each open 1 connection, wait for a signal, and then close them all. It's a bit of coding but nothing very complicated.
Sorry, you are right I never considered using a connection outside a using statement. Complicated no, just looks ugly. When it comes to code I got OCD. :-D Never the less a valid solution for now.
I generally agree - as long as we also understand that a sudden burst would still make your application stall if the extra capacity isn't there.
Yes, of course. Every optimization has its limits. This is just a numbers game waging the cost of capacity vs. the probability of increased latency. It's not that our application wouldn't tolerate a single delayed request, but it must be so rare that it has no measurable impact on overall output of an assembly line. It is also highly unlikely that a manufacturing system, or any application for that matter, doubles its load in a matter of seconds. There is always some kind of slope and a good value for X should cover that.
If the above is more or less what you want, let me know and I'll open an issue with a short description on https://github.com/npgsql/npgsql for this. Just be aware that while I agree this is valuable, it's unlikely to be done soon because of so much other work I have at the moment (including inside the pool).
Yes please. I would love to contribute but if I am being realistic I can't even properly maintain my own project besides work and family. What I can offer is to test what you come up with in our application and give feedback. And if I somehow find myself suddenly bored on the couch I will definitely take a shot at this.
Opened npgsql/npgsql#2929 to track this, thanks for your thoughts!