TCP Loopback fast path
In Windows 8 and Windows server 2012, Microsoft introduced a new “TCP Loopback fast path” feature (see the Microsoft Technet article: Fast TCP Loopback Performance and Low Latency with Windows Server 2012 TCP Loopback Fast Path). This new feature is intended to improved the performance of “standalone” TCP client/server systems where both the client and server are on the same Windows machine.
In an earlier post—Improving the performance of MySQL on Windows—I showed some results comparing the performance of MySQL 5.6.24 running the Sysbench OLTP RW test using standard TCP connections with TCP loopback fast path based connections. Those results showed a modest increase in performance.
MySQL 5.7 Benchmark
I have recently repeated this experiment, patching the most recent development release MySQL 5.7.8-RC to add support for enabling the TCP loopback fast path.
The performance impact of the loopback fast path result is even more significant with this version of MySQL:
The results above were obtained on a 24 core machine running Windows Server 2012 R2, and show the average Transactions Per Second (TPS) values measured by Sysbench for the OLTP read/write test at a variety of thread counts.
The upper black trace in the above plot is the result with the loopback fast path enabled, and the lower red trace is the result without loopback fast path.
In the results shown above the average increase in TPS when using the loopback fast path is 67% when using 16 or more concurrent threads, with a maximum increase of 86% when using 512 concurrent threads.
When would you want to use the loopback fast path? From the results above, it would seem reasonable to enable it by default, and only turn it off if you find a reason to do so (for example, if you had a particular requirement to exercise those parts of the network stack that the loopback fast path bypasses). Note that the performance benefit will be most apparent when the network communication overhead is relatively high compared to the file IO and processing overhead that MySQL incurs when handling requests. In the example shown above, the InnoDB Buffer Pool is large relative to the size of the table used by the sysbench queries, and the queries themselves are relatively simple, thus the performance benefit of the loopback fast path is highlighted and significant. In other scenarios, the impact may well be lower.
A 64 bit Windows build of MySQL 5.7.8-RC with a patch to enable the loopback fast path is available as a Labs release at http://labs.mysql.com/ — select the “MySQL Loopback Fast Path” build. This Labs release introduces a new Boolean system variable — loopback_fast_path — that is examined at MySQL server startup to determine whether or not to enable the loopback fast path.
Details for reproducing the results shown above:
The version of sysbench on the 0.4 branch can be used on Windows to test this labs version of MySQL server. Note that you’ll need to build/use a 64 bit version of sysbench to link successfully with the libmysql.lib import library in this Labs release of MySQL.
Using the Labs release version of libmysql.dll with sysbench is necessary as the Labs release code makes the client (sysbench) side of the TCP socket request the loopback fast path by default, whereas the server code’s TCP socket loopback behaviour is controlled by the loopback_fast_path system variable. Both the server and client sides of the TCP socket must request the loopback fast path behaviour in order for it to take effect. I chose to enable the loopback fast path feature on the client side for this Labs release by default to avoid having to modify all of the connectors/client code to take advantage of it.
The benchmarking process was:
- Start mysqld.exe with an empty database with
--loopback_fast_path=ON - Create a test database
- Execute the sysbench prepare command to create and populate a table in the test database:
1sysbench --num-threads=64 --test=oltp --oltp-table-size=10000000 --oltp-dist-type=uniform --oltp-connect-delay=10000 --oltp-auto-inc=on --init-rng=off --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --mysql-table-engine=innodb --max-requests=0 --max-time=300 --oltp-reconnect-mode=session --oltp-test-mode=complex --mysql-dry-run=off prepare - Execute the sysbench oltp run command to exercise the system for 5 minutes:
1sysbench --num-threads=64 --test=oltp --oltp-table-size=10000000 --oltp-dist-type=uniform --oltp-connect-delay=10000 --oltp-auto-inc=on --init-rng=off --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --mysql-table-engine=innodb --max-requests=0 --max-time=300 --oltp-reconnect-mode=session --oltp-test-mode=complex --mysql-dry-run=off run - Record the average transactions per second results reported by sysbench
- Repeat the above process three times for each thread count
- Repeat above steps passing the command line option
--loopback_fast_path=OFFto mysqld.exe on startup (or omitting the loopback_fast_path option entirely, as the server will default it to OFF)
Note the use of the sysbench –mysql-dry-run=off command line option in steps 3 and 4 above: this is mandatory as the default value of the mysql-dry-run option is “on”, which would prevent communication between sysbench and MySQL.
The labs release code will function on earlier versions of Windows (e.g. Windows 7 and Windows Server 2008), but as these earlier versions of Windows do not support the loopback fast path MySQL will not benefit from its performance improvement. Attempting to enable loopback fast path on versions of Windows earlier than Windows 8/Windows Server 2012 will result in the MySQL error log showing an error message of the form: loopback_fast_path is not supported on this version of Windows.
The following is the contents of the MySQL configuration file used to obtain the results shown above:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
[mysqld] ## Server Settings ## disable-log-bin join_buffer_size=32K key_buffer_size=200M log-error=error.log loose-local-infile=1 low_priority_updates=1 max_allowed_packet=1048576 max_connections=4000 max_connect_errors=50 max_prepared_stmt_count=1048576 port=3306 sql-mode="NO_ENGINE_SUBSTITUTION" table_open_cache=2048 table_open_cache_instances=10 transaction_isolation=REPEATABLE-READ query-cache-size=0 query_cache_type=0 user=root ## Performance Schema ## performance_schema=ON ## Innodb Settings ### innodb_buffer_pool_size=16384M innodb_buffer_pool_instances=8 innodb_checksums=0 innodb_data_file_path=ibdata1:2000M:autoextend innodb_doublewrite=0 innodb_file_per_table=1 innodb_lru_scan_depth=2000 innodb_max_dirty_pages_pct=50 innodb_monitor_enable=% innodb_open_files=4000 innodb_log_buffer_size=64M innodb_log_files_in_group=3 innodb_log_file_size=2048M innodb_stats_persistent=1 innodb_support_xa=0 innodb_thread_concurrency=0 |
Conclusion
We feel that the TCP Loopback fast path shows great promise, and we are considering enabling it by default in future versions of MySQL for Windows. We’d love to get your input on this! If you have any questions or comments, please let me know here in the comments.
That’s it for now, as always, THANK YOU for using MySQL!