Changing defaults using arbitrary parameters

You can use optional parameters to change server defaults.

Optional Parameters to change server defaults

You can configure optional parameters to change certain VMware Tanzu SQL with MySQL for VMs server defaults. You might want to configure optional parameters in the following cases:

You have a read-heavy or write-heavy application. See Workloads.
You are migrating from a case-insensitive database. See Lowercase table names.
You want to use a different character set or collation than the default. See Character sets.
You want to configure leader-follower replication. See Synchronous replication.
You have heavy traffic, or spikes in traffic, while using a high availability topology. See WSREP applier threads.

The procedures in this topic use the Cloud Foundry Command Line Interface (cf CLI). You can also use Apps Manager to perform the same tasks using a graphical UI.

Tanzu SQL for VMs service instances are configured by default with industry best practices. For information about the configured server defaults, see About MySQL Server Defaults.

Set optional parameters

You can change the default configuration of optional parameters by creating a new service instance or updating an existing service instance.

The available optional parameters include workload, enable_lower_case_table_names, default-charset and default-collation, replication_mode, backup-schedule, optimize_for_short_words, and wsrep_applier_threads.

To set optional parameters:

Do one of the following:
- If you want to create a new service instance, run:
```
cf create-service p.mysql PLAN SERVICE-INSTANCE \
-c '{ "PARAMETER": "PARAMETER-VALUE"}'
```
- If you want to update an existing service instance, run:
```
cf update-service SERVICE-INSTANCE \
-c '{ "PARAMETER": "PARAMETER-VALUE"}'
```
  The -c flag accepts a valid JSON object containing service-specific configuration parameters, provided either in-line or in a file.

Verify that the cf command ran successfully by running:

watch cf services

Wait for the last operation for your instance to show as create succeeded.

For example:

$ watch cf services
Getting services in org my-org / space my-space as user@example.com...\
OK

name          service       plan        bound apps    last operation
myDB          p.mysql       db-small                  create succeeded

Workloads

The following table describes how to use the workload optional parameter to adjust server default settings for different workload profiles:

workload
Type	String
Default	`mixed`
Description	Set this parameter to `mixed`, `read-heavy` or `write-heavy`. See Workload Profile Types.
Usage	`create-service` or `update-service`

Workload profile types

The following table lists the workload profiles that developers can use to configure MySQL instances based on their specific app workloads:

Profile	Description
Mixed Workload	By default, each MySQL service instance is configured for a mixed workload. This workload is equally heavy on reads and writes. The configuration for this profile is described in About MySQL server defaults.
Read-Heavy Workload	For apps that have a large number of reads, you can configure your service instances with a read-heavy workload. The read-heavy profile changes the following server defaults listed in About MySQL server defaults: `innodb_buffer_pool_size` is increased to 75% of the available memory on each service instance. `innodb_flush_method` is set to `O_DIRECT`.
Write-Heavy Workload	For apps that write to the database frequently, you can configure your service instances with a write heavy workload. The write heavy profile changes the following server defaults that are listed in About MySQL server defaults: `innodb_buffer_pool_size` is increased to 75% of the available memory on each service instance. `innodb_flush_method` is set to `O_DIRECT`. `innodb_log_file_size` is increased to 1 GB. `max_allowed_packets` is increased to 1 GB.

Lowercase table names

If you are migrating a database from a system that was case insensitive, you can enable lowercase table names to change all table names to lowercase.

For example, if your database had the table names TableName and TABLEname, when you enable lowercase table names both of the names change to tablename and are interpreted as the same table.

For more information, see the MySQL documentation.

The following table describes how to use the enable_lower_case_table_names optional parameter:

enable_lower_case_table_names
Type	Boolean
Default	Set by the operator in the Mysql Configuration pane in the tile. See Configure MySQL.
Description	The operator can set a default for this parameter and permit developers to override the default. If you set this to `true`, table names are stored in lowercase. See About lowercase table names. Before you enable this feature, ensure all tables have lowercase names. Tables with uppercase names are inaccessible after enabling lowercase table names.
Usage	`create-service` or `update-service`

Character sets

The following table describes how to use the default-charset and default-collation optional parameters to change the character sets used in databases:

default-charset
Type	String
Default	`utf8`
Description	You can set this to any MySQL 5.7 supported character set. For information about character sets and collations, see the MySQL documentation.
Usage	`create-service` or `update-service`

default-collation
Type	String
Default	`utf8_general_ci`
Description	The `default-collation` changes based on the `default-charset`. To set the `default-collation`, first set the `default-charset`. For instructions for viewing available and default collations, see the MySQL documentation.
Usage	`create-service` or `update-service`

Synchronous replication for leader-follower

If you use a leader-follower service instance, Tanzu SQL for VMs supports synchronous replication in addition to the default asynchronous replication. In sync replication, data does not get committed to the leader node until the follower acknowledges the commit and can replicate it.

The guarantee of redundancy gives sync replication an advantage over asynchronous replication in data integrity. However, depending on latency, sync replication reduces the performance of write operations.

The following table describes how to use the replication_mode optional parameter:

replication_mode does not work for single node, HA cluster, or multi-site replication plans.

replication_mode
Type	String
Default	`async`
Description	Set this parameter to one of the following: `semi-sync:` This enables sync replication on a leader-follower service instance. `async:` This restores the default asynchronous replication for a leader-follower service instance.
Usage	`create-service` or `update-service`

In Tanzu SQL for VMs, replication is called sync, rather than semi-sync. This is because it is as synchronous as possible given the limits of MySQL. For more information about MySQL semi-sync replication, see the MySQL documentation.

Synchronous replication timeout

By default, the timeout for sync replication is set to approximately 292 million years. Therefore, the leader always waits for the follower to confirm receipt of the transaction. This guarantees that if the leader is lost, a redundant copy of the data exists on the follower.

When the replication mode timeout is reached, the replication mode automatically reverts to asynchronous without any user intervention. You can manually override this timeout by setting a lower value.

The following table describes how to use the semi_sync_ack_timeout_in_ms optional parameter:

semi_sync_ack_timeout_in_ms
Type	Integer
Default	2⁶³ milliseconds
Description	Sets the timeout in milliseconds for the leader to acknowledge a replication operation.
Usage	`create-service` or `update-service`

Backup schedule

The following table describes how to use the backup-schedule optional parameter:

backup-schedule
Type	Cron expression
Default	The operator sets the default
Description	Enter a cron expression using standard syntax. The cron expression sets the backup schedule for your service instance. For example, entering a cron expression of `15 10 * * *` triggers a backup 10:15 AM every day. Test your cron expression using a website such as Crontab Guru. Configuring a cron expression overrides the default schedule for your service instance.
Usage	`create-service` or `update-service`

Optimize for short words

The following table describes how to use the optimize_for_short_words optional parameter:

optimize_for_short_words
Type	Boolean
Default	`false`
Description	Set this parameter to `true` to change the MySQL system variable `innodb_ft_min_token_size` to 1. This allows shorter words to be stored in the InnoDB full-text index. Because this has the side effect of increasing the size of the index, you must monitor the memory usage of the service instance and choose a larger VM type when necessary. Also, the operator needs to prevent the index from becoming too large and ineffective by removing entries as described in the following paragraphs. How often this needs to be done depends on the workload and how much data is changed in the full-text index. For more information about the `innodb_ft_min_token_size` system variable, see the MySQL documentation. To remove full-text index entries for deleted records or old records: Edit `my.cnf` to set `innodb_optimize_fulltext_only=ON`. For single node and leader-follower plans, the path to the file is `/var/vcap/jobs/mysql/config/my.conf`. For HA cluster and Multi‑Site Replication plans the path to the file is `/var/vcap/jobs/pxc-mysql/config/my.conf`. Run `OPTIMIZE TABLE` on the indexed tables. When the optimization is done, set `innodb_optimize_fulltext_only=OFF` so that the query behaves normally for other tables. For more information about InnoDB full-text index deletion, see the MySQL documentation.
Usage	`create-service` or `update-service`

WSREP applier threads

The following table describes how to use the wsrep_applier_threads optional parameter:

wsrep_applier_threads
Type	Integer
Default	Number of CPU cores
Description	Specifies the number of threads that can apply replication transactions in parallel in a high-availability cluster. VMware MySQL for VMs defaults to the number of available CPU cores, whereas Percona XtraDB Cluster 5.7 defaults to 1. To return the property to the default, use a value of -1. This parameter only applies to "high-availablity" service plans, and is rejected for service plans using other topologies.
Usage	`create-service` or `update-service` as per Set optional parameters