MySqlConnector: Reset session when returning to pool
I currently have a use case were I get far better performance setting ConnectionReset
to false
but I still want the connection reset for safety purposes.
I was thinking that when ReturnToPool
is called the connection could be reset there (if the enabled) instead of when it’s pulled out of the pool, leaving the reset to be done in the background (before being added back into the pool).
It would however mean that the pool is easier to deplete.
About this issue
- Original URL
- State: open
- Created 7 years ago
- Comments: 29 (20 by maintainers)
Commits related to this issue
- Reset connection when returned to pool. Fixes #178 — committed to mysql-net/MySqlConnector by bgrainger 7 years ago
- Reset connection asynchronously. Fixes #178 — committed to bgrainger/MySqlConnector by bgrainger 7 years ago
- Reset connections in background. Fixes #178 — committed to mysql-net/MySqlConnector by bgrainger 4 years ago
- Reset connections in background. Fixes #178 — committed to mysql-net/MySqlConnector by bgrainger 4 years ago
Another attempt at solving this problem: https://github.com/mysql-net/MySqlConnector/tree/reset-connection-in-background
The new solution is conceptually very simple: closing/disposing a connection starts resetting it (asynchronously) in the background, then returns immediately. A background thread awaits the reset, then returns the connection to the pool. This may cause a few extra connections to be used while the reset happens.
This is available for testing in 1.3.0-beta.1.
Benchmarks from the new code:
Open
/OpenAsync
= default connection string options (SslMode=None
)OpenNoReset
/OpenNoResetAsync
=ConnectionReset=false
– no connection reset, but still pings onOpen
OpenNoPing
/OpenNoPingAsync
=ConnectionReset=false;ConnectionIdlePingTime=1000
– no reset, no ping onOpen
OpenDefer
/OpenDeferAsync
(new code only) =DeferConnectionReset=true
– should be the same asOpen
in the “Before” codeLocal Docker Container
Before
After
Remote MySQL (~16ms ping)
Before
Summary
In the new code,
Open
is almost as quick asOpenNoReset
in the old code, but brings the benefit that the connection is still reset and in a known good state when it’s retrieved from the pool. For ultimate speed, you can disable pinging (with the caveat that a broken connection may be returned and you need retry logic around any database operation, but you should generally have that anyway).BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19042 Intel Core i7-10875H CPU 2.30GHz, 1 CPU, 16 logical and 8 physical cores .NET Core SDK=5.0.200-preview.20601.7 [Host] : .NET Core 5.0.1 (CoreCLR 5.0.120.57516, CoreFX 5.0.120.57516), X64 RyuJIT Job-NRYEMX : .NET Core 5.0.1 (CoreCLR 5.0.120.57516, CoreFX 5.0.120.57516), X64 RyuJIT
Platform=X64 Runtime=.NET Core 5.0
Added in 1.3.0.
An approach that I think would be equivalent to mine:
await
ing for the session clean up.await
ing the clean up when getting it connections from the pool.What is the downside of doing it like this?
Sorry if I am missing something obvious, I’m not very knowledgable about
async
/Task
internals and their implications when developing general-purpose libraries.