OOM Exceptions

For SELECT * FROM t1, if the data volume in table t1 is large (for example, millions or tens of millions of rows) and the LIMIT clause is not used to limit the number of rows returned, the result set will occupy a large amount of memory in the server session buffer and client result cache. If the network transmission or client processing speed is slow, the memory cannot be released in a timely manner. As a result, the memory usage may keep increasing, and even an OOM exception may occur.

• Query only necessary fields instead of using select *.
• Use pagination queries with LIMIT instead of querying all data at a time.

If a query cannot directly return ordered results through indexes (for example, no index is created for the sorting field), the database performs sorting. The memory buffer (sort_buffer) is preferentially used during sorting. The size of the buffer is controlled by the sort_buffer_size parameter.

• Memory sorting: If the amount of data to be sorted is less than the value of sort_buffer_size, the sorting is completed in memory, which is efficient.
• Disk sorting: If the amount of data to be sorted exceeds the value of sort_buffer_size, the database writes some data to temporary files on disks, significantly reducing the sorting efficiency.

Each connection that performs sorting is allocated an independent sort buffer. Even if the sorting memory requirement of a single connection is not high, multiple connections sorting data simultaneously in high-concurrency scenarios may still cause a sharp increase in memory usage and even trigger an OOM exception.

• Sorting a large table (for example, hundreds of millions of rows) where the sorting field has no index requires a full table scan and sorting.

  SELECT * FROM massive_table ORDER BY non_indexed_column DESC;

• Complex group aggregation requires sorting before grouping.

  SELECT user_id, COUNT(*) FROM huge_log_table GROUP BY user_id;

• Sorting the result set after a multi-table JOIN operation where the sorting field has no index requires sorting of massive amounts of data.

  SELECT a.*, b.name FROM big_table a
  JOIN another_table b ON a.id = b.a_id
  ORDER BY a.created_at
  LIMIT 1000 OFFSET 1000000;

• Use indexes to avoid sorting whenever possible. For example, create composite indexes for sorting fields.

  If the query is WHERE a = ? ORDER BY b, create a composite index (a, b) so that ORDER BY can directly use the index sequence to avoid sorting.

• Decrease the value of sort_buffer_size to ensure that it does not exceed 1 MB. For large-specification instances, you can moderately increase the value, but be aware of the memory accumulation effect in high-concurrency scenarios.

When a JOIN operation is performed on a large table and the join condition does not have an index, the database uses the join buffer to cache the scanned rows to accelerate the JOIN operation.

If the join field (such as non_indexed_key) does not have an index, MySQL needs to perform a full table scan and load all matching rows to the join buffer.

The larger the data volume, the higher the memory usage of the join buffer, which may quickly exhaust memory resources.

The join buffer is independently allocated for each JOIN operation. In high-concurrency scenarios, multiple connections perform JOIN operations at the same time, and each connection is allocated an independent join buffer. As a result, the memory usage increases linearly, which may trigger an OOM exception.

• Scenario 1: No index is available for the join condition. As a result, a full table scan is required, and a large amount of data needs to be loaded to the join buffer.

  SELECT *
  FROM large_table_a A
  JOIN large_table_b B ON A.non_indexed_key = B.non_indexed_key;

• Scenario 2: A Cartesian product (without the ON condition) causes all rows in the two tables to be combined, generating a massive M x N result set, which consumes a large amount of memory.

  SELECT * FROM table1, table2;

• Ensure that the join columns have indexes.
  • The join columns of the driven table must have indexes (such as t2.t1_id), which is key to optimizing the performance of JOIN operations.
  • Create indexes for columns used in the ON clause.

  SQL example:

  Assume the query: SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id.

  An index needs to be created for t2.t1_id.

  CREATE INDEX idx_t1_id ON t2(t1_id);

• Select the driving table and driven table.

  MySQL usually selects a small table as the driving table (which can be forcibly specified using STRAIGHT_JOIN) to reduce the number of iterations. A large table is used as the driven table, where matching is accelerated via indexes.

  The following SQL statement forcibly specifies the driving sequence, with small_table as the driving table and large_table as the driven table.

  SELECT* FROM small_table t1 STRAIGHT_JOIN large_table t2 ON t1.id = t2.ref_id;

• Verify the optimization effect using EXPLAIN.
  • type field: Check whether the driven table uses an index (such as ref or range).
  • rows field: Evaluate whether the number of scanned rows decreases due to index optimization.
  • Extra field: Check whether "Using filesort" or "Using temporary" is displayed (further optimization is required).

• Small-scale IN clauses: When the number of values in the IN list is small (for example, a few dozens), MySQL uses a temporary array to store matching values, resulting in low memory usage.
• Large-scale IN clauses: When the number of values in the IN list is large (for example, thousands), it may exceed the limit specified by range_optimizer_max_mem_size. As a result, the optimizer abandons range scan optimization and falls back to full table scan, indirectly causing a sharp increase in memory usage. In addition, an ultra-large IN list increases the time required for SQL parsing and optimization. It is recommended that the number of values in the IN list be limited to within 200. If the number exceeds 200, use batch query or temporary tables instead.

The IN list contains 1,000 IDs.

SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);

Optimization strategy: batch query + service layer combination

Split the large IN list into multiple small batches to reduce the memory usage of a single query and prevent the hash table from becoming too large.

1. Batch query:
   • Split 1,000 IDs into 10 batches, with 100 IDs in each batch.
   • Only 100 IDs are processed in each query, reducing the memory usage of the hash table for a single query.

   -- Batch 1: 100 IDs
   SELECT * FROM users WHERE id IN (1, 2, ..., 100);

   -- Batch 2: 100 IDs
   SELECT * FROM users WHERE id IN (1001, 1002, ..., 200);

   -- Batch 10: 100 IDs
   SELECT * FROM users WHERE id IN (9001, 9002, ..., 1000);

2. Service layer combination:

   Combine the 10 batches of query results at the application layer (such as Java and Python) to return the complete data.

During large-scale concurrent insert or update operations, especially in large transaction scenarios, the InnoDB engine needs to maintain transaction-related lock information and dirty pages in the buffer pool. In addition, memory resources related to the redo log buffer and undo logs are not completely released before the transaction is committed. If a large number of secondary indexes are updated, the buffer pool usage will increase significantly, which may cause a sharp increase in memory usage and even trigger an OOM exception.

• Transaction resource overhead
  • Undo log: used for transaction rollback and Multi-Version Concurrency Control (MVCC). It occupies memory before a transaction is committed.
  • Redo log: records modifications made by transactions to data pages to ensure crash recovery.
• Secondary index update:

  Each update to an index field (for example, UPDATE table SET col1 = value1, col2 = value2) requires maintenance of the corresponding index tree, leading to linear growth in memory consumption.

• Limit the number of values inserted at a time to within 100 to prevent a single transaction from becoming too large.
  Commit the transaction (COMMIT) promptly for each batch to release occupied undo/redo resources.

  -- Insert data in batches (100 rows per batch).
  INSERT INTO users (id, name) VALUES  (1, 'Alice'), (2, 'Bob'), ..., (100, 'Zoe');

• Use LIMIT N to control the number of rows updated at a time (recommended: N ≤ 100) to avoid large transactions.

  Avoid creating unnecessary indexes on columns that are frequently updated to reduce the overhead of updating secondary indexes.

  -- Update data in batches (100 rows per batch).
  UPDATE large_table 
  SET status = 'active' 
  WHERE condition = true 
  LIMIT 100;

When a query or update operation involves a large number of large fields (such as TEXT and BLOB), the database needs to cache a larger result set in memory, causing a sharp increase in memory usage. Specific scenarios include:

• Result set caching: When a large field is queried (for example, SELECT big_text_col FROM logs), the entire field needs to be loaded into memory.
• Sorting operation: When a large field is sorted, only the first max_sort_length bytes and rowid are stored in the sort buffer, and the complete field content is not loaded. However, the complete field still needs to be read into memory during the result set transmission phase.
• Deduplication operation: SELECT DISTINCT big_text_col requires maintaining a temporary hash table or sorting structure in memory.
• Update operation: Updating large fields in batches (for example, UPDATE logs SET big_text_col = REPEAT('a', 10*1024*1024)) generates a large amount of temporary data, which occupies memory.

• Scenario 1: Query a large field, with the result set occupying a large amount of memory.

  SELECT big_text_col FROM logs;  

• Scenario 2: Sorting a large field causes a sharp increase in memory usage.

  SELECT * FROM logs ORDER BY big_text_col;  

• Scenario 3: Deduplicating a large field requires maintaining a temporary structure.

  SELECT DISTINCT big_text_col FROM logs;  

• Scenario 4: Updating a large field in batches generates temporary data.

  UPDATE logs SET big_text_col = REPEAT('a', 10*1024*1024);

• Limit the amount of data to be queried.
  • Use pagination query: Use LIMIT to control the number of rows returned at a time, preventing excessive data from being loaded at once.
  • Avoid full table scans: If only some fields are needed, avoid using SELECT * and query only the necessary fields.

• Optimize sorting and deduplication.
  • Reduce the sorting field size: If the sorting field is a large field, extract the key part (such as the prefix) for sorting.
  • Use indexes: Create indexes for sorting fields to avoid memory sorting.

• Control update operations.
  • Perform batch updates: Split large field updates into small batches to prevent a single transaction from being too large.
  • Avoid redundant updates: Update only the rows that actually need to be modified to reduce unnecessary data processing.