How Network Bandwidth Affects MySQL Performance

Network is a major part of a database infrastructure. However, often, performance benchmarks are done on a local machine where a client and a server are collocated — I am guilty of this myself. This is done to simplify the setup and to exclude one more variable (the networking part), but with this, we also miss looking at how network affects performance.

The network is even more important for clustering products like Percona XtraDB Cluster and MySQL Group Replication. Also, we are working on our Percona XtraDB Cluster Operator for Kubernetes and OpenShift, where network performance is critical for overall performance.

In this post, I will look into networking setups. These are simple and trivial but are a building block towards understanding networking effects for more complex setups.

Setup

I will use two bare-metal servers connected via a dedicated 10Gb network. I will emulate a 1Gb network by changing the network interface speed with ethtool -s eth1 speed 1000 duplex full autoneg off  command.

I will run a simple benchmark:

sysbench oltp_read_only --mysql-ssl=on --mysql-host=172.16.0.1 --tables=20 --table-size=10000000 --mysql-user=sbtest --mysql-password=sbtest --threads=$i --time=300 --report-interval=1 --rand-type=pareto

This is run with the number of threads varied from 1 to 2048. All data fits into memory — innodb_buffer_pool_size is big enough — so the workload is CPU-intensive in memory: there is no IO overhead.

Operating System: Ubuntu 16.04

Benchmark N1. Network Bandwidth

In the first experimen,t I will compare 1Gb network vs 10Gb network.

Obviously, the 1Gb network performance is a bottleneck here, and we can improve our results significantly if we move to the 10Gb network.

To see that 1Gb network is bottleneck, we can check the network traffic chart in PMM:

We can see we achieved 116MiB/sec (or 928Mb/sec) in throughput, which is very close to the network bandwidth.

But what we can do if our network infrastructure is limited to 1Gb?

Benchmark N2. Protocol Compression

There is a feature in MySQL protocol where you can see the compression for the network exchange between client and server:  --mysql-compression=on  for sysbench.

Let’s see how it will affect our results.

Here is an interesting result. When we use all available network bandwidth, the protocol compression actually helps to improve the result.

But this is not the case with the 10Gb network. The CPU resources needed for compression/decompression are a limiting factor, and with compression, the throughput actually only reaches half of what we have without compression.

Now let’s talk about protocol encryption and how using SSL affects our results.

Benchmark N3. Network Encryption

For the 1Gb network, SSL encryption shows some penalties — about 10 percent for the single thread — but otherwise, we hit the bandwidth limit again. We also see some scalability hit on a high amount of threads, which is more visible in the 10Gb network case.

With 10Gb, the SSL protocol does not scale after 32 threads. Actually, it appears to be a scalability problem in OpenSSL 1.0, which MySQL currently uses.

In our experiments, we saw that OpenSSL 1.1.1 provides much better scalability, but you need to have a special build of MySQL from source code linked to OpenSSL 1.1.1 to achieve this. I don’t show them here, as we do not have production binaries.

Conclusions

1. Network performance and utilization will affect the general application throughput.
2. Check if you are hitting network bandwidth limits
3. Protocol compression can improve the results if you are limited by network bandwidth, but also can make things worse if you are not
4. SSL encryption has some penalty (~10 percent) with a low amount of threads, but it does not scale for high concurrency workloads.