search

grandchamp / Identity.Dapper

Identity package that uses Dapper instead EntityFramework for use with .NET Core
MIT License
268 stars 62 forks source link

Use MySqlConnector #63

Closed bgrainger closed 6 years ago

bgrainger commented 6 years ago

Fixes #62.

I tested locally with dotnet test -c Release --filter="FullyQualifiedName~Identity.Dapper.Tests.Integration.MySQL". (The docker-compose setup described in the repo made testing very easy—thanks!)

This PR also removes a connection leak (that I observed when checking MySqlConnector logs); this could cause performance problems if the connections were being leaked faster than the connection pool was reclaiming them.

I added a simple (read-only) benchmark and ran it before and after the MySqlConnector change (I included the connection leak change when benchmarking MySql.Data): https://github.com/mysql-net/Identity.Dapper/commit/517ffefd50da395b663dbbabe79ecbd5fd4c989c

BenchmarkDotNet=v0.10.12, OS=Windows 10 Redstone 3 [1709, Fall Creators Update] (10.0.16299.192)
Intel Xeon CPU E5-1650 v3 3.50GHz, 1 CPU, 12 logical cores and 6 physical cores
Frequency=3410072 Hz, Resolution=293.2489 ns, Timer=TSC
.NET Core SDK=2.1.4
  [Host]     : .NET Core 2.0.5 (Framework 4.6.26020.03), 64bit RyuJIT
  DefaultJob : .NET Core 2.0.5 (Framework 4.6.26020.03), 64bit RyuJIT

MySql.Data

Method Mean Error StdDev
FindUserByName 1.615 ms 0.0315 ms 0.0481 ms
GetClaims 3.173 ms 0.0625 ms 0.1449 ms
ChangeEmail 5.094 ms 0.1014 ms 0.1775 ms
ChangePhoneNumber 4.833 ms 0.0961 ms 0.2742 ms

MySqlConnector

Method Mean Error StdDev
FindUserByName 1.563 ms 0.0309 ms 0.0770 ms
GetClaims 3.104 ms 0.0611 ms 0.0895 ms
ChangeEmail 5.037 ms 0.0998 ms 0.1263 ms
ChangePhoneNumber 4.979 ms 0.0895 ms 0.1926 ms

These results are very close; it looks like MySqlConnector could be slightly faster, although the difference is usually within the measured experimental error.

However, MySql.Data doesn't reset the connection by default, so you may hit MySQL bug 77421. This also means that MySqlConnector is doing extra work. If we enable ConnectionReset=true in the MySql.Data connection string (to get equivalent behaviour), we get the following timings for MySql.Data; now MySqlConnector is clearly 5‒6× faster.

Method Mean Error StdDev
FindUserByName 8.704 ms 0.1701 ms 0.2698 ms
GetClaims 17.944 ms 0.3524 ms 0.4457 ms
ChangeEmail 26.620 ms 0.5515 ms 0.9513 ms
ChangePhoneNumber 26.204 ms 0.3112 ms 0.2911 ms
grandchamp commented 6 years ago

Nice! The connection leak was there for all DBMS and i've not seen it. Thanks.