About MySQL Server Defaults
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:
- Common to all plans, see Server Defaults for All Plans below.
- Specific to single node and leader-follower plans, see Server Defaults for Single Node and Leader-Follower Plans below.
- Specific to highly available (HA) cluster plans, see Server Defaults for Highly Available Cluster Plans below.
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. |