Re: Interpretting WAL debug. - Mailing list pgsql-admin

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
>