Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Database

How to Use MySQL Tuning Patches?

  • MySQL lock-free tuning:

    In MySQL OLTP applications, a large number of DML statements (INSERT, UPDATE, and DELETE) are concurrently executed on key data structures in the trx_sys global structure, causing resource competition and synchronization bottlenecks in the critical section. After the reconstruction, the lock-free hash table is used to maintain transaction units, prevent lock conflicts, and improve concurrency.

    For details about how to use the patch, see Usage Description.

  • MySQL fine-grained lock tuning:

    In MySQL OLTP applications, a large number of DML statements (INSERT, UPDATE, and DELETE) are concurrently executed on the key data structures in the lock_sys->mutex global lock, causing severe lock competition and performance deterioration. The original lock is replaced with a fine-grained hash bucket lock to prevent lock conflicts and improve concurrency.

    For details about how to use the patch, see Usage Description.

  • MySQL parallel query tuning:

    In the MySQL database, only one thread can be scheduled for a single SQL query, and the multi-core CPU advantages cannot be utilized. This limitation results in suboptimal single-query performance. To resolve this, parallel query is implemented to enhance overall query efficiency.

    For details about how to use the patch, see Patch Usage.

  • MySQL NUMA scheduling tuning:

    In high-concurrency MySQL OLTP applications, the default thread scheduling of the system causes frequent cross-NUMA access of threads. In this case, the CPU overhead increases and the performance deteriorates. Therefore, the foreground threads need to be dynamically bound to fixed NUMA CPUs to reduce cross-NUMA access and ensure that the CPU access load is balanced. Background threads need to be statically bound to fixed NUMA CPUs to reduce cross-NUMA access and improve background thread efficiency.

    For details about how to use the patch, see Usage Description.

  • MySQL pluggable thread pool:

    In high-concurrency MySQL OLTP applications, there are many threads, and the CPU is consumed by resource contention and frequent switchovers. By enabling the thread pool, all tasks are queued for execution based on the system execution capability. The number of tasks processed by each CPU at a time is limited (2 to 5 for the best), which is to ensure stable service processing capabilities.

    For details about how to use the patch, see MySQL Pluggable Thread Pool Feature Guide.

What Are the Technical Principles of the MySQL Thread Pool?

In high-concurrency scenarios, there are many threads, and the CPU is consumed by resource contention and frequent switchovers. By enabling the thread pool, all tasks are queued for execution based on the system execution capability. The number of tasks processed by each CPU at a time is limited (2 to 5 for the best), which is to ensure stable service processing capabilities.

How to Configure the MySQL NUMA Scheduling Parameters?

You can set a valid value for the sched_affinity_foreground_thread parameter by running the following SQL statement:

  • Leave it blank:
    set global sched_affinity_foreground_thread = '';
  • Set it to 0:
    set global sched_affinity_foreground_thread = '0';
  • Set it to 2-5:
    set global sched_affinity_foreground_thread = '2-5';

In addition, MySQL NUMA Scheduling Tuning Feature Guide provides three methods for configuring MySQL NUMA scheduling parameters. You can configure the parameters in the configuration file, startup parameters, or at runtime. Select one of the methods as required.

What Does "The Number of Concurrent Requests Exceeds 32 in the TPC-C Model" Mean?

In Kunpeng BoostKit for Database Technical White Paper, we write "When the TPC-C model is used, the system performance is significantly improved when the number of concurrent requests exceeds 32, and the write performance is improved by more than 15% when the number of concurrent requests exceeds 64." In the TPC-C model, the number of concurrent requests corresponds to the value of terminals, which indicates the number of terminals that are executing concurrently.

If the number of concurrent requests exceeds 32 in the TPC-C model, the system can simultaneously process or support more than 32 independent user sessions or operations. These sessions or operations may include transaction commits, queries, and updates, which execute in parallel on the system and together form the total system load.

What Are the Differences Between JT_INDEX_SCAN and JT_REF?

  • JT_INDEX_SCAN
    • Definition: a query operation in which the database system scans all leaf nodes of an index. This process traverses every entry within the index, regardless of the query conditions.
    • Application scenario: full table scan or query without a clear range restriction.
    • Example: SELECT a FROM t1;
    • This SQL statement returns all records in column a of table t1.
  • JT_RANGE
    • Definition: a range scan operation on an index. The database system scans a specific subset of data within the index, utilizing defined range conditions to establish the start and end points of the scan.
    • Application scenario: range queries, for example, using conditions such as BETWEEN, >, or < to limit the result set to be retrieved.
    • Example: SELECT a FROM t1 WHERE a BETWEEN 10 AND 20;
    • This SQL statement returns values between 10 and 20 in column a.
Parent topic: Acceleration Features