Feature Request: Support for Discrepant MySQL Configurations Between Primary and Replica vttablets

[yydoow]

Feature Description

Description

Currently, Vitess requires the MySQL configurations on both primary and replica vttablets to be identical. However, given the different roles and performance characteristics of primary and replica databases, it would be beneficial to allow for different MySQL configurations on each. This feature request proposes the ability to configure MySQL settings independently for primary and replica vttablets.

Benefits

Performance Optimization: Tailoring configurations to the specific roles of primary and replica can lead to significant performance improvements.
Resource Utilization: Different configurations can help in better resource utilization based on the workload characteristics of primary and replica databases.
Flexibility: Provides more flexibility in managing database configurations and optimizing for different use cases.

Request: Evaluate the possibility of allowing different MySQL configurations for primary and replica vttablets in Vitess.

Use Case(s)

Use Case

In many scenarios, the primary database is optimized for write performance and durability, while replicas are optimized for read performance and may have different durability requirements. For example:

Primary vttablet Configuration:

innodb_flush_log_at_trx_commit=1
sync_binlog=0
binlog_group_commit_sync_delay=100000

Replica vttablet Configuration:

innodb_flush_log_at_trx_commit=0
sync_binlog=0
binlog_group_commit_sync_delay=0

These configurations reflect the different roles of the primary and replica databases. The primary is configured for maximum durability with innodb_flush_log_at_trx_commit=1, while the replica is configured for performance with innodb_flush_log_at_trx_commit=0.

[arthurschreiber]

Mh. Are you running with semi sync or without? With semi sync, you could just use the config options you have on replicas on the primary as well, because each transaction is guaranteed to have been replicated to at least one replica.

[mattlord]

Which tablet is a replica and which is a primary is not static. You can already dynamically configure a MySQL instance whenever/however you like (e.g. disabling syncing for binlogs and Innodb Redo logs). Vitess does not constantly check all MySQL config options in running instances and enforce they be the same across the fleet. I must be missing something as I'm not sure exactly what is being requested here?

Maybe you're saying that you want to be able to define a static MySQL config for different tablet types and have Vitess enforce that when a tablet's type changes?

[GuptaManan100]

I agree with Matt, I don't think we should be adding this to Vitess.

[yydoow]

Maybe you're saying that you want to be able to define a static MySQL config for different tablet types and have Vitess enforce that when a tablet's type changes?

Yes, exactly. Our MySQL database implementation requires something like this:

{
 "master": {
   "innodb_flush_log_at_trx_commit": "1",
   "max_connect_errors": 4294967295,
   "read_only": 0,
   "sync_binlog": 1
 },
 "slave": {
   "innodb_flush_log_at_trx_commit": "2",
   "max_connect_errors": 4294967295,
   "read_only": 0,
   "sync_binlog": 0
 }
}

We have a mechanism that detects the role of MySQL and applies the appropriate configuration. This helps reduce slave lag and other issues.

Another example, consider binlog_group_commit_sync_delay=100000. We only want this setting on the primary for parallel replication. The replica doesn't need to delay for 100 ms for each group.

This approach can save time when rebuilding replicas from backups, especially for busy MySQL instances with a high volume of transactions.

[arthurschreiber]

Another example, consider binlog_group_commit_sync_delay=100000. We only want this setting on the primary for parallel replication. The replica doesn't need to delay for 100 ms for each group.

I know this is off-topic, but have you looked into WRITESET? If your goal with that commit sync delay is to have better parallelism on your multi threaded replicas, you probably should use WRITESET instead.

[yydoow]

binlog_group_commit_sync_delay is just a case what about innodb_flush_log_at_trx_commit ?

[bluecrabs007]

Maybe you're saying that you want to be able to define a static MySQL config for different tablet types and have Vitess enforce that when a tablet's type changes?

Yes, pretty much this.
Vitess already does this to some extent, such as setting replicas as read_only.

[antonrubtsov]

It will be a very useful feature.
We use binlog_group_commit_sync_delay on primary to speed up parallel replication and have found that binlog_group_commit_sync_delay enabled on replica slows down replication.
So now, in case of changing primary, we need to set up binlog_group_commit_sync_delay on shard.