Hologres sometimes may not perform as expected when you use Hologres to write or update data. This topic describes how to optimize data writes and updates to help you improve data write and update performance in your business.

Background information

As an all-in-one data warehouse engine, Hologres supports writes and updates of a large amount of data in real time and provides high performance and low latency in big data scenarios. Hologres provides multiple methods to write and update data.

How it works

Before you learn how to optimize the write or update performance, you must understand how Hologres works. This helps you better estimate write performance of Hologres in different write modes when you use Hologres in actual business.
  • The write or update performance varies based on the storage model that Hologres uses.
    • When Hologres writes data to or updates data in all columns of a table, the performance is sorted in the following way in descending order:

      Row-oriented storage model > column-oriented storage model > row-column hybrid storage model

    • When Hologres writes data to or updates data in the specified columns of a table, the performance is sorted in the following way in descending order:

      Row-oriented storage model > row-column hybrid storage model > column-oriented storage model

  • The write or update performance varies based on the write mode that Hologres uses.
    The following table describes different write modes of Hologres.
    Write modeDescription
    InsertWrites data to a table in Append-Only mode if the result table contains no primary key.
    InsertOrIgnoreDiscards the data that you want to write if the inserted primary key has the same value as the primary key in the result table.
    InsertOrReplaceUpdates the table based on the inserted primary key if the inserted primary key has the same value as the primary key in the result table. If the written data does not cover all columns, the null value is inserted into the columns that are not overwritten.
    InsertOrUpdateUpdates the table based on the inserted primary key if the inserted primary key has the same value as the primary key in the result table. This write mode involves updates of an entire row and updates of specific columns. If the written data does not cover all columns, the columns that are not overwritten are not updated.
    • Write performance of column-oriented tables varies based on the write mode that Hologres uses and is sorted in the following way in descending order.
      • The result table without a primary key has the highest performance.
      • If the result table has a primary key, write performance is sorted in the following way in descending order:

        InsertOrIgnore > InsertOrReplace ≥ InsertOrUpdate (entire row) > InsertOrUpdate (specified columns)

    • Write performance of row-oriented tables varies based on the write mode that Hologres uses and is sorted in the following way in descending order:

      InsertOrReplace = InsertOrUpdate (entire row) ≥ InsertOrUpdate (specified columns) ≥ InsertOrIgnore.

  • The write or update performance of tables for which binary logging is enabled is sorted in the following way in descending order:

    Row-oriented storage model > row-column hybrid storage model > column-oriented storage model.

Troubleshoot a write performance issue

If write performance is low when you write data to or update data in a table, you can view the CPU Utilization metric on the Monitoring Information tab of the instance details page in the Hologres console to locate the performance issue.
  • The CPU utilization is low.

    This indicates that Hologres resources are not fully used and that the performance issue does not occur in Hologres. You can check whether input data is read slowly.

  • The CPU utilization is high, for example, it is kept at 100% for a long period of time.
    This indicates that Hologres reaches the limit of resource usage. You can use the following methods to troubleshoot the issue:

Basic optimization methods

Hologres can implement high write performance in most cases. If write performance does not meet your expectations when you write data, you can use the following methods to optimize performance:
  • Connect a Hologres instance over a private network to reduce network overheads

    Hologres supports different network types such as virtual private cloud (VPC), classic network, and the Internet. For more information about the application scenarios of Hologres in different network types, see Network configurations. We recommend that you connect a Hologres instance by using applications such as Java Database Connectivity (JDBC) and PostgreSQL over a VPC when you perform a write operation. The Internet places limits on traffic, and is more unstable than a VPC.

  • Use a fixed plan to perform write operations
    The following figure shows the execution process of an SQL statement in Hologres. For more information, see QE. Execution process of an SQL statement
    • If the SQL statement complies with the characteristics of online analytical processing (OLAP), Hologres follows the path on the left side of the figure. Hologres uses components such as the query optimizer (QO) and query engine (QE) to process the SQL statement. When you write data to or update data in a table, the entire table is locked. If statements such as INSERT, UPDATE, and DELETE are executed concurrently or repeatedly, a next SQL statement can be executed only after the execution of the preceding SQL statement is complete. This increases latency.
    • If the SQL statement complies with the characteristics of a fixed plan, Hologres follows the path on the right side of the figure. In this path, the fixed plan is used. Without components such as the QO, queries can be performed in a simple way by using fixed plans. When Hologres writes data to or updates a row, only this row is locked. This greatly improves the concurrency and performance of the queries.
    Therefore, you can use fixed plans to perform write or update operations when you optimize the write or update performance.
    • Perform write or update operations by using fixed plans
      SQL statements that can be executed by using fixed plans must comply with the characteristics of the fixed plans. In the following scenarios, an SQL statement is executed without using a fixed plan:
      • The INSERT ON CONFLICT statement is executed to insert data into multiple rows. The following sample code provides an example:
        insert into test_upsert values(1,2,5,6), (2,3,7,8) 
        on conflict(pk1,pk2) do update 
        set col1 = excluded.col1, col2 = excluded.col2;
      • When the INSERT ON CONFLICT statement is executed for partial updates, the columns in the result table do not map to the inserted data in the columns of the source table.
      • The result table contains columns of the SERIAL data type.
      • Default properties are set for the result table.
      • The update or delete operation is performed based on a primary key. Example: update table set col1 = ?, col2 = ? where pk1 = ? and pk2 = ?;
      • The data type of some columns is not supported by a fixed plan.
      If an SQL statement is executed without using a fixed plan, the type of the statement is displayed as INSERT in the Real-time Import (RPS) metric section on the Monitoring Information tab of the instance details page in the Hologres console, as shown in the following figure. RPS metricsHologres uses Hologres Query Engine (HQE) or PostgreSQL Query Engine (PQE) to process SQL statements that are executed without using fixed plans. In most cases, write operations are processed by using HQE. If write or update operations are slow, you can execute the following statement to query the slow query logs and check the engine type used in the queries. The engine type is specified by the engine_type parameter.
      -- Query the INSERT, UPDATE, or DELETE statements that are executed without using fixed plans in the past 3 hours.
      select *
      from hologres.hg_query_log
      where query_start >= now() - interval '3 h'
          and command_tag in ('INSERT','UPDATE','DELETE')
          and 'HQE'=ANY(engine_type)
      order by query_start desc limit 500;
      Change an SQL statement that is processed by using HQE to a Software Development Kit (SDK) SQL statement that complies with the characteristics of a fixed plan. This improves performance. The following table lists the Grand Unified Configuration (GUC) parameters that can be set to support use of fixed plans. We recommend that you set the parameters to on at the database level. This way, fixed plans can be used to accelerate queries. For more information about how to use fixed plans, see Accelerate the execution of SQL statements by using fixed plans.
      ScenarioGUC settingDescription
      Use a fixed plan to execute the INSERT ON CONFLICT statement to write multiple rows.
      alter database <databasename> 
      set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
      We recommend that you set this GUC parameter to on at the database level.
      Use a fixed plan to write data to columns of the SERIAL data type.
      alter database <databasename> 
      set hg_experimental_enable_fixed_dispatcher_autofill_series =on;
      We recommend that you do not specify the SERIAL data type for a table. This data type affects write performance. This GUC parameter is set to on by default in Hologres V1.3.25 and later.
      Use a fixed plan to write data to columns for which default properties are set. In Hologres V1.3 and later, when you execute the INSERT ON CONFLICT statement to write data to columns for which default properties are set, a fixed plan is used by default. In Hologres V1.1, columns for which default properties are set cannot be processed by using fixed plans. In Hologres V1.3 and later, columns for which default properties are set can be processed by using fixed plans. We recommend that you do not set default properties for a table. These properties affect write performance.
      Use a fixed plan to perform an update operation based on a primary key.
      alter database <databasename> 
      set hg_experimental_enable_fixed_dispatcher_for_update =on;
      In Hologres V1.3.25 and later, this GUC parameter is set to on by default.
      Use a fixed plan to perform a delete operation based on a primary key. alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_delete =on;In Hologres V1.3.25 and later, this GUC parameter is set to on by default.
      If an SQL statement is executed by using a fixed plan, the engine type of the statement is displayed as SDK in the Real-time Import (RPS) metric section on the Monitoring Information tab of the instance details page in the Hologres console, as shown in the following figure. The engine type of the SQL statement is SDK in slow query logs. Perform write or update operations by using fixed plans
    • Write performance is not improved for write operations that are accelerated by using fixed plans
      Possible causes for low write performance if fixed plans are used to accelerate write operations:
      • The table is written or updated by using SDK and HQE at the same time. HQE causes the table to lock. This increases the time required by Hologres to write data by using SDK. You can execute the following SQL statement to check for statements that are processed by using HQE. Change the SQL statements to SDK SQL statements based on your business requirements.
        -- Query the INSERT, UPDATE, or DELETE statements that are executed without using fixed plans in the past 3 hours.
        select *
        from hologres.hg_query_log
        where query_start >= now() - interval '3 h'
            and command_tag in ('INSERT','UPDATE','DELETE')
            and 'HQE'=ANY(engine_type)
            and table_write ='<table_name>'
        order by query_start desc limit 500;
      • If the table is written by using SDK but write performance is low, check the CPU utilization metric. If the CPU utilization is kept high, scale up your instance.
  • Disable binary logging to improve the write throughput
    Hologres binary logs record data changes that are caused by executing the INSERT, UPDATE, or DELETE statements, including data changes in each row. The following sample code shows an UPDATE statement that is executed to update the data of a table for which binary logging is enabled:
    update tbl set body =new_body where id='1';
    Binary logs record data of all the columns in a row. When you query data of a column in the result table, Hologres needs to scan the entire table to select the required column from the binary logs. In the preceding sample statement, the id column is queried. Point queries performed on a column-oriented table consume more resources than those performed on a row-oriented table. If the binary logging feature is enabled for the tables, write performance is sorted in the following way in descending order: row-oriented table > column-oriented table.
  • Avoid performing real-time write and offline write operations at the same time

    If you perform offline write operations, for example, you write MaxCompute data to a Hologres table, the Hologres table is locked. If you perform real-time write operations by using a fixed plan, for example, you write data to a Hologres table by using Realtime Compute for Apache Flink or DataWorks Data Integration, only the row into which you insert data is locked. If the offline write and real-time write operations are performed on a table at the same time, the table is locked due to the offline write operations. This increases latency and reduces write performance. Therefore, we recommend that you avoid performing real-time write and offline write operations on a table at the same time.

Optimize write performance for tables for which Holo Client or JDBC is used

You can use the following methods to improve write performance when you write data by using a client such as Holo Client or JDBC.
  • Perform batch write operations
    When you write data by using a client, batch write operations provide higher throughput than single write operations. Batch write operations improve write performance.
    • When you use Holo Client, Hologres automatically performs batch write operations. We recommend that you use the default settings of Holo Client. For more information, see Use Holo Client to read and write data.
    • When you use JDBC, set the WriteBatchedInserts parameter to true to implement batch write operations. The following sample code shows a statement that is used to implement batch write operations. For more information about JDBC, see Use JDBC to connect to Hologres.
      jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
    The following examples show how to change two separate SQL statements that do not support batch write operations to one SQL statement that supports batch write operations:
    -- Two SQL statements that do not support batch write operations:
    insert into data_t values (1, 2, 3);
    insert into data_t values (2, 3, 4);
    
    -- SQL statement that supports batch write operations:
    insert into data_t values (1, 2, 3), (4, 5, 6);
    -- Another SQL statement that supports batch write operations:
    insert into data_t select unnest(ARRAY[1, 4]::int[]), unnest(ARRAY[2, 5]::int[]), unnest(ARRAY[3, 6]::int[]);
  • Execute a prepared statement to perform write operations

    Hologres is compatible with the PostgreSQL ecosystem and is built on top of the extended protocols of PostgreSQL. Hologres allows you to execute a prepared statement to cache the SQL compilation results of a server. This reduces the overheads caused by components such as frontend (FE) and QO, and improves write performance.

    For more information about how to execute a prepared statement to write data by using JDBC or Holo Client, see Use JDBC to connect to Hologres.

Optimize write performance for tables for which Realtime Compute for Apache Flink is used

  • Usage notes for different tables used in Hologres
    • Binary log source table
      • If you specify a table that contains columns of SMALLINT or other unsupported data types for a job, the system may fail to run the job even if these columns are not specified for consumption. Realtime Compute for Apache Flink whose engine is VVR-6.0.3-Flink-1.15 or later can consume Hologres binary logs in JDBC mode. In this mode, columns of multiple data types can be consumed.
      • If the binary logging feature is enabled for a table, we recommend that you specify the row-oriented storage model for the table. If you specify the column-oriented storage model for a table for which the binary logging feature is enabled, more resources are required, and write performance is affected.
    • Dimension table
      • Dimension tables must use row-oriented storage or row-column hybrid storage. Column-oriented tables have large overheads in point queries.
      • When you create a row-oriented table, you must set a primary key and specify the primary key as a clustering key for the table.
      • When you join a Hologres dimension table with another table, you must specify all the fields in the primary keys of the dimension table in the ON clause.
    • Result table
      • Wide tables that are merged and tables whose columns are partially updated must have primary keys. The values of the primary key columns must be stated in and written to each result table in InsertOrUpdate mode. The ignoredelete parameter must be set to true for each result table. This prevents retraction messages from generating delete requests.
      • If a wide table is a column-oriented table and a large number of requests are initiated per second, the CPU utilization becomes high. We recommend that you disable dictionary encoding for the columns in the table.
  • Suggestions on parameter settings of Realtime Compute for Apache Flink
    The default values of the parameters for Hologres connectors are optimal values in most cases. If one of the following issues occurs, you can change the values of the parameters based on your business requirements.
    • Consumption of binary logs causes high latency.

      By default, the number of read rows specified by the binlogBatchReadSize parameter is 100. If the size of data in a row specified by the byte size parameter is small, you can increase the value of the binlogBatchReadSize parameter to reduce consumption latency.

    • Performance of point queries performed on dimension tables is low.
      • Set the async parameter to true to enable the asynchronous mode. In asynchronous mode, multiple requests and responses are processed concurrently. This accelerates queries and improves the query throughput. However, requests are not processed in an absolute order in asynchronous mode.
      • If a dimension table contains a large amount of data and is infrequently updated, we recommend that you use the dimension table cache to optimize query performance. Set the cache parameter to LRU and set the cacheSize parameter to a value greater than the default value 10000 based on your business requirements.
    • Connections are insufficient.

      By default, connections are implemented by using JDBC. If Realtime Compute for Apache Flink has a large number of jobs to process, the number of connections allocated to Hologres may be insufficient. In this case, you can set the connectionPoolName parameter to implement connection sharing among tables that are allocated to the same pool in the same TaskManager.

  • Recommendation order of job development methods

    Flink SQL is more maintainable and portable than DataStream. Therefore, we recommend that you use Flink SQL to implement jobs. If DataStream is required, we recommend that you use Hologres DataStream connectors. For more information, see Hologres DataStream connector. If you want to use a custom DataStream connector, we recommend that you use Holo Client instead of JDBC. The recommended job development methods are sorted in the following way in descending order: Flink SQL > Flink DataStream (connector) > Flink DataStream (Holo Client) > Flink DataStream (JDBC).

  • Diagnosis of low write performance

    In most cases, low write performance may be caused in other steps in a Flink job. You can split a Flink job into multiple nodes and check for backpressure in the Flink job. If backpressure occurs in the data source or some complex compute nodes, the write operations on the result table are slow. In this case, issues occur in Realtime Compute for Apache Flink.

    If the CPU utilization of a Hologres instance is high, for example, it is kept at 100% for a long period of time, the write latency is high. In this case, issues occur in Hologres.

  • For more information about other common exceptions and troubleshooting methods, see Troubleshoot Blink and Flink issues.

Optimize write performance for tables for which Data Integration is used

  • Number of threads and number of connections

    The number of connections to Data Integration in non-script mode is 3 per thread. In script mode, you can set the maxConnectionCount parameter to configure the total number of connections in a job, or set the insertThreadCount parameter to configure the number of connections in a single thread. In most cases, the default values of the number of threads and the number of connections can ensure optimal write performance. You can modify the settings based on your business requirements.

  • Exclusive resource group

    Most jobs of Data Integration are processed in exclusive resource groups. Therefore, the specifications of exclusive resource groups determine the peak performance of the jobs. To ensure high performance, we recommend that you allocate one CPU core to a thread in an exclusive resource group. If the specifications of the resource group are small but the number of concurrent jobs is great, the Java virtual machine (JVM) memory may be insufficient. If the bandwidth for the exclusive resource group is used up, write performance of a job is also affected. In this case, we recommend that you split the job into small jobs and assign the jobs to different resource groups. For more information about the specifications and metrics of exclusive resource groups in Data Integration, see Performance metrics.

  • Diagnosis of low write performance
    • When you write data to Hologres by using Data Integration, if the waiting time consumed on the source is longer than that consumed on the destination, issues occur in the source.
    • If the CPU utilization of a Hologres instance is high, for example, it is kept at 100% for a long period of time, and the write latency is high, issues occur in Hologres.

Advanced optimization methods

After you use the optimization methods described in the preceding sections of this topic to optimize write performance, write performance can be improved in most cases. If write performance does not meet your expectations, check for other factors that affect write performance, such as index settings and data distribution. This section introduces advanced optimization methods to improve write performance based on basic optimization methods. The advanced optimization methods are used to optimize performance in terms of technical principles of Hologres.
  • Uneven distribution of data

    If data is unevenly distributed or the specified distribution key is inappropriate, computing resources of a Hologres instance cannot be evenly distributed. This reduces the use efficiency of resources and affects write performance. For more information about troubleshooting of uneven data distribution issues and solutions, see Query the shard allocation among workers.

  • Inappropriate setting of a segment key

    When you write data to a column-oriented table for which the segment key specified for the table is inappropriate, write performance is affected. As the data volume of the table increases, write performance degrades. The segment key is used to segment underlying files. When you write data to or update data in a table, Hologres queries old data based on the primary key. If the query is performed on a column-oriented table, Hologres locates the underlying files based on the segment key. A large number of files need to be scanned during a data query in one of the following scenarios: No segment key is specified for the column-oriented table; the segment key that you specify is inappropriate; the columns that constitute a segment key are not strongly correlated with time; data in the columns that constitute a segment key is unordered. In this case, a large number of I/O operations are performed, and a large number of CPU resources are consumed. As a result, write performance degrades, and the loads on the instance increase. In the I/O Throughput metric section on the Monitoring Information tab of the instance details page in the Hologres console, the value of the Read metric is high even if the ongoing job involves mainly write operations.

    Therefore, we recommend that you specify columns of the TIMESTAMP or DATE data type as the segment key, and make sure that the data that you want to insert is strongly correlated with the time at which the data is written.

  • Inappropriate setting of a clustering key
    If a primary key is specified for a table, Hologres queries the old data in the table based on the primary key when you write data to or update data in the table.
    • If the query is performed on a row-oriented table and the clustering key is inconsistent with the primary key, Hologres queries the data based on the primary key and the clustering key separately. This increases write latency. Therefore, the clustering key and primary key must be set to the same columns for the row-oriented table.
    • For column-oriented tables, the settings of clustering keys mainly affect query performance but do not affect write performance.