Re: Help me tuning this wait event:log file sync

From: chao_ping <chao_ping_at_163.com>
Date: 17 Jul 2002 07:18:13 GMT
Message-ID: <3d351a35$7@usenetgateway.com>

Yong Huang wrote:

  > chao_ping <chao_ping_at_163.com> wrote in message
  > news:<3d312489$1_at_usenetgateway.com>...
  > > Yong Huang wrote:
  > >   > If you can't control the frequency of commits, reduce log_buffer.
  > >   > How big is it?
  > > now log buffer is 2M, In early time, it was 1M, and i see much wait
  > > time in log buffer space,(once it was the 4th top wait event), so i
  > > enlarged the log buffer size from 1m to 2m.
  > Never use a log_buffer larger than 1M. It's useless over that limit.
  > Do you have multiple log members per group? Use 1 member.
  > If you got 40 commits per second, the only solution may be rewrite the
  > application. If you can't, try a competitor's product.
  > Yong Huang

Thanks for yong, Howard, Lewis and others who read and answer it.You are all experts i admire. Since this database support internet application, and all other service depend on this database, I cannot restart it as i wish,So, have to think it again and again before i make any modification to it then bounce it. As for the commits/second, i caculate it from the statspack report: Load Profile ~
~~~~~~~~~~~~~~~~~~~~~~~~Per~Second~~~~~~~Per~Transaction ~
Redo~size:~~~~~~~~~~~~~76,336.86~~~~~~~~~~~~~~2,208.94

Logical~reads:~~~~~~~~~~~~~12,555.73~~~~~~~~~~~~~~~~363.32 I get the

commits per second by devide the (values per second)/(values per transacion).Is the method to caculate the commits(transactions per second, since few rollback) right?

I carefully checked the IO on the redo log volume(all redo log files on a seperate volumn with Veritas Quick IO), and get the following data:(via vxstat ) There is about about 10-12 write action to the redo volumn per second, and every write action write about 10 blocks, so every write action is about 5k data, and there is 60-80K data written to the redo log file per second, and there is around 250M data written to redo log(log switch happens about 40-60 minutes and my redo log size is 256M).

So, it is clear that it is not amount of data written to the redo logfile and the disk array, it is the frequency of the write, that caused the log file sync wait event.

The painful thing is i cannot change the slow raid-5 disk array, CTO said it need too much downtime and some risk, and As for the log_buffer size,
SQL> select * from v$sysstat where name like '%redo%';

STATISTIC#~NAME~~~~~~~~~~~~~~~~~~~~~~CLASS~~~~~~VALUE
----------~------------------------------~----------~----------


~~~~~~~~61~redo~synch~writes~~~~~~~~~~8~~~38142734

~~~~~~~~62~redo~synch~time~~~~~~~~~~~~~8~~322428926

~~~~~~~100~redo~entries~~~~~~~~~~~~~~~~~2~~304270016

~~~~~~~101~redo~size~~~~~~~~~~~~~~~~~2~8.2670E+10

~~~~~~~102~redo~buffer~allocation~retries~~~~~2~~~~~~50007

~~~~~~~103~redo~wastage~~~~~~~~~~~~~~~2~~600523436

~~~~~~~104~redo~writer~latching~time~~~~~~2~~~~~~~2660

~~~~~~~105~redo~writes~~~~~~~~~~~2~~~29240900

~~~~~~~106~redo~blocks~written~~~~~~~~~2~~185244483

~~~~~~~107~redo~write~time~~~~~~~~~~~2~~~93445220
~~~~~~~108~redo~log~space~requests~~~~~~~2~~~~~~~4816

~~~~~~~109~redo~log~space~wait~time~~~~~2~~~~~245683
~~~~~~~110~redo~log~switch~interrupts~~~~2~~~~~~~~~~0
~~~~~~~111~redo~ordering~marks~~~~~~~~2~~~~~~~~213

I also read some thread on google and ixora site, from link:http://www.ixora.com.au/q+a/redo.htm#25_09_1999, steve adams said that 120-160K should be proper, but i wonder maybe that value is not suitable to my system as there is still "log buffer space request" and "redo log space wait time" even if i set it to 2M. Currently both my former dba and me did not change the value of log_checkpoint, leave it as the default value: log_checkpoint_interval =10000 and log_checkpoint_timeout = 1800 So, I think i has better set interval to some larger value and disable timeout checkpoint. As for the redo log members, i have just one member. We are on raid5 and if single disk fail, it is still ok.Have two member give the raid more pressure, i would like to take this risk, for we have an standby database:) So, the concern here is the log_buffer size. My main concern is to reduce the log file sync wait event. I want to do some test on my another machine with similiar hardware and software. Can someone give me some suggestions to simulate the workload? I hear from Lewis's post that loop and commit in PLSQL doesnot cause log file sync.So, how to test whether large log buffer is better or smaller one is ok? Thanks!!!

--
an oracle fan, 
an oracle beginner

an oracle fan, 
an oracle beginner

an oracle fan, 
an oracle beginner

an oracle fan, 
an oracle beginner

Posted via dBforums
http://dbforums.com

Received on Wed Jul 17 2002 - 02:18:13 CDT