Postgresql 转存恢复数据经验 原作者:阿弟 创作时间:2016-12-18 20:24:41+08 |
doudou586 发布于2016-12-18 20:24:41 评论: 2 浏览: 8154 顶: 892 踩: 1157 |
恢复数据是 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 一般 都是导入完成后再执行,现在我们调整这两个参数
如果不关闭的话,系统在你导入数据的过程中会启用 vacuum 进程来清理数据,vacuum 进程会导致内存占用,一般出现这样的错误 ERROR: canceling autovacuum task。测试过程 中遇到最严重的还会导致服务重启,导致恢复数据失败.
禁用方法:打开 postgresql.conf 找到#autovacuum = on 项目,修改成 autovacuum = off , 注意前面的”#“要去掉,这个参数修改后要重启 postgresql 服务才能生效
在恢复数据的过程,我们希望减少 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_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 |
又提升了一点点
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 参数,关闭磁盘同步,注意,如果在线上系统请不要关闭这个参数来提高性 能,否则可能导致你的数据系统受到破坏,不管你的硬件保障有多好,除非你是用于升级数 据恢复。可能的话可以通过设置 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 参数可以减少 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
从 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 的话还可以提高一点的性能。