优化写入和更新性能

在了解写入或更新的调优手段前，先了解基本的原理，以帮助业务在使用过程中，对不同写入模式的写入性能有着更加合理的预估。

• 不同表存储格式的写入或更新性能。

  • 全列写入或更新时，性能排序如下。

    行存 > 列存 > 行列共存。

  • 部分列写入或更新时，性能排序如下。

    行存 > 行列共存 > 列存。

• 不同写入模式的性能。

  写入模式类型如下。

  写入模式	说明
  Insert	以追加（Append-Only）的方式写入，结果表无主键（PK）。
  InsertOrIgnore	写入时忽略更新，结果表有主键，实时写入时如果主键重复，丢弃后到的数据。
  InsertOrReplace	写入覆盖，结果表有主键，实时写入时如果主键重复，按照主键更新。如果写入的一行数据不包含所有列，缺失的列的数据补Null。
  InsertOrUpdate	写入更新，结果表有主键，实时写入时如果主键重复，按照主键更新。分为整行更新和部分列更新，部分列更新指如果写入的一行数据不包含所有列，缺失的列不更新。

  • 列存表不同写入模式的性能排序如下。

    • 结果表无主键性能最高。

    • 结果表有主键时：

      InsertOrIgnore > InsertOrReplace >= InsertOrUpdate（整行）> InsertOrUpdate（部分列）。

  • 行存表不同写入模式的性能排序如下。

    InsertOrReplace = InsertOrUpdate（整行）>= InsertOrUpdate（部分列） >= InsertOrIgnore。

• 开启Binlog的表写入或更新性能排序如下。

  行存 > 行列共存 > 列存。

在表数据写入或更新时，如果写入性能慢，可通过查看管理控制台的CPU使用率监控指标，初步判断性能瓶颈：

• CPU使用率很低。

  说明Hologres资源没有完全用上，性能瓶颈不在Hologres侧，可自行排查是否存在上游数据读取较慢等问题。

• CPU使用率较高（长期100%）。

  说明已经达到了Hologres的资源瓶颈，可以通过如下方法处理。

  • 通过基本调优手段排查是否因基础设置不到位导致资源负载较高，影响写入性能，详情请参见基本调优手段。

  • 在基本调优手段已经检查完毕后，可以通过写入渠道（常见的如Flink、数据集成）以及Hologres的高级调优手段，更深层次的判断是否存在写入瓶颈，并做相应的处理，详情请参见Flink写入调优、数据集成调优和高级调优手段。

  • 查询影响写入，二者共同执行会导致资源使用率较高，通过慢Query日志排查同一时间查询的CPU消耗。若是查询影响写入，可以考虑为实例配置读写分离高可用部署，详情请参见主从实例读写分离部署（共享存储）。

  • 所有调优手段已操作完毕，但写入性能仍然不满足预期，可适当扩容Hologres实例。

一般情况下Hologres就能达到非常高的写入性能，如果在数据写入过程中觉得性能不符合预期，可以通过以下方法进行常规调优。

• 避免使用公网，减少网络开销。

  Hologres提供VPC、经典、公网等网络类型，适用场景请参见网络配置。推荐在进行数据写入时，尤其是使用JDBC、PSQL等业务应用连接Hologres时，尽量使用VPC网络连接而不是公网连接。因为公网有流量限制，相比VPC网络会更加不稳定。

• 尽量使用Fixed Plan写入。

  一个SQL在Hologres中的执行流程如下图所示，详细原理请参见执行引擎。

  • SQL为普通OLAP写入，那么就会走左侧的链路，会经过优化器（QO）、执行引擎（QE）等一系列组件，数据写入或更新时会整个表拿锁即表锁，如果是并发执行INSERT、UPDATE或DELETE命令，那么SQL间会相互等锁，导致延迟较高。

  • SQL为点查点写，那么就会走右侧的执行链路，此链路统称为Fixed Plan。Fixed Plan的Query特征足够简单，没有QO等组件的开销，因此在写入或更新时是行锁，这样就能极大的提高Query的并发能力及性能。

  因此在优化写入或更新性能时，优先考虑让Query尽量走Fixed Plan。

  • Query走Fixed Plan

    走Fixed Plan的SQL需要符合一定的特征，常见未走Fixed Plan的情形如下。

    • 使用insert on conflict语法进行多行插入更新时，示例如下。

      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;

    • 使用insert on conflict语法进行局部更新时，结果表的列和插入数据的列没有一一对应。

    • 结果表中有SERIAL类型的列。

    • 结果表设置了Default属性。

    • 基于主键的update或delete，如：update table set col1 = ?, col2 = ? where pk1 = ? and pk2 = ?;。

    • 使用了Fixed Plan不支持的数据类型。

    如果SQL没有走Fixed Plan，那么在管理控制台监控指标中实时导入RPS指标则会显示插入类型为INSERT，示例如下。没有走Fixed Plan的SQL，其执行引擎类型为HQE或PQE，大多数情况的写入为HQE。因此当发现写入或更新较慢时，可以通过如下示例语句查看慢Query日志，排查Query的执行引擎类型（engine_type）。

    --示例查看过去3小时未走Fixed Plan的insert/update/delete
    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;

    尽量将执行引擎类型为HQE的Query改写为符合Fixed Plan特征的SDK SQL，从而提高性能。重点关注如下GUC参数，建议DB级别开启GUC参数，更多关于Fixed Plan的使用请参见Fixed Plan加速SQL执行。

    场景	GUC设置	说明
    支持使用insert on conflict语法多行记录的Fixed Plan写入。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;	建议DB级别开启。
    支持含有SERIAL类型列的Fixed Plan写入。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_autofill_series =on;	不建议为表设置SERIAL类型，对写入性能有一定的牺牲。Hologres从 V1.3.25版本开始此GUC参数默认为on。
    支持Default属性的列的Fixed Plan写入。	Hologres从 V1.3版本开始，使用insert on conflict语法写入数据时含有设置了Default属性的字段，则会默认走Fixed Plan。	不建议为表设置Default属性，对写入性能有一定的牺牲。Hologres V1.1版本不支持含有设置了Default属性的字段走Fixed Plan，从V1.3版本开始支持。
    基于主键的UPDATE。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_update =on;	Hologres从 V1.3.25版本开始此GUC参数值默认为on。
    基于主键的DELETE。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_delete =on;	Hologres从 V1.3.25版本开始此GUC参数值默认为on。

    如果SQL走了Fixed Plan，如下图所示监控指标实时导入RPS的类型为SDK。并且在慢Query日志中SQL的engine_type也为SDK。

  • 走了Fixed Plan后写入仍然比较慢。

    SQL已经走了Fixed Plan仍然耗时较长，可能原因如下。

    • 通常情况是该表既有Fixed Plan的SDK写入或更新，又有HQE的写入或更新，HQE是表锁，会导致SDK的写入因为等锁而耗时较长。可以通过以下SQL查询当前表是否有HQE的操作，并根据业务情况优化为SDK的SQL。可以通过HoloWeb Query洞察快速识别出某个Fixed Plan的Query是否有HQE的锁，详情请参见Query洞察。

      --查询表在过去3小时未走Fixed Plan的insert/update/delete
      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;

    • 如果表都是SDK写入，但仍然慢，观察CPU使用率监控指标，若是持续较高，可能已经达到实例资源瓶颈，可适当进行扩容。

• 开启Binlog会降低写入吞吐。

  Hologres Binlog记录了数据变更记录（INSERT、UPDATE、DELETE），会完整的记录每行数据的变更情况。为某张表打开Binlog，以UPDATE语句为例，示例SQL如下：

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

  由于Binlog记录的是整行所有字段的数据，因此在生成Binlog的过程中，需要通过过滤字段（示例中的id字段）去点查目标表的整行数据。如果是列存表的话，这种点查SQL相比行存表会消耗更多的资源，因此开启Binlog的表在写入性能上行存表 > 列存表。

• 同一张表避免并发实时和离线写入。

  离线写入如MaxCompute写入Hologres时是表锁，实时写入大多数是Fixed Plan写入为行锁（例如Flink实时写入或者DataWorks数据集成实时写入），如果对同一个表并发执行离线写入和实时写入，那么离线写入就会拿表锁，实时写入会因为等锁而导致写入性能慢。所以建议同一张表避免并发进行实时和离线写入。

Holo Client、JDBC等客户端在写入数据时，提高写入性能的调优手段如下。

• 攒批写入数据。

  在通过客户端写入时，攒批写入相比单条写入能够提供更高的吞吐，从而提升写入性能。

  • 使用Holo Client会自动攒批，建议使用Holo Client默认配置参数，详情请参见通过Holo Client读写数据。

  • 使用JDBC时可以在JDBC连接串配置WriteBatchedInserts=true，如下所示则可实现攒批的功能，更多JDBC详情请参见JDBC。

    jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true

  未攒批的SQL改造成可攒批的SQL示例如下。

  --未攒批的两个sql
  insert into data_t values (1, 2, 3);
  insert into data_t values (2, 3, 4);
  
  --攒批后的sql
  insert into data_t values (1, 2, 3), (4, 5, 6);
  --攒批的另一种写法
  insert into data_t select unnest(ARRAY[1, 4]::int[]), unnest(ARRAY[2, 5]::int[]), unnest(ARRAY[3, 6]::int[]);

• 使用Prepared Statement模式写入数据。

  Hologres兼容PostgreSQL生态，并基于Postgres的extended协议，支持了Prepared Statement模式，会缓存服务器的SQL编译结果，从而降低了FE、QO等组件的开销，提高写入的性能。相关技术原理请参见如何支持超高QPS在线服务（点查）场景。

  JDBC、Holo Client使用Prepared Statement模式写入数据请参见JDBC。

基本调优手段已经覆盖提升写入性能的基本方法，若是使用正确就能达到很好的写入性能。但是在实际情况中，还有一些其他因素影响性能，比如索引的设置、数据的分布等，高级调优将会介绍在基本调优手段的基础上，如何进一步的排查并提升写入性能，适用于对Hologres原理有进一步了解的业务。

• 数据倾斜导致写入慢。

  如果数据倾斜或Distribution Key设置的不合理，就会导致Hologres实例的计算资源出现倾斜，导致资源无法高效使用从而影响写入性能，排查数据倾斜和对应问题解决方法请参见查看Worker倾斜关系。

• Segment Key设置不合理导致写入慢。

  写入列存表时，设置了不合理的Segment Key可能会极大的影响写入性能，表已有数据量越多性能下降越明显。这是因为Segment Key用于底层文件分段，在写入或更新时Hologres会根据主键反查对应的旧数据，列存表的反查操作需要通过Segment Key快速定位到数据所在的底层文件。如果这张列存表没有配置Segment Key或者Segment Key配置了不合理的字段或者Segment Key对应的字段在写入时没有与时间有强相关性（比如基本乱序），那反查时需要扫描的文件将会非常之多，不仅会有大量的IO操作，而且也可大量占用CPU，影响写入性能和整个实例的负载。此时管控台监控页面的IO吞吐指标往往表现为即使主要是写入作业，其Read指标也非常高。

  因此推荐使用时间戳、日期等字段作为Segment Key，并且在写入时使对应字段的数据与写入时间有强相关性。

• Clustering Key设置不合理导致写入慢

  有主键（PK）的情况下，在写入或更新时，Hologres会根据主键反查对应的旧数据。

  • 对于行存表来说，当Clustering Key与PK不一致时，反查就会需要反查两次，即分别按照PK索引和Clustering Key索引，这种行为会增加写入的延时，所以对于行存表推荐Clustering Key和PK保持一致。

  • 对于列存表，Clustering Key的设置主要会影响查询性能，不会影响写入性能，可以暂不考虑。