Run SQL commands on primary and replica whenever status changes

Add monitor settings primary_state_sql and replica_state_sql, which define SQL queries that the monitor runs on a server when it gains [Master] or [Slave]. This allows the DBA to change server variables depending on server role. The SQL is given in the configuration file itself and can contain multiple queries. These settings could be generic monitor settings supported by all monitors.

primary_state_sql="set global binlog_commit_wait_count=10;

    set global binlog_commit_wait_usec=1000;"

replica_state_sql="set global binlog_commit_wait_count=0;

    set global binlog_commit_wait_usec=0;"

Original description:
Note this is most applicable when parallel replication is configured to use conservative mode, but can be helpful for optimistic as well.

https://mariadb.com/kb/en/parallel-replication/?msclkid=e5e356a3d15711ec8f4e03f95e015af9#conservative-mode-of-in-order-parallel-replication

In such setups, we want to tune the primary MariaDB server with binlog_commit_wait_count and binlog_commit_wait_usec. This trades some maximum write throughput on the primary to have the primary group and organize commits to the binary log in such a way that a replica can confidently identify groups of transactions it can safely apply in parallel.

The problem this creates- which is how this feature relates to MaxScale's MariaDB Monitor- is that there is no reason or desire to impose these requirements or limitations on the replicas. We don't want or need the replicas to sacrifice write performance to try to reorganize their binary logs like this. Instead, we want this to be setup on the primary but disabled on the replicas.

The below GitHub link provides further clarification and context for this, including instructions on how to achieve this result with promotion and demotion scripts-

https://github.com/mariadb-faisalsaeed/documentation/blob/master/parallel-replicaiton.md#understanding-parallel-replication

So again, it is nice that we can achieve this with promotion and demotion scripts... but this is such a core piece of functionality for conservative parallel replication that it would be much better to not leave this as something we are asking customers to manage on their own. If instead we could embed this logic in MariaDB Monitor and allow customers to configure it there, that would be much more ideal. It would also improve customer adoption and adherence to best-practice for this. As incorrect configuration here will cause replication lag or other problems, improving customer adherence to best-practice for this will directly improve overall customer experience with MariaDB and MaxScale asynchronous replication.

Larger opportunity...

The minimum ask of this feature is detailed above this header. However, this feature could also be more generally resolved via a more general request like this-

Enhance MariaDB Monitor to enable configuration of MariaDB configurations where users can provide one value for the primary and one value for replica(s).

Ex, rather than adding an option like, enforce_group_commit_tuning=true, there could be a more general setting like-

[MariaDB-Monitor]

...

primary_replica_variables=(binlog_commit_wait_count,10,0), (binlog_commit_wait_usec,1000,0), (sync_binlog,1,0), (innodb_flush_log_at_trx_commit,1,0)

The above setup is-

primary_replica_variables=(VARIABLE, VALUE FOR PRIMARY, VALUE FOR REPLICAS), (VARIABLE, VALUE FOR PRIMARY, VALUE FOR REPLICAS)

This is a little messy, but there are likely viable alternatives for how to handle this (no shortage of examples with optimizer_switch and wsrep_provider_options). Could even go with just having customers provide config files, ex-

[MariaDB-Monitor]

...

primary_configs=/var/lib/maxscale/primary.cnf

replica_configs=/var/lib/maxscale/replicas.cnf

Where /var/lib/maxscale/primary.cnf-

binlog_commit_wait_count=10

binlog_commit_wait_usec=1000

sync_binlog=1

innodb_flush_log_at_trx_commit=1

And where /var/lib/maxscale/replicas.cnf-

binlog_commit_wait_count=0

binlog_commit_wait_usec=0

sync_binlog=0

innodb_flush_log_at_trx_commit=0

In an ideal world, MaxGUI will provide an interface to configure all of this as well, and also in an ideal world all of this can be configured dynamically.