physical log might be too small
A DBA reported IDS warning that the physical log may be too small (server recommended at least ~158 MB), even though the common rule of thumb (active connections x 160 KB, giving ~60 MB) suggested a smaller size. Art Kagel advised simply following the engine's recommendation and enlarging the physical log, noting the connections formula is only a rough starting point for initial sizing. He also advised monitoring with onstat -F before checkpoints during peak update activity to ensure the log never exceeds ~75% full (which triggers early checkpoints), and clarified there is no maximum physical log size (minimum 200 KB) — the 1 GB limit applies to logical log files.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, We are getting following error while checking checkpoints "Based on the current workload, the physical log might be too small to accommodate the time it takes to flush the buffer pool during checkpoint processing. The server might block transactions during checkpoints. If the server blocks transactions, increase the physical log size to at least 158400 KB." Currently we have 375 active connections and according to physical log formula we need only 60000 size Formula for physical log = active connection * 160 Kindly help us for resolving this issue.
I would suggest taking the recommendation of the engine and increasing the physical log to at least 158MB Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 6, 2009 at 11:15 PM, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello, > > We are getting following error while checking checkpoints > > "Based on the current workload, the physical log might be too small > to accommodate the time it takes to flush the buffer pool during > checkpoint processing. The server might block transactions during > checkpoints. > If the server blocks transactions, increase the physical log size to > at least 158400 KB." > > Currently we have 375 active connections and according to physical log > formula > we need only 60000 size > > Formula for physical log = active connection * 160 > > Kindly help us for resolving this issue. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b26c07b9470470cd0d47
Interesting.... I heard something to the effect that the Physical Log DB space
should be the same size at Logical Log DB space. One of the things that I run
into the at my IDS 10.00.xC9 engine sometimes goes into "LongTX" state when I
use "onstat -l" or "onstat -g ses" after someone reports that the DB server is
"slow". Is that purely a Logical Log DB space issue or a combination of
Physical and Logical logs?
My current physical log dbspace is about 100MB, and my logical log dbspace is
current 1.2GB. I'm thinking of creating a new volume on my EMC SAN for 24Gb
for Logical Logs, should I do the same for the Physical Logs? I have lots and
lots of unused disk space on my EMC SAN, so disk space isn't a problem. Any
performance issues with a 24GB Physical Log space? Or is that just a complete
waste of space?
Thought?
Gracias!
Jonathan B. Smaby
Pomona College
phone: (909) 621-8506
email: jonathan.smaby@pomona.edu
--
"Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning."
- Rich Cook
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
Jonathan,
Your physical log only needs to be big enough so that it does not exceed 75
full between checkpoints. Monitor it with onstat -F shortly before a few
checkpoints during peak update periods to determine if you are getting
close. If you have ever reached 75 full before a scheduled checkpoint that
condition would trigger an early checkpoint so you can tell that also by
examining the message log.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Aug 10, 2009 at 2:01 PM, Jonathan Smaby
<Jonathan.Smaby@pomona.edu>wrote:
> Interesting.... I heard something to the effect that the Physical Log DB
> space
> should be the same size at Logical Log DB space. One of the things that I
> run
> into the at my IDS 10.00.xC9 engine sometimes goes into "LongTX" state when
> I
> use "onstat -l" or "onstat -g ses" after someone reports that the DB server
> is
> "slow". Is that purely a Logical Log DB space issue or a combination of
> Physical and Logical logs?
>
> My current physical log dbspace is about 100MB, and my logical log dbspace
> is
> current 1.2GB. I'm thinking of creating a new volume on my EMC SAN for 24Gb
> for Logical Logs, should I do the same for the Physical Logs? I have lots
> and
> lots of unused disk space on my EMC SAN, so disk space isn't a problem. Any
> performance issues with a 24GB Physical Log space? Or is that just a
> complete
> waste of space?
>
> Thought?
>
> Gracias!
>
> Jonathan B. Smaby
> Pomona College
> phone: (909) 621-8506
> email: jonathan.smaby@pomona.edu
> --
> "Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning."
> - Rich Cook
>
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016368e2bc9b84d630470d15206
Isn't the size of physical log restricted to 1 GB - or is that outdated?
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> Jonathan Smaby
> Sent: Monday, August 10, 2009 8:01 PM
> To: ids@iiug.org
> Subject: RE: physical log might be too small [16631]
>
>
> Interesting.... I heard something to the effect that the
> Physical Log DB space
> should be the same size at Logical Log DB space. One of the
> things that I run
> into the at my IDS 10.00.xC9 engine sometimes goes into
> "LongTX" state when I
> use "onstat -l" or "onstat -g ses" after someone reports that
> the DB server is
> "slow". Is that purely a Logical Log DB space issue or a
> combination of
> Physical and Logical logs?
>
> My current physical log dbspace is about 100MB, and my
> logical log dbspace is
> current 1.2GB. I'm thinking of creating a new volume on my
> EMC SAN for 24Gb
> for Logical Logs, should I do the same for the Physical Logs?
> I have lots and
> lots of unused disk space on my EMC SAN, so disk space isn't
> a problem. Any
> performance issues with a 24GB Physical Log space? Or is that
> just a complete
> waste of space?
>
> Thought?
>
> Gracias!
>
> Jonathan B. Smaby
> Pomona College
> phone: (909) 621-8506
> email: jonathan.smaby@pomona.edu
> --
> "Programming today is a race between software engineers
> striving to build
> bigger and better idiot-proof programs, and the Universe
> trying to produce
> bigger and better idiots. So far, the Universe is winning."
> - Rich Cook
>
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi,
I am using 11.5 FC3, and I found that if you plan to use RTO_SERVER_RESTART
feature, it can require you to have a phsycal file larger than 1 Gb
> To: ids@iiug.org
> From: RHabichtsberg@arz-emmendingen.de
> Subject: RE: physical log might be too small [16643]
> Date: Tue, 11 Aug 2009 05:20:03 -0400
>
> Isn't the size of physical log restricted to 1 GB - or is that outdated?
>
> > -----Original Message----- ,
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> > Jonathan Smaby
> > Sent: Monday, August 10, 2009 8:01 PM
> > To: ids@iiug.org
> > Subject: RE: physical log might be too small [16631]
> >
> >
> > Interesting.... I heard something to the effect that the
> > Physical Log DB space
> > should be the same size at Logical Log DB space. One of the
> > things that I run
> > into the at my IDS 10.00.xC9 engine sometimes goes into
> > "LongTX" state when I
> > use "onstat -l" or "onstat -g ses" after someone reports that
> > the DB server is
> > "slow". Is that purely a Logical Log DB space issue or a
> > combination of
> > Physical and Logical logs?
> >
> > My current physical log dbspace is about 100MB, and my
> > logical log dbspace is
> > current 1.2GB. I'm thinking of creating a new volume on my
> > EMC SAN for 24Gb
> > for Logical Logs, should I do the same for the Physical Logs?
> > I have lots and
> > lots of unused disk space on my EMC SAN, so disk space isn't
> > a problem. Any
> > performance issues with a 24GB Physical Log space? Or is that
> > just a complete
> > waste of space?
> >
> > Thought?
> >
> > Gracias!
> >
> > Jonathan B. Smaby
> > Pomona College
> > phone: (909) 621-8506
> > email: jonathan.smaby@pomona.edu
> > --
> > "Programming today is a race between software engineers
> > striving to build
> > bigger and better idiot-proof programs, and the Universe
> > trying to produce
> > bigger and better idiots. So far, the Universe is winning."
> > - Rich Cook
> >
> > -------------------------------------------------------------
> > This message has been scanned by Postini anti-virus software.
> >
> >
> > **************************************************************
> > *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
What can you do with the new Windows Live? Find out
http://www.microsoft.com/windows/windowslive/default.aspx
Hi, The issue here is not what IDS recommendations are, the issue is why the Engine not following the rule: Formula for physical log = active connection * 160 If IDS does not follow the criteria to calculate Physical Log size, then on what basis the formula has been devised. Regards Shahzad Salam Kasi
I found in
http://publib.boulder.ibm.com/infocenter/idshelp/v115/
Limit on Logical Log File Size
Logical log files cannot exceed 1 GB on systems with a 2K page size and 2 GB
on systems with a 4K page size.
But nothing about limits for physical logfile size. Is there no limit in IDS
11.50?
Reinhard.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> Habichtsberg, Reinhard
> Sent: Tuesday, August 11, 2009 11:20 AM
> To: ids@iiug.org
> Subject: RE: physical log might be too small [16643]
>
>
> Isn't the size of physical log restricted to 1 GB - or is
> that outdated?
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On
> Behalf Of
> > Jonathan Smaby
> > Sent: Monday, August 10, 2009 8:01 PM
> > To: ids@iiug.org
> > Subject: RE: physical log might be too small [16631]
> >
> >
> > Interesting.... I heard something to the effect that the
> > Physical Log DB space
> > should be the same size at Logical Log DB space. One of the
> > things that I run
> > into the at my IDS 10.00.xC9 engine sometimes goes into
> > "LongTX" state when I
> > use "onstat -l" or "onstat -g ses" after someone reports that
> > the DB server is
> > "slow". Is that purely a Logical Log DB space issue or a
> > combination of
> > Physical and Logical logs?
> >
> > My current physical log dbspace is about 100MB, and my
> > logical log dbspace is
> > current 1.2GB. I'm thinking of creating a new volume on my
> > EMC SAN for 24Gb
> > for Logical Logs, should I do the same for the Physical Logs?
> > I have lots and
> > lots of unused disk space on my EMC SAN, so disk space isn't
> > a problem. Any
> > performance issues with a 24GB Physical Log space? Or is that
> > just a complete
> > waste of space?
> >
> > Thought?
> >
> > Gracias!
> >
> > Jonathan B. Smaby
> > Pomona College
> > phone: (909) 621-8506
> > email: jonathan.smaby@pomona.edu
> > --
> > "Programming today is a race between software engineers
> > striving to build
> > bigger and better idiot-proof programs, and the Universe
> > trying to produce
> > bigger and better idiots. So far, the Universe is winning."
> > - Rich Cook
> >
> > -------------------------------------------------------------
> > This message has been scanned by Postini anti-virus software.
> >
> >
> > **************************************************************
> > *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
There's no maximum size for the physical log only a minimum of 200K,
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Aug 11, 2009 at 4:20 AM, Habichtsberg, Reinhard <
RHabichtsberg@arz-emmendingen.de> wrote:
> Isn't the size of physical log restricted to 1 GB - or is that outdated?
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> > Jonathan Smaby
> > Sent: Monday, August 10, 2009 8:01 PM
> > To: ids@iiug.org
> > Subject: RE: physical log might be too small [16631]
> >
> >
> > Interesting.... I heard something to the effect that the
> > Physical Log DB space
> > should be the same size at Logical Log DB space. One of the
> > things that I run
> > into the at my IDS 10.00.xC9 engine sometimes goes into
> > "LongTX" state when I
> > use "onstat -l" or "onstat -g ses" after someone reports that
> > the DB server is
> > "slow". Is that purely a Logical Log DB space issue or a
> > combination of
> > Physical and Logical logs?
> >
> > My current physical log dbspace is about 100MB, and my
> > logical log dbspace is
> > current 1.2GB. I'm thinking of creating a new volume on my
> > EMC SAN for 24Gb
> > for Logical Logs, should I do the same for the Physical Logs?
> > I have lots and
> > lots of unused disk space on my EMC SAN, so disk space isn't
> > a problem. Any
> > performance issues with a 24GB Physical Log space? Or is that
> > just a complete
> > waste of space?
> >
> > Thought?
> >
> > Gracias!
> >
> > Jonathan B. Smaby
> > Pomona College
> > phone: (909) 621-8506
> > email: jonathan.smaby@pomona.edu
> > --
> > "Programming today is a race between software engineers
> > striving to build
> > bigger and better idiot-proof programs, and the Universe
> > trying to produce
> > bigger and better idiots. So far, the Universe is winning."
> > - Rich Cook
> >
> > -------------------------------------------------------------
> > This message has been scanned by Postini anti-virus software.
> >
> >
> > **************************************************************
> > *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b52bb47e200470dfe7df
As a suggested rule of thumb for initially sizing the physical log. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 11, 2009 at 6:39 AM, SHAHZAD SALAM KASI <skasi@i2cinc.com>wrote: > Hi, > The issue here is not what IDS recommendations are, the issue is why the > Engine not following the rule: > > Formula for physical log = active connection * 160 > > If IDS does not follow the criteria to calculate Physical Log size, then on > what basis the formula has been devised. > > Regards > Shahzad Salam Kasi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b2f076b77f0470dfecb6