Meeting notes 5/31/2018

[divega]

Attendees: @ajcvickers, @DamianEdwards, @divega, @sebastienros

We discussed today the next steps we can focus on:

Low hanging fruit

• Platform versions for EF Core and Dapper: @sebastienros to adapt @benaadams versions
• Can we do better on Data Update, single and multiple query tests? @sebastienros can take a look. We wonder if @benaadams would be interested in these.
• RavenDB : There is a PR, @sebastienros is tracking it

Longer term investments

• Pipelining:
  • @divega to invite @roji and @bgrainger to next meeting to brainstorm.
  • We want to understand exactly what it is. For example, pipelining vs. multiplexing (out of order) vs. just batching queries from multiple public facing connections into the same physical connection?
  • Once we understand we can find out what we could do for each database.
  • Can have a mode in which we keep the same public API but with limitations (for example, no transactions)?
  • @sebastienros to check if we are already doing batching everywhere we can (we can ask for batching to be revisited if it is disallowed)
• SqlClient:
  • @divega setting up something up to discuss future SqlClient development, performance issues, and the fact that TE wants to include SQL Server numbers.
• Make our infra easy to access for the community: @sebastienros to look into this.
• Mongo (@divega to find contacts, @sebastienros to enable TE
• EF Core: We want to do more optimizations but no concrete plan ATM
• EF6: Once on .NET Core it could be useful, if anything to compare with EF Core
• ADO.NET improvements: lots of System.Data issues in Futures milestones
• Features that cannot be used in benchmarks:
  • Coalescing: Probably a knob once we have pipelining

[benaadams]

[TE] Add SQL Server (on linux) as a database type TechEmpower/FrameworkBenchmarks#3807

[bgrainger]

Pipelining in MySQL

Background

The MySQL protocol is packet-oriented (details). (Note: multiple MySQL packets may be contained in a single TCP packet if they're small enough.) Each MySQL packet (up to 16MiB) has a one-byte sequence number. Client/server interactions are grouped into "conversations" where the first packet has sequence number 0 and each subsequent packet has the next sequence number (with wraparound). If a party in the conversation needs to send multiple packets, it numbers them i, i+1, ..., i+k and the other party replies (if necessary) with i+k+1.

Request/Response

Some conversations have multiple back-and-forth packets, e.g., log on:

<-- Initial handshake (0)
--> Handshake response (1)
<-- Authentication method switch (2)
--> Authentication data (3)
<-- OK (4)

Others (e.g., ping) are very simple, one packet from each side:

--> Ping (0)
<-- OK (1)

A query will result in multiple packets being sent back from the server; note that they're numbered consecutively:

--> "SELECT 1, 2;" Query (0)
<-- Column count (1)
<-- Column 1 (2)
<-- Column 2 (3)
<-- EOF (4)
<-- Row 1 (5)
<-- EOF (6)

Batching

I understand "batching" to mean putting multiple individual SQL statements into one COM_QUERY packet. The server will parse this into multiple SQL statements, then send back multiple result sets. This is easily accomplished in ADO.NET by setting DbCommand.CommandText to multiple semicolon-delimited SQL statements. This looks something like:

--> "SELECT 1; SELECT 2; SELECT 3;" Query (0)
<-- Column count (1)
<-- Column 1 (2)
<-- EOF (4)
<-- Row 1 (5)
<-- EOF + more results (6)
<-- Column count (7)
<-- Column 1 (8)
<-- EOF (9)
<-- Row 1 (10)
<-- EOF + more results (11)
<-- Column count (12)
<-- Column 1 (13)
<-- EOF (14)
<-- Row 1 (15)
<-- EOF (16)

This is done, for example, here: https://github.com/TechEmpower/FrameworkBenchmarks/blob/724a773096f403d149cfaf59b8257465bbf70103/frameworks/CSharp/aspnetcore/Benchmarks/Data/RawDb.cs#L130

Pipelining

By "pipelining" in MySQL, I mean starting multiple separate client conversations before the server response has been received. This is not an official part of the MySQL protocol and some servers (e.g., Amazon Aurora) will simply not respond to it. It looks something like:

--> Ping (0)
--> Reset Connection (0)
--> "SELECT 1;" Query (0)
<-- OK (1)
<-- OK (1)
<-- Column count (1)
<-- Column 1 (2)
<-- EOF (3)
<-- Row 1 (4)
<-- EOF (5)

It appears to work because MySQL Server reads the first packet with sequence ID 0, sends its response, then pulls the next packet off its receive buffer. It reads that as a new conversation with sequence ID 0, so it sends its response to that, and so on. One reason that it works is that very few conversations in the MySQL protocol have multiple replies from both sides; it's generally a client request followed by one or more server packets, then the conversation is implicitly over.

One core difference between batching and pipelining is that pipelining lets you send packets of different kinds; batching is just one outgoing packet with a larger payload.

The only place I've attempted to use it is in the library internals, e.g., sending one TCP packet that includes all the MySQL packets needed to send a "reset connection" request. (As hinted at earlier, this works great with MySQL Server, but not Amazon Aurora, so I reverted it and now all connection resets require multiple network roundtrips.)

This pipelining doesn't map easily on to the ADO.NET API. Conceivably one could let a client execute multiple ExecuteReaderAsync calls before consuming each reader, but because MySQL Server streams the result sets back in order, the client would have to read and dispose each reader in the order they were created. (Right now a client is simply forbidden from calling ExecuteXxx if there is still an open MySqlDataReader.)

Hope this helps; please let me know if anything's unclear.

[benaadams]

By "pipelining" in MySQL, I mean starting multiple separate client conversations before the server response has been received.

As the authentication is the same, could connection pooling work to some degree down the same connection for multiplexing? (e.g. use less "physical connections" than the total "active connections" for fuller TCP packets and less chatter)

--> Ping (0)
--> Reset Connection (0)
--> "SELECT 1;" Query (0)
--> "SELECT 1;" Query (0)
--> "SELECT 1;" Query (0)
--> "SELECT 1;" Query (0)
<-- OK (1)
<-- OK (1)
<-- Column count (1)
<-- Column count (1)
<-- Column count (1)
<-- Column count (1)
<-- Column 1 (2)
<-- Column 1 (2)
<-- Column 1 (2)
<-- Column 1 (2)
<-- EOF (3)
<-- EOF (3)
<-- EOF (3)
<-- EOF (3)
<-- Row 1 (4)
<-- Row 1 (4)
<-- Row 1 (4)
<-- Row 1 (4)
<-- EOF (5)
<-- EOF (5)
<-- EOF (5)
<-- EOF (5)

[sebastienros]

The current model that is suggested by ADO.NET is to pool actual connections and associate them with a single ADO.NET connection. What other "fast" framework do is open a specific number of database connections, and not associate them with the ADO.NET connection itself. But when a command is sent, the ADO.NET connection will look for any currently open connection, or one by affinity, and then use it. It means that two requests, getting two ADO.NET connections, could be using the same database connection, and allowing pipelining. Even without using pipelining this could still improve perf as less database connections are active, and prepared statements are shared more efficiently.

[benaadams]

What other "fast" framework do is open a specific number of database connections, and not associate them with the ADO.NET connection itself.

Base pooling around data stream rather than ADO connection; like?

-- Get from pool
--> "SELECT 1, 2;" Query (0)
<-- Column count (1)
<-- Column 1 (2)
<-- Column 2 (3)
<-- EOF (4)
<-- Row 1 (5)
<-- EOF (6)
-- Return to pool

Makes sense...

[bgrainger]

As the authentication is the same, could connection pooling work to some degree down the same connection for multiplexing?

I think this is theoretically possible, but haven't ever tested it. Presumably each MySqlDataReader would have to know its "order" in the queue (according to the sequence in which requests were sent) and if they were read "out of order" (e.g., on different threads) then it would pull responses off the socket and buffer them on the MySqlDataReader that they were for. Then you'd need to add locking around all I/O, or have a dedicated thread for each socket?

What other "fast" framework do is open a specific number of database connections, and not associate them with the ADO.NET connection itself. But when a command is sent, the ADO.NET connection will look for any currently open connection

The assumption of a dedicated connection is pretty common in client code: variables, temp tables, session settings, etc. are all associated with the current session, which is represented by a MySqlConnection. It would be a breaking change (and quite unexpected) to make a single MySqlConnection object use different physical connections behind the scenes; it could also break client code to multiplex multiple logical connections onto the same physical connection. The driver would have to detect if START TRANSACTION was ever executed, then put that connection into an "exclusive" mode that's no longer shared. It feels like there's a lot of edge cases here. (This could all be opt-in with an EnablePipelining=true connection string option, for clients who know they are using ADO.NET in a compatible way.)

[sebastienros]

It would be a breaking change (and quite unexpected) to make a single MySqlConnection object use different physical connections behind the scenes

I was suggesting the opposite, a single physical connection shared by multiple ado.net ones.

[bgrainger]

It still would cause problems if two non-pipeline-aware clients both execute CREATE TEMPORARY TABLE temp on different MySqlConnection objects that share the same physical server session.

[roji]

A few comments from me...

First, the batching vs. pipelining distinction @bgrainger made above isn't very important for the PostgreSQL case. PostgreSQL does have two sub-protocols - simple and extended. The simple protocol has a single wire message (called Query), which may contain multiple SQL statements separated by semicolons, and which PostgreSQL parses on its end (this is what @bgrainger calls "batching"). Unfortunately the simple protocol is quite crippled - it does not support binary value transfers, parameters or prepared statements.

The extended protocol, on the other hand, requires a series of messages for each SQL statement. Executing an unprepared statements involves sending 5 protocol messages (Parse/Bind/Describe/Execute/Sync), while a prepared statement involves sending 3 (Bind/Execute/Sync). It is not permitted to include multiple SQL statements separated by semicolons, so Npgsql performs rudimentary SQL parsing client-side, splitting the CommandText on semicolons and sending the above set of messages for each parsed SQL statement.

I think that the difference between the above two types of batching is rather uninteresting - it's an internal protocol/implementation detail that doesn't affect much, and I tend to think of both of the above as "batching". Note that PostgreSQL messages do not have any sort of ID or running number, so we can send as many messages as we want up-front; PostgreSQL just reads and executes messages as they come in, and the client's responsibility is to keep track of ordering and parse the results accordingly.

I tend to think of pipelining as the ability of a single client (important!) to send a 2nd request before the 1st one has completed; in ADO.NET terms this would mean invoking DbCommand.Execute*() while another command is still running. This isn't currently supported, and AFAIK there's no TechEmpower test which potentially allows this to be exercised (but I may be mistaken).

Finally there's multiplexing, which to me describes the ability to combine queries from multiple clients into one or more physical connections - we're talking about something like a multiple-producer/multiple-consumer model.

• As @bgrainger correctly pointed out, there's an ordering issue: since clients may attempt to consume query results in any order, it would require buffering entire resultsets in memory, killing the perf benefits of streaming (which I think can be quite important in many scenarios).
• Transactions are obviously out of the question. In theory we could "degrade gracefully": the moment BeginTransaction() is invoked on a DbConnection, a physical connection is bound/affined to its producer in the same way that it is today. This could provide the best of both worlds.
• As @bgrainger mentions above, physical connections are stateful objects (beyond transactions), so there can be other issues (temporary tables, various session parameters and settings...). FWIW, pgbouncer is a popular out-of-process connection pool which deals exactly with this kind of stuff: you can do session, transaction or statement pooling. I think that few usage scenarios out there actually make use of session state in this way, but who knows.
• I think there's nothing preventing a multiplexing driver from implementing the ADO.NET interface (even if it wasn't the original intent). Nothing actually requires a DbConnection to correspond to a physical connection: Open() be a no-op, and the Execute*() methods would dispatch to one of the available physical connections (ideally the least loaded one). They would then await some TaskCompletionSource until the results are received etc.

And now for some perf analysis:

• It's true that in a saturated scenario (all connections busy), multiplexing has its benefit. In the traditional implementation DbConnection.Open() blocks until a physical connection frees up. With multiplexing we can write our query to the server, at least until the TCP window is exhausted.
• On the other hand, as long as we're not saturated (some physical connections are available), this may be a degradation. In the traditional model applications are responsible for keeping the connection open for as short a time as possible; as long as they don't hold open connections longer than they have to, multiplexing shouldn't have any advantage (unless I'm missing something) but only increased overhead (see below).
• The plumbing for implementing multiplexing probably has some cost. The "synchronization point" is moved from DbConnection.Open() (now implemented in Npgsql by a lock-free pool) to DbCommand.Execute*(). For cases where multiple commands are being executed in series, that means we do synchronization more often when multiplexing. In other words, there may be an advantage to allocating your connection for multiple commands rather than allocating it again and again for each command.
• I also suspect that the traditional model may simply have lower overhead, since Execute*() simply and directly awaits on the socket (rather than to a TaskCompletionSource behind plumbing etc.)

I understand that one (some?) of the top TechEmpower performers apparently multiplexes (have not actually looked into this), but do we have any justification for thinking that this is what makes it faster?

[bgrainger]

Unfortunately the simple protocol is quite crippled - it does not support binary value transfers, parameters or prepared statements.

Oh, that reminds me... MySQL also supports a binary protocol, which you have to use if you want to use prepared statements (which are not currently supported in MySqlConnector). Like PG, it doesn't support multiple statements, so MySqlConnector would have to parse the SQL and break them up. It has a three-phase Bind/Execute/Reset model.

Implementing prepared statements would provide an estimated 15% improvement for aspcore-*-my on the TFB benchmarks, so it's definitely a higher priority for me than hypothetical pipelining improvements.

[roji]

Huh, interesting that the protocols are so similar in that way... I guess both started out from a simple text-based protocol and later added the advanced features as a new sub-protocol...

Implementing preparation definitely sounds like a high priority perf gain, you're welcome to help yourself to Npgsql's query parser if it can help you out.

[benaadams]

It is not permitted to include multiple SQL statements separated by semicolons, so Npgsql performs rudimentary SQL parsing client-side, splitting the CommandText on semicolons and sending the above set of messages for each parsed SQL statement.

Interesting... as the final command in data updates is 20 update statements separated by semicolons. What would the best approach be to send this as a batch of commands? (From user api side)