MySqlConnector: Reset session when returning to pool

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 on Open OpenNoPing/OpenNoPingAsync = ConnectionReset=false;ConnectionIdlePingTime=1000 – no reset, no ping on Open OpenDefer/OpenDeferAsync (new code only) = DeferConnectionReset=true – should be the same as Open in the “Before” code

Local Docker Container

Before

Method	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
Open	1,128.7 µs	15.9 µs	14.1 µs	-	-	-	649 B
OpenAsync	1,141.4 µs	14.6 µs	13.0 µs	-	-	-	3498 B
OpenNoReset	548.5 µs	5.0 µs	4.4 µs	-	-	-	648 B
OpenNoResetAsync	640.5 µs	12.2 µs	12.6 µs	-	-	-	2377 B
OpenNoPing	0.8 µs	0 µs	0 µs	0.0772	-	-	648 B
OpenNoPingAsync	0.8 µs	0 µs	0 µs	0.0429	-	-	360 B

After

Method	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
OpenDefer	1,127.2 µs	8.3 µs	7.4 µs	-	-	-	649 B
OpenDeferAsync	1,141.1 µs	8.4 µs	7.0 µs	-	-	-	3497 B
Open	649.6 µs	9.8 µs	9.1 µs	-	-	-	3374 B
OpenAsync	891.2 µs	15.6 µs	27.7 µs	-	-	-	5103 B
OpenNoReset	547.3 µs	3.7 µs	3.1 µs	-	-	-	648 B
OpenNoResetAsync	668.2 µs	6.2 µs	5.5 µs	-	-	-	2376 B
OpenNoPing	0.8 µs	0 µs	0 µs	0.0772	-	-	648 B
OpenNoPingAsync	0.7 µs	0 µs	0 µs	0.0429	-	-	360 B

Remote MySQL (~16ms ping)

Before

Method	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
Open	38.17 ms	0.758 ms	1.308 ms	-	-	-	669 B
OpenAsync	38.98 ms	0.778 ms	2.076 ms	-	-	-	3518 B
OpenNoReset	18.82 ms	0.375 ms	0.488 ms	-	-	-	657 B
OpenNoResetAsync	18.84 ms	0.370 ms	0.564 ms	-	-	-	2395 B

Method	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
OpenDefer	37.88 ms	0.755 ms	1.262 ms	-	-	-	670 B
OpenDeferAsync	38.71 ms	0.744 ms	1.569 ms	-	-	-	3518 B
Open	23.36 ms	0.414 ms	0.443 ms	-	-	-	3396 B
OpenAsync	23.49 ms	0.450 ms	0.518 ms	-	-	-	5110 B
OpenNoReset	19.49 ms	0.387 ms	0.833 ms	-	-	-	657 B
OpenNoResetAsync	19.05 ms	0.374 ms	0.537 ms	-	-	-	2385 B

Summary

In the new code, Open is almost as quick as OpenNoReset 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:

• Starting the clean up when disposing the connection.
• Adding it to the pool without awaiting for the session clean up.
• awaiting 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.