Warning: MySQL for PCF v1.10 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.

This topic describes the high-level component architecture of the MySQL for Pivotal Cloud Foundry (PCF) service, in both its single-node and HA configurations. It also describes the defaults and other design decisions applied to the service’s MariaDB servers and the Galera Cluster that manages them.

HA Topology

In HA topology, the MySQL for PCF service uses the following:

  • Three MySQL servers running MariaDB as the MySQL engine and Galera to keep them in sync

  • Two Switchboard proxies that direct queries to a single backend and provide superfast failover in the event of a backend server failure

  • A customer-provided load balancer that directs traffic to one of the proxies, or to the other proxy in the event of proxy failure

  • Two service brokers that create and bind service instances with failover in the event of a service broker failure

If you use multiple availability zones, the MySQL installer spreads MySQL and Proxy components across zones to make the service durable in the event of a zone failure.


How It Works

At any time, MySQL for PCF normally only sends queries to one of the three MySQL servers. The server that receives the queries is the current master node. Galera ensures that the other two servers mirror any changes made on the current master.

In a three-node Galera cluster, two nodes define a quorum that automatically maintains availability when any node becomes inaccessible. This happens when the host fails or in the event of a network partition.

When a single node cannot communicate with the rest of the cluster, it stops accepting traffic. The other two nodes continue to function normally. When the isolated node regains connectivity, it rejoins the cluster.

If two nodes are no longer able to connect to the cluster, quorum is lost and the cluster becomes inaccessible. To bring the cluster back up, you must restart it manually, as documented in the bootstrapping topic.

Avoid an Even Number of Nodes

Pivotal recommends deploying either one or three MySQL server nodes, avoiding an even number. This prevents a network partition from causing the entire cluster to lose quorum, because neither side has more than half of the nodes.

The minimum number of nodes required to tolerate a single node failure is three. With a two-node cluster, a single node failure causes loss of quorum.

Cluster Scaling Behavior

This section explains how the MySQL cluster behaves when you change its number of nodes.

Scaling Up from One to Three Nodes

When you add new MariaDB nodes to an existing node, they replicate data from the existing primary node and join the cluster once replication is complete. Performance may temporarily degrade while the new nodes sync all data from the original node.

After new nodes have joined the cluster, the proxy continues to route incoming connections to the primary node.

If the proxy detects that the primary node is unhealthy, it severs connections to the primary node and routes all new connections to a different, healthy node.

If the proxy detects that no healthy MariaDB nodes exist in the cluster, it rejects all connections and the cluster becomes inaccessible.

How Apps Are Affected by Scaling Up

When you scale MySQL for PCF from a single-node to a multi-node high availability topology, this single node is restarted and reconfigured as a part of a cluster. During the restart, apps consuming MySQL for PCF lose their connection to the node. The apps should be able to reconnect after the MySQL node is restarted.

Scaling Down from Three to One Node

When you scale down from multiple MariaDB nodes to a single node, the primary node continues to survive as the sole remaining node (provided it remains healthy), and the proxy continues to route incoming connections to the node.

Graceful Removal of Nodes

Removing nodes from a cluster gracefully means that the cluster size decreases while the cluster maintains its healthy state.

You can remove nodes gracefully by manually shutting each down with monit or by decreasing the cluster size as described in the Switch Between Single and HA Topologies section of the Configuring MySQL for PCF topic.

MySQL Server Defaults for MariaDB Components

This section describes the defaults that the MySQL for PCF tile applies to its MariaDB components. There are additional properties which can be customized. See the configuration documentation for more information.

Max User Connections

To ensure all users get fair access to system resources, MySQL for PCF defaults each user’s number of connections to 40. Operators can override this setting for each service plan in the Service Plans configuration pane.

Skip External Locking

Each virtual machine only runs one mysqld process, so they do not need external locking.

Max Allowed Packet: 256 MB

MySQL for PCF allows blobs up to 256 MB. This size is unlikely to limit a user’s query, but is also manageable for our InnoDB log-file size.

Query Cache Type and Size

MySQL for PCF matches MariaDB’s default as of version 10.1.7 and later, by disabling query_cache_type and setting query_cache_size to 0.

InnoDB File-Per-Table Tablespaces

InnoDB allows using either a single file to represent all data, or a separate file for each table. MySQL for PCF uses a separate file for each table (innodb_file_per_table = ON) to optimize flexibility and efficiency. For a full list of pros and cons, see MySQL’s documentation for InnoDB File-Per-Table Mode.

InnoDB File Format: Barracuda

MySQL for PCF uses the Barracuda file format to take advantage of extra features available with the innodb_file_per_table = ON option.

InnoDB Log Files: 1 GB

MySQL for PCF clusters default to a log-file size of 1 GB to support large blobs.

InnoDB Buffer Pool Size

Read the MariaDB documentation on the InnoDB Buffer Pool for more information.

InnoDB Flush Method

Defines the method used to flush data to InnoDB data files, which can affect I/O throughput. MySQL for PCF uses MariaDB’s default, fsync(). Read the MariaDB documentation on InnoDB Flush Method for more information.

InnoDB Flush Log Policy: 2

Galera performs replication before writing to disk. If a single node crashes before the buffer is flushed, the data is safe because Galera has already replicated the transaction to other nodes in the cluster. If all nodes crash simultaneously, it is possible to lose up to a second of transactions.

  • MySQL for PCF v1.9.13 and v1.10.6 set innodb_flush_log_at_trx_commit to 2, which improves the performance of heavily active clusters. Both 0 and 2 are considered safe when running a Galera cluster, because you can recover writes from another node. The last second’s transactions are not flushed to disk only if all nodes in the cluster crash simultaneously.

  • When set to 2, the buffer is flushed at commit but it is not synced. This means that you can safely kill mysqld, but if the OS or the host crashes, you might lose some transactions on the local node.

  • For full ACID compliance, set this policy to 1, which flushes to disk after every commit. Previous versions of MySQL for PCF v1 used 1.

  • When set to 0, the buffer is not flushed at each commit. This means that the buffer can be lost if the mysqld process crashes. If you run kill -9 mysqld, you might lose some transactions on the local node.

Innodb Large Prefix

The innodb_large_prefix feature is enabled by default. When disabled, large index prefixes are silently truncated. When enabled, larger index key prefixes may be created by additionally specifying DYNAMIC or COMPRESSED row formats. Row format is not defined by default. Thus, when innodb_large_prefix is enabled, applications must specify row format for each table that will use large index prefixes.

See also the section on SQL mode before disabling.

InnoDB Strict Mode

Enabling this feature causes the database storage engine (InnoDB) to return errors instead of warnings in certain cases. For more information, refer to the MariaDB strict mode documentation.

Temporary Tables

MySQL converts temporary in-memory tables to temporary on-disk tables when either of the following occurs:

  • A query generates more than 16 million rows of output
  • A query uses more than 32 MB of data space

Read the MariaDB documentation on tmp_table_size for more information.

Table Open Cache

Read the MariaDB documentation on optimizing table_open_cache size for more information.

Table Definition Cache Size

Read the MariaDB documentation on table_definition_cache for more information.

Reverse Name Resolution Off

Disabling reverse DNS lookups improves performance. Clearing this option causes the MySQL servers to perform a reverse DNS lookup on each new connection. Typically, MySQL uses this to restrict access by hostname, but MySQL for PCF uses user credentials to authenticate access, not hostnames. Thus, most deployments do not need reverse DNS lookups.

Slow Query Log

MySQL for PCF automatically enables the slow query log and sets it to record any query that takes longer than 10 seconds. By default, those logs appear in the file /var/vcap/sys/log/mysql/mysql_slow_query.log on each node. For a consolidated view, use a syslog server.

sql_mode Additionally Enables Strict Mode

sql_mode is set to: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES. sql_mode was not set on versions before p-mysql v1.8.0.

  • NO_ENGINE_SUBSTITUTION: Guards against apps unintentionally creating non-InnoDB tables. Previously, the current master would accept, but not replicate such tables across the cluster.
  • NO_AUTO_CREATE_USER: Prevents implicitly creating users without a password.
  • STRICT_ALL_TABLES: Enables strict mode
    • Strict mode means that apps may see errors, such as when inserting strings that are too long, or numeric values that are out of range. Without this setting the default MariaDB behavior is to silently modify data on commit.
    • Strict mode also does not allow a user to specify large indices even if innodb_large_index is set to ON. Addtionally, users need to specify ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED in each table create statement. Apps that do not specify this additional clause see an error during DDL similar to: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
    • The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

Security Configuration Defaults

Restrict Client Access to Server File System

The MariaDB setting secure_file_priv is set to only allow file access from the server directory, /var/vcap/data/mysql/files/. This prevents clients from reading arbitrary files on the server file system. If a user wishes to do a bulk-import, an administrator must first deliver the file to that directory.

MySQL for PCF is configured to prevent the use of symlinks to tables. This is a recommended security setting which prevents users from manipulating files on the server’s file system.

Allow Client to Send Files

This option allows clients to send files to the server. Disabling ensures that clients will not be directed to inadvertently share data with unknown database servers.

Allow Remote Admin Access

When enabled, mysql clients are allowed to connect as administrator from remote hosts. When disabled, administrators must BOSH SSH into each MySQL VM to connect as the MySQL super user. This is a server setting; network configuration and Application Security Groups restrictions may still limit a client’s ability to establish a connection with the databases.

This setting affects the admin and roadmin accounts.

Note: Some errands, notably verify-cluster-schemas, require administrator access. Some errands fail when remote admin access is disabled.

Allow Command History

When disabled, this prohibits the creation of command line history files on the MySQL nodes. If administrators frequently ssh into the MySQL VMs without using BOSH SSH, the default setting of Enabled automatically creates history files of their interactions with the server. These files may contain sensitive information. Disable this option to ensure that no trace files are left on the file system.

MySQL Server Defaults for Galera Components

This section describes some defaults that the MySQL for PCF tile applies to its Galera components.

SST Method: Xtrabackup

When a new node is added to or rejoins a cluster, a primary node from the cluster is designated as the state donor. Then the new node synchronizes with the donor through the State Snapshot Transfer (SST) process.

MySQL for PCF uses Xtrabackup for SST, which lets the state donor node continue accepting reads and writes during the transfer. Galera defaults to using rsync to perform SST, which is usually fastest, but blocks requests during the transfer.

Large Data File Splitting Enabled

MySQL for PCF enables wsrep_load_data_splitting to split large data imports into separate transactions. This facilitates loading large files into a MariaDB cluster.

Maximum Transaction Sizes

These are the maximum transaction sizes set for Galera:

For general information, see Transaction Size.