PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
Postgresql 转存恢复数据经验

原作者:阿弟  创作时间:2016-12-18 20:24:41+08  
doudou586 发布于2016-12-18 20:24:41           评论: 0   浏览: 7246   顶: 812  踩: 1045 

Postgresql 转存恢复数据经验

作者: 阿弟

日期: 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
参数使用时间
shared_buffers=32MB,work_mem=1MB,
maintenance_work_mem=16MB,checkpoint_segments=3,
autovacuum=on,fsync=on
00:06:19

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

[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_buffers=32MB,work_mem=1MB,
maintenance_work_mem=16MB,checkpoint_segments=30,
autovacuum=off,fsync=on
00:06:10

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

三、调整 shared_buffer 参数后测试

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

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

 [postgres@pgsqldb-master pgxlog]$ psql -f res.sql 
参数使用时间
shared_buffers=512MB,work_mem=1MB,
maintenance_work_mem=16MB,checkpoint_segments=30,
autovacuum=off,fsync=on
00:05:55

又提升了一点点

四、调整 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
参数使用时间
shared_buffers=512MB,work_mem=30MB,
maintenance_work_mem=256MB,checkpoint_segments=30,
autovacuum=off,fsync=on
00:04:40

增加这两个参数值后提升的性能是非常明显的,原因很简单,恢复数据时需要做大量的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 
参数使用时间
shared_buffers=512MB,work_mem=30MB,
maintenance_work_mem=256MB,checkpoint_segments=30,
autovacuum=off,fsync=off
00:03:52
[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
参数使用时间
shared_buffers=512MB,work_mem=30MB,
maintenance_work_mem=256MB,checkpoint_segments=30,
autovacuum=off,fsync=off,full_page_writes=off
00:03:52

几乎没有变法,看来是 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 的话还可以提高一点的性能。

pg_bot_banner.jpg


评论:0   浏览: 7246                   顶: 812  踩: 1045 

请在登录后发表评论,否则无法保存。


发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)