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:
| Parameter | Type | Default | Description | Usage |
|---|---|---|---|---|
workload |
String | mixed |
Set this to read-heavy, mixed, or write-heavy.
See Understanding 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 Understanding 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
Understanding 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.
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.
| Profile | Description |
|---|---|
| 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:
|
| 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:
|
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.
Understanding Synchronous Replication
In a leader-follower topology, 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 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 PCF, 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.