Changing MySQL Server Defaults

Page last updated:

This topic provides instructions for how to use optional parameters to change server defaults.

Overview

You can configure optional parameters to change certain MySQL for Pivotal Cloud Foundry (PCF) server defaults. You might want to configure optional parameters in the following cases:

Note: You cannot change server defaults for HA cluster plans.

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.

Note: MySQL for PCF 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.

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 \
        -c '{ "PARAMETER": "PARAMETER-VALUE"}'
      
    • If you want to update an existing service instance, run:

      cf update-service p.mysql PLAN SERVICE-INSTANCE \
        -c '{ "PARAMETER": "PARAMETER-VALUE"}'
      

    For a list of available optional parameters, see Optional Parameters below. 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 executed 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 table below 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 below.
Usage create-service or update-service

Workload Profile Types

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

ProfileDescription
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 a lot, you can configure your service instances with a write-heavy workload.

The write-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.
  • 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 above names change to tablename and are interpreted as the same table.

For more information, see the MySQL documentation.

The table below 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 enabled in the tile, you can only set the parameter when you create a service instance.

If you set this to true, table names are stored in lowercase. See About Lowercase Table Names below.

Warning: 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

Character Sets

The table below 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, MySQL for PCF 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 table below describes how to use the replication_mode optional parameter:

Note: replication_mode does not work for single node or HA cluster 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 restore the default asynchronous replication for a leader-follower service instance.
Usage create-service or update-service

Note: In MySQL for PCF, 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.

Note: 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 table below describes how to use the semi_sync_ack_timeout_in_ms optional parameter:

semi_sync_ack_timeout_in_ms
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