Thread: Re: Interpretting WAL debug.
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
>
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
>-----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) >