Run "MySqlDirect" benchmark #1676
Comments
@bgrainger Nice! I had this next on my list. Crossing it off now. |
|
I now have three separate commits to benchmark independently (for
|
|
@bgrainger I've done a bit of infra work and should be able to launch runs for you pretty easily now. However, can you please tag the commits you want me to run? I'm not able to specify a raw SHA1 with the tooling at the moment (dotnet/crank#311). I also highly recommend that we do a run on the commit before the changes each time, to exclude unrelated perf changes from creeping in (so you'd need to tag that too). So basically the easiest would be to get a list of tags for running. |
|
Tags to run in https://github.com/bgrainger/FrameworkBenchmarks:
|
|
Here are the results. For
BTW on the PS As we're just starting out with these runs, please be suspicious - it's very easy to accidentally get the command-line wrong or mess up in some other way. Don't hesitate to ask me to re-verify a specific run or introduce sanity checks to make sure things are right, etc. Crank cmdlines |
|
@roji A couple of questions:
|
|
That would put |
The benchmark application is a docker image, so which .NET is used depends on how that image is configured/built. At least in .NET, TechEmpower generally uses released components, so this should be 5.0.x.
I can post everything that comes out of the run, sure.
Unfortunately not... It's all completely internal and there's no outside access... But I can copy-paste out. |
|
BTW no need to tag commit hashes any more (thanks @sebastienros).
Why not give it a try :) I'll be happy to run it for you. Am also curious to know where exactly the big changes are with the regular MySqlConnector! |
The major change is that it doesn't use MySqlConnector at all! 😀
Some MySqlConnector overhead that's omitted: parsing server handshakes to support backends other than MySQL 8.0; complicated state machines to adapt the MySQL protocol to |
|
I would be very interested to see this run against the other benchmark types as well (not just fortunes). |
|
That sounds great :) Should we just call it MySqlConnector.Core? 🤣 So... (As you probably know) I'm interested by how much ADO.NET adds a necessary overhead, i.e. would an ADO.NET shim over this reduce perf and by how much. For example, I'm interested in how MySqlDataReader forces you to do complicated (and bad-for-perf) state machines to adapt the MySQL protocol. But of course, that's not an urgent question or anything, just something I'm interested in. I can launch runs against other benchmarks too - just give me commits and URLs. (BTW server handshakes are something that happens only when opening physical connection, no? So with pooling shouldn't be important?) |
Correct; the per-connection cost should be extremely low (amortised over all the requests), so it was a bit of a red herring to mention it.
But there is |
|
@roji I have a "Platform" benchmark ( Not quite sure if I did this right:
|
|
@bgrainger there are some technical issues running the above (probably related to the fact that it's platform), I'll be looking into it over the weekend... After we get up and running it should be very easy/fast to send me cmdlines and get the results. |
|
@roji I have another submission, for both Benchmarks ( Benchmarks: PlatformBenchmarks: won't try to guess the full command-line but also (BTW, would still like to see the PlatformBenchmarks for 4e6fab769dabce3eab38aadb3348ad88c0e2740c as requested in #1676 (comment) to compare before/after prepared statements.) |
I just realised that pipelining was only used for "multiple queries", so it would have had no effect on "fortunes" (this benchmark). Thus, this must just be experimental variation (of 0.2%). |
|
Another test (for both Benchmarks |
|
OK, got everything working. Here are the cmdlines for the platform and non-platform benchmarks: Non-platform:Platform:Note that you can specify a commit hash, but you must also specify a branch from where it can be reached. The rest probably won't need to change.
Interesting that platform and non-platform are so close... Note that I haven't looked into the Docker images themselves to see what things look like. Average run results4e6fab769dabce3eab38aadb3348ad88c0e2740c, aspcore-mysql97f150543d97a7bd31a3d2f17694a291246d06df, aspcore-mysql7f236842d71208b17a296952a621f1f89a272539, aspcore-mysql97f150543d97a7bd31a3d2f17694a291246d06df, aspcore-mw-mysql7f236842d71208b17a296952a621f1f89a272539, aspcore-mw-mysql |
|
If this would make it into a publicly available (and realistically usable) library without much additional overhead, it would currently be the top ASP.NET Core middleware fortunes benchmark period. On a second thought, the ASP.NET Core platform benchmarks use very specialized classes, like So as far as I can see, we could actually already run the platform benchmarks with this code. Of course this would not be a fit for the realistic MW and MVC benchmarks, unless it is publicly available (and realistically usable) through a library (as seems to be a requirement for those benchmarks, which also makes a lot of sense). |
|
Yeah, the interesting thing is if this level of perf can be maintained while making it into a more-or-less fully-featured driver (e.g. support for transactions, cancellation, whatever). And as I keep harping on, what the perf hit of an ADO.NET shim would be like 😉
I'm not sure I agree there. Once again, it's very different to write benchmark code that's very minimal and optimized, and to use a web framework or database driver that have been deliberately stripped (or written from the ground up) to not support mainstream "required" features (see discussion in TechEmpower/FrameworkBenchmarks#6566 (comment)). At least, as far as I'm aware, the top runners use database drivers which are realistically-usable, "standard" drivers in their language (we may want to verify that). Otherwise everyone would just fling together the minimal code needed to transmit the binary wire representation for TechEmpower Fortunes to their database - that doesn't seem very valuable. |
Yeah, that is really the hard part to make this publicly available. Though I guess restricting it in some ways might be good, because moving from a general DAL model to a many individual ones is also kind of a troubling development. So keeping it slim might be preferable design goal.
So, I checked this before I was writing the post above.
So the
I think this is very valuable. Because we learn the overhead of the ADO.NET provider (or the equivalent for other frameworks), which we would not know otherwise. It is pretty much the maximum in database access performance you can get from an application. We even indirectly learn, how effective the different database systems are in comparison to each other. We established in PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1408 (comment), that measuring the difference of the middleware and MVC approaches in contrast to the raw server approach is important. In the end, it is the same tradeoff on the database access layer, as on the web request process level. Trading reasonable/sane comfort for custom/insane code and raw performance under the umbrella of the In fact, I am convinced that this provides so much high quality information that is not available otherwise, that I don't even think it is a question of whether benchmarks like that should be added to the TFB, but just how this should be done: Conceptionally, the I think it could be debated, whether the From my point of view, the only real discussion here is, whether this can be categorized as I guess my main point here is: The fact that nobody else has used this approach so far should not matter for whether a new benchmark is viable or not. Instead, us potentially pushing this approach should lead to other framework benchmarks adding a direct approach as well, which will not just make this benchmark comparable to those other frameworks/databases, but also their framework specific default implementation overhead measurable within their own framework boundary. I think benchmarks like this one provide very valuable information, and will lead to more optimized/performant database providers (not just using this "direct" approach, but really in general), because when the gap is big between the "direct" approach and the default database provider, there will be a natural urge to close it as much as reasonably possible, which is really a gain for everybody. And since at this point, database systems have a huge impact on RPS, improving database access performance in general will increase throughput, and as far as I understand, this is one of the main goals of the TFB, I don't see how benchmarks like this one are not beneficial to the TFB. |
I think there's some conflation here... It's one thing to write a full-featured, non-ADO.NET driver that supports the basic/standard functionality that's expected from a database driver. I think that's valuable, and indeed could help understand if ADO.NET itself imposes a perf penalty as an abstraction (e.g. by writing a well-optimized ADO.NET shim on top of it). It's quite another thing to slap together a thing that only supports some very basic scenarios - fitted to make TE Fortunes pass - and call that a driver. Aside from maybe comparing MySQL vs. PG (which is explicitly a non-goal of TE), I don't see what anyone learns from it. (note that I'm not saying anything at all about the current state of MySqlConnector.Core!) To make this very concrete, let's say you implement a fast, low-level driver that doesn't support cancellation, transactions, or even parameterization. Chances are it's going to be pretty fast, because you've decided not to implement a lot of standard DB functionality. This doesn't tell us anything about ADO.NET as an abstraction, since you can write an ADO.NET driver that doesn't support the above. Maybe, once you add these essential things, you'll be more or less back where you started. So I'm not arguing about the API - ADO.NET or not. All I'm saying, is that for this to be useful in benchmarking and comparisons, it needs to reach some sort of minimal level of functionality as a database driver, so that we have a rough apples-to-apples comparison. |
This appears to be the case to me. The top MySQL entrants (which are all PHP) seem to use the PHP MySQL PDO API (which maybe wraps the official C client? not sure). I'm generally with @roji here that (to use an extreme example) a raw TCP sockets implementation that sends the minimal necessary HTTP & MySQL packets that just happens to be written in C# is not a "useful" benchmark to include in TFB. (We may want to run it privately to evaluate the costs of various pieces of the framework, but don't need to upstream it to TFB.) |
|
I think you guys are missing the following points here:
Nobody is expecting users to implement their own DAL for a realistic approach, in the same way that nobody expect users to implement their own web request processing pipeline. Or to rephrase this, are you not interested to know the performance of the underlying database system and the overhead of the ADO.NET provider? Because you get this information with a benchmark like this one (and I am not even aware of a credible source, that would provide you with similar information, so this is pretty unique out there). And while it is true, that comparing different database systems to each other is not one of the TFB's primary goals (though in practice, it is definitely used for this), it is to increase awareness for performance issues and to move framework and database developers to remove those if possible. And that is the natural result of benchmarks like this one. If you see what can theoretically be achieved and what the current state of things is, you will look at ways to improve them, as we currently do by thinking about ways to improve MySqlConnector, e.g. through an auto preparation feature. Finally, this benchmark gives a good measurement for database level improvements between runs, in contrast to DAL level improvements or web framework improvements. This is not just interesting information in general, but should also further stimulate database developers to improve their systems, since their work can be measured (mostly) independent of the DALs. |
If everyone else is using incomplete or stripped-down drivers created purely to show better perf numbers, then maybe. I don't think that's the case; if other platforms use real-world drivers, than we learn nothing from the comparison with them (not apples-to-apples).
Oh, I definitely feel strongly that we can't call anything "realistic" that runs on an incomplete DB driver. Re doing "stripped" scenarios... There's a huge amount of scenarios we could potentially benchmark and publish; the question is what we can learn, and I'm just not seeing that. Otherwise we're just working on something with little value, and creating more results which confuse/obscure more than they reveal. But I don't have super-strong feelings here - as long as there's no interference with the mainstream benchmarks, you can add more if you feel that's important.
Aside from it being a non-goal of the TE benchmarks to measure DB performance (as here, they are simply ill-suited to compare raw database performance; they are full-stack benchmarks which include many moving parts that would make this imprecise in various ways. If I wanted to compare PG and MySQL, I'd use a an extremely minimal client program (definitely no web, 2 machines instead of 3), to isolate the DB as much as possible. Such tools and benchmarks undoubtedly exist out there.
I'm not really interested in knowing the overhead of features I consider necessary, like cancellation, transactions or parameterization, because I consider them standard (again, regardless of the ADO.NET abstraction). I don't consider a DB driver complete without certain things, so why would I be interested in how much perf those things take? In case it's not clear, I love what @bgrainger is doing - and that's why I'm investing my time in running experiments on our perf lab. I just don't see the point of including an early prototype in the official benchmarks - here - so that they appear in the ranks and give everyone the impression that there's a stable, feature-complete driver they should be using. I'll take a step back here, since we've been discussing a lot. My main priority here is to keep us from regressing any of our mainstream/official benchmarks; so I indeed want to keep a close eye on changes there. Anything else is really less important for me. I'd rather we avoided producing too much noise through scenarios whose exact meaning and "comparability" with other scenario is unclear; but if you feel strongly that various stripped experiments should be added, and the TechEmpower people think that's valuable, then by all means go ahead. |
We are in full agreement there. But that is not my argument. My argument is, that we learn important information by comparing the
From my previous post:
Also, keep in mind that the benchmarks are not just about learning (assuming this is one of their goals). They are also explicitly about encouraging performance improvements for all of the involved components. I addressed this above:
I think that is a fair point. However, adding some kind of acceptable baseline will greatly help in measuring improvements to the DAL layer in general. It should also lead to more performance improvements in general (see previous point). Also, keep in mind that this statement is a counter argument to the one you gave in PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1408 (comment) (mainly the first two paragraphs).
I am honestly very interested in those. Something of quality, that continuously measures database performance for realistic scenarios in high concurrency situations, and is open source. So if you have something concrete here, please drop a link, as this will greatly help us with our general optimization improvements.
So when this is part of the stripped approach, it is not displayed by default, and should not mislead users (also, its description should ideally state its intention).
And I think this is probably the main area where our personal interests diverge, since I am quite interested in measuring those in a highly concurrent environment (which I cannot do well locally). Because if we can indirectly calculate, how much time is spend on average in a particular provider (e.g. a specific ADO.NET provider) in particular scenarios, we can optimize the default provider much more effectively, because we know what is just "database server noise" and what is DAL level overhead (kind of).
Yeah, I did not really intend to go that much down the rabbit hole. I guess it was not completely unexpected by looking at how it went the last time I posted something of that nature. Though the last one (even though at the time I was just publishing some findings) could have had potential consequences to other tests if actually implemented. This one here really hasn't. My concrete intention here for us for now was, that we use the experimental work of @bgrainger in a positive way to improve performance for MySqlConnector and thereby for Pomelo. Adding it as a stripped test seems like a simple and effective way of doing so, without having to increase your workload for running benchmarks for us when we make changes in that area, so we know what is database time and what is DAL time (kind of).
I think there would be no harm in doing this and we could always remove it later, if it proves to be of no help. Adding/removing benchmarks to the TFB seems to be a common practice. |
That's what I said:
But I'm still thinking:
I'm getting faster turnaround on benchmark results here, without generating unnecessary work for the TFB maintainers to keep merging WIP prototype code. |
|
@bgrainger If you think its not worth it, we are not doing it. |
|
Am closing this this there's nothing specifically actionable, but we can keep posting run requests and results here. Another way is to ping me on PRs on MySqlConnector... Basically whatever works. |
Please run
aspcore-mw-ado-myandaspcore-mw-mysqlfrom the tip of https://github.com/bgrainger/FrameworkBenchmarks/tree/mysql-direct and report the benchmark results.This adds a "direct" implementation of the MySQL protocol that bypasses all of ADO.NET (inspired by some of the discussion on dotnet/datalab#6) to see what's possible with a dedicated "direct" MySQL API (which many other languages/frameworks expose).
If this is promising, further work could include:
CLIENT_OPTIONAL_RESULTSET_METADATA: MySQL: Can providers leverage CLIENT_OPTIONAL_RESULTSET_METADATA? DataAccessPerformance#34Long-term future work could include packaging this API up into an (experimental?) NuGet package and submitting this as another entrant in TFB (since .NET is at risk of falling out of the top ten for MySQL).
The text was updated successfully, but these errors were encountered: