Postgresql转存恢复数据经验
恢复数据是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.965129 |
查看一下导入数据时系统有什么警告的信息
[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.40027 |
从上面的结果来看，有提升，但效果不明显。
三、调整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.143318 |
又提升了一点点。
四、调整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.792329 |
增加这两个参数值后提升的性能是非常明显的，原因很简单，恢复数据时需要做大量的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.434318 |
[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.842156 |
几乎没有变法，看来是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的话还可以提高一部的性能。
- 发表于 2017-03-27 09:53
- 阅读 ( 46 )
