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:

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:

  1. Do one of the following:

    • If you want to create a new service instance, run:

      cf create-service p.mysql PLAN SERVICE-INSTANCE \
    • If you want to update an existing service instance, run:

      cf update-service SERVICE-INSTANCE \

      The -c flag accepts a valid JSON object containing service-specific configuration parameters, provided either in-line or in a file.

  2. 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\
    name          service       plan        bound apps    last operation
    myDB          p.mysql       db-small                  create succeeded


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

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:

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:

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
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.

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:

Type Integer
Default 263 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:

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:

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:

  1. 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.
  2. Run OPTIMIZE TABLE on the indexed tables.
  3. 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:

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
check-circle-line exclamation-circle-line close-line
Scroll to top icon