PostgreSQL数据库导入大量数据时如何优化?

2022年9月11日09:42:48数据库教程评论86 views字数 2117阅读模式

使用PostgreSQL的时候,我们某些时候会往库里插入大量数据,例如,导入测试数据,导入业务数据等等。本篇文章介绍了在导入大量数据时的一些可供选择的优化手段。可以结合自己的情况进行选择。

一、关闭自动提交

关闭自动提交,并且只在每次(数据拷贝)结束的时候做一次提交。
如果允许每个插入都独立地提交,那么PostgreSQL会为所增加的每行记录做大量的处理。 而且在一个事务里完成所有插入的动作的最大的好处就是,如果有一条记录插入失败, 那么,到该点为止的所有已插入记录都将被回滚,这样就不会面对只有部分数据,数据不完整的问题。

postgres=#  \echo :AUTOCOMMIT
on
postgres=# \set AUTOCOMMIT off
postgres=#  \echo :AUTOCOMMIT
off

二、导入阶段不创建索引,或者导入阶段删除索引

如果你正导入一张表的数据,最快的方法是创建表,用COPY批量导入,然后创建表需要的索引。 在已存在数据的表上创建索引要比递增地更新表的每一行记录要快。
如果你对现有表增加大量的数据,可以先删除索引,导入表的数据,然后重新创建索引。 当然,在缺少索引的期间,其它数据库用户的数据库性能将有负面的影响。 并且我们在删除唯一索引之前还需要仔细考虑清楚,因为唯一约束提供的错误检查在缺少索引的时候会消失。(慎重考虑索引带来的影响

三、删除外键约束

和索引一样,整体地检查外键约束比检查递增的数据行更高效。 所以我们也可以删除外键约束,导入表地数据,然后重建约束会更高效。

四、增大maintenance_work_mem

在装载大量的数据的时候,临时增大 maintenance_work_mem 可以改进性能。 这个参数也可以帮助加速CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令。 它不会对COPY本身有很大作用,但是它可以加速创建索引和外键约束。

postgres=# show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

五、单值insert改多值insert

减少SQL解析的时间。

六、关闭归档模式并降低wal日志级别

当使用WAL归档或流复制向一个安装中录入大量数据时,在导入数据结束时,执行一次新的basebackup比执行一次增量WAL更快。
为了防止录入时的增量WAL,可以将wal_level暂时调整为minimal, archive_modet关闭,max_wal_senders设置为0来禁用归档和流复制。 但需修改这些设置需要重启服务。

postgres=# show wal_level;
 wal_level 
-----------
 minimal
(1 row)

postgres=# show  archive_mode;
 archive_mode 
--------------
 off
(1 row)

postgres=# show max_wal_senders;
 max_wal_senders 
-----------------
 0
(1 row)

七、增大max_wal_size

临时增大max_wal_size配置变量也可以让大量数据载入更快。 这是因为向PostgreSQL中载入大量的数据将导致检查点的发生比平常(由checkpoint_timeout配置变量指定)更频繁。
发生检查点时,所有脏页都必须被刷写到磁盘上。 通过在批量数据载入时临时增加max_wal_size,减少检查点的数目。

postgres=# show max_wal_size;
 max_wal_size 
--------------
 1GB
(1 row)

八、使用copy替代insert

COPY针对批量数据加载进行了优化。
COPY命令是为装载数量巨大的数据行优化过的; 它没INSERT那么灵活,但是在大量装载数据的情况下,导致的荷载也少很多。 因为COPY是单条命令,因此填充表的时候就没有必要关闭自动提交了。

如果不能使用COPY,可以使用PREPARE来创建一个预备INSERT, 然后使用EXECUTE多次效率更高。 这样就避免了重复分析和规划INSERT的开销。

九、禁用触发器

导入数据之前先DISABLE掉相关表上的触发器,导入完成后重新让他ENABLE。

ALTER TABLE tab_1 DISABLE TRIGGER ALL; 
导入数据
ALTER TABLE tab_1 ENABLE TRIGGER ALL;

十、相关导数工具:pg_bulkload

pg_bulkload 是 PostgreSQL 的一个高速数据加载工具,相对于 copy 命令。最大的优势是速度。在 pg_bulkload 的直接模式下,它将跳过共享缓冲区和 WAL 缓冲区,直接写入文件。它还包括数据恢复功能,可在导入失败时进行恢复。
地址:https://github.com/ossc-db/pg_bulkload

十一、导入数据后,使用analyze

运行ANALYZE 或者VACUUM ANALYZE可以保证规划器有表数据的最新统计。
如果没有统计数据或者统计数据太陈旧,那么规划器可能选择性能很差的执行计划,导致表的查询性能较差。

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/27665.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定