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

Most upvoted comments

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.