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 describes the defaults that the MySQL for PCF tile applies to its MariaDB components.
InnoDB Log Files: 1 GB
MySQL for PCF clusters default to a log-file size of 1 GB to support large blobs.
Max User Connections: 40
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.
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.
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
Reverse Name Resolution Off
MySQL for PCF authenticates with user credentials, which does not restrict the host that a user connects from. The service defaults to disabling reverse name resolution, to save time on each new connection.
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:
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.
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.
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.
When you scale down from multiple MariaDB nodes to a single node, the primary node continues survives 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.