As we have discussed in one of our previous blog posts, Galera is sensitive to network latency. This discussion also comes up quite often in our MySQL consulting engagements. We have thought that it might be interesting to take a look at this in more detail and show you what kind of impact it has. Let’s take a quick look at the setup.
Initial setup
First, we have to mention that this setup has nothing to do with the production-grade environment. We have used a couple small VM’s colocated on the same host. What is interesting, this setup was more than enough to show the impact of the network latency.
To emulate the latency we used tc. We have tested following cases:
- No delay
- tc qdisc add dev eth1 root netem delay 10ms 10ms
- tc qdisc add dev eth1 root netem delay 100ms 10ms
- tc qdisc add dev eth1 root netem delay 100ms 10ms + additional tuning for OS and Galera Cluster
We used Sysbench to generate the traffic and we used ProxySQL to connect to and distribute the load across all nodes in the Galera cluster.
The exact command that we executed was:
sysbench /root/sysbench/src/lua/oltp_read_write.lua --threads=4 --events=0 --time=300 --mysql-host=192.168.10.124 --mysql-user=sbtest --mysql-password=sbtest --mysql-port=6033 --tables=32 --report-interval=1 --skip-trx=off --table-size=100000 --db-ps-mode=disable run
We executed five times, each run taking 5 minutes. Every test run was preceded by a warm-up run. Again, this is not too scientific approach, if you want to make sure you have the best data, you may want to run the test for a longer period of time.
Let’s take a look at the results.
Results
No delay
The output shows that the number of queries per second is typically in 3000 – 3500 area and the latency 95 percentile is around 50ms for the transaction.
10 ms delay and 10 ms jitter
Let’s spice things up by increasing the network latency a bit. We’ve done that by executing:
tc qdisc add dev eth1 root netem delay 10ms 10ms
on one of the nodes.
There is a significant drop in the performance after we have added the delay. We are now talking about ~2000 queries per second, quite significant drop compared to the previous ~3000 – 3500 queries per second.
As you can see, the latency 95% itself did not increase too much, without precise data it’s hard to tell exactly what the change was. Luckily, we have the data for all test runs.
No added latency:
root@Ubuntu-2004-focal-64-minimal:~/output# grep Latency -A 4 output_latency_0_jitter_0.* output_latency_0_jitter_0.1:Latency (ms): output_latency_0_jitter_0.1- min: 9.36 output_latency_0_jitter_0.1- avg: 21.88 output_latency_0_jitter_0.1- max: 248.90 output_latency_0_jitter_0.1- 95th percentile: 40.37 -- output_latency_0_jitter_0.2:Latency (ms): output_latency_0_jitter_0.2- min: 9.74 output_latency_0_jitter_0.2- avg: 24.43 output_latency_0_jitter_0.2- max: 271.49 output_latency_0_jitter_0.2- 95th percentile: 45.79 -- output_latency_0_jitter_0.3:Latency (ms): output_latency_0_jitter_0.3- min: 10.40 output_latency_0_jitter_0.3- avg: 24.92 output_latency_0_jitter_0.3- max: 256.84 output_latency_0_jitter_0.3- 95th percentile: 47.47 -- output_latency_0_jitter_0.4:Latency (ms): output_latency_0_jitter_0.4- min: 10.32 output_latency_0_jitter_0.4- avg: 25.19 output_latency_0_jitter_0.4- max: 156.78 output_latency_0_jitter_0.4- 95th percentile: 48.34 -- output_latency_0_jitter_0.5:Latency (ms): output_latency_0_jitter_0.5- min: 9.70 output_latency_0_jitter_0.5- avg: 25.29 output_latency_0_jitter_0.5- max: 236.66 output_latency_0_jitter_0.5- 95th percentile: 48.34
Added 10ms of latency and 10ms of jitter:
root@Ubuntu-2004-focal-64-minimal:~/output# grep Latency -A 4 output_latency_10_jitter_10.* output_latency_10_jitter_10.1:Latency (ms): output_latency_10_jitter_10.1- min: 16.12 output_latency_10_jitter_10.1- avg: 41.46 output_latency_10_jitter_10.1- max: 364.29 output_latency_10_jitter_10.1- 95th percentile: 66.84 -- output_latency_10_jitter_10.2:Latency (ms): output_latency_10_jitter_10.2- min: 15.58 output_latency_10_jitter_10.2- avg: 40.94 output_latency_10_jitter_10.2- max: 282.63 output_latency_10_jitter_10.2- 95th percentile: 64.47 -- output_latency_10_jitter_10.3:Latency (ms): output_latency_10_jitter_10.3- min: 16.48 output_latency_10_jitter_10.3- avg: 42.29 output_latency_10_jitter_10.3- max: 292.05 output_latency_10_jitter_10.3- 95th percentile: 68.05 -- output_latency_10_jitter_10.4:Latency (ms): output_latency_10_jitter_10.4- min: 16.89 output_latency_10_jitter_10.4- avg: 41.37 output_latency_10_jitter_10.4- max: 295.39 output_latency_10_jitter_10.4- 95th percentile: 66.84 -- output_latency_10_jitter_10.5:Latency (ms): output_latency_10_jitter_10.5- min: 16.35 output_latency_10_jitter_10.5- avg: 42.00 output_latency_10_jitter_10.5- max: 434.43 output_latency_10_jitter_10.5- 95th percentile: 68.05
As we can see, the 95% latency increased from ~45-48ms to 66-68ms, pretty much expected, given the delay we have added.
100 ms delay and 10ms jitter
What about adding 100ms delay? Let’s take a look at the performance:
Well, we are in 600-ish queries per second area, again, huge dip from 10ms delay, not to mention no delay setup.
When it comes to latency, 95th percentile increased towards 150-200 milliseconds.
100 ms delay, 10 ms jitter and applied tuning
We have attempted to apply some very basic and generic tuning related to both operating system and Galera:
net.core.optmem_max = 25165824 net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 net.core.rmem_default = 16777216 net.core.wmem_default = 16777216 net.ipv4.tcp_rmem = 8192 87380 16777216 net.ipv4.tcp_wmem = 8192 87380 16777216 net.core.netdev_max_backlog = 80000 net.ipv4.tcp_max_syn_backlog = 50000 net.ipv4.tcp_mtu_probing = 1 net.ipv4.tcp_congestion_control = htcp net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_max_tw_buckets = 3000000 net.ipv4.tcp_fin_timeout = 40 evs.user_send_window=1024 evs.send_window=1024 gcs.fc_factor=1 gcs.fc_limit=280 gcs.max_packet_size=2097152 evs.inactive_check_period=PT30S evs.inactive_timeout=PT1M evs.suspect_timeout=PT1M evs.stats_report_period=PT3M
The result was not satisfactory. We were able to reduce the latency 95% by 10-15ms and thus slightly improve the performance, but the change was not visible enough:
root@Ubuntu-2004-focal-64-minimal:~/output# grep Latency -A 4 output_latency_100_jitter_10.* output_latency_100_jitter_10.1:Latency (ms): output_latency_100_jitter_10.1- min: 106.71 output_latency_100_jitter_10.1- avg: 145.41 output_latency_100_jitter_10.1- max: 375.08 output_latency_100_jitter_10.1- 95th percentile: 200.47 -- output_latency_100_jitter_10.2:Latency (ms): output_latency_100_jitter_10.2- min: 106.96 output_latency_100_jitter_10.2- avg: 134.72 output_latency_100_jitter_10.2- max: 309.49 output_latency_100_jitter_10.2- 95th percentile: 186.54 -- output_latency_100_jitter_10.3:Latency (ms): output_latency_100_jitter_10.3- min: 104.20 output_latency_100_jitter_10.3- avg: 134.61 output_latency_100_jitter_10.3- max: 298.90 output_latency_100_jitter_10.3- 95th percentile: 186.54 -- output_latency_100_jitter_10.4:Latency (ms): output_latency_100_jitter_10.4- min: 105.90 output_latency_100_jitter_10.4- avg: 134.19 output_latency_100_jitter_10.4- max: 299.66 output_latency_100_jitter_10.4- 95th percentile: 183.21 -- output_latency_100_jitter_10.5:Latency (ms): output_latency_100_jitter_10.5- min: 108.16 output_latency_100_jitter_10.5- avg: 134.57 output_latency_100_jitter_10.5- max: 386.00 output_latency_100_jitter_10.5- 95th percentile: 186.54 root@Ubuntu-2004-focal-64-minimal:~/output# grep Latency -A 4 output_latency_100_jitter_10_tuning.* output_latency_100_jitter_10_tuning.1:Latency (ms): output_latency_100_jitter_10_tuning.1- min: 103.68 output_latency_100_jitter_10_tuning.1- avg: 132.28 output_latency_100_jitter_10_tuning.1- max: 279.15 output_latency_100_jitter_10_tuning.1- 95th percentile: 176.73 -- output_latency_100_jitter_10_tuning.2:Latency (ms): output_latency_100_jitter_10_tuning.2- min: 106.11 output_latency_100_jitter_10_tuning.2- avg: 132.34 output_latency_100_jitter_10_tuning.2- max: 300.18 output_latency_100_jitter_10_tuning.2- 95th percentile: 173.58 -- output_latency_100_jitter_10_tuning.3:Latency (ms): output_latency_100_jitter_10_tuning.3- min: 104.51 output_latency_100_jitter_10_tuning.3- avg: 132.14 output_latency_100_jitter_10_tuning.3- max: 254.05 output_latency_100_jitter_10_tuning.3- 95th percentile: 176.73 -- output_latency_100_jitter_10_tuning.4:Latency (ms): output_latency_100_jitter_10_tuning.4- min: 104.86 output_latency_100_jitter_10_tuning.4- avg: 131.04 output_latency_100_jitter_10_tuning.4- max: 257.80 output_latency_100_jitter_10_tuning.4- 95th percentile: 170.48 -- output_latency_100_jitter_10_tuning.5:Latency (ms): output_latency_100_jitter_10_tuning.5- min: 105.29 output_latency_100_jitter_10_tuning.5- avg: 131.90 output_latency_100_jitter_10_tuning.5- max: 268.95 output_latency_100_jitter_10_tuning.5- 95th percentile: 173.58
We haven’t proceeded with further tuning as this whole setup is quite artificial. This is something we will definitely dig into WAN tuning in the future, it is showing up quite often during our MySQL consulting engagements, but it will be “real” WAN, with several AWS regions across the globe, something more resembling real live scenario.
Summary
As you can see, by increasing the network delay between nodes the performance of Galera Cluster was significantly impacted.
The same can be said for the latency. Latency is especially important if you have any kind of hot spots in your data set. First of all, latency is the majority of the time to commit the transaction. The delay between executing COMMIT and getting the transaction actually committed is pretty much related to the network latency. If you have particular set of rows that are often modified, each modification can only happen after the previous one finished. Let’s say you have a commit time of 200ms due to network latency. It pretty much means that you can modify the same row only 5 times per second. Yes, if you update multiple rows, the average QPS will be way higher but for that one particular row it can’t be higher than 5 queries per second. Again, this is a topic that quite common shows up during the architecture design and consultations for MySQL. Galera Cluster is great when it comes to the High Availability but it comes with significant drawbacks you have to be aware of.
