Compartir a través de

在 Azure Database for PostgreSQL 灵活服务器中批量上传数据的最佳做法

  • Artículo

适用于： Azure Database for PostgreSQL 灵活服务器

本文介绍了在 Azure Database for PostgreSQL 灵活服务器中批量加载数据的各种方法，以及在空数据库中执行初始数据加载和执行增量数据加载的最佳做法。

加载方法

以下数据加载方法按从最耗时到最不耗时的顺序排列：

  • 运行单记录 INSERT 命令。
  • 在每次提交中分批成 100 到 1000 行。 可以使用事务块在每次提交时包装多条记录。
  • 使用多个行值运行 INSERT。
  • 运行 COPY 命令。

将数据加载到数据库的首选方法是使用 COPY 命令。 如果无法使用 COPY 命令，则使用批处理 INSERT 是次优方法。 使用 COPY 命令进行多线程处理是批量加载数据的最佳方法。

初始数据加载的最佳做法

删除索引

在执行初始数据加载之前，我们建议删除表中的所有索引。 在加载数据后创建索引始终更有效。

删除约束

下面描述了主删除约束：

  • 唯一键约束

    为实现强大性能，我们建议在执行初始数据加载之前删除唯一键约束，并在数据加载完成后重新创建这些约束。 但是，删除唯一键约束会取消对重复数据的保护。

  • 外键约束

    建议在执行初始数据加载之前删除外键约束，并在数据加载完成后重新创建这些约束。

    将 session_replication_role 参数更改为 replica 也会禁用所有外键检查。 但请注意，如果使用不当，进行更改可能会导致数据处于不一致状态。

无日志记录表

在初始数据加载中使用无日志记录表之前，考虑使用此类表的优点和缺点。

使用无日志记录表可以加快数据加载速度。 写入无日志记录表的数据不会写入预写日志。

使用无日志记录表的缺点如下：

  • 崩溃时不安全。 发生崩溃或者执行不彻底的关机后，无日志记录表将被自动截断。
  • 无日志记录表中的数据无法复制到备用服务器。

若要创建无日志记录表或将现有表更改为无日志记录表，请使用以下选项：

  • 使用以下语法创建新的无日志记录表：

    CREATE UNLOGGED TABLE <tablename>;

  • 使用以下语法将现有日志记录表转换为无日志记录表：

    ALTER TABLE <tablename> SET UNLOGGED;

服务器参数优化

  • autovacuum：在初始数据加载期间，最好关闭 autovacuum。 初始加载完成后，我们建议对数据库中的所有表手动运行 VACUUM ANALYZE，然后打开 autovacuum。

注意

请仅在内存和磁盘空间足够时遵循此处的建议。

  • maintenance_work_mem：在 Azure Database for PostgreSQL 灵活服务器实例上最大可设置为 2 千兆字节 (GB)。 maintenance_work_mem 有助于加快自动清空、索引和外键的创建。

  • checkpoint_timeout：在 Azure Database for PostgreSQL 灵活服务器实例上，checkpoint_timeout 值可从从默认设置 5 分钟增加到最大 24 小时。 建议在 Azure Database for PostgreSQL 灵活服务器实例上首次加载数据之前将该值增加到 1 小时。

  • checkpoint_completion_target：建议设置为值 0.9。

  • max_wal_size：可以设置为 Azure Database for PostgreSQL 灵活服务器实例上允许的最大值，在执行初始数据加载时为 64 GB。

  • wal_compression：可以打开。 启用此参数可能会导致预写日志 (WAL) 日志记录期间的压缩和 WAL 重放期间的解压缩会产生一些额外的 CPU 成本。

Azure Database for PostgreSQL 灵活服务器建议

在 Azure Database for PostgreSQL 灵活服务器实例上开始初始数据加载之前，建议：

  • 在服务器上禁用高可用性。 在主服务器上完成初始加载后，可以启用高可用性。
  • 完成初始数据加载后创建只读副本。
  • 在初始数据加载期间尽量减少日志记录量或完全禁用它（例如：禁用 pgaudit、pg_stat_statements、查询存储）。

重新创建索引并添加约束

假设在初始加载之前删除了索引和约束，我们建议使用 maintenance_work_mem 中的高值（如前所述）来创建索引并添加约束。 此外，从 PostgreSQL 版本 11 开始，可以修改以下参数以在初始数据加载后更快地创建并行索引：

  • max_parallel_workers：设置系统可为并行查询提供的最大工作进程数。

  • max_parallel_maintenance_workers：控制可在 CREATE INDEX 中使用的最大工作进程数。

还可以通过在会话级别设置推荐设置来创建索引。 以下示例演示如何执行此操作：

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

增量数据加载的最佳做法

将表分区

始终建议将大型表分区。 分区的一些优点（尤其是在增量加载期间）包括：

  • 基于新增量创建新分区可以高效地将新数据添加到表中。
  • 使表的维护更容易。 可以在增量数据加载期间删除分区，以避免在大型表中进行耗时的删除。
  • 仅增量加载期间更改或添加的分区上会触发 Autovacuum，这使得可以更轻松地维护表中的统计信息。

维护表中的最新统计信息

监视和维护表统计信息对于数据库的查询性能很重要。 这还包括使用增量负载的方案。 PostgreSQL 使用自动清空守护程序来清理死元组并分析表，以更新统计信息。 有关详细信息，请参阅 Autovacuum 监视和优化。

基于外键约束创建索引

在以下情况下，在子表中基于外键创建索引可能有好处：

  • 在父表中更新或删除数据。 在父表中更新或删除数据时，将对子表执行查找。 为加快查找，可以编制子表外键的索引。
  • 查询，其中键列上显示父表和子表的联接。

识别未使用的索引

识别数据库中未使用的索引并删除。 索引是数据加载的开销。 表中的索引越少，数据引入期间的性能就越好。

可以通过两种方式识别未使用的索引：查询存储和索引使用情况查询。

查询存储

查询存储功能有助于识别可根据数据库中的查询使用模式删除的索引。 有关分步指南，请参阅查询存储。

在服务器上启用查询存储后，可以使用以下查询来识别可通过连接到 azure_sys 数据库删除的索引。

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

索引使用情况

还可以使用以下查询来识别未使用的索引：

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

number_of_scans、tuples_read 和 tuples_fetched 列将指示索引使用情况。number_of_scans 列值为零表示未使用的索引。

服务器参数优化

注意

仅当有足够的内存和磁盘空间时，才遵循以下参数的建议。

  • maintenance_work_mem：此参数在 Azure Database for PostgreSQL 灵活服务器实例上最大可设置为 2 GB。 maintenance_work_mem 有助于加快创建索引和添加外键。

  • checkpoint_timeout：在 Azure Database for PostgreSQL 灵活服务器实例上，checkpoint_timeout 值可从从默认设置 5 分钟增加到 10 或 15 分钟。 将 checkpoint_timeout 增加为更大的值（例如 15 分钟）可以减少 I/O 负载，但缺点是如果发生崩溃，则需要更长时间才能恢复。 我们建议在做出更改之前考虑清楚。

  • checkpoint_completion_target：建议设置为值 0.9。

  • max_wal_size：此值取决于 SKU、存储和工作负载。 以下示例演示了为 max_wal_size 设置正确值的一种方法。

    在业务高峰期，通过以下方法得出一个合理值：

    a. 运行以下查询获取当前的 WAL 日志序列号 (LSN)：

    SELECT pg_current_wal_lsn ();

    b. 等待 checkpoint_timeout 的秒数。 运行以下查询获取当前的 WAL LSN：

    SELECT pg_current_wal_lsn ();

    c. 使用这两个结果来检查差异（以 GB 为单位）：

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression：可以打开。 启用该参数可能会导致 WAL 日志记录期间的压缩和 WAL 重放期间的解压缩会产生一些额外的 CPU 成本。

后续步骤