LATEST VERSION: 2.2 - CHANGELOG

MySQL Server Defaults

This topic provides information about the defaults that MySQL for Pivotal Cloud Foundry (PCF) 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 PCF 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 PCF 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 PCF 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 PCF 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 PCF 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 CSVs to /var/vcap/sys/log/mysql/mysql-audit-log as well as a remote syslog drain if it is enabled.
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 PCF clusters default to a log-file size of 256 MB.
InnoDB Log Buffer Size innodb-log-buffer-size 32 MB MySQL for PCF 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 PCF 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 PCF, see Using MySQL for PCF.

Optional Parameters for the MySQL for PCF Service Instances

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

Key Type Default Description
workload String mixed You can set this to read-heavy, mixed, or write-heavy. For more information, see Understanding Workload Types.
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. For more information, see Understanding Lowercase Table Names.

Use Optional Parameters

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

  • workload—When creating a new instance, or updating an existing one
  • enable_lower_case_table_names—When creating a new instance

Change Default Parameters when Creating a Service Instance

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

cf create-service SERVICE SERVICE-PLAN 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 p.mysql db-small 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 SERVICE-PLAN 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 p.mysql db-small myDB -c '{ "workload": "mixed" }'

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

Understanding Workload Types

MySQL for PCF 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

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

Create a pull request or raise an issue on the source for this page in GitHub