All Products
Search
Document Center

PolarDB:Manual request distribution among row store and column store nodes

Last Updated:Nov 13, 2023

If different applications are used to send requests for OLAP and OLTP services, you can configure different cluster endpoints for the applications, and then associate row store and column store nodes to different cluster endpoints to implement request distribution.

Manual request distribution solution

Request distribution rules:

  • OLTP service: generally includes read and write requests. All OLTP write requests are distributed to the primary node, and OLTP read requests are distributed to read-only row store nodes or the primary node.

  • OLAP service: generally includes only read requests. All OLTP read requests are distributed to read-only column store nodes.

Manual request distribution solution (Read/Write (Automatic Read/Write Splitting) or Read-only)

  • You can associate the application for OLTP requests with a cluster endpoint that does not contain the read-only column store node, so that OLTP read requests are processed by the primary node or read-only row store node.

  • You can associate the application for OLAP requests with a cluster endpoint that contains only the read-only column store node, so that OLAP read requests are processed by the read-only column store node.

独立

Procedure

You must configure different cluster endpoints for OLTP and OLAP services. For more information about how to configure cluster endpoints, see Manage the endpoints of a cluster.

Take note of the following items when you configure cluster endpoints:

  • Cluster endpoints for the OLTP service:

    • In Read-only mode, only read-only row store nodes are required.

    • In Read/Write (Automatic Read/Write Splitting) mode, we recommend that you select at least one read-only row store node. If Primary Node Accepts Read Requests is enabled in this case, read requests are also distributed to the primary node.

      Note

      In Read/Write (Automatic Read/Write Splitting) mode, all write requests are distributed only to the primary node, regardless of whether the primary node is selected in the Node Settings section.

  • Cluster endpoints for the OLAP service: The OLAP service generally involves only read requests. Therefore, we recommend that you select the Read-only mode. In this mode, at least one read-only column store node is required.

Use hints to forcefully execute a request by using a column store execution plan or a row store execution plan

If the outcome of automatic request distribution does not meet your expectations, you can use hints to manually specify to execute a request by using a column store execution plan or a row store execution plan.

Note
  • A hint takes effect only for the SQL statement in which it is contained, and not for the other statements in the same connection or the statements in other connections.
  • If you execute statements with hints by using MySQL 5.7.7 or earlier, you must add the -comments option when you connect to the database engine. You can use the mysql -version command to check the version of your MySQL client.
  • Forcefully execute a statement by using a column store execution plan

    After a statement is distributed to a read-only column store node, the system determines whether to execute the statement by using a row store execution plan or a column store execution plan based on the estimated execution cost. If the execution cost is larger than the loose_cost_threshold_for_imci value, a column store execution plan is used. Otherwise, a row store execution plan is used. f you want to forcefully execute a statement by using a column store execution plan regardless of the loose_cost_threshold_for_imci value, you can change the threshold value to 0 by adding a /*+SET_VAR(cost_threshold_for_imci=0)*/ hint in the statement. Example:

    /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
    Note When you use /*+SET_VAR()*/ to change the threshold value, the loose_ prefix of the parameter name must be omitted. Otherwise, the hint does not take effect.
  • Forcefully execute a statement by using a row store execution plan
    If you want to forcefully execute a statement by using a row store execution plan, you can add a /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ hint in the statement. Example:
    EXPLAIN SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;