批量导入性能优化
关闭自动提交
在批量插入数据时,如果每条数据都被自动提交,当中途出现系统故障时,不仅不能保障本次批量插入的数据一致性,而且由于有多次提交操作的发生,整个插入效率也会受到很大的打击。解决方法是,关闭系统的自动提交(SET AUTOCOMMIT = OFF),并且在插入开始之前,显式的执行 BEGIN TRANSACTION 命令,在全部插入操作完成之后再执行 COMMIT 命令提交所有的插入操作。
使用 COPY 命令
使用 COPY 在一条命令里装载所有记录,而不是一系列的 INSERT 命令。COPY 命令是为装载数量巨大的数据行优化过的,它不像 INSERT 命令那样灵活,但是在装载大量数据时,系统开销也要少很多。 注:因为 COPY 是单条命令,因此在填充表的时候就没有必要关闭自动提交了。
删除索引
如果你正在装载一个新创建的表,最快的方法是创建表,用 COPY 批量装载,然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间,其它有关该表的查询操作的性能将会受到一定的影响,唯一性约束也有可能遭到破坏。
删除外键约束
和索引一样,"批量地"检查外键约束比一行行检查更加高效。因此,我们可以先删除外键约束,装载数据,然后再重建约束。
临时增大 MAINTENANCE_WORK_MEM
在装载大量数据时,临时增大 maintenance_work_mem 系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX 命令和 ALTER TABLE ADD FOREIGN KEY 命令的执行效率,但是它不会对 COPY 操作本身产生多大的影响。
临时增大 CHECKPOINT_SEGMENTS
临时增大 checkpoint_segments 系统变量的值也可以提高大量数据装载的效率。这是因为在向 PostgreSQL 装载大量数据时,将会导致检查点操作(由系统变量 checkpoint_timeout 声明)比平时更加频繁的发生。在每次检查点发生时,所有的脏数据都必须 flush 到磁盘上。通过提高 checkpoint_segments 变量的值,可以有效的减少检查点的数目。
设置为 UNLOGGED 表
PostgreSQL 的 unlogged table 是不记录 xlog 的,因此批量导入的时候 unlogged 表速度比 logged 表性能要快上一倍。但是, unlogged 表不是异常安全的:数据库崩溃或者异常关闭后, unlogged 表中的数据会被自动删减;另外, unlogged 表中的数据也不会备份到从服务器。任何 unlogged 表上创建的索引也是 unlogged 的。对于批量数据导入,我们可以临时设置表为 unlogged(ALTER TABLE tb SET UNLOGGED),之后再将表重新设置为 logged(ALTER TABLE tb SET LOGGED)。
事后运行 VACUUM ANALYZE
在增加或者更新了大量数据之后,应该立即运行 VACUUM ANALYZE 命令,这样可以保证规划器得到基于该表的最新数据统计。换句话说,如果没有统计数据或者统计数据太过陈旧,那么规划器很可能会选择一个较差的查询规划,从而导致查询效率过于低下。
查询性能优化
关于 PostgreSQL 的性能参数,主要以下5个起重要作用:
-
shared_buffers 这是最重要的参数, PostgreSQL 通过 shared_buffers 和内核和磁盘打交道,因此应该尽量大,让更多的数据缓存在 shared_buffers 中。通常设置为实际 RAM 的 10% 是合理的,比如50000(400M),也有说法是 25% – 40%. 要在系统中设置 kernel.shamax 的值,该值决定了进程可调用最大共享内存数量。 简单的计算方法是: kernel.shmmax = postgres shared_buffers + 32MB
-
work_mem 在 PostgreSQL 8.0之前叫做 sort_mem。PostgreSQL 在执行排序操作时,会根据 work_mem 的大小决定是否将一个大的结果集拆分为几个小的和 work_mem 差不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加 work_mem 有助于提高排序的速度。通常设置为实际 RAM 的 2% – 4%,根据需要排序结果集的大小而定,比如 81920(80M) 注意:这是per connection and per sort的设定。如果有100个连接,每个连接有2个 sort 运算,那么需要的总内存是: 100 * 2 * work_mem。 利用 EXPLAIN ANALYZE 可以检查是否有足够的 work_mem。
-
effective_cache_size 设置稍大,优化器更倾向使用索引扫描而不是顺序扫描,建议的设置为可用空闲内存的 25%,这里的可用空闲内存指的是主机物理内存在运行 pg 时的空闲值。
-
maintenance_work_mem 这里定义的内存只是在 CREATE INDEX,VACUUM 等时用到,因此用到的频率不高,但是往往这些指令消耗比较多的资源,因此应该尽快让这些指令快速执行完毕:给 maintence_work_mem 大的内存,比如 512M(524288)
-
max_connections 通常, max_connections 的目的是防止 max_connections * work_mem 超出了实际内存大小。比如,如果将 work_mem 设置为实际内存的 2% 大小,则在极端情况下,如果有50个查询都有排序要求,而且都使用2% 的内存,则会导致 swap 的产生,系统性能就会大大降低。
-
FSYNC vs ASYNC PostgreSQL 默认做 fsync,也就是说 PostgreSQL 会等待数据被写入硬盘,才会给 query返回成功的信号。如果设定 sync=no 关闭 fsync 的话, PostgreSQL 不会等待 WAL 写回硬盘,就直接返回 query 成功。通常这个会带来 15-25% 的性能提升.但是缺点就是,如果系统崩溃(断电, PostgreSQL 挂掉)的时候,你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。如果说在系统出问题的时候丢失1-2笔数据是可以接受的,那么 25% 的性能提升是很可观的。
-
WAL设定 fsync 可以选择 on 或者 off
-
wal_buffers WAL 的储存大小。default 是 64 kb。 实验证明, 设定这个值在 256 kb 到 1 MB 之间会提升效能。
-
wal_writer_delay WAL 检查 WAL 数据(回写)的间隔时间。值是毫秒(milliseconds)
-
Checkpoints 确保数据回写硬盘。 如果 checkpoint 运行频率高于checkpint_warning 值,dirty data page 会被 flushed 回硬盘。PostgreSQL 会在日志(log)中记录出来,通过观察log,可以来决定 checkpoint_segments 的设定。增加 checkpoint_segments 或者 checkpoint_timeout 可以有一定的效能提升。而唯一的坏处就是如果系统挂了,在重启的时需要多一点时间来回复(系统启动恢复期间数据库是不能用的)鉴于 PostgreSQL 很少挂掉,这个其实可以设定的很长(1天都可以)。 设定:
- checkpoint_segments: 最多的wal log数量,到达后会激发 checkpoint,通常设置定为30
- checkpoint_timeout: 一般设置15-20分钟,常的可以设定1天也没关系
- checkpoint_completion_target: 这个保持不动就好。内建是0.5,意思就是每个 checkpoint 预计在下个 checkpoint 完成前的一半时间内完成。
- checkpoint_warning: 如果checkpint速度快于这个时间,在log中记录。内建是30秒
Reference: PostgreSQL Performance Optimization PostgreSQL 优化数据的批量插入 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|