Expected Increase

Scenarios

The memory usage of a DB instance gradually increases as the service runs, but no out of memory (OOM) or performance exception is triggered. This memory usage increase is positively correlated with the service data volume, number of connections, and cache policy, representing expected resource consumption.

Cause Analysis

• Data volume growth
  • The number of table data and index pages increases, and the InnoDB buffer pool needs to cache more data pages to improve query efficiency.
  • Operations such as intermediate result set query, sorting (ORDER BY), and hash operations (JOIN) occupy more temporary memory.
• Increased number of connections
  • Each connection consumes memory (such as session variables, connection buffers, and temporary tablespaces).
  • In persistent connection scenarios, unreleased session-level caches or temporary resources accumulate.
• Cache policy optimization
  • The database automatically expands the buffer pool to improve the cache hit ratio (for example, MySQL dynamically adjusts the buffer pool by default).
  • The query plan cache and stored procedure cache occupy more memory as the usage frequency increases.
• Write load change
  • Frequent writes increase the memory usage of redo logs and binlog caches.
  • Batch operations or large transactions temporarily occupy extra memory (for example, LOAD DATA INFILE and deleting large tables).

Optimization Suggestions

• Proactive monitoring and prevention
  1. Periodically check the memory usage trend: On the TaurusDB console, check whether the memory usage increase is consistent with the service expectation.
  2. Optimize queries and indexes:
     • Avoid full table scans and reduce temporary tables and sorting operations (for example, add indexes for ORDER BY fields).
     • Use LIMIT for pagination queries to avoid returning large result sets all at once.
• Parameter tuning
  1. Adjust the size of the InnoDB buffer pool:
     • The recommended size is 50% to 70% of the physical memory.
     • Setting it too large may cause insufficient system memory and setting it too small may affect cache efficiency.
  2. Limit the number of connections and resources:
     • Optimize the connection pool configuration at the application layer to prevent a large number of connections from flooding in a short period of time.
     • Use max_connections to limit the maximum number of connections and prevent memory overload.
• Emergency handling
  1. Terminate abnormal sessions: Run the following command to terminate sessions that have not ended for a long time:

     KILL ID;

  2. Scale up the instance: If the memory usage exceeds 85% for a long time, manually upgrade the instance specifications.