Re: Interpretting WAL debug.

Marc Mitchell wrote:
> 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?

Add timestamps to the logs.  See my article on Hardware performance
tuning on the techdocs.postgresql.org site.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073