MySQL :: Improving the Performance of MySQL on Windows

In this blog entry I’d like to describe how you might be able to improve how MySQL performs on Windows by ensuring that you take advantage of a Windows specific configuration setting.

On Unix systems, MySQL programs treat the localhost host name specially. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file, which has some performance advantages over a TCP/IP connection. Windows does not support Unix sockets, however, and hence does not benefit from this optimisation.

However, the use of shared memory connections on Windows can offer significant performance improvements over the use of TCP/IP connections. Shared memory connections are obviously only useful when both the MySQL client and server processes are executing on the same machine, but when they are the performance benefits of using shared memory connections can be helpful. To enable shared memory connections, you would use the shared_memory system variable.

The following screenshot compares the sysbench OLTP read write test performance when using shared memory connections versus when using TCP connections with MySQL 5.6.24 on Windows Server 2012 R2. The graph shows the average number of transactions per second (TPS) measured by sysbench at a variety of thread counts. The TPS value averaged over five runs is plotted at each thread count, with each run taking 300 seconds.

Graph comparing Shared Memory and TCP connection performance using MySQL 5.6.24 — Comparing shared memory and TCP connection performance using MySQL 5.6.24

The uppermost red plot in the graph above shows the results obtained when using shared memory connections between sysbench and MySQL. The lower black plot shows the results obtained when using TCP connections.

Note that as well as giving improved performance when using shared memory connections, the shared memory connections results show reduced variability (apart from the results at 512 threads).

Obviously, changing to a more efficient communication path between the MySQL server and client will only make a dramatic difference like that shown above when the MySQL server isn’t spending a lot of time processing queries and doing file I/O. So if you use MySQL on Windows in a standalone configuration (with the application clients and the MySQL server on the same machine) and switch from using TCP connections to shared memory connections, then the performance improvement that you experience may not be as great.

The test machine used to produce the results shown above is a Sun Fire X4170 M2 Server with 24 logical CPUs running at 2930MHz.

The following graph shows the results of running the same TCP vs shared memory connection tests using the MySQL 5.7.7 release candidate:

Graph showing shared Memory vs TCP connection performance using MySQL 5.7.7rc — Comparing shared memory and TCP connection performance using MySQL 5.7.7rc

Note that the MySQL 5.7.7 release candidate shows better performance at high thread counts than MySQL 5.6.24 in the graphs above. Profiling the shared memory test runs at 1024 threads for the different versions of MySQL reveals that the second “hottest” function in MySQL 5.6.24 at this thread count is RtlpEnterCriticalSectionContended, whereas this function has dropped to fourth place in the MySQL 5.7.7 profile. A call to RtlpEnterCriticalSectionContended on Windows indicates contention for a critical section (a multithreading lock).

The reduction in lock contention with MySQL 5.7.7 fits nicely with the performance and scalability improvements in MySQL 5.7 mentioned in Geir’s blog entry: What’s new in MySQL 5.7 (First Release Candidate).

The tests used to produce the results shown above are part of the regular performance tests that run daily in the Oracle test labs against the latest builds of MySQL on various platforms. The results of these daily performance tests are automatically checked for performance regressions and failures and engineers are alerted if any are found.

One of the characteristics of a good performance test is repeatability. In order to reduce the variability of the TPS results reported by sysbench on the machine used for the test results, the sysbench process was affinitized to use CPUs 0,1,2,3,12,13,14,15 and the MySQLD process was affinitized to use CPUs 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23. These CPU affinities were arrived at by experimenting to find the affinity settings that provided the highest and most stable TPS results.

Microsoft has also introduced an enhancement to the “loopback” TCP/IP performance on Windows 8 and Windows 2012 via the “TCP Loopback Fast Path” (see http://blogs.technet.com/b/wincat/archive/2012/12/05/fast-tcp-loopback-performance-and-low-latency-with-windows-server-2012-tcp-loopback-fast-path.aspx ). The graph below shows the results I obtained when I experimented with a patch on the communications library used by MySQL 5.7.6 m16 and sysbench that set the SIO_LOOPBACK_FAST_PATH option to 1.

Graph showing Fast Loopback TCP vs standard TCP connection performance using MySQL 5.7.6-m16 — Comparing Fast Loopback TCP and standard TCP connection performance using MySQL 5.7.6-m16

The lower black plot shows the results from the “normal” unpatched TCP connections. The upper red plot shows the results when using the communications library patched with the “Loopback Fast Path” setting.

Note that the performance gain from using the “TCP Loopback Fast Path” patch over normal TCP connections is not as significant as that available from switching to shared memory connections. The data in the graph above is also not directly comparable with the previous graphs showing the shared memory connection performance as the CPU affinity settings differ.

In my next post, I hope to show how some previously undocumented settings can further improve MySQL’s performance on Windows. So please stay tuned!

As always, thank you for using MySQL!