About MySQL Server Defaults

Warning: MySQL for Pivotal Platform v2.7x is no longer supported because it has reached the End of General Support (EOGS) phase as defined by the Support Lifecycle Policy. To stay up to date with the latest software and security updates, upgrade to a supported version.

Page last updated:

This topic provides information about the defaults that MySQL for Pivotal Platform applies to its Percona Server components.

Overview

This section lists the server defaults for MySQL for Pivotal Platform service plans. Most of the server defaults are the same for all service plans. However, some server defaults differ depending on the service plan.

For server defaults that are:

Note: You can use optional parameters to change certain server defaults. For more information, see Changing Defaults Using Arbitrary Parameters.

Server Defaults for All Plans

The following table lists the MySQL for Pivotal Platform server defaults that are common to all service plans.

Name Variable Name Default Notes
Max Allowed Packet max-allowed-packet 256 MB If necessary, you can change this size in a session variable.
Table Definition Cache table-definition-cache 8192 For information about changing this setting, see the MySQL documentation.
Reverse Name Resolution skip-name-resolve ON This disables reverse DNS lookups to improve performance. MySQL for Pivotal Platform uses user credentials, rather than 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. Pivotal recommends this security setting to prevent users from manipulating files on the server file system.
For more information, see Making MySQL Secure Against Attackers.
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.
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 permit developers to override the default when they create a service instance.
For more information about using lowercase table names, see the MySQL documentation.
Audit Log audit-log OFF To set to ON, select Enable Server Activity Logging in Monitoring. Logs are written in JSON 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 This setting is dynamically configured to be 50% of the available memory on each service instance.
InnoDB Transaction Log Durability innodb_flush_log_at_trx_commit 1 At each transaction commit, logs are written and flushed to disk.
For more information, see the MySQL documentation.
InnoDB Log Buffer Size innodb-log-buffer-size 32 MB This setting defaults to 32 MB to avoid excessive disk I/O when issuing large transactions.
InnoDB Auto Increment Lock Mode innodb-autoinc-lock-mode 2 This setting uses the interleaved mode. This enables multiple statements to execute at the same time. There can be gaps in auto-incrementing columns.
InnoDB Flush Method innodb_flush_method fsync This setting defines the method used to flush data to InnoDB data and log files.
For more information, see the MySQL documentation.
Character Set character-set-server utf8 This setting defaults all character sets. You can override this during a session.
Relay Log Recovery relay-log-recovery ON When enabled, relay log recovery happens automatically after server startup.
For more information, see the MySQL documentation.
Binary Log Removal expire_log_days 3 This setting is the number of days before binary log files are automatically removed.
For more information, see the MySQL documentation.

Server Defaults for Single Node and Leader-Follower Plans

In addition to the server defaults that are common to all plans, single node and leader-follower plans use the server defaults listed in the following table.

Name Variable Name Default Notes
Max Connections max-connections 750 connections per service instance System processes count towards this limit.
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.
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.
InnoDB Log File Size innodb-log-file-size 256 MB MySQL for Pivotal Platform clusters default to a log-file size of 256 MB.
Collation Server collation-server utf8_general_ci You can override this during a session.
For instructions about viewing available and default collations, see the MySQL documentation.

Server Defaults for Highly Available Cluster Plans

In addition to the server defaults that are common to all plans, HA cluster plans use the server defaults listed in the following table.

Name Variable Name Default Notes
Max Connections max-connections 1500 connections per service instance System processes count towards this limit.
MyISAM Recover Options myisam-recover-options OFF This setting enables MySQL for Pivotal Platform to recover from most MyISAM problems without human intervention.
For more information, see the MySQL documentation.
Event Scheduler event-scheduler OFF MySQL for Pivotal Platform enables the event scheduler so users can create and use events in their dedicated service instances.
InnoDB Log File Size innodb-log-file-size 1024 MB MySQL for Pivotal Platform clusters default to a log-file size of 256 MB.
Collation Server collation-server utf8_unicode_ci You can override this during a session.
For instructions about viewing available and default collations, see the MySQL documentation.