log file sync一次剖析

log file sync总结y

原文转载于 http://blog.chinaunix.net/xmlrpc.php?r=blog/article&id=4774739&uid=23284114

log file sync等待时间发生在redo log从log buffer写入到log file期间。

下面对log file sync做个详细的解释。

何时发生日志写入：

1.commit或者rollback

2.每3秒

3.log buffer 1/3满或者已经有1M的redo数据。

      更精确的解释：_LOG_IO_SIZE 大小默认是LOG_BUFFER的1/3,当log buffer中redo数据达到_LOG_IO_SIZE 大小时，发生日志写入。

4.DBWR写之前

_log_io_size隐含参数：

LOG_BUFFER（bytes）写入的数量超过_LOG_IO_SIZE会触发lgwr写日志的条件,缺省值为LOG BUFFER的1/3或1M。

但是这个说法通过查询并不能验证，隐含参数尽量不要修改。

col name for a25

col VALUE for a20

col DESCRIB for a50

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

FROM SYS.x$ksppi x, SYS.x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm LIKE '_log_io_size';

NAME                      VALUE                DESCRIB

------------------------- -------------------- --------------------------------------------------

_log_io_size              0                    automatically initiate log write if this many redo

                                                blocks in buffer

log file sync发生的过程：

此等待事件用户发出提交或回滚声明后,等待提交完成的事件,提交命令会去做日志同步,也就是写日志缓存到日志文件， 在提交命令未完成前,用户将会看见此等待事件.

注意,它专指因提交,回滚而造成的写缓存到日志文件的等待.当发生此等待事件时,有时也会伴随log file parallel write.因为此等待事件将会写日志缓存，如果日志的I/O系统较为缓慢的话,

这必将造成log file parallel write 等待.当发生log file sync等待后,判断是否由于缓慢的日志I/O造成的,可以查看两个等待事件的等待时间,如果比较接近,就证明日志I/O比较缓慢或重做日志过多,这时,造成log file sync的原因是因为log file parallel write,可以参考解决log file parallel write的方法解决问题,

**如果log file sync的等待时间很高,而log file parallel write的等待时间并不高,这意味着log file sync的原因并不是缓慢的日志I/O,而是应用程序过多的提交造成。

  当log file sync的等待时间和 log file parallel write等待时间基本相同，说明是IO问题造成的log file sync等待事件。

-----

更好理解的解释：

回顾一下单机数据库中的'log file sync' 等待事件，当user session 提交（commit）时，user session会通知LGWR进程将redo buffer中的信息写入到redo log file，当LGWR进程完成写操作后，LGWR再post（通知）user session 写操作已经完成，user session 接收到LGWR的通知后提交操作才完成。因此user session 在没有收到LGWR post（通知）之前一致处于等待状态，具体的等待事件为'log file sync'。

-----

引起log file sync的原因：

1.频繁提交或者rollback,检查应用是否有过多的短小的事务，如果有，可以使用批处理来缓解。

2.OS的IO缓慢：解决办法是将日志文件放裸设备上或绑定在RAID 0或RAID 1+0中，而不是绑定在RAID 5中。

3.过大的日志缓冲区（log_buffer ）

        过大的log_buffer,允许LGWR变得懒惰，因为log buffer中的数据量无法达不到_LOG_IO_SIZE，导致更多的重做条目堆积在日志缓冲区中。

当事务提交或者3s醒来时，LGWR才会把所有数据都写入到redo log file中。

由于数据很多，LGWR要用更多时间等待redo写完毕。

这种情况，可以调小参数_LOG_IO_SIZE参数，其默认值是LOG_BUFFER的1/3或1MB，取两者之中较小的值。

换句话说，你可以具有较大的日志缓冲区，但较小的_LOG_IO_SIZE将增加后台写入次数，从而减少log file sync的等待时间。

4.CPU负载高。详见下面的描述。

5.RAC私有网络性能差，导致LMS同步commit SCN慢。

如何诊断log file sync：

1.AWR：发生log file sync时，先做个snapshot，然后做AWR，AWR时间选择在10-30分钟。

       已发生的log file sync，那么通过AWR依然可以分析，也要保持在10-30分钟。

2.Lgwr trace file（10.2.0.4开始），大于500ms会写入

trace文件中如果有Warning: log write time 1000ms, size 2KB，很有可能IO慢。

3.分析CPU资源使用情况的工具，CPU过于繁忙，lgwr无法及时获取CPU调度，出现log file sync。

vmstat，关注r是否大于CPU核数，大于说明cpu繁忙。

OSW:OSWatcher,同上。

4.Alert：确认log file 15到20分钟切换一次

5.Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql) [Document 1064487.1]

解决办法：

1.如果确实是因为频繁提交造成的log file sync,那么减少commit。

2.如果确实是因为io引起的，那么解决办法是将日志文件放裸设备上或绑定在RAID 1+0中，而不是放在在RAID 5中（切记，redo log file一定不要放在SSD上！！！）。

3.确保CPU资源充足。CPU资源不足，LGWR通知user session后，user session无法及时获得CPU调度，不能正常工作。

4.是否有些表可以使用nologging，会减少redo产生量

5.检查redo log file足够大，确保redo log file每15到20分钟切换一次。

更深入分析log file sync：

如果上面的分析没有解决log file sync等待事件，那么需要做下面的分析。

The log file sync wait may be broken down into the following components:

log file sync 能拆解为一下步骤：

1. Wakeup LGWR if idle  1.唤醒LGWR进程

2. LGWR gathers the redo to be written and issue the I/O 2.LGWR进程收集redo，然后发给I/O

3. Time for the log write I/O to complete 3.等待log写入I/O完成

4. LGWR I/O post processing 4.LGWR I/O post processing

5. LGWR posting the foreground/user session that the write has completed 5.LGWR通知前台/用户回话，redo写入完成

6. Foreground/user session wakeup 6.前台/用户会话唤醒

Steps 2 and 3 are accumulated in the "redo write time" statistic. (i.e. as found under STATISICS section of Statspack and AWR)

步骤2和3消耗的时间在AWR中的"redo write time"中有所体现。(AWR中 Instance Activity Stats )

Step 3 is the "log file parallel write" wait event. (Document:34583.1 "log file parallel write" Reference Note)

步骤3产生"log file parallel write"等待事件。

另外：如果是最大保护模式的DATAGUARD(SYNC传输),这一步骤还包含网络写、RFS/redo写入到备库的standby log file sync的时间。

Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level.

在系统负载高时（尤其是CPU高的情况，看vmstat r值），步骤5和6会变得非常明显。因为，前台收到LGWR写入完成的通知后，操作系统需要消耗一些时间调度Foreground/user session进程唤醒（也就是CPU调度）。需要系统级别监控。

几个技术指标：

log file sync 等待时间小于20ms算正常

log file parallel write 等待时间小于20ms算正常

log file parallel wirte 和log file sync等待时间很接近，说明就是IO问题，因为大部分时间都花在了log写入到磁盘上。

相关脚本：

--等待时间平均等待时间

select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT
 from   v$system_event 
 where  event in ('log file sync','log file parallel write'); 
select value from v$parameter where name = 'log_buffer';

---------------新特性：log file sync 两种方式--------------

Adaptive Log File Sync 

Adaptive Log File sync was introduced in 11.2. The parameter controlling this feature, _use_adaptive_log_file_sync, is set to false by default in 11.2.0.1 and 11.2.0.2.

_use_adaptive_log_file_sync参数在11gR2提出。11.2.0.1和11.2.0.2两个版本该参数默认是false。

从11.2.0.3开始，这个参数默认值是true，也就是开始启用“自适应日志同步机制”。

11.2.0.1和11.2.0.2也可以开启改参数

ALTER SYSTEM SET "_use_adaptive_log_file_sync"=  scope=;

开启改参数后，日志同步机制会在2种方式中切换。

该参数决定了，foreground/user session 和LGWR进程通过什么方式获知commit操作已完成（也就是redo写log file完成）。

Post/wait, traditional method for posting completion of writes to redo log 

传统方式，在11.2.0.3之前，user session等待LGWR通知redo写入到log file完毕，被动方式。

优点：post/wait方式，user session几乎能立即发现redo已刷到磁盘。

Polling, a new method where the foreground process checks if the LGWR has completed the write.

新方式，主动监测LGWR是否完成写入，主动方式。这种方式比Post/wait方式响应速度慢，但是可以节约CPU资源。

优点：当commit完成后，LGWR会把commit完成的消息通知给很多user session，这个过程消耗大量CPU。

  Polling方式采用朱勇监测LGWR释放写入redo完成，所以释放了LGWR占用的CPU资源。

  

系统负载高（CPU繁忙）采用Polling方式更好。

系统负载低（CPU清闲）采用post/wait方式更好，它能够提供比polling方式更好的响应时间。

ORACLE根据内部统计信息决定采用何种方式。post/wait和polling方式互相切换能引起过热，为了确保安全，切换不要太频繁。

LGWR的trace文件记录了switch记录，关键字是 "Log file sync switching to ...":

Switch to polling:

*** 2015-01-21 08:19:04.077
kcrfw_update_adaptive_sync_mode: post->poll long#=2 sync#=5 sync=62 poll=1056 rw=454 ack=0 min_sleep=1056
*** 2015-01-21 08:19:04.077
Log file sync switching to polling
Current scheduling delay is 1 usec
Current approximate redo synch write rate is 1 per sec
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=1 switch_sync_count_delta=5

Switch to post/wait:

*** 2015-01-21 08:46:09.428
Log file sync switching to post/wait
Current approximate redo synch write rate is 0 per sec
*** 2015-01-21 08:47:46.473
kcrfw_update_adaptive_sync_mode: post->poll long#=2 sync#=11 sync=228 poll=1442 rw=721 ack=0 min_sleep=1056

相关脚本：

查询当前log file sync 方式是post-wait还是poll

SQL> select name,value from v$sysstat where name in ('redo sync poll writes','redo synch polls');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch polls                                                  325355850

每小时采用poll log file sync方式的次数

col begin_interval_time format a25
col instance_number format 99 heading INST
col stat_name format a25
select snap.BEGIN_INTERVAL_TIME,hist.instance_number , hist.stat_name,hist.redo_synch_polls
from ( select snap_id,instance_number,stat_name,value -lag(value,1,null) over ( order by snap_id,instance_number,stat_name) redo_synch_polls
        from dba_hist_sysstat
        where stat_name='redo synch polls'
        and dbid=(select dbid from v$database)
        and instance_number = nvl('&instance_number',1)) hist,
        dba_hist_snapshot snap
where redo_synch_polls >0
and hist.snap_id=snap.snap_id
and hist.instance_number=snap.instance_number
order by 1,2
/
BEGIN_INTERVAL_TIME       INST STAT_NAME                 REDO_SYNCH_POLLS
------------------------- ---- ------------------------- ----------------
06-JAN-15 07.00.02.884 AM    2 redo synch polls                       734
06-JAN-15 08.00.08.425 AM    2 redo synch polls                     23767
06-JAN-15 09.00.13.770 AM    2 redo synch polls                     39827
06-JAN-15 10.00.19.233 AM    2 redo synch polls                     48479
06-JAN-15 11.00.24.431 AM    2 redo synch polls                     41541
06-JAN-15 12.00.29.670 PM    2 redo synch polls                     47566
06-JAN-15 01.00.35.029 PM    2 redo synch polls                     32169
06-JAN-15 02.00.04.159 PM    2 redo synch polls                     37405
06-JAN-15 02.59.04.536 PM    2 redo synch polls                     41469
06-JAN-15 04.00.08.556 PM    2 redo synch polls                     38683
06-JAN-15 05.00.12.523 PM    2 redo synch polls                     51618
06-JAN-15 06.00.16.584 PM    2 redo synch polls                     52511
06-JAN-15 07.00.03.352 PM    2 redo synch polls                     42229
06-JAN-15 08.00.08.663 PM    2 redo synch polls                     35229
06-JAN-15 09.00.13.882 PM    2 redo synch polls                     18499
      
      

       
       实际案例：
      
      
      
      

       
         今日在维护的数据库系统中，出现应用列队堆积的问题，分析对比awr报告发现在io stat项目中，
      
      
      
      

       
       需求读取的io量明显增加，io出现瓶颈，最后将涉及io操作问题的sql语句会话和应用协商进行删除。查询segment 物理读取的数进行sql优化，避免出现大量大表的全表扫描导致io过于繁忙，
      
      
      
      

       
       导致一些列相关io的等待事件出现。