作者: 阿弟

日期: 2016-12-18

恢复数据是 dba 工作的一部分，特别是 pg 在做大版本升级时需要停机进行，所以希望 恢复数据的时间越短越好。那如何提高数据恢复的性能呢，下面说说那些参数有影响。

恢复数据的过程其实就是 io 的负载,特别考验硬盘的写能力，还有就是数据写入磁盘的 方法，另外一个就是 cpu 的处理能力，再一个就是内存的大量使用，影响的参数大概有这些:

shared_buffers
work_mem
maintenance_work_mem
checkpoint_segments
autovacuum
fsync
full_page_writes

现在我们来测试恢复一个 2.5G 的 sql_ascii 编码数据文件，恢复后大约是 6G 的数据

硬件环境及 OS

CPU:intel i7 3770K
内存：kingston DDRIII 1600 8G*2
硬盘：SATAIII SSD 120G*2
OS: centos 6.4 kernel 3.2.41
PG 的版本：9.2.3

实验环境准备,将 postgresql 的参数设置成原来默认的参数,修改后重启 postgresql 服务

[postgres@pgsqldb-master data]$ vi postgresql.conf
[postgres@pgsqldb-master data]$ cd ..
[postgres@pgsqldb-master pgdata]$ pg_ctl restart -D ./data/ -l ./data/logfile.txt & 2&>1

建立一个结果存储表

postgres=#
postgres=#CREATE TABLE res(kssj timestamp NOT NULL,jssj timestamp,bz text);
CREATE TABLE
postgres=#
[postgres@pgsqldb-master pgdata]$ cd /pgxlog
[postgres@pgsqldb-master pgxlog]$vi res.sql

下面再写一个 sql 恢复脚本本，取名 res.sql,下面是文件的内容

\c postgres
DROP DATABASE cysoft_database;
CREATE DATABASE cysoft_database ENCODING 'sql_ascii' TEMPLATE template0;
INSERT INTO res (kssj) VALUES(CURRENT_TIMESTAMP);
\c cysoft_database
\i 16.dump
\c postgres
UPDATE res SET jssj=CURRENT_TIMESTAMP,
bz='shared_buffers='||CURRENT_SETTING('shared_buffers')
|| ',work_mem='||CURRENT_SETTING('work_mem')
|| ',maintenance_work_mem='||CURRENT_SETTING('maintenance_work_mem')
|| ',checkpoint_segments='||CURRENT_SETTING('checkpoint_segments')
|| ',fsync='||CURRENT_SETTING('fsync')
WHERE jssj IS NULL;

按 ESC 键再输入:qw 退出

一、不做任何参数恢复数据

[postgres@pgsqldb-master pgxlog]$ psql -f res.sql

查看一下导入数据时系统有什么警告的信息

[postgres@pgsqldb-master pgxlog]$ cat /pgdata/data/logfile.txt
STATEMENT: CREATE PROCEDURAL LANGUAGE plpgsql;
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (1 second apart)
......
......
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: sending cancel to blocking autovacuum PID 7179
DETAIL: Process 7163 waits for AccessExclusiveLock on relation 544596186 of database
544595470.
STATEMENT: ALTER TABLE ONLY bom_yl_detail
ADD CONSTRAINT bom_yl_detail_auto_bh_key UNIQUE (auto_bh);
ERROR: canceling autovacuum task
CONTEXT: automatic analyze of table "cysoft_database.public.bom_yl_detail"
LOG: sending cancel to blocking autovacuum PID 7179
.....
.....
CONTEXT: automatic analyze of table "cysoft_database.public.cl"
ERROR: shared buffer hash table corrupted
STATEMENT: CREATE INDEX kq_month_report_yf_idx ON kq_month_report USING btree (yf);

从上面的信息可以看出，checkpoint_segments 设置太少了，还有 ERROR: canceling autovacuum task 这样的错误，这是 autovacuum 进程开启的导致的，vacuum 和 analyze 一般 都是导入完成后再执行，现在我们调整这两个参数

二、增大 checkpoint_segments,autovacuum = off 恢复数据测试

关闭 autovacuum 进程

如果不关闭的话，系统在你导入数据的过程中会启用 vacuum 进程来清理数据，vacuum 进程会导致内存占用，一般出现这样的错误 ERROR: canceling autovacuum task。测试过程 中遇到最严重的还会导致服务重启，导致恢复数据失败．

禁用方法：打开 postgresql.conf 找到#autovacuum = on 项目，修改成 autovacuum = off ， 注意前面的”#“要去掉，这个参数修改后要重启 postgresql 服务才能生效

增大 checkpoint_segments 参数

在恢复数据的过程,我们希望减少 checkpoint 的次数,所以需要增大 checkpoint_segment 的值。

修改方法： 打开 postgresql.conf 找到 #checkpoint_segment = 3 项 目 ， 修 改 成 checkpoint_segment = 30 ，注意前面的”#“要去掉，这个参数修改后要重启 postgresql 服 务才能生效

[postgres@pgsqldb-master pgxlog]$ psql -f res.sql

从上面的结果来看，有提升，但效果不明显

三、调整 shared_buffer 参数后测试

增大 shared_buffers 参数,就是增大 postgresql 的专用内存数

修改方法：打开 postgresql.conf 找到#shared_buffers = 30MB 项目，修改成 shared_bufferd= 512MB ，注意前面的”#“要去掉，这个参数修改后要重启 postgresql 服务才能生效

 [postgres@pgsqldb-master pgxlog]$ psql -f res.sql 

又提升了一点点

四、调整 work_mem,maintenance_work_memr 参数后测试

pg 建立索引时影响比较大的参数 work_men,maintenance_work_mem,这两个是会话可以 调整的，我们修改一下 res.sql 文件即可

\c postgres
DROP DATABASE cysoft_database;
CREATE DATABASE cysoft_database ENCODING 'sql_ascii' TEMPLATE template0;
INSERT INTO res (kssj) VALUES(CURRENT_TIMESTAMP);
\c cysoft_database
set work_mem = '30MB';
set maintenance_work_mem = '256MB';
\i 16.dump
\c postgres
set work_mem = '30MB';
set maintenance_work_mem = '256MB';
UPDATE res SET jssj=CURRENT_TIMESTAMP,
bz='shared_buffers='||CURRENT_SETTING('shared_buffers')
|| ',work_mem='||CURRENT_SETTING('work_mem')
|| ',maintenance_work_mem='||CURRENT_SETTING('maintenance_work_mem')
|| ',checkpoint_segments='||CURRENT_SETTING('checkpoint_segments')
|| ',fsync='||CURRENT_SETTING('fsync')
WHERE jssj IS NULL;
[postgres@pgsqldb-master pgxlog]$ psql -f res.sql

增加这两个参数值后提升的性能是非常明显的，原因很简单，恢复数据时需要做大量的CREATE INDEX,ALTER TABLE ADD FOREIGN KEY，准备使用的内存大了，所以性能明显的提高了.

五、调整参数 fsync=off 后测试

调整 fsync 参数，关闭磁盘同步，注意，如果在线上系统请不要关闭这个参数来提高性 能，否则可能导致你的数据系统受到破坏，不管你的硬件保障有多好，除非你是用于升级数 据恢复。可能的话可以通过设置 synchronous_commit =off（操作系统或数据库崩溃可能导致 丢失一些最近提交的事务）来提高系统的性能

修改方法：打开 postgresql.conf 找到#fsync = on 项目，修改成 fsync = off ，注意前面的” #“要去掉，这个参数修改后要重启 postgresql 服务才能生效

 [postgres@pgsqldb-master pgxlog]$ psql -f res.sql 

[postgres@pgsqldb-master pgxlog]#sync 

#将数据刷到硬盘上,一定要执行上面的语句

关闭 fsync 后提升的性能是非常明显的，原因很简单，不用磁盘同步，整块的写入提高 了磁盘 io 的性能

六、调整参数 full_page_writes=off 后测试

看文档调整 full_page_writes 参数可以减少 IO 量，减少 wal 的页写入量，注意，如果在 线上系统不要关闭这个参数来提高性能，否则可能导致你的数据系统受到破坏。它的危害类似于 fsync

修改方法：打开 postgresql.conf 找到#full_page_writes = on 项目，修改成 full_page_writes = off ，注意前面的”#“要去掉，这个参数修改后要重启 postgresql 服务才能生效

 [postgres@pgsqldb-master pgxlog]$ psql -f res.sql

几乎没有变法，看来是 fsync 起了优先的作用

总结,恢复数据的参数设置大概如下，修改前后相差 6:19 - 3:52 = 2:27 前面差不多有40%性能的提升

shared_buffers = 512MB
work_mem =30MB
maintenance_work_mem = 256MB
checkpoint_segments = 30
autovacuum =off
fsync = off
full_page_writes=off/on

七、利用 pg_restore 的多进程大幅提高数据恢复的速度

从 CPU 的角度来谈谈如何搞升级数据恢复的性能，恢复数据的过程中最慢就是建立索 引的过程，建立索引时占用系统资源比较大主要是内存，io，cpu 的使用，内存我们可以加 大，硬盘我们可以将 xlog,data,index 分开，就是 cpu 有多个核心无法充分利用，这主要是指 建立一个索引时无法利用多个核心，但我们可以利用多个核心同时建立不同的索引，在 postgresql8.4 以后的版本支持使用 pg_restore 多进程恢复数据功能，但数据备份时必需使用 -Fc 的格式才能支持多进程并发恢复数据，使用 pg_restore 恢复数据时只要加上-j x ,x 代表要 使用多少进程，一般我们有多少个核心，x 就是多少，如我的 cpu 是 8 个核心的话就是-j 8 最合适不过，多了反而会造成 cpu 争用

[postgres@pgsqldb-master pgxlog]$pg_dump cysoft_database -Fc > pg_restore.dump 
#备份一个 pg_restore 恢复格式的数据文件
[postgres@pgsqldb-master pgxlog]$ vi pg_restor_test.sh 
#下面我们建立一个恢复测试脚本内容如下
#!/bin/sh
date;
dropdb cysoft_database;
createdb cysoft_database -T template0 -E sql_ascii;
pg_restore -j 8 -d cysoft_database pg_restore.dump
date;

最后按"ESC"键退出

[postgres@pgsqldb-master pgxlog]$chmod +x pg_restor_test.sh
[postgres@pgsqldb-master pgxlog]$./pg_restor_test.sh > log.txt 2>&1 &
[postgres@pgsqldb-master pgxlog]$ cat log.txt
2013 年 03 月 30 日 星期六 19:54:04 CST
2013 年 03 月 30 日 星期六 19:54:59 CST
[postgres@pgsqldb-master pgxlog]$
[postgres@pgsqldb-master bin]$ psql cysoft_database
psql (9.2.3)
Type "help" for help.
cysoft_database=#
cysoft_database=# select sum(pg_relation_size(relname::text))/1024/1024 as tablesize 
from pg_class where relkind='r' and relname not like 'pg_%' and relname not like 'sql%';
tablesize
-----------------------
2626.5468750000000000
(1 row)
cysoft_database=# select sum(pg_relation_size(relname::text))/1024/1024 as indexsize from
pg_class where relkind='i' and relname not like 'pg_%';
indexsize
-----------------------
1156.0625000000000000
(1 row)

完美，指定 8 进程恢复，用时 55 秒，只有上面单进程最快的 3：52 的零头，这样的硬 件能做到 1 分钟 4G 的数据量恢复已经非常不错了,而且还是指定的 locale，如果是--no-locale 的话还可以提高一点的性能。