tencent cloud

Feedback

Enabling/Disabling Parallel Query

Last updated: 2024-10-09 10:53:40
    This document describes how to enable or disable the parallel query feature of TDSQL-C for MySQL via the console or command line.

    Prerequisites

    Database version: TDSQL-C for MySQL 8.0 on kernel version 3.1.8 or later.
    Using new parameters requires the kernel version to be 3.1.15 or later for TXSQL 8.0.

    Parameters

    Note:
    The parallel query feature can be enabled for both read-write and read-only instances, as long as their number of CPU cores is greater than or equal to 4.
    You can enable the parallel query feature for the current instance by setting the txsql_max_parallel_worker_threads and txsql_parallel_degree parameters to a value other than 0 via the console or command line. Parameters and suggested settings are as follows: Parameter information
    Parameter
    Variable Type
    Scope
    Default Value
    Value Range
    Description
    txsql_max_parallel_worker_threads
    Integer
    Global
    0
    0–1024
    The total number of threads of the instance node that can be used for parallel query. If it is set to 0, no parallel thread is available, indicating to disable the parallel query feature.
    txsql_parallel_degree
    Integer
    Global/session
    0
    0–1024
    The maximum number of threads (default parallelism) that can be used during the parallel query of a single statement. 0 indicates to disable the parallel query feature.
    Suggested settings
    Parallelism limit: txsql_parallel_degree indicates the maximum number of threads for the parallel query of a single statement, i.e., the default parallelism. We recommend you limit this value to half of the CPU core quantity of the instance. To ensure the stability, the parallel query feature is disabled for small clusters with fewer than four CPU cores, and you cannot adjust parallel query parameters via the console or command line.
    During the parallel query of a SQL statement, the parallelism set by txsql_parallel_degree will be used by default, which can be adjusted through the HINT statement. For more information, see HINT Statement Control.
    txsql_max_parallel_worker_threads indicates the number of threads of the instance that can be used for parallel query, and txsql_max_parallel_worker_threads / txsql_parallel_degree indicates the maximum number of SQL statements allowed in a parallel query.
    txsql_max_parallel_worker_threads and txsql_parallel_degree control the status of the parallel query feature. When either of them is 0, the feature is disabled.
    TDSQL-C for MySQL offers various parameters for you to set the execution conditions of parallel query for business adaptation and stability. After conditions are set, TDSQL-C for MySQL will check whether each SQL statement can be executed against such conditions like execution cost, number of table rows, and memory usage for the parallel statement execution. Parameters are described as follows:
    Parameter
    Variable Type
    Scope
    Default Value
    Value Range
    Description
    txsql_parallel_cost_threshold
    Numeric
    Global/Session
    50000
    0–9223372036854775807
    The threshold of parallel execution cost. Only statements with an execution cost higher than this threshold will be executed parallelly.
    txsql_parallel_table_record_threshold
    Integer
    Global/Session
    5000
    0–9223372036854775807
    The threshold of parallel table row quantity. Only tables with a row quantity higher than this threshold will be selected as parallel tables.
    txsql_optimizer_context_max_mem_size
    Integer
    Global
    8388608
    0–9223372036854775807
    The maximum memory size that a single statement can apply for in the parallel query plan environment.
    txsql_parallel_execution_max_lob_size
    Integer
    Global/Session
    65536
    128–9223372036854775807
    The memory limit of a single overflow field.
    Note:
    Parallel query parameters take effect immediately after being set, with no instance restart required.
    If the scope of a parameter is session, it takes effect only for the statement.

    Enabling or disabling parallel query in the console

    You can enable or disable the feature by setting parameters on the Parameter Settings page in the TDSQL-C for MySQL console.
    Set txsql_max_parallel_worker_threads and txsql_parallel_degree to a value other than 0 to enable parallel query.
    Set txsql_max_parallel_worker_threads or txsql_parallel_degree to 0 to disable parallel query.
    You can also set execution conditions on the Parameter Settings page. For detailed directions, see Setting Instance Parameters.
    
    

    Specifying the parallel execution mode of a SQL statement through the HINT statement

    TDSQL-C for MySQL allows you to specify the parallel execution mode of a SQL statement through the HINT statement. For detailed directions, see HINT Statement Control.

    Appendix: New Parameters

    Parameter
    Variable Type
    Scope
    Default Value
    Value Range
    Description
    txsql_parallel_partition_verbose
    Boolean
    Global Session
    OFF
    ON/OFF
    Supports using EXPLAIN ANALYZE to print more detailed partition information when it is set to ON.
    txsql_parallel_limit_enabled
    Boolean
    Global Session
    ON
    ON/OFF
    Supports parallel query for ordered data streams (no additional sorting required) with LIMIT constraints when it is set to ON.
    txsql_parallel_rollup_pushdown_threshold
    Integer
    Global Session
    4
    0-100
    Threshold for enabling the ROLL UP pushdown algorithm. Parallel computing is used when the number of GROUP BY fields exceeds this threshold.
    txsql_parallel_force_scan_enabled
    Boolean
    Global Session
    OFF
    ON/OFF
    Enables or disables parallel query for full table scans and full index scans. Parallel query is supported for the two scan modes only when it is set to ON.
    txsql_parallel_force_range_enabled
    Boolean
    Global Session
    ON
    ON/OFF
    Enables or disables parallel query for index range scans (RANGE/REF). Parallel query is supported for the two scan modes only when it is set to ON.
    txsql_parallel_subselect_cost_threshold
    Numeric
    Global Session
    50000
    0-DBL_MAX
    Parallel Execution of cost threshold. Only subqueries and derived tables with an estimated execution cost higher than this threshold can be executed in parallel.
    txsql_parallel_partition_threshold
    Integer
    Global Session
    10000
    0-ULONG_MAX
    NDV threshold for parallel inner tables in Nested Loop Join. Only tables with the number of distinct values in the join field higher than this threshold can be used as parallel tables.
    txsql_parallel_force_probe_enabled
    Boolean
    Global Session
    OFF
    ON/OFF
    Parallel query threshold for a probe table in Hash Join. Parallel query for the probe table is considered when the hash table is larger than the join buffer size.
    txsql_parallel_derived_enabled
    Boolean
    Global Session
    ON
    ON/OFF
    Supports separate parallel query for derived tables when it is set to ON.

    References

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support