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 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.

network test topology

I will run a simple benchmark:

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 experiment I will compare 1Gb network vs 10Gb network.

1gb vs 10gb network

threads/throughput1Gb network10Gb network
1326.13394.4
41143.361544.73
162400.195647.73
322665.6110256.11
642838.4715762.59
962865.2217626.77
1282867.4618525.91
2562867.4718529.4
5122867.2717901.67
10242865.416953.76
20482761.7816393.84

 

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:

network traffic 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 the our network infrastructure is limited to 1Gb?

Benchmark N2. Protocol compression

There is a feature in MySQL protocol whereby 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.

1gb network with compression protocol

threads/throughput1Gb network1Gb with compression protocol
1326.13198.33
41143.36771.59
162400.192714
322665.613939.73
642838.474454.87
962865.224770.83
1282867.465030.78
2562867.475134.57
5122867.275133.94
10242865.45129.24
20482761.785100.46

 

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

threads/throughput10Gb10Gb with compression
1394.4216.25
41544.73857.93
165647.733202.2
3210256.115855.03
6415762.598973.23
9617626.779682.44
12818525.9110006.91
25618529.49899.97
51217901.679612.34
102416953.769270.27
204816393.849123.84

 

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 reach 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

1gb network and 1gb with SSL

threads/throughput1Gb network1Gb SSL
1326.13295.19
41143.361070
162400.192351.81
322665.612630.53
642838.472822.34
962865.222837.04
1282867.462837.21
2562867.472837.12
5122867.272836.28
10242865.41830.11
20482761.781019.23

10gb network and 10gb with SSL

threads/throughput10Gb10Gb SSL
1394.4359.8
41544.731417.93
165647.735235.1
3210256.119131.34
6415762.598248.6
9617626.777801.6
12818525.917107.31
25618529.44726.5
51217901.673067.55
102416953.761812.83
204816393.841013.22

 

For the 1Gb network, SSL encryption shows some penalty – about 10% 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%) with a low amount of threads, but it does not scale for high concurrency workloads.

Share this post

Comments (5)

Leave a Reply