How much difference do different slave_parallel_mode settings make when we use parallel replication in MariaDB? In most cases, not much. The conservative setting has been the default for a long time, and in most cases it performs similarly or better than optimistic. But every once in a while there is a very large difference.

One of our MariaDB support engineers was recently called to investigate why a newly built server was struggling to keep up with replication. Since it was built, it hardly managed to close the gap at all, and at the current rate it wouldn’t be able to be used in the foreseeable future. All of the usual settings were already very well optimised.

The only thing that remained to try was slave_parallel_mode=optimistic, and surprisingly, in this case it made a huge difference.

You can see where the setting was changed around 13:20 and the replication lag started to rapidly decay, and the server caught up with 20 hour lag in approximately 3.5 hours.

slave_parallel_mode=optimistic may not always yield a significant improvement, but if you have already applied all other optimisations and the replication lag just isn’t budging, it’s definitely worth a try.