Hologres:Optimize data write or update performance

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 table storage mode.

  • If you write data to or update data in all columns of a table, the storage modes are listed in the following descending order by performance:

    Row-oriented storage > Column-oriented storage > Row-column hybrid storage.

  • If you write data to or update data in the specified columns of a table, the storage modes are listed in the following descending order by performance:

    Row-oriented storage > Row-column hybrid storage > Column-oriented storage.

• The write or update performance varies based on the write mode.

  The following table describes different write modes of Hologres.

  Write mode	Description
  Insert	Writes data to a table in Append-Only mode if no primary key is configured for the destination table.
  InsertOrIgnore	Discards the data that you want to write if you write data whose primary key values already exist.
  InsertOrReplace	Replaces existing data if you write data whose primary key values already exist. If the written data does not cover all columns, the null value is inserted into the columns that are missing in the new data.
  InsertOrUpdate	Updates existing data if you write data whose primary key values already exist. This write mode involves updates of an entire row and updates of specific columns of a row. If the written data does not cover all columns, the columns that are missing in the new data are not updated.

  • The write performance of column-oriented tables varies based on the write mode:

    • The destination table without a primary key has the highest performance.

    • If the destination table has a primary key, the write modes are listed in the following descending order by performance:

      InsertOrIgnore > InsertOrReplace ≥ InsertOrUpdate (entire row) > InsertOrUpdate (specific columns).

  • The write modes of row-oriented tables are listed in the following descending order by performance:

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

• Storage modes of tables for which binary logging is enabled are listed in the following descending order by write or update performance:

  Row-oriented storage > Row-column hybrid storage > Column-oriented storage.

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 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 other issues such as slow data reads from source tables occur.

• The CPU utilization is high. For example, the CPU runs at 100% for a long period of time.

  This indicates that the resource usage of Hologres has reached the upper limit. You can use the following methods to troubleshoot the issue:

  • Use basic optimization methods to check whether the high resource usage is caused by invalid basic settings. For more information, see Basic optimization methods in this topic.

  • After you complete the basic optimization operations, check the data synchronization service such as Flink or Data Integration and perform advanced optimization operations to further identify and troubleshoot write issues. For more information, see Optimize write performance of Flink, Optimize write performance of Data Integration, and Advanced optimization methods.

  • Check whether write performance is affected by queries. Concurrent write and query operations may cause high resource usage. You can use slow query logs to check the CPU resource consumption of queries at the time when write operations are performed. If queries negatively affect write performance, you can configure multi-instance high-availability deployment. For more information, see Configure read/write splitting for primary and secondary instances (shared storage).

  • After all optimization operations are complete, if write performance does not meet expectations, you can scale out the Hologres instance.

Hologres can deliver 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 to Hologres instances over a private network to reduce network overheads.

  Hologres supports different network types such as virtual private clouds (VPCs), the classic network, and the Internet. For more information about the application scenarios of Hologres in different network types, see Network information in the "Instance configurations" topic. We recommend that you connect to a Hologres instance over a VPC for data writes especially when you use applications such as Java Database Connectivity (JDBC) and PostgreSQL. The Internet places limits on traffic, and is more unstable than a VPC.

• Use fixed plans to perform write operations.

  The following figure shows the execution process of an SQL statement in Hologres. For more information, see QE.

  • If the SQL statement complies with the characteristics of online analytical processing (OLAP), Hologres follows the implementation process shown on the left side of the figure. Hologres uses components such as the query optimizer (QO) and query engine (QE) to process SQL statements. 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, the next SQL statement can be executed only after the execution of the current SQL statement is complete. This increases latency.

  • If the SQL statement complies with the characteristics of a fixed plan, Hologres follows the implementation process shown on the right side of the figure. In this process, the fixed plan is used. Without components such as the QO, queries can be performed in a simple way by using fixed plans. When you write data to or update data in a row, only the row is locked. This greatly improves the concurrency and performance of 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, fixed plans are not supported:

    • The INSERT ON CONFLICT statement is executed to insert data into multiple rows. Sample statement:

      INSERT INTO test_upsert(pk1, pk2, col1, col2)
          VALUES (1, 2, 5, 6), (2, 3, 7, 8)
      ON CONFLICT (pk1, pk2)
          DO UPDATE SET
              col1 = excluded.col1, col2 = excluded.col2;

    • The INSERT ON CONFLICT statement is executed for partial updates, and some columns of the inserted data do not map to the columns in the destination table.

    • The destination table contains columns of the SERIAL data type.

    • Default properties are configured for the destination 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 types of some columns are not supported by fixed plans.

    If you execute an SQL statement 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 page in the Hologres console, as shown in the following figure. In this case, Hologres uses Hologres Query Engine (HQE) or PostgreSQL Query Engine (PQE) to process the SQL statement. 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 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, and DELETE statements that are executed without using fixed plans in the previous 3 hours.
    SELECT
        *
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '3 h'
        AND command_tag IN ('INSERT', 'UPDATE', 'DELETE')
        AND ARRAY['HQE'] && 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 used to support the use of fixed plans. We recommend that you set the parameters to on at the database level. For more information about how to use fixed plans, see Accelerate the execution of SQL statements by using fixed plans.

    Scenario	GUC setting	Description
    Use a fixed plan to execute the INSERT ON CONFLICT statement to insert data into 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 negatively 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 configured.	In Hologres V1.3 and later, if you execute the INSERT ON CONFLICT statement to write data to columns for which default properties are configured, fixed plans are used by default.	We recommend that you do not configure default properties for a table. Default properties negatively affect write performance. In Hologres V1.1, statements on columns for which default properties are configured cannot be processed by using fixed plans. In Hologres V1.3 and later, statements on columns for which default properties are configured can be processed by using fixed plans.
    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 type of the statement is displayed as SDK in the Real-time Import (RPS) metric section on the Monitoring Information page in the Hologres console, as shown in the following figure. The engine type specified by engine_type of the SQL statement is SDK in slow query logs.

  • Write performance is not improved by using fixed plans

    Possible causes for poor write performance after fixed plans are used:

    • An SDK and HQE are used at the same time to write data to or update data in the table. HQE causes the table to be locked. As a result, data writes by using the SDK need to wait for the lock to be released, which is time-consuming. 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. You can use the Query Insight feature of HoloWeb to quickly check whether a query that uses the fixed plan is waiting for an HQE-caused lock to be released. For more information, see Query Insight.

      -- Query the INSERT, UPDATE, and DELETE statements that are executed without using fixed plans in the previous 3 hours.
      SELECT
          *
      FROM
          hologres.hg_query_log
      WHERE
          query_start >= now() - interval '3 h'
          AND command_tag IN ('INSERT', 'UPDATE', 'DELETE')
          AND ARRAY['HQE'] && engine_type
          AND table_write = '<table_name>'
      ORDER BY
          query_start DESC
      LIMIT 500;

    • If data is written by using an SDK but write performance is poor, check the CPU Utilization (%) metric. If the CPU utilization is kept high, scale out 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. For the generation of binary logs, point queries are required on all the columns in the row of the destination table based on the filtering field. In this example, the point query is performed based on the id field. Point queries on a column-oriented table consume more resources than those performed on a row-oriented table. Storage modes are sorted in the following descending order by performance in writing data to tables with binary logging enabled: Row-oriented table > Column-oriented table.

• Prevent performing real-time write and offline write operations at the same time

  If you write data in offline mode, for example, from MaxCompute to a Hologres table, the Hologres table is locked. If you use Flink or DataWorks Data Integration to write data to a Hologres table in real time by using a fixed plan, 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 real-time write performance. Therefore, we recommend that you prevent performing real-time write and offline write operations on a table at the same time.

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 achieve 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 command that is used to implement batch write operations. For more information about JDBC, see JDBC.

    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[]);

• Write data by executing prepared statements

  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 JDBC.

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 invalid, 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.

• Invalid 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. For 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 if no or invalid segment key is specified for the column-oriented table or the columns that constitute a segment key are not strongly correlated with time. For example, 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 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.

  • For a row-oriented table, if 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, we recommend that you configure the same columns as the clustering key and primary key for row-oriented tables.

  • For column-oriented tables, the settings of clustering keys mainly affect query performance but do not affect write performance.