OOM stands for Out of Memory. When the size of memory consumed by queries exceeds the allocated quota, the system throws an OOM error to indicate such an exception. This topic provides answers to some frequently asked questions (FAQ) about OOM.

What is the cause of an OOM error?

Some systems may cache data to the disk to implement the Spill to Disk mechanism when memory resources are insufficient. To ensure high query efficiency, all operators of Hologres consume memory resources to execute queries. Therefore, you may encounter OOM errors that are caused by insufficient memory resources when you use Hologres.

  • Allocation of and limits on memory resources

    A Hologres instance is a distributed system that consists of multiple worker nodes. Different instance types correspond to different numbers of worker nodes. For more information, see Instance types.

    16 CPU cores and 64 GB of memory are allocated to a single worker node in Hologres. This means that a worker node can consume up to 64 GB of memory. If the size of available memory is insufficient for a worker node to execute a query, an OOM error is reported. The memory of a worker node is evenly allocated to computing, caches, and metadata and resident processes. In earlier Hologres versions, up to 20 GB of memory can be allocated to a worker node. From Hologres V1.1.24, this limit is removed, and the system starts to dynamically adjust the memory allocation. To be specific, the system regularly checks the memory usage of worker nodes and allocates available memory resources for query execution as much as possible if a small amount of metadata exists. This way, sufficient memory resources can be reserved for queries.

  • Understand the message of an OOM error
    If the size of memory consumed for computing reaches or exceeds the upper limit, which is 20 GB, an OOM error is reported. The following error message is an example:
    Total memory used by all existing queries exceeded memory limitation. 
    memory usage for existing queries=(2031xxxx,184yy)(2021yyyy,85yy)(1021121xxxx,6yy)(2021xxx,18yy)(202xxxx,14yy); Used/Limit: xy1/xy2 quota/sum_quota: zz/100
    The error message contains the following parts:
    • queries=(2031xxxx,184yy)

      This part is in the format of queries=(Query ID,Required memory size). For example, queries=(2031xxxx,18441803528) indicates that a single worker node consumes 18 GB of memory to execute a query that is identified by the ID of 2031xxxx. An error message displays the five queries that require the most memory resources. You can identify the queries based on the displayed IDs and view more details in slow query logs. For more information, see Query and analyze slow query logs.

    • Used/Limit: xy1/xy2

      This part indicates Consumed memory size for computing/Maximum memory size for computing of a single worker node. The two values are both in bytes. Consumed memory size for computing indicates the total size of memory consumed by the worker node to execute all relevant ongoing queries. For example, Used/Limit: 33288093696/33114697728 indicates that a worker node consumes a total of 33.2 GB of memory to execute all relevant ongoing queries. However, up to 33.1 GB of memory for computing is allowed for a single worker node after dynamic memory allocation. Therefore, an OOM error is reported.

    • quota/sum_quota: zz/100

      In this part, quota indicates a resource group, and zz indicates the percent of resources allocated to the resource group. For example, quota/sum_quota: 50/100 indicates that a resource group is configured for a Hologres instance and 50% of the total instance resources are allocated to the resource group.

  • View the memory usage
    • The Hologres console allows you to view the memory usage by instance. The console aggregates the memory usage of all worker nodes in a Hologres instance. For more information, see Metrics of Hologres.
    • The memory_bytes field in a slow query log displays the memory usage for a single query. The field values are only for reference and may be imprecise. For more information, see Query and analyze slow query logs.

How can I deal with high memory usage?

You can use the Memory Usage metric in the Hologres console to check the overall memory usage of an instance. The memory usage of an instance is considered high if the metric value remains over 80 for a long period. In Hologres, memory resources are reserved by backend processes. Even if no queries are in progress, specific metadata, index data, or cache is still loaded to the memory to improve the computing speed. In this case, a memory usage of 30% to 50% is normal. If the memory usage continues to rise or even approaches 100%, system performance, instance stability, and instance performance may degrade. The following section describes the possible causes, impacts, and solutions of this issue:
  • Causes
    • Metadata occupies excessive memory.

      As the number of tables and data volume grow, the size of memory occupied by metadata is increased. In this case, even if no queries are in progress, the memory usage still grows. We recommend that you include no more than 3,000 tables, including child partitioned tables, in each table group. The more shards a table group contains, the more parts and metadata are generated, and the more memory resources are occupied.

    • Indexes are not properly set.

      For example, for a table in which the majority of fields are of the TEXT type, bitmap indexes are created or dictionary encoding is enabled for excessive fields.

    • Excessive memory is required for computing.

      If a large amount of data needs to be scanned or the computing logic is extremely complex due to one of the following reasons, excessive memory is required for computing: a large number of Count Distinct operations, complex JOIN operations, GROUP BY operations on multiple fields, and operations related to windows.

  • Impacts
    • Instance stability is affected.

      For example, if a large amount of metadata occupies the memory space available for queries, errors such as SERVER_INTERNAL_ERROR, ERPC_ERROR_CONNECTION_CLOSED, and Total memory used by all existing queries exceeded memory limitation may occur during query execution.

    • Instance performance is affected.

      If a large amount of metadata occupies the cache space available for queries, the number of cache hits decreases, and the query latency increases.

  • Solutions
    • If the memory usage of your instance is high due to excessive metadata, delete the data records and tables that no longer need to be queried or reduce the number of partitions as required. This way, specific occupied memory resources can be released.
    • If the memory usage of your instance is high due to improper index settings, adjust the settings by deleting bitmap indexes or disabling dictionary encoding for specific fields as required. When you perform such an adjustment, relevant data is merged, which also consumes resources. We recommend that you adjust index settings during off-peak hours.
    • If the memory usage of your instance is high due to complex computing, respectively optimize the SQL statements for data reads and data writes. For more information, see What can I do if an OOM error is reported for a query? and What can I do if an OOM error is reported during data import or export?.
    • Upgrade the specifications of your instance to increase its computing and storage resources. For more information, see Instances.

What can I do if an OOM error is reported for a query?

  • When you query data, an OOM error may be reported due to one of the following reasons:
    • The execution plan is improper. To be specific, the collected statistics are invalid, or the join order is invalid.
    • A large number of queries are being executed at the same time, and each query requires a large memory size.
    • The query is complex, or a large amount of data needs to be scanned.
    • The query contains the UNION ALL keyword, which increases executor parallelism.
    • A resource group is configured for the instance, but only few resources are allocated to the resource group.
    • A data skew issue occurs or shard pruning is enabled, which leads to an unbalanced load. In this case, some worker nodes have insufficient memory resources.
  • The following section provides more details of each cause and the relevant solutions:
    • Improper execution plan
      • Type 1: Invalid statistics
        You can execute the explain <SQL> statement to query the current execution plan. rows=1000 shown in the following figure indicates that statistics are missing or invalid. In this case, the generated execution plan is improper, which leads to an OOM error that is directly caused by the consumption of a large amount of memory resources. Invalid statistics
        Solutions:
        • Execute the analyze <tablename> statement to update statistics.
        • Use the auto-analyze feature to enable the automatic update of statistics. For more information, see ANALYZE and auto-analyze.
      • Type 2: Invalid join order
        When you perform a hash join operation on two tables, it is reasonable to generate the output hash table based on the table with fewer data rows. You can execute the explain <SQL> statement to query the current execution plan. If the table with fewer data rows is displayed above the table with more data rows, as shown in the following figure, the table with more data rows is used to generate the output hash table. In this case, the join order is invalid, and an OOM error will occur. The following list describes the common causes of invalid join orders:
        • Statistics are not updated in time for tables. In the example shown in the following figure, the number of data rows of the first table framed in red is not updated before the execution plan is generated. In this case, rows=1000 is not the latest statistic. Invalid join order
        • The optimizer fails to generate the optimal execution plan.
        Solutions:
        • Execute the analyze <tablename> statement on tables that you want to join to update statistics in time. This way, a valid join order can be generated.
        • Modify the relevant Grand Unified Configuration (GUC) parameter if the generated join order is still invalid after you execute the analyze <tablename> statement. For example, you can use the following statements that contain optimizer_join_order=query to request the optimizer to determine the join order based on the SQL statement. This method applies to complex queries.
          set optimizer_join_order = query;
          select * from a join b on a.id = b.id; -- Use the b table as the build side of the hash table.
          You can also adjust the join order based on your business requirements.
          Parameter and syntax Description
          set optimizer_join_order = <value> Specifies the join order algorithm to be used by the optimizer. Valid values:
          • query: uses the join order that is specified in the SQL statement. This algorithm has the lowest optimizer overheads.
          • greedy: uses a greedy algorithm to obtain a better join order. This algorithm has moderate optimizer overheads.
          • exhaustive: uses a dynamic planning algorithm to obtain the optimal join order. This is the default value and generates the optimal execution plan. However, this algorithm has the highest optimizer overheads.
      • Type 3: Inaccurate data size estimation

        A join operation usually uses a small table or a subquery with a small data size as the build side to generate a hash table. This optimizes the performance and saves memory resources. However, if a query is too complex or statistics are invalid, the data size estimation becomes inaccurate, and a large table or a subquery with a large data size is used as the build side. In this case, excessive memory resources are required to generate a hash table, and an OOM error occurs.

        Hash (cost=727353.45..627353.35 , rows=970902134 witdh=94) shown in the following figure indicates the build side, in which rows=970902134 indicates the estimated data size that is used to generate a hash table. If the actual data size is smaller, the data size estimation is inaccurate. Execution plan
        Solutions:
        • Check whether statistics are updated for the table that corresponds to the subquery or whether the existing statistics are valid. If they are invalid, execute the analyze <tablename> statement.
        • Use the following GUC parameter to disable data size estimation for hash tables.
          Note By default, this estimation is disabled. It may be enabled for optimization purposes. You can disable it as required.
          set hg_experimental_enable_estimate_hash_table_size =off;
      • Type 4: Broadcasting of a large table

        A broadcast operation replicates the data of a table to all shards. The Broadcast Motion operator is superior only in scenarios where both the number of shards and the number of broadcast tables are small. When a join operation is initiated, the execution plan performs a broadcast operation first. This means that a hash table starts to be generated after the build side data is broadcast. In this case, the data used to generate the hash table in each shard is the full build side data. If a large amount of shards exists or the data size is large, excessive memory resources are required, which leads to an OOM error.

        The example in the following figure shows that the estimated number of data rows is 1 and 80 data rows are used for broadcasting. However, the table contains 80,000,000 data rows. All these data rows are broadcast during actual operations, which consumes excessive memory resources, and thus an OOM error occurs. Type 4
        Solutions:
        • Check whether the estimated number of data rows in the execution plan is valid. If the estimated number is invalid, execute the analyze <tablename> statement to update statistics.
        • Use the following GUC parameter to disable broadcasting and use the Redistribute Motion operator instead.
          set optimizer_enable_motion_broadcast = off;
    • High query concurrency
      The query concurrency is high if only a small size of memory is consumed for a single query but the QPS metric shows a prominent value increase or an error message similar to the following one is returned: HGERR_detl memory usage for existing queries=(2031xxxx,184yy)(2021yyyy,85yy)(1021121xxxx,6yy)(2021xxx,18yy)(202xxxx,14yy);. In this case, you can use the following solutions:
    • Complex query
      If an OOM error occurs for a single query because the query is complex or a large amount of data needs to be scanned, you can use the following solutions:
      • Write cleansed data to Hologres to implement pre-computing. This avoids extract, transform, load (ETL) operations on a large amount of data in Hologres.
      • Add filter conditions to the query.
      • Use a fixed plan or the COUNT DISTINCT operator to optimize SQL statements. For more information, see Optimize the performance of internal table queries.
    • UNION ALL

      The following sample statement contains a large number of subqueries that use the UNION ALL keyword. In this case, the executor needs to process each subquery, which requires excessive memory resources and leads to an OOM error.

      subquery1 union all subquery2 union all subquery3 ...
      You can use the following GUC parameters to force the executor to perform serial execution. This lowers the risk of OOM but also reduces the query speed.
      set hg_experimental_hqe_union_all_type=1;
      set hg_experimental_enable_fragment_instance_delay_open=on;
    • Improper resource group settings
      If an error message similar to the following one is returned, the resource group settings are improper: memory usage for existing queries=(3019xxx,37yy)(3022xxx,37yy)(3023xxx,35yy)(4015xxx,30yy)(2004xxx,2yy); Used/Limit: xy1/xy2 quota/sum_quota: zz/100. In the sample error message shown in the following figure, the value of zz is 10. This indicates that the resource group has only 10% of the instance resources. Improper resource group settings

      Solution: Adjust the resource group settings to ensure that each resource group can have at least 30% of the instance resources.

    • Data skew or shard pruning
      An OOM error may occur even if the overall memory usage of an instance is acceptable. This may be because the memory usage of one or more worker nodes in the instance is high due to a data skew error or shard pruning.
      Note Shard pruning is a pruning feature for queries. After shard pruning is enabled, only data in partial shards are scanned.
      • Execute the following SQL statement to identify whether a data skew error occurs. hg_shard_id is a built-in field in a table, which indicates the shard in which the table resides.
        select count(1) from t1 group by hg_shard_id;
      • View the information about shard pruning from the execution plan. For example, if the value of shard selector is 10(1), only one shard is selected for the query. Execution plan with data skew
      Solutions:
      • Use an appropriate distribution key to avoid data skew.
      • Rebuild your business if a data skew error has occurred.

What can I do if an OOM error is reported during data import or export?

You can import and export data between two Hologres tables or between an internal table and a foreign table. For example, you can import data from a MaxCompute table to a Hologres table. In such scenarios, OOM errors may occur.

  • Tables with many columns or tables with large-sized columns and a high scan concurrency
    When you import data from MaxCompute, if the source MaxCompute table contains a large number of columns or the table contains large-sized columns and has a high scan concurrency, an OOM error occurs. You can use the following GUC parameters as required to control the concurrency of data import and lower the risk of OOM.
    • Tables with many columns (common)
      Note Use the following GUC parameters together with your SQL statements. We recommend that you select at least the first two parameters. If an OOM error persists, specify smaller parameter values.
      -- Set the maximum degree of parallelism (MAXDOP) to read a MaxCompute table. Default value: the number of CPU cores of the instance. Maximum value: 128. We recommend that you do not use a large value. This prevents your queries from affecting other queries and causing errors due to system overloading, especially in data import scenarios. This parameter takes effect in Hologres V1.1 and later. 
      set hg_foreign_table_executor_max_dop = 32;
      
      -- Set the number of data rows to be read at a time from a MaxCompute table. Default value: 8192. 
      set hg_experimental_query_batch_size = 4096;
      
      -- Set the maximum number of DML statements that can be executed at the same time when you read a MaxCompute table. Default value: 32. This parameter is specifically provided for data import and export scenarios to prevent import operations from occupying excessive system resources. This parameter takes effect in Hologres V1.1 and later. 
      set hg_foreign_table_executor_dml_max_dop = 16;
      
      -- Set the size of each shard of a MaxCompute table. Default value: 64. Unit: MB. The shard size affects the concurrency. If a table is large in size, you can increase the value of this parameter to prevent excessive shards from deteriorating query performance. This parameter takes effect in Hologres V1.1 and later. 
      set hg_foreign_table_split_size = 128;
    • Tables with large-sized columns and a high scan concurrency
      If you have adjusted the values of the preceding four parameters, but an OOM error persists, check whether the foreign table has large-sized columns. If yes, set the following parameters as required:
      -- Set the maximum number of batches that can be processed by a single shuffle operation. This parameter can reduce the amount of accumulated data in large-sized columns.
      set hg_experimental_max_num_record_batches_in_buffer = 32;
      
      -- Set the number of data rows to be read at a time from a MaxCompute table. Default value: 8192. 
      set hg_experimental_query_batch_size=128;
  • Excessive duplicate data in foreign tables

    If a foreign table contains excessive duplicate data, data import from the table is slow, and an OOM error may even occur. You can determine the threshold based on your business requirements. For example, you have a foreign table that contains 100,000,000 data rows. You can determine that the table contains excessive duplicate data if 80,000,000 data rows are duplicate.

    Solution: Deduplicate the data in the foreign table before data import or import the data in batches. This prevents a large amount of duplicate data from being imported at the same time.