Differences in # of connections between MySql.Data and MySqlConnector

[nvivo]

This is not an actual issue, but more for information purposes. I migrated 30 VMs to MySqlConnector, and I noticed a slight increase in the number of open connections to the database, even though all connection strings and services are the same.

The stable part are the services running without intervention. The middle gap is the upgrade window where servers were up and down all the time, so additional connections are expected.

Interestingly, when doing the math, the # of open connections of all servers multiplied by the maxPoolSize should be around 1400, which matches the MySqlConnector behavior. So MySql.Data might be either smarter or more conservative with connections, even though it uses queues instead of stacks to manage the pool.

This is not being an issue, and it seems to be obbeying the limits. But since there is a difference in behavior, I thought it would be nice to share, in case this is something worth investigating.

[nvivo]

One caveat here is the lack of default command timeout difference that could be causing MySqlConnector to wait longer for some queries to finish while they might be timing out before and releasing the connection. Might be an explanation.

[caleblloyd]

Does your app make use of async? If so you are probably getting much higher concurrency with MySqlConnector. Higher concurrency = need for more parallel connections.

[nvivo]

Can you elaborate more on that @caleblloyd ? Why would that be the case?

I do use async, but I was already using the same number of threads before. So, with MySql.Data, a thread opens a connection, does it's thing and returns it to the pool. With MySqlConnector supporting async correctly, what would change?

[caleblloyd]

MySql.Data uses Sync I/O under the covers, even for async calls. So a single MySql.Data connection server one thread pool thread.

MySqlConnector uses Async I/O for async calls. This means that many MySqlConnector connections can be serviced by one thread pool thread.

We published a Concurrency Benchmark that shows the difference in practice.

If a flood of requests for async connections come in at the same time MySqlConnector will be able to service them all very quickly in parallel even if there are few thread pool threads available, having the side effect of maxing out the Connection Pool. MySql.Data will be limited by the thread pool, however, and may have to wait for thread pool threads to come available, having the side effect of "spreading out" the flood of requests and never hitting the max in the Connection Pool.

[bgrainger]

So MySql.Data might be either smarter or more conservative with connections, even though it uses queues instead of stacks to manage the pool.

This is not being an issue, and it seems to be obbeying the limits. But since there is a difference in behavior, I thought it would be nice to share, in case this is something worth investigating.

Thanks for sharing this; it's a really interesting finding. Along with #302 this suggests that there may possibly be some kind of leak of open connections over time that could be worth digging deeper into.

Is it easy for you to estimate how many open connections "should" be needed based on the number of requests per second to the service(s), the average response time, and the average number of MySQL connections made for each request? Should the number of connections be holding at a steady state instead of slowly increasing over time?

[bgrainger]

Are you using any ORM layer, or directly using the MySqlConnector ADO.NET objects? If the latter, is your code disposing (either via .Dispose or using) every MySqlCommand, MySqlDataReader, MySqlConnection? (The performance of the library when objects aren't disposed is not a scenario that I've tested heavily yet.)

[bgrainger]

One caveat here is the lack of default command timeout difference that could be causing MySqlConnector to wait longer for some queries to finish while they might be timing out before and releasing the connection. Might be an explanation.

@nvivo A command timeout throws a MySqlException (usually with a TimeoutException inner exception). Was your code explicitly catching that and recovering/retrying the statement? If so, and if that was happening frequently, that might indeed explain why the number of connections is increasing with MySqlConnector. The timeout exceptions would have been artificially limiting the number of concurrent statements being executed.

[nvivo]

@caleblloyd Now that I think about, it makes sense. I was thinking about having the same number of threads starting the query, but not what happens after that. I use Akka, and with real async, once it starts awaiting, Akka schedules the continuation of async as a message to the actor. In that case, it considers the message as processed and as you said, that frees the thread in the pool, allowing it to start processing another message in another actor. Given a lot of actors, it may be the case that more actors start processing messages to the point of starting queries than they would with MySql.Data. It's a real possibility.

@bgrainger It's a mix of dapper and EF Core with Pomelo's driver. It's always either
using (var ctx = contextFactory()){ } for EF or
using (var cn = await openConnectionAsync()) { } for dapper.

I noticed this morning that although I have a max pool size of 45 for most services, some of them had a more than 70 connections open (counting through mariadb processlist table).

I don't have an estimate of the ideal, but I have a history. Looking for the last 2 weeks:

There is definitely a difference. My guess now is it's a mix of being able to better use the thread pool and something allowing more connections to be open than the max pool (although I'm not sure all services have a max pool set, most of them have - that specific one with 70 connections was set to 45). But it doesn't look like a permanent leak so far, otherwise I would have seen it increasing constantly over the last 2 days, and it decreases from time to time.

[nvivo]

Okay, I may have some evidence that indeed something strange is happening and there may be a leak here. I added some reflection to log some internals, specifically the counts of m_sessions and m_leasedSessions in ConnectionPool. This is what I get (note these are all mixed hosts):

This is what the ProcessList table shows me:

The Max Pool Size is 45. It started as 45, after a few seconds it started to grow 1 every second or so per host.

When I look at one specific host at some point, this is what I see:

But mysql shows me:

If the watchdog runs every 3 minutes or so, there shouldn't be sleeping connections open for more than that. Before adding the log, there were open connections with 20000 seconds. If the pattern from the log means what I think it means, it seems I'm using 40 connections when the service starts, but then it drops to only 1-3 per server after that. Still, I had dozens of open connections with 20000+ seconds open in sleeping state.

Makes sense?

[bgrainger]

Thanks; this is fantastic debugging info. I'm still not quite sure exactly what it means yet, but it definitely provides a direction for further investigation.

[nvivo]

Hi, some additional evidence to help with this. This is the processlist since yesterday:

https://docs.google.com/spreadsheets/d/1zoCpy0JAneE3ecdZEo6FYJQpH7hMS80XaUjCuBpXkpE/edit?usp=sharing

These processes come from a cluster of 25 servers with the same service, same connection string and same access pattern. Aside from basic settings, I override these settings:

Maximum Pool Size=40; Connect Timeout=120; Connection Lifetime=240;

Some interesting things here:

1. Queries are happening on most recent connections, old ones are sleeping
2. Lots of connections are sleeping for more than ConnectionIdleTimeout
3. There is almost a pattern in the time interval between old connections in increments of 12.5 minutes. The interval may be because I run some process that peaks connection use at these intervals or something that happens in the driver that is forgetting to close them at these intervals. In any case, it looks like every now and then the driver is letting one connection open for some reason and it's not counting them in the m_sessions list.

Edit: The incremenets seem to have a pattern, but it's actually different for each server.

[bgrainger]

1. This is by design. MySqlConnector uses a stack so that the most recently-used connection keeps being used, so extra ones can become idle and be cleaned up. Compare https://bugs.mysql.com/bug.php?id=66476.
2. I can't explain this yet (and haven't been able to reproduce it with a simple test app); will keep investigating.
3. It definitely does seem like a pattern but I can't explain it yet.

[nvivo]

For 1 makes perfect sense, just didn't connect the dots.

Just a note, this morning I had to restart all servers as no new connections were able to be open. From the logs this was gradual from 2am to 6am.

I'm not discarding a bug in my code, since the migration to EF Core required some additional changes, but since the driver is not seeing the connections in the leased or the idle sessions, my bet is in a bug in the driver. For now I'm adding some periodic housekeeping to a background job to kill long standing connections.

[bgrainger]

Just a note, this morning I had to restart all servers as no new connections were able to be open.

This makes me wonder if this and #302 share the same underlying issue.

For now I'm adding some periodic housekeeping to a background job to kill long standing connections.

Thanks for your help working through this, and sorry you have to employ a workaround; I want to figure this out.

Are you running the client on Linux or Windows?

[bgrainger]

OK, I can repro long-running idle connections if--in my test app--I periodically omit calling MySqlConnection.Dispose. So it seems like the code to recover "leaked" sessions isn't working properly. (However, I still don't ever see more than MaximumPoolSize concurrent connections, even in this scenario.) But there's definitely a connection recovery bug to fix first.