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.
In HA topology, the MySQL for PCF service uses the following:
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.
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.
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.
This section explains how the MySQL cluster behaves when you change its number of 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.
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.
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.
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.
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.
Read the MariaDB documentation on the InnoDB Buffer Pool for more information.
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.
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
2are 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
When set to
0, the buffer is not flushed at each commit. This means that the buffer can be lost if the
mysqldprocess crashes. If you run
kill -9 mysqld, you might lose some transactions on the local node.
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.
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.
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.
Read the MariaDB documentation on optimizing table_open_cache size for more information.
Read the MariaDB documentation on table_definition_cache for more information.
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 is set to:
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_indexis set to
ON. Addtionally, users need to specify
ROW_FORMAT=COMPRESSEDin 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
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.
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.
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.
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.
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:
wsrep_max_ws_size is set to 2 GB, the maximum allowed by Galera.
For general information, see Transaction Size.