Doubts after evaluating Xlogdump

From: Vinay Gupta <vdadagupta(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Doubts after evaluating Xlogdump
Date: 2014-02-27 15:39:08
Message-ID: CAEzDN6yFcoEYSwMurBpnsod1QXcqsjQCD9uzP4r7d8Bo3mpUSg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Is dere any better way we can understand the contents of pg_xlog files. I
am using xlogdump for analysing my xlog contents. Sometimes in my database
server in some particular hours, the pgxlog files generation exceeded than
the expected counts almost 3X than the normal count.

*--- Size of 1 hour xlog files (620 files)*

[root(at)db xlogdump1]# du -sh /tmp/pgxlogdump/
*9.9*G /tmp/pgxlogdump/
[root(at)db1002 xlogdump1]#

*--- Parsing Xlog files*

[root(at)db1002 xlogdump1]# /usr/pgsql-9.2/bin/xlogdump /tmp/pgxlogdump/* >
xlog_analysis24feb2014_18.txt
Unable to read continuation page?
Unable to read continuation page?
...
.. so on

*-- Final output file size after parsing*

[root(at)db xlogdump1]# du -sh xlog_analysis24feb2014_18.txt
*3.6*G xlog_analysis24feb2014_18.txt

I don't think it parsed all files completely because output file size is
too small.

So to analyse the output file , i use the below command :

cat xlog_analysis24feb2014_18.txt |awk '{print $8 "," $7}' | sort | uniq
-dc | sort

that gives me output like below :-
195068 s/d/r:1663/1228184/1363132,clean:
200548 s/d/r:1663/1228184/1363155,bkpblock[1]:
203964 s/d/r:1663/112743/5263051,insert:
205003 s/d/r:1663/112743/5263054,insert_leaf:
205673 s/d/r:1663/112743/5263053,insert_leaf:
208101 s/d/r:1663/112743/5263208,insert:
269840 ,vacuum:
210556 s/d/r:1663/112743/5263212,insert_leaf:
250651 s/d/r:1663/1228184/1522128,update:
250867 s/d/r:1663/1228184/1522132,insert_leaf:
253874 s/d/r:1663/1228184/1291583,insert_leaf:
253887 s/d/r:1663/1228184/1291579,update:
280151 s/d/r:1663/112743/919064,insert_leaf:
281578 s/d/r:1663/112743/919027,update:
293516 s/d/r:1663/1228184/1363132,bkpblock[1]:
351713 s/d/r:1663/1228184/1363648,hot_update:
361884 s/d/r:1663/1228184/5263067,insert_leaf:
382961 s/d/r:1663/1228184/1363654,bkpblock[1]:
392147 s/d/r:1663/1228184/1363652,bkpblock[1]:
392957 s/d/r:1663/1228184/5263063,insert:
393086 s/d/r:1663/1228184/1522150,update:
412499 s/d/r:1663/1228184/5263286,insert_leaf:
437279 s/d/r:1663/1228184/5263282,insert:
927606 s/d/r:1663/1228184/1363648,update:
932525 s/d/r:1663/1228184/1363652,insert_leaf:
932668 s/d/r:1663/1228184/1363654,insert_leaf:
1078769 s/d/r:1663/1228184/5263108,insert_leaf:
1155642 s/d/r:1663/1228184/5263105,insert:
1181241 s/d/r:1663/1228184/5263107,insert_leaf:

Is dere any better way where i can analyse who is contributing most in my
pgxlog file generation.

Also, how can i link my insert_leaf statements to my tables/indexes ?

1078769 s/d/r:1663/1228184/5263108,insert_leaf:

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2014-02-27 15:43:30 Re: Cancelling of autovacuums considered harmful
Previous Message Adrian Klaver 2014-02-27 15:14:31 Re: Issue with default values and Rule