Help Center/ TaurusDB/ Best Practices/ Best Practices for TaurusDB Resource Overload/ Configuration Suggestions
Updated on 2026-08-18 GMT+08:00
View PDF
Share

Configuration Suggestions

During the operation of a database system, resource overload is a major factor that can lead to system performance degradation and service interruption. To effectively address and prevent resource overload issues, this section provides detailed configuration suggestions, covering three common overload scenarios: CPU spike, high memory load, and connection exhaustion. These configuration suggestions cover three phases: before overload, during overload, and after overload, and aim to help you take appropriate measures in different phases to ensure system stability and service continuity.

  • Before overload: prevention and optimization

    Appropriate configuration and optimization measures can effectively prevent resource overload.

  • During overload: emergency handling

    When resource overload occurs, emergency measures need to be taken to quickly restore system performance.

  • After overload: continuous optimization

    After the overload issue is resolved, continuous optimization is required to prevent recurrence.

The following table provides configuration suggestions for different phases, allowing you to take appropriate measures to effectively address and prevent resource overload issues and ensure the stable operation of the database system.

Table 1 Configuration suggestions for overload

Overload Type

Phase

Before Overload

During Overload

After Overload

CPU spike
  • When deploying new workloads, use tools such as EXPLAIN and SQL diagnosis to analyze the execution plans of key SQL statements in advance and add indexes based on the optimization suggestions to avoid full table scanning.
  • If the instance specifications are small, scale up the instance specifications appropriately.
  • Enable read/write splitting to route read requests to read replicas, reducing the load on the primary node.
  1. Obtain the SQL statements that obviously cause high CPU usage and perform SQL throttling on them.
  2. If it is difficult to find the SQL statements that obviously cause high CPU usage, use any of the following methods to quickly restore services:
    1. Performing a primary/standby switchover: If the CPU usage of the primary node is high but the CPU usage of a read replica is not high, you can perform a primary/standby switchover to quickly restore services.
    2. Rebooting a read replica: If the CPU usage of a read replica is high, you can reboot it.
    3. Rebooting the instance: Exercise caution when performing this operation because it will clear hot data in the buffer pool. Rebooting the instance is the last resort. If the CPU usage of all nodes is high, you can reboot the instance to quickly restore services.

Optimize SQL statements by using EXPLAIN to analyze the execution plan and adding appropriate indexes to avoid full table scans.

High memory load

Obtain the SQL statements that obviously cause high memory usage and perform SQL throttling on them.

Optimize abnormal SQL statements to prevent them from consuming a large amount of memory. For example:

  1. Query only necessary fields instead of using select *.
  2. Use pagination queries with LIMIT instead of querying all data at a time.
  3. Use indexes to avoid sorting whenever possible. For example, create composite indexes for sorting fields.
  4. When using JOIN, ensure that the join columns of the driven table have indexes. Generally, a small table is selected as the driving table (which can be forcibly specified using STRAIGHT_JOIN).
  5. When using IN for queries, ensure that the value list contains no more than 200 values and that the IN column has an index.
  6. For large INSERT statements, it is recommended that you insert data in batches. Ensure that the number of values following VALUES does not exceed 100.
  7. For large UPDATE statements, it is recommended that you update data in batches. Add LIMIT N to the end of the SQL statement, where N is usually no more than 100.
  8. Avoid querying too many rows containing BLOB or TEXT fields at once. You can use pagination with LIMIT to reduce the amount of data to be queried.

Connection exhaustion

1. Configure a connection pool on the application side. Without a connection pool, the number of connections can easily reach its upper limit.

2. Set parameters such as max_connections, max_user_connections, wait_timeout, and interactive_timeout based on service requirements.

3. Periodically kill idle sessions and long sessions.
  • Temporarily increase the value of max_connections. You are advised to double the current value. After services are restored, change the value back to the original one.
  • Perform SQL throttling on abnormal SQL statements.

Troubleshoot and identify the cause of connection leakage, and rectify the fault. For example, if no connection pool is used on the application side, configure a connection pool.
Parent Topic: Best Practices for TaurusDB Resource Overload