SQL database performance in Docker containers vs natively on the host

[Xitric]

We have started to look into using Docker containers in general for supporting all of our integration and benchmark testing projects due to the ease of use for us as developers (not having to configure databases locally and manage connection strings, worrying about ports, etc.). This is really cool, but we have unfortunately also experienced a degradation in performance of all testing projects that involve MsSql databases hosted in Docker containers.

This is not an issue with testcontainers, since we experience the same degradation when simply using Docker compose to prepare our databases.

To give an example, we have certain database-heavy integration tests that went from executing in 1.5 seconds when the database was installed natively on the host, to spending 5-6 seconds when the database was in a Docker container.

We also have some benchmark tests that are extremely database-heavy, and here we see a performance degradation of a factor 10.

Here is some additional information for context:

• All developers use Windows 10 together with Docker Desktop
• Our docker containers do not hit the resource limits during the tests
• We are not doing anything fancy with our database containers:
  • Image: mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04
  • A port binding
  • Username and password via environment variables

We suspect there is some form of overhead, perhaps related to the Windows file system of the host, which affects the performance of our tests when using a Docker container to host our databases. Certain things we have tried so far:

• Allocating 20 GB of memory and 14 CPU cores to the database containers alone, with little to no measurable impact on performance
• Setting the WithPrivileged flag on the Docker container, with little to no measurable impact on performance
• Reduce consistency of all volumes mounted to the SQL container to either delegated or cached, with a small improvement to performance, but not enough to make up for the degradation

But we would really like to hear if anyone else in the community has experienced any performance issues when running any type of SQL server in a Docker container, and what, if anything, they have done to improve upon this!

[HofmeisterAn]

What setup do you use? A single shared instance or multiple parallel instances? How do you access the database? With the native client? What is the MSSQL max server memory? @kiview @eddumelendez Do you have ideas or maybe made experiences that could reduce the container performance?

[Xitric]

@HofmeisterAn Sorry for pinging you, but did you ever have time to look into this? We are still having trouble with the performance of our SQL based integration tests when running the SQL server in Docker containers.

[HofmeisterAn]

Yes, I started working on it weeks ago, but then got distracted 😬. Just recently, I disabled durability for PostgreSQL. According to this article, it has reduced the test time by 20%. Do you know if there are similar optimizations for MSSQL?

[HofmeisterAn]

I spent some time looking at the example project and remembered that I had trouble getting the test running. Specifically, the code that prepares the performance test (database) did not work as expected - for example, CreateTable(string) did not create any table. Additionally, I was unsure if StartTest(SqlConnection) was running as intended. I had to make some changes to get it working, and I noticed that the example project could benefit from following more best practices and being cleaned up. Altogether, the example appears too complicated and disorganized (that is why I did not start further analysis and performance investigations).

In particular, I would recommend cleaning up the project and perhaps replacing MsSqlTestcontainer with a proper module implementation, then re-testing it. Out of curiosity, how long did your test run for? In my case, it took less than 300ms.

[Xitric]

I believe the tests in our project in general leave a lot to desire in terms of best practices. They are unfortunately also ridiculously complicated. I think the reason why the sample project appears chaotic is that we attempted to extract the parts of our test harness that leave a measurable impact on performance, without exposing the inner details of our software itself.

We can certainly try to use the built-in MSSQL test container now. We could not originally, when we started implementing test container support in our projects, but with some of the recent changes to that module I believe it now supports all of our needs.

I will also check to see if we can remove additional code from that sample project while still being able to reproduce the performance issues, and then give you some information on the execution times that we observe.

[HofmeisterAn]

We can certainly try to use the built-in MSSQL test container now.

Either you can wait for the next release, or you can copy the module configuration. However, I don't believe that it will make a significant difference. The configurations are similar, and I don't expect any performance improvements. It will simply be a little cleaner.

Did I understand correctly that you are benchmarking against ALTER TABLE ? NOCHECK CONSTRAINT ALL? Can you share the results of your test? I haven't done a lot of database development in the past, so I'm not sure if that is a good performance indicator, although it may show significant differences.

Have you considered reaching out to Microsoft? It seems that you're not the only one experiencing this issue: microsoft/mssql-docker#644. I believe that a proper test with the support of TC could help to identify the bottleneck, and it would be a good way to benchmark it.