神刀安全网

PostgreSQL 性能调优

批量导入性能优化

关闭自动提交

在批量插入数据时,如果每条数据都被自动提交,当中途出现系统故障时,不仅不能保障本次批量插入的数据一致性,而且由于有多次提交操作的发生,整个插入效率也会受到很大的打击。解决方法是,关闭系统的自动提交(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个起重要作用:

  1. shared_buffers

    这是最重要的参数, PostgreSQL 通过 shared_buffers 和内核和磁盘打交道,因此应该尽量大,让更多的数据缓存在 shared_buffers 中。通常设置为实际 RAM 的 10% 是合理的,比如50000(400M),也有说法是 25% – 40%.

    要在系统中设置 kernel.shamax 的值,该值决定了进程可调用最大共享内存数量。

    简单的计算方法是: kernel.shmmax = postgres shared_buffers + 32MB

  2. 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。

  3. effective_cache_size设置稍大,优化器更倾向使用索引扫描而不是顺序扫描,建议的设置为可用空闲内存的 25%,这里的可用空闲内存指的是主机物理内存在运行 pg 时的空闲值。

  4. maintenance_work_mem这里定义的内存只是在 CREATE INDEX, VACUUM 等时用到,因此用到的频率不高,但是往往这些指令消耗比较多的资源,因此应该尽快让这些指令快速执行完毕:给 maintence_work_mem 大的内存,比如 512M(524288)

  5. max_connections通常, max_connections 的目的是防止 max_connections * work_mem 超出了实际内存大小。比如,如果将 work_mem 设置为实际内存的 2% 大小,则在极端情况下,如果有50个查询都有排序要求,而且都使用2% 的内存,则会导致 swap 的产生,系统性能就会大大降低。

  6. FSYNC vs ASYNCPostgreSQL 默认做 fsync,也就是说 PostgreSQL 会等待数据被写入硬盘,才会给 query返回成功的信号。如果设定 sync=no 关闭 fsync 的话, PostgreSQL 不会等待 WAL 写回硬盘,就直接返回 query 成功。通常这个会带来 15-25% 的性能提升.但是缺点就是,如果系统崩溃(断电, PostgreSQL 挂掉)的时候,你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。如果说在系统出问题的时候丢失1-2笔数据是可以接受的,那么 25% 的性能提升是很可观的。

  7. WAL设定fsync 可以选择 on 或者 off

  8. wal_buffersWAL 的储存大小。default 是 64 kb。 实验证明, 设定这个值在 256 kb 到 1 MB 之间会提升效能。

  9. wal_writer_delayWAL 检查 WAL 数据(回写)的间隔时间。值是毫秒(milliseconds)

  10. 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秒

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » PostgreSQL 性能调优

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址