Configuring Multi-Statement Processing Mode

Scenarios

When you enable multi-statement execution for a proxy instance, you can set multi-statement processing mode to Strict (default value), Loose, or Parse. For details, see Multi-Statement Processing Mode.

Procedure

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page, choose Database > GaussDB(for MySQL).
4. On the Instances page, click the instance name.
5. In the navigation pane, choose Database Proxy, select a proxy instance, and click its name.
6. In the navigation pane, choose Parameter Modification, change the value of multiStatementType to Strict, Loose, and Parse. For details, see Multi-Statement Processing Mode.
7. Click Save to save your change. In the displayed dialog box, click Yes.

Multi-Statement Processing Mode

The changed multi-statement processing mode applies to your proxy instance immediately. You do not need to reboot the proxy instance. If a read/write splitting connection fails due to a multi-statement execution, changing the multi-statement processing mode will not restore the connection. You will need to reconnect the connection manually.

• Strict (default mode)

  If a request containing multiple statements is routed to the primary node, the subsequent requests are all routed to the primary node. Read/write splitting can be restored only after you disconnect the current connection and reconnect it.

  Your proxy instances will not parse these statements, so the performance is better. It is suitable for short connections.

• Loose

  If a request containing multiple statements is routed to the primary node, the subsequent requests of the current connection can still be routed to the primary node or read replicas.

  Your proxy instances will not parse these statements, so the performance is better. It is good for when multiple statements contain only DML SQL statements and do not contain operations like setting session variables, creating temporary tables, creating stored procedures, or executing uncommitted transactions.

• Parse

  If a request containing multiple statements is routed to the primary node, your database proxy instance parses these statements and determines whether to restore read/write splitting for subsequent requests of the current connection based on the operations in the SQL statements. For details about operations in SQL statements, see Parse Description.

  Parsing statements affects the proxy instance performance. The degree of the impact depends on the length and complexity of statements. It is recommended that the statements be less than 100 MB.

Parse Description

If multi-statements contain the operations, listed here, all subsequent requests are routed to the primary node. To restore read/write splitting, you need to disconnect the connection and then re-establish it.

• Creating temporary tables
• Creating stored procedures
• Executing uncommitted transactions (for example, begin is executed but commit or rollback is not executed)
• Executing complex or special syntax. These statements will not be parsed.