MySQL Server Defaults

Page last updated:

This topic provides information about the defaults that MySQL for Pivotal Platform applies to its Percona Server components. This topic also provides instructions for how to use optional parameters to change certain server defaults.

Server Defaults

The following table lists the MySQL for Pivotal Platform server defaults.

Name Variable Name Default Notes
Max Connections max-connections 750 connections per service instance System processes count towards this limit.
Max Allowed Packet max-allowed-packet 256 MB You can change this size in a session variable if necessary.
Table Definition Cache table-definition-cache 8192 For more information about updating this variable, see the MySQL documentation.
Reverse Name Resolution skip-name-resolve ON This disables reverse DNS lookups, which improves performance. MySQL for Pivotal Platform uses user credentials, not hostnames, to authenticate access. Therefore, most deployments do not need reverse DNS lookups.
To enable reverse name resolution, clear this option.
Skip Symbolic Links symbolic-links OFF MySQL for Pivotal Platform is configured to prevent the use of symlinks to tables. This recommended security setting prevents users from manipulating files on the server’s file system. For more information, see Making MySQL Secure Against Attackers.
MyISAM Recover Options myisam-recover-options BACKUP, FORCE This setting enables MySQL for Pivotal Platform to recover from most MyISAM problems without human intervention. For more information, see the MySQL documentation.
Log Bin Trust Function Creators log-bin-trust-function-creators ON This setting relaxes constraints on how MySQL writes stored procedures to the binary log. For more information, see the MySQL documentation.
Event Scheduler event-scheduler ON MySQL for Pivotal Platform enables the event scheduler so users can create and use events in their dedicated service instances.
Lower Case Table Names lower-case-table-names 0 By default, all table names are case sensitive. Operators can change this default on the MySQL Configuration page, and may allow developers to override the default when creating a service instance. For more information about the use for lowercase table names, see the MySQL documentation.
Audit Log audit-log OFF When enabled on the MySQL Monitoring pane, logs are written as JSONs to /var/vcap/store/mysql_audit_logs/mysql_server_audit.log.
InnoDB Buffer Pool Size innodb-buffer-pool-size 50% of the available memory on each service instance Dynamically configured to be 50% of the available memory on each service instance.
InnoDB Log File Size innodb-log-file-size 256 MB MySQL for Pivotal Platform clusters default to a log-file size of 256 MB.
InnoDB Log Buffer Size innodb-log-buffer-size 32 MB MySQL for Pivotal Platform defaults to 32 MB to avoid excessive disk I/O when issuing large transactions.
InnoDB Auto Increment Lock Mode innodb-autoinc-lock-mode 2 Auto Increment uses “interleaved” mode. This enables multiple statements to execute at the same time. There may be gaps in auto-incrementing columns.
Collation Server collation-server utf8-general-ci You can override this during a session.
Character Set character-set-server utf8 Defaults all character sets. You can override this during a session.

Changing MySQL Server Defaults

You can set optional parameters to change the MySQL for Pivotal Platform server defaults to accommodate apps with a read-heavy or write-heavy workload, or if you need to use lowercase table names.

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

For general information about using MySQL for Pivotal Platform, see Using MySQL for Pivotal Platform.

Optional Parameters for the MySQL for Pivotal Platform Service Instances

MySQL for Pivotal Platform service instances are configured by default with industry best practices. For more specific use cases, you can customize the following parameters:

Parameter Type Default Description Usage
workload String mixed Set this to read-heavy, mixed, or write-heavy. See About Workload Types below. create-service or update-service
enable_lower_case_table_names Boolean Set by the operator The operator sets a default for this parameter and may allow you to override the default. If allowed, you can only set this when creating a service instance. If you set this to true, table names are stored in lowercase. See About Lowercase Table Names below. create-service
default-charset String utf8 You can set this to any MySQL 5.7 supported character set. See Character Sets and Collations in MySQL. create-service or update-service
default-collation String utf8-general-ci The default-collation changes based on the default-charset. To set the default-collation, first set the default-charset. For a list of available and default collations, see Supported Character Sets and Collations. create-service or update-service
replication_mode String async Set this to async or semi-sync. See About Synchronous Replication. create-service or update-service
semi_sync_ack_timeout_in_ms Integer 263 milliseconds Sets the timeout in milliseconds for the leader to acknowledge a replication operation. See Synchronous Replication Timeout. create-service or update-service

Use Optional Parameters

You can change these default configuration parameters using the cf CLI as follows:

Change Default Parameters when Creating a Service Instance

To create a service instance using optional parameters use the following command:

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

For example:

$ cf create-service myDB -c '{ "workload": "mixed" }'
Creating service instance myDB in org system / space system as admin...
OK
Create in progress. Use 'cf services' or 'cf service myDB' to check operation status.

If you get an error using optional parameters, see Troubleshooting Instances.

Change Default Parameters on an Existing Service Instance

To change the default parameters of an existing instance use the following command:

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.

For example:

$ cf update-service myDB -c '{ "workload": "mixed" }'

If you get an error using optional parameters, see Troubleshooting Instances.

About Workload Types

MySQL for Pivotal Platform offers three workload profiles that developers can use to configure MySQL instances based on their specific app workloads. For instructions on changing configuration options, see Use Optional Parameters.

ProfileDescription
Mixed Workload By default, each MySQL service instance is configured for a mixed workload, that is, a workload that is equally heavy on reads and writes. The configuration for this profile is described in detail in MySQL Server Defaults.
Read-Heavy Workload For apps that have a large number of reads, the service instance can be configured for a read-heavy workload. The read-heavy profile changes these two properties from the MySQL Server Defaults:
  • innodb_buffer_pool_size is increased to 75% of the MySQL VM’s memory
  • innodb_flush_method is set to O_DIRECT
Write-Heavy Workload For apps that write to the database a lot, the service instance can be configured for a write-heavy workload. The write-heavy profile changes these four properties from the MySQL Server Defaults:
  • innodb_buffer_pool_size is increased to 75% of the MySQL VM’s memory
  • innodb_flush_method is set to O_DIRECT
  • innodb_log_file_size is increased to 1GB
  • max_allowed_packets is increased to 1GB

About Lowercase Table Names

If you are migrating a database from a system that was case insensitive, that is, where TableName is the same as TABLEname, then forcing lowercase turns both into tablename. In this way both names are interpreted as the same table.

For more information, see the MySQL documentation.

About Synchronous Replication

In a leader-follower topology, MySQL for Pivotal Platform 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 in data integrity over asynchronous replication. However, sync replication reduces the performance of write operations, depending on latency.

To enable sync replication on a leader-follower service instance, set replication_mode to semi-sync. To restore the default asynchronous replication for a leader-follower service instance, set replication_mode to async.

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 modes automatically reverts to asynchronous without any user intervention. You can manually override this timeout with a lower value.

MySQL Semi-Synchronous Replication

In MySQL for Pivotal Platform, the 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 16.3.9 Semisynchronous Replication in the MySQL documentation.