Database Driver benchmarks

[Wraith2]

While working on performance improvements to SqlClient I've been using BDN to measure test case improvements. It's been mentioned several times now that it might be worth adding a suite of performance tests into this repository so we have tracked performance measures for ADO drivers.

How would we go about setting those benchmarks up? The primary requirement for most of them is going to be the ability to locate and connect to a network based database server of some kind. Is there a specific way that exists that this should be done? If not does anyone have any good ideas on how best to do this?

/cc @roji

[billwert]

Hello!

This is a good idea. Unfortunately everything we're doing in this repo at the moment is heavily aimed at single machine scenarios. (Microbenchmarks, UI apps, etc.)

@sebastienros - is this the sort of scenario that's perhaps easier to run in your lab?

[sebastienros]

Yes, this is something that we already do, and that @roji uses for the PostgresQL driver too.

When external contributors want to verify their improvements I gladly run the benchmarks on our machines to validate the results. I can also provide profiles (linux and windows). We can also test on diverse machine sets.

@Wraith2 if you have local builds you want to benchmark ping me on twitter/gitter/skype/email.

[billwert]

I'm going to close this issue - let me know if I can help further.

[roji]

@Wraith2 the way I see this, the first step would be simply to start creating benchmarks into this repo, without necessarily making them run automatically as part of a continuous perf platform. I imagine these benchmarks as executing by default on LocalDB (simply because it is always available, on Windows at least) with an environment variable allowing users to point the benchmark at a real SQL Server instance. Note that this is how the EF Core functional tests (and benchmark suite) operate.

This first step would already allow us to exchange standardized, precise benchmark results on how exactly proposed changes affect perf - which is what we really need here. Enabling automatic runs would be a separate second step, involving setting up a reliable database server etc, so I wouldn't want us to block on this.

Note that I started hacking up a first benchmark, and could probably get something ready in a few days (lots going on at the moment) - but it's really no big deal and you could do it as well for SqlClient calls affected by your PRs.

[Wraith2]

As long as we're fine with local only comparisons that's ok. Getting a standard-ish set of ADO benchmarks setup somewhere is what we've discussed in several PR's now and were pointed here as the correct place. Getting an overview of whether a particular change has effect outside a given PR is good for oversight.

So how generic could we be? and where do we put them in the folder hierarchy? because a lot of basic things can be shared between drivers by setting a connection string and driver factory class in environment variables. Specific SqlClient things (like table valued parameters etc) can be put in System.Data.SqlClient since they relate to part of corefx, I imagine drivers not under the dotnet/ org will have to be external but sharing standardised ADO stuff could be useful.

I'm in no rush to get benches merged I just want to sort out whether this is he right place as suggested.

Oh, and I don't have LocalDB installed but my setup is a bit peculiar...

[billwert]

If they run on a single machine and are good about cleanup, etc, this is a great place for them.

Ordinarily I would recommend putting them in the existing Microbenchmarks project we have. It sounds like we might want something with some portability, though, so a new project might make more sense.

You could start under src\benchmarks\ADO and go from there. (If there's a better name than ADO that's fine, of course.)

[Wraith2]

There will need to be a simple database setup for each particular driver but if we're going to try and share most of the tests we'll probably end up with a simple structure which maps to the things defined in System.Data. Users will need to be responsible for their local setup something like we have for the SqlClient manual tests https://github.com/dotnet/corefx/tree/master/src/System.Data.SqlClient/tests/ManualTests

Is the method of using environment to setup the factory instance useable/acceptable? I'd like to make the tests usable for multiple drivers with minimum fuss if possible.

[billwert]

Is the method of using environment to setup the factory instance useable/acceptable? I'd like to make the tests usable for multiple drivers with minimum fuss if possible.

Can you elaborate a bit more? In general the tests should not depend on anything being on the machine to begin with, and clean up any state they changed when they're done. We can't pre-install software on the test machines as they come out of a pool which is shared with other testing.

[Wraith2]

The ADO api supports a factory pattern. You provide a factory type which is then instantiated and used through the base classes. So There's System.Data.SqlClient.SqlClientFactory which you instantiate (probably through activator) and cast into a DbProviderFactory then everything works with Db* typed objects.

We can make those static dependencies restored through nuget as is done with https://github.com/aspnet/DataAccessPerformance/tree/master/src/BenchmarkDb for dotnet repo things (sqlclient, odbc, oledb). External users could swap in their own dependency as long as it can ve sourced by in the packet restore somehow.

The purpose would be to avoid having to maintain the same test multiple times and allow authors of ADO libraries like @roji to swap in their own driver and use the same suite of tests.

[roji]

@Wraith2 it's definitely possible to think about a provider-independent benchmark suite, which each provider would then be able to implement. This is somewhat similar @bgrainger's work on an ADO.NET "specification test suite" (see https://github.com/mysql-net/AdoNetApiTest), which we hope to evolve and which should help detect variation in ADO provider behavior. The same thing could be done for a standardized benchmarking suite - I can definitely see this as a valuable thing.

However, I think that such a provider-independent benchmark suite may be a bit out of scope for the dotnet/performance repo. For one thing, in order to be valuable it would need to be published as a nuget for consumption by providers.

For this reason, I'd propose that for now we limit the scope and just concentrate on having benchmarks for SqlClient that match the specific work you're trying to do - just in the interest of moving quickly and unblocking your work. At some point we should definitely look at what various providers are doing in terms of benchmarks, and possibly unify that into a single "standardized" suite, but I just wouldn't want us to get hung up on that right away.

Of course, if you'd rather work on standardizing benchmarks first that's entirely possible too, and I can help from the Npgsql side where there already are quite a few benchmarks we use.

[bgrainger]

I've worked on increasing MySql ADO.NET performance for https://github.com/TechEmpower/FrameworkBenchmarks. In my experience, the benchmark results are highly dependent on the test environment hardware. Without TFB's hardware (three 28-core Xeons with 10GbE), the top results that I can get locally start tapering off at a much lower requests/second level.

As a result, I can't tell any more if my changes will improve ADO.NET performance because I'm already maxing out my system resources. I suspect a similar problem might apply here, particularly if you're envisioning a "portable" approach that can run on arbitrary machines in a testing pool (e.g., running the server in a Docker container). That is, the maximum performance a benchmark can reach will be limited by the test hardware instead of the code, and improving the code will have no visible effect (even though it would be faster on more powerful hardware). To effectively run benchmarks, you might need dedicated testing hardware that is only used for running these benchmarks. It also seems likely that at the high end, the benchmark results will be highly affected by DB server tuning.

(Side note: I'd love to have access to a private fork of TFB that continually runs just the .NET tests (similar to https://tfb-status.techempower.com/) where I could push commits to master and get them tested immediately.)

There may still be aspects we could benchmark, such as memory allocation, or perf for local-only operations.

[roji]

@bgrainger I've had a very similar experience. However, I've learned to distinguish between two kinds of perf efforts.

First, there's the kind of optimizations that show up on microbenchmarks. Reducing the memory allocations done in some code path (e.g. reading a column from DbDataReader), reducing the time spent in such a method, etc. etc. It is generally easy to write BDN microbenchmarks for these, and improvements show up rather easily once the change is done.

Then, there are the kinds of stuff which microbenchmarks do not capture well. One crucial point is anything having to do with concurrency (which BDN currently does not benchmark well), or other, wider resource questions. Solutions tend to be a bit more architectural/systemic in this type, rather than making a specific method a bit faster. As you say, slower dev machines frequently bottleneck on something else (e.g. CPU) before reaching a point where optimizations start showing up.

There is typically a lot of work of the first kind to be done - optimizing specific code paths can go a long way, assuming work is done on the hot paths of course. I suspect (or perhaps hope) there is a lot of potential and low hanging fruits of this sort within SqlClient.

(the above distinction isn't meant to be scientific or anything - it's just a very pragmatic overview of my experience optimizing Npgsql).

[bgrainger]

I agree: some of my biggest perf wins have simply come from reducing allocations in a particular code path, which is very suitable for microbenchmarking.

[Wraith2]

I suspect (or perhaps hope) there is a lot of potential and low hanging fruits of this sort within SqlClient.

Some, not as much as there used to be. Sadly I don't have any comparative numbers for the 2.2 version and 3.0 versions but it might be interesting to try and create some.

[adamsitnik]

I think that we can split the potential System.Data benchmarks into two categories similar to unit and integration tests:

• benchmarks that don't require a real database. We can mock the input/data and write some nice microbenchmarks that shows the improvements in the "units".
• benchmarks that require real db and non-trivial setup but show the perf gains in real-world scenarios.

FWIW I would recommend starting with adding some simple BDN micro-benchmarks for the types that can use mocks to the corefx subfolder.

[roji]

@adamsitnik I'm not sure it's practical to benchmark SqlClient without a real database - I know very little about its internal architecture but I'd be surprised if parts of it are mockable.

Note that on Windows the benchmarks can be run against LocalDB, which is "zero-setup". On Linux/Mac a real SQL Server needs to be set up, though.

[Wraith2]

There is some work that can be done without a real database but not very much. Some of the existing functional tests use a fake TDS server that can respond to simple requests. It's complicated and not a scalable solution to the entire feature set we want to test. If it's running in the same process it's also going to end up being part of the benchmark which isn't desirable.

[billwert]

Closing old issues. If this is still something you'd like to work on please re-open.