You can create custom cluster endpoints for PolarDB for MySQL clusters. When you create custom cluster endpoints, you can set the read/write mode, consistency level, and associated read-only nodes to meet your business requirements and improve service flexibility. This topic describes how to create a custom cluster endpoint for a PolarDB for MySQL cluster.

Procedure

  1. Log on to the PolarDB console.
  2. On the top of the page, select the region where the target cluster is deployed.
  3. Find the target cluster and click the ID of the cluster. The Overview page is displayed.
  4. In the Endpoints section, click Create Custom Cluster Endpoint.Custom cluster endpoint
  5. In the Create Custom Cluster Endpoint dialog box that appears, set the following parameters.
    Parameter Description
    Read/write Mode Specifies the read/write mode for the custom cluster endpoint. Read Only and Read and Write (Automatic Read-write Splitting) are available.
    Note You can modify the read/write mode after you create the custom cluster endpoint. After you modify the read/write mode, existing connections to the cluster endpoint are closed. Make sure that your application can automatically reconnect to the PolarDB for MySQL cluster.
    Reader Nodes The nodes that you associate with the cluster endpoint to process read requests. You can select the primary node and read-only nodes in the Unselected Nodes section.
    Note
    • If you set Read/write Mode to Read and Write (Automatic Read-write Splitting), you must select at least two nodes, including the primary node. Write requests are sent only to the primary node regardless of whether the primary node is selected.
    • If you set Read/write Mode to Read Only, you can select one or more nodes. You can also create a single-node endpoint to fit your needs. For more information, see FAQ.
    Automatically Associate New Nodes Specifies whether a new node is automatically associated with the cluster endpoint.
    Load Balancing Policy The policy for scheduling read requests among multiple read-only nodes if read/write splitting is enabled. The default value is Load-based Automatic Scheduling and cannot be changed.
    Consistency Level
    • If you set Read/write Mode to Read and Write (Automatic Read-write Splitting), the following consistency levels are available: Eventual Consistency, Session Consistency (Recommended), and Global Consistency. For more information, see Data consistency levels.
    • If you set Read/write Mode to Read Only, the default consistency level is Eventual Consistency and cannot be changed.
    Global Consistency Timeout The timeout period elapsed before the latest data is synchronized to read-only nodes. Unit: ms. Valid values: 0 to 6000. Default value: 20.
    Note This parameter is supported only if you set Consistency Level to Global Consistency.
    Global Consistency Timeout Policy Specifies the default policy that is used after data synchronization to a read-only node times out. Valid values:
    • Send Requests to Primary Node (Default)
    • SQL Exception: Wait replication complete timeout, please retry.
    Note You can configure this feature only if you set Consistency Level to Global Consistency.
    Offload Reads from Primary Node After this feature is enabled, SQL statements are sent to only read-only nodes. This reduces the load on the primary node and ensures the stability of the primary node.
    Note You can configure this feature only after you set Read/write Mode to Read and Write (Automatic Read-write Splitting).
    Transaction Splitting Specifies whether to enable the transaction splitting feature. For more information, see Configure transaction splitting.
    Note You can configure this feature only after you set Read/write Mode to Read and Write (Automatic Read-write Splitting) and set Consistency Level to Session Consistency (Recommended) or Global Consistency.
    Connection Pool
    • Off: This is the default option.
    • Session-level Connection Pool

      This option is applicable to the scenarios that involve PHP short-lived connections.

      Frequent PHP short-lived connections increase the load on a database. The session-level connection pool helps you reduce the load that is caused by frequent PHP short-lived connections. If a client is disconnected from a connection, the system determines whether the connection is idle. If the connection is idle, PolarProxy retains the connection in the connection pool for a short period. If the client sends a connection request again and the idle connection is available in the connection pool, the system assigns the idle connection to the client. The assigned connection must match the user, clientip, and dbname variables that are specified in the client request. This reduces overheads that are required to establish a new connection to a database. If no idle connections are available in the connection pool, the system establishes a new connection to the database.

      Note If the session-level connection pool feature is used, the number of concurrent connections to a database is not reduced. However, the times for establishing connections between the client and the database are reduced. This helps you reduce the number of main threads that are consumed to run MySQL queries and improve service performance. However, the connections to the database include the idle connections that are retained in the connection pool.
    • Transaction-level Connection Pool

      This option is applicable to the scenarios that involve a large number of connections, for example, more than 10,000 connections.

      The transaction-level connection pool is used to reduce the number of connections to the database and the load that is caused by frequent short-lived connections. A large number of connections can be established between a client and PolarProxy, but only a small number of connections can be created between PolarProxy and a database. When a client sends a connection request, PolarProxy selects an appropriate connection in the connection pool to forward the request to the database. The selected connection matches the system variables that are specified in the request. After the current transaction is terminated, PolarProxy retains the connection in the connection pool for a short period.

      A transaction connection pool has the following limits:

      • If you perform one of the following operations, a connection is locked until the connection is closed. The locked connection is not retained in the connection pool and is unavailable to other clients.
        • Execute the PREPARE statement.
        • Create a temporary table.
        • Modify user variables.
        • Process large packets, for example, a packet of 16 MB or a larger size.
        • Execute the LOCK TABLE statement.
        • Execute multiple statements in a query.
        • Call stored procedures.
      • The FOUND_ROWS, ROW_COUNT, and LAST_INSERT_ID functions are not supported. These functions can be called, but may return invalid results.
      • If the wait_timeout parameter is set for a connection, the connection to the client may not time out. This is because the system assigns a connection in the connection pool to each client request. If the connection times out, the system closes only the connection between PolarProxy and the database, but retains the connection between the client and PolarProxy.
      • The connection pool matches requests to connections by using the sql_mode, character_set_server, collation_server, and time_zone variables. If the requests include other session-level system variables, you must execute the SET statement in an explicit way to set these additional variables after the connections are established. Otherwise, the connection pool may reuse connections whose system variables have been changed.
      • Connections may be reused. Therefore, the SELECT CONNECTION_ID() statement may return different thread IDs for the same connection in multiple queries.
      • Connections may be reused. Therefore, after you execute the SHOW PROCESSLIST statement, the returned IP addresses and port numbers may be different from the actual IP addresses and port numbers of the clients.
      • PolarProxy merges the results of the SHOW PROCESSLIST statement for all nodes and returns the results to clients. After the transaction-level connection pool is enabled, the thread ID of the connection between the client and PolarProxy is different from that between PolarProxy and the database. As a result, the KILL statement may return an error message even if the KILL statement is executed. You can execute the SHOW PROCESSLIST statement to check whether the connection is disconnected.
    Note You can configure this feature only after you set the read/write mode to Read and Write (Automatic Read-write Splitting) for the default cluster endpoint.
    Parallel Query Specifies whether to enable the parallel query feature. For more information, see Parallel queries.
    Note You can configure this feature only after you set the read/write mode to Read Only for a custom cluster endpoint of the PolarDB for MySQL cluster that is compatible with MySQL 8.0.
  6. Click OK.

FAQ

  • Q: How do I create a single-node endpoint?
    A: You can create one or more single-node endpoints only if Read/write Mode is set to Read Only and the cluster has at least three nodes. When you create single-node endpoints, you can specify only read-only nodes as reader nodes. The reader nodes are the backend nodes that process read requests. For more information, see Procedure.
    Warning However, if you create a single-node endpoint for a read-only node and the read-only node becomes faulty, the single-node endpoint may be unavailable for up to one hour. We recommend that you do not create single-node endpoints in your production environment.
  • Q: What is the maximum number of single-node endpoints that I can create in a cluster?

    A: The maximum number of the single-node endpoints that are allowed in a cluster varies based on the number of nodes in the cluster. If your cluster has three nodes, you can create a single-node endpoint for only one of the read-only nodes. If your cluster has four nodes, you can create a single-node endpoint for two of the read-only nodes, respectively. Similar rules apply if your cluster has five or more nodes.

  • Q: If a single-node endpoint is created for a read-only node, can the read-only node function as the new primary node after a failover?

    A: The read-only node that has a single-node endpoint configured cannot automatically function as the new primary node after a failover. However, you can manually specify the read-only node as the new primary node. For more information, see Switch over services between primary and read-only nodes.

  • Q: What is the maximum number of cluster endpoints that a cluster can have?

    A: A cluster can have a maximum of four cluster endpoints. One is the default cluster endpoint and the other three are custom cluster endpoints.

  • Q: Can I modify a cluster endpoint?

    A: Yes, you can modify the default cluster endpoint and custom cluster endpoints. For more information, see Modify a cluster endpoint.

  • Q: Can I delete a cluster endpoint?

    A: Yes, you can delete a cluster endpoint. However, you can delete only custom cluster endpoints, and you cannot delete the default cluster endpoint. For more information, see Delete a custom cluster endpoint.

Related operations

Operation Description
CreateDBClusterEndpoint Creates a custom cluster endpoint for a PolarDB cluster.
DescribeDBClusterEndpoints Queries cluster endpoints for a PolarDB cluster.