Connection.Open() slow

[kodeo]

I'm finding Connection.Open() very slow when using a remote database. It's taking approximately 3x the ping time. Is it making three round trips?

[bgrainger]

Yes (or more). This is primarily due to attempting to put the connection in a known good state.

If you are not using connection pooling, the client needs to connect to the server, receive a handshake packet, send an auth/login packet, and receive a success response.

If you are using connection pooling, then a connection is pulled from the pool and a ping packet is sent (to confirm the connection is still alive). [Note: this may be able to be removed; see below.] Then, if Connection Reset=True (the default), a reset connection packet is sent (>= 5.7) or a change user packet is sent (<= 5.6). In the former case, a SET NAMES command has to be issued to work around MySQL bug 85185. In the latter case, optimistic reauthentication always fails with MySQL Server (a server-side bug, not present in MariaDB), so another auth packet needs to be sent.

You can achieve a speedup (at the cost of correctness and safety) by not resetting the connection; set Connection Reset=false in the connection string. This is the default in Connector/NET, but see MySQL bug 77421. I would strongly advise against this unless you never use session variables or change any other session state.

See #178 for the suggestion to reset the connection (in the background) when it's returned to the pool, which could eliminate 1-2 roundtrips when retrieving a pooled connector.

Even without implementing the suggestion in that case, I think it should be safe for us to remove the "ping" when ConnectionReset=true; if we attempt to reset the connection but get a socket error, we can assume that the connection has been closed, evict that connection from the pool, and open a new one. That should remove one round-trip.

What is the network latency between your client and the MySQL Server?

[kodeo]

My database server is a 25ms round trip from my web server. I'm trying to migrate away from ASP.NET 4 / Entity Framework / SQL Server, where I've had the luxury of not having to worry about this very much because the the whole context creation-query-disposal process was completing in single round trip time. Now on dotnetcore and MySQL with three round trips to open the connection plus a fourth for the query, suddenly it's noticeable.

Thanks for the Connection Reset=false advice. This gets the time down to 2x trips, and I think I can live with the risks in this particular case. Your docs currently say Connection Reset=false is the default by the way.

Thanks for such a fast and thorough response.

[bgrainger]

Your docs currently say Connection Reset=false is the default by the way.

Thanks for pointing this out (it was very out-of-date); fixed in ec3d086.

[bgrainger]

Even without implementing the suggestion in that case, I think it should be safe for us to remove the "ping" when ConnectionReset=true; if we attempt to reset the connection but get a socket error, we can assume that the connection has been closed, evict that connection from the pool, and open a new one. That should remove one round-trip.

This has been implemented in 0.26.4.

[bgrainger]

I don't think there's anything more for this case that isn't already covered elsewhere. Summary:

• As a workaround for now, use ConnectionReset=false for better performance at the cost of correctness
• Reset session when returning to pool #178 covers the idea of resetting the connection in the background for both perf and correctness