Thread: Re: Interpretting WAL debug.

Re: Interpretting WAL debug.

From
Murthy Kambhampaty
Date:
Marc, when I ran up the load on our server with the default settings, and no
WAL debug to the log, I got a message in the log as follow:

DEBUG:  copy: line 9542, XLogWrite: new log file created - consider
increasing WAL_FILES
DEBUG:  copy: line 61478, XLogWrite: new log file created - consider
increasing WAL_FILES

The other thing I was looking for was the interval between log flushes,
along the lines of

2002-08-11 00:08:28 DEBUG:  recycled transaction log file 00000000000000DF

(make sure you have "log_timestamp = true" in your postgres.conf)

Just keep increasing checkpoint segments from 3, untill you see the log
flushes space out to five minutes or more.

At bottom, I suspect you can get your machine tuned without WAL debug, and
Bruce's paper on tuning, which it looks like you've been reading. I don't
think, you need to turn on WAL debug for the particular question you asked.

Cheers,
    Murthy



>-----Original Message-----
>From: Marc Mitchell [mailto:marcm@eisolution.com]
>Sent: Thursday, August 29, 2002 11:13
>To: pgsql-admin@postgresql.org
>Subject: [ADMIN] Interpretting WAL debug.
>
>
>In an attempt to review a machine for optimal OLTP configuration of
>Postgres box, turned WAL debug to 1 and ran under load for 24
>hours.  That
>resulted in a 67+ meg postmaster logfile.  But I'm not sure how to
>interpret the results.  Without going through the 700,000+
>lines, the basic
>info looks like this:
>
>
>INSERT @ 7/2838581988: prev 7/2838573716; xprev 7/2838573716;
>xid 38868268;
>bkpb
> 1: Btree - insert: node 18720/20077; tid 219/75
>INSERT @ 7/2838590260: prev 7/2838581988; xprev 7/2838581988;
>xid 38868268;
>bkpb
> 1: Btree - insert: node 18720/11144803; tid 201/94
>INSERT @ 7/2838598532: prev 7/2838590260; xprev 7/2838590260;
>xid 38868268:
>Heap
> - update: node 18720/19299; tid 431/8; new 431/21
>XLogFlush: rqst 7/2838540592; wrt 7/2838593536; flsh 7/2838524040
>XLogFlush: rqst 7/2838557172; wrt 7/2838598740; flsh 7/2838598740
>XLogFlush: rqst 7/2838565444; wrt 7/2838598740; flsh 7/2838598740
>XLogFlush: rqst 7/2838573716; wrt 7/2838598740; flsh 7/2838598740
>
>
>I know in general that I'm looking at inserts into the log buffers and
>flushes of the buffers to permanent storage.  I also know that a bad
>situation is one where all buffers fill up and an insert must
>wait for a
>flush.  How can I examine this output to know if that is
>happening?  Also,
>is there anything else I can look for within this data to tell
>me if I have
>a configuration problem that could use some tuning?
>
>FYI:
>                           version
>-------------------------------------------------------------
> PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96
>
>
>Thanks for any help that can be provided.
>
>Marc Mitchell - Senior Application Architect
>Enterprise Information Solutions, Inc.
>Downers Grove, IL 60515
>marcm@eisolution.com
>
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to
>majordomo@postgresql.org
>

Re: Interpretting WAL debug.

From
Tom Lane
Date:
Murthy Kambhampaty <Murthy.Kambhampaty@goeci.com> writes:
> At bottom, I suspect you can get your machine tuned without WAL debug, and
> Bruce's paper on tuning, which it looks like you've been reading. I don't
> think, you need to turn on WAL debug for the particular question you asked.

I agree.  The WAL debug code is really intended for *debugging* WAL, not
for tuning purposes.  I think all you really need care about is whether
you get "new log file created" messages and what the frequency of
checkpoints is.

            regards, tom lane

Re: Interpretting WAL debug.

From
Murthy Kambhampaty
Date:
>-----Original Message-----
>From: Marc Mitchell [mailto:marcm@eisolution.com]
>Sent: Thursday, August 29, 2002 16:13
>To: Murthy Kambhampaty
>Subject: Re: [ADMIN] Interpretting WAL debug.
>
>
[snip]
>So, is it fair to assume that you have enough WAL_FILES declared if you
>never receive these messages?  I've observed these in the past but only
>during timing of running Vacuums or restores of entire
>databases.  During
>normal operation I don't think I see these.

Right. Of course the WAL files don't take up too much room on disk,
relatively speaking, so I just set that parameter to the max of 64 (1G) on
all our servers, and then worry about the checkpoint interval.

>
>Marc
>
>> >-----Original Message-----
>> >From: Marc Mitchell [mailto:marcm@eisolution.com]
>> >Sent: Thursday, August 29, 2002 11:13
>> >To: pgsql-admin@postgresql.org
>> >Subject: [ADMIN] Interpretting WAL debug.
>> >
>> >
>> >In an attempt to review a machine for optimal OLTP configuration of
>> >Postgres box, turned WAL debug to 1 and ran under load for 24
>> >hours.  That
>> >resulted in a 67+ meg postmaster logfile.  But I'm not sure how to
>> >interpret the results.  Without going through the 700,000+
>> >lines, the basic
>> >info looks like this:
>> >
>> >
>> >INSERT @ 7/2838581988: prev 7/2838573716; xprev 7/2838573716;
>> >xid 38868268;
>> >bkpb
>> > 1: Btree - insert: node 18720/20077; tid 219/75
>> >INSERT @ 7/2838590260: prev 7/2838581988; xprev 7/2838581988;
>> >xid 38868268;
>> >bkpb
>> > 1: Btree - insert: node 18720/11144803; tid 201/94
>> >INSERT @ 7/2838598532: prev 7/2838590260; xprev 7/2838590260;
>> >xid 38868268:
>> >Heap
>> > - update: node 18720/19299; tid 431/8; new 431/21
>> >XLogFlush: rqst 7/2838540592; wrt 7/2838593536; flsh 7/2838524040
>> >XLogFlush: rqst 7/2838557172; wrt 7/2838598740; flsh 7/2838598740
>> >XLogFlush: rqst 7/2838565444; wrt 7/2838598740; flsh 7/2838598740
>> >XLogFlush: rqst 7/2838573716; wrt 7/2838598740; flsh 7/2838598740
>> >
>> >
>> >I know in general that I'm looking at inserts into the log
>buffers and
>> >flushes of the buffers to permanent storage.  I also know that a bad
>> >situation is one where all buffers fill up and an insert must
>> >wait for a
>> >flush.  How can I examine this output to know if that is
>> >happening?  Also,
>> >is there anything else I can look for within this data to tell
>> >me if I have
>> >a configuration problem that could use some tuning?
>> >
>> >FYI:
>> >                           version
>> >-------------------------------------------------------------
>> > PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96
>> >
>> >
>> >Thanks for any help that can be provided.
>> >
>> >Marc Mitchell - Senior Application Architect
>> >Enterprise Information Solutions, Inc.
>> >Downers Grove, IL 60515
>> >marcm@eisolution.com
>> >
>> >
>> >
>> >---------------------------(end of
>> >broadcast)---------------------------
>> >TIP 1: subscribe and unsubscribe commands go to
>> >majordomo@postgresql.org
>> >
>>
>> ---------------------------(end of
>broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to
>majordomo@postgresql.org)
>