文档章节

PostgreSQL的pg_xlog文件数计算和在线清理

kenyon_君羊
 kenyon_君羊
发布于 2013/07/15 20:39
字数 1575
阅读 4345
收藏 9
postgresql的pg_xlog是记录数据库事务信息用的,类似oracle的redo信息,也叫wal日志(write ahead log),就是在写数据到磁盘里成为固定数据之前,先写入到日志里,然后一定条件下触发调用fsync()将此数据刷到磁盘。好处不多说,下面主要看看postgresql通常会保留多少个pg_xlog文件,以及如何在线清理。

一、环境及参数
环境:
postgresql 9.2.4(单台,未做流复制)
vmware 6.0
centos 6.2

参数:
postgres=# show checkpoint_segments ;   --设置相对大一点,默认太小,防止频繁checkpoint
checkpoint_segments
---------------------
30
(1 row)

postgres=# show checkpoint_timeout ;
checkpoint_timeout
--------------------
5min
(1 row)

postgres=# show checkpoint_completion_target ;
checkpoint_completion_target
------------------------------
0.5
(1 row)

postgres=# show wal_keep_segments ;     --未开启流复制预留xlog参数
 wal_keep_segments 
-------------------
 0
(1 row)

postgres=# show archive_mode ;             --关闭归档
archive_mode 
--------------
off
(1 row)
理论上合理的pg_xlog一般在(2+checkpoint_completion_target)*checkpoint_segment+1左右浮动,但是高并发环境下如果checkpoint_segment设置比较大,且checkpoint较少被触发的时候,pg_xlog下会存有很多的事务日志,严重会耗掉磁盘存储,所以设置checkpoint_segment的大小要视磁盘容量和每个pg_xlog文件的大小来看,有些时候需要手工调整参数来收缩。

二、测试过程:

1.模拟pg_xlog文件增长
模拟大数据量载入,开启多个窗口插入数据,每个窗口插入1000W数据,可以看到pg_xlog增长很多,原来只有8个文件
postgres=# create table tbl_kenyon(id int,cname varchar(50),remark text);
CREATE TABLE

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000
此时检查pg_xlog下面的文件数量
[postgres@localhost pg_xlog]$ ll
total 4063240
-rw-------  1 postgres postgres 67108864 Jul 14 23:05 00000001000000020000002F
-rw-------  1 postgres postgres 67108864 Jul 14 23:13 000000010000000200000030
-rw-------  1 postgres postgres 67108864 Jul 14 23:18 000000010000000200000031
-rw-------  1 postgres postgres 67108864 Jul 14 23:25 000000010000000200000032
-rw-------  1 postgres postgres 67108864 Jul 14 22:42 000000010000000200000033
-rw-------  1 postgres postgres 67108864 Jul 14 22:42 000000010000000200000034
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000035
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000036
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 000000010000000200000037
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000038
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000200000039
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003A
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 00000001000000020000003B
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003C
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 00000001000000020000003D
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 00000001000000020000003E
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000000
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000001
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000002
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000003
-rw-------. 1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000004
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000005
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000006
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000007
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000008
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000009
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000000A
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 00000001000000030000000B
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000C
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000000D
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000E
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 00000001000000030000000F
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000010
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000011
-rw-------  1 postgres postgres 67108864 Jul 14 22:44 000000010000000300000012
-rw-------  1 postgres postgres 67108864 Jul 14 22:43 000000010000000300000013
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000014
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000015
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000016
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000017
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000018
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000019
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001A
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001B
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001C
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001D
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000001E
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 00000001000000030000001F
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 000000010000000300000020
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000021
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000022
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000023
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000024
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000025
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000026
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 000000010000000300000027
-rw-------  1 postgres postgres 67108864 Jul 14 22:45 000000010000000300000028
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 000000010000000300000029
-rw-------. 1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002A
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002B
-rw-------  1 postgres postgres 67108864 Jul 14 22:46 00000001000000030000002C
-rw-------  1 postgres postgres 67108864 Jul 14 22:56 00000001000000030000002D
drwx------. 2 postgres postgres     4096 Jun 12 23:19 archive_status
[postgres@localhost pg_xlog]$ ll|wc -l
64
2.模拟pg_xlog文件清理
这类文件对DB是很重要的,不能手工直接清理。每个文件64MB,是源码编译时--with-wal-segsize时指定的,或者登陆查看
postgres=# show wal_segment_size ;  
wal_segment_size 
------------------
 64MB
(1 row)
要收缩这个事务日志空间,减少事务日志数,可以调整参数 checkpoint_segments,本次调为3
postgres=# show checkpoint_segments ;
 checkpoint_segments 
---------------------
 3
(1 row)


postgres=# checkpoint;
CHECKPOINT
 xlog文件有可能不是立即减少的,如果想模拟立即减少,可以模拟数据库频繁checkpoint,如重新做上述的大数据库加载或者更新操作,此时去看pg_xlog的文件数,能看到在减少了,开启两个窗口操作:
postgres=# insert into tbl_kenyon select generate_series(1,10000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 10000000

postgres=# insert into tbl_kenyon select generate_series(1,2000000),'kenyon good boy',md5('kenyon good boy');
INSERT 0 2000000

[postgres@localhost pg_xlog]$ ll|wc -l
62
[postgres@localhost pg_xlog]$ ll|wc -l
49
[postgres@localhost pg_xlog]$ ll|wc -l
46
[postgres@localhost pg_xlog]$ ll|wc -l
46
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
43
[postgres@localhost pg_xlog]$ ll|wc -l
40
[postgres@localhost pg_xlog]$ ll|wc -l
40
[postgres@localhost pg_xlog]$ ll|wc -l
31
[postgres@localhost pg_xlog]$ ll|wc -l
25
后续发生的checkpoint,将会逐步收缩pg_xlog的文件数量,直到满足条件为止。之前写过另外一种较为激进的清理pg_xlog的方式,需要停机清理,参考 http://my.oschina.net/Kenyon/blog/101432

3.其他
如果是流复制,还有wal_keep_segments这个参数也要考虑一下。后续。

4.说明
checkpoint_segments (integer)
Maximum number of log file segments between automatic WAL checkpoints (each segment
is normally 16 megabytes). The default is three segments. Increasing this parameter can increase
the amount of time needed for crash recovery. This parameter can only be set in the
postgresql.conf file or on the server command line.
5.参考:
http://my.oschina.net/Kenyon/blog/101432
http://francs3.blog.163.com/blog/static/4057672720112182158386/

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
PostgreSQL如何删除pg_xlog数据

之前讨论过如果归档或者流复制出现问题的时候,pgxlog下面会不断地产生WAL日志,严重会塞满磁盘空间。但是pgxlog下面的日志又不能随意删除,否则有可能导致数据库起不来。PostgreSQL自带了清...

kenyon_君羊
2013/01/10
0
3
PostgreSQL9.x和10.x中xlog的变化

目前PostgreSQL已经出了PG11Beta2版本,细心的朋友可能已经发现,PG9.x及之前版本的xlog做了一些改动,在10以后的版本,xlog改成了wal,官方出于安全性的考虑做出了该改动。 随着这些变化,有...

闻术苑
2018/07/15
0
0
PostgreSQL 如何从崩溃状态恢复(上)

背景 为了合并I/O提高性能,PostgreSQL数据库引入了共享缓冲区,当数据库非正常关闭,比如服务器断电时,共享缓冲区即内存中的数据就会丢失,这个时候数据库操作系统重启时就需要从非正常状态...

卓刀
2018/07/10
0
0
PostgreSQL 归档出错

Running out of disk space in the pg_xlog directory is a fairly common Postgres problem. This important directory holds the WAL (Write Ahead Log) files. (WAL files contain a reco......

MtrS
2015/01/06
0
0
PostgreSQL中的XLOG(一)基础概念和初始化

写在前面: 打算花点时间好好的研究一下WAL(Write-Ahead Logging),在关系型数据库中WAL是必不可少也绕不过去的知识,数据库依旧是PostgreSQL,内容可能会划分为: 数据库版本为PG 10.3 https...

RDBMS原理分析
2018/04/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

data.world

产品思路: 1、共享数据集。 2、数据结构化。 3、数据展示。 https://data.world

colin_86
13分钟前
2
0
冒泡排序的三种写法

冒泡排序是程序员最耳熟能详的排序了,本文简单叙述下我写过的三种冒泡排序写法。 冒泡,顾名思义,待排序的数字像气泡一样,通过比较,以升序或降序的方式排序。 我最开始是这样写冒泡排序的...

niithub
39分钟前
2
0
Akka实战:HTTP大文件断点上传、下载,秒传

访问:https://github.com/yangbajing/scala-applications/tree/master/file-upload 获取本文所述完整源码,包括Akka HTTP后端和HTML5实现的前端。 在很多应用里面都会有类似大文件上传的需求...

羊八井
49分钟前
5
0
node:event-loop & 宏任务 & 微任务

event-loop(事件轮询) 代码进入执行栈后会判断当前代码是同步任务还是异步任务,如果是同步任务则会将任务调到主线程同步执行,如果是异步任务则会将任务调到异步队列中。 主线程同步任务执...

小草先森
53分钟前
3
0
php-fpm配置文件详解

php-fpm配置文件详解 php-fpm配置文件路径: /usr/local/php-fpm/etc/php-fpm.conf ,还包含了一个配置文件路径下的所有以.conf结尾的配置文件(子配置文件)/usr/local/php-fpm/etc/php-fp...

李超小牛子
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部