Hologres:Optimize data write or update performance

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

• Use basic optimization methods to check the basic settings for inappropriate settings that cause high resource loads and affect write performance. For more information, see Basic optimization methods.
• After the basic optimization methods are used, you can use Realtime Compute for Apache Flink, Data Integration, or advanced optimization methods of Hologres to determine whether write performance issue exists, and take appropriate optimization measures. For more information, see Optimize write performance for tables for which Realtime Compute for Apache Flink is used, Optimize write performance for tables for which Data Integration is used, 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 by queries at the time when write operations are performed. If queries affect data writes, you can configure multi-instance high-availability deployment. For more information, see Configure multi-instance high-availability deployment.
• After all optimization methods are complete, if write performance does not meet expectations, you can scale up the Hologres instance.

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

• 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. Hologres 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.

  Scenario	GUC setting	Description
  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.
• 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.

update tbl set body =new_body where id='1';

• 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

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

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