log file sync的再次思考

来源：互联网发布：windows的相对路径编辑：程序博客网时间：2024/05/22 04:30

昨天系统整体慢，最终定位到是数据库层面的问题。在TOP的等待事件中可以看到log file sync平均等待达到了157，根据之前的经验这个值超过20就有性能问题。

如果按照以前的诊断思路，就是认定服务器的IO有问题，建议用IO快一点的存储。

不过通过这次问题的解决，思路有些变化，发生log file sync等待事件过高说明服务器IO出现瓶颈，这是没有问题的。如果此服务器IO一直没有问题，就今天出了问题，可能是SQL导致，接着分析Segments by Physical Reads，可以看到是查询那些表的无力度高。再通过这些表找到对应的SQL优化。

本次的优化就是优化SQL后，整个系统的物理读降下来了，log file sync也降到了5。

Host NamePlatformCPUsCoresSocketsMemory (GB)gg-sjk-01AIX-Based Systems (64-bit)12832 240.00

Snap IdSnap TimeSessionsCursors/SessionInstancesBegin Snap:544625-Nov-15 15:00:421084138.82End Snap:544725-Nov-15 16:00:111086131.42Elapsed: 59.49 (mins) DB Time: 6,185.41 (mins)

Top 10 Foreground Events by Total Wait Time

EventWaitsTotal Wait Time (sec)Wait Avg(ms)% DB timeWait Classenq: TX - row lock contention4,89588K1797123.7Applicationdirect path read450,23559.7K13316.1User I/Ogc buffer busy acquire996,23637.3K3710.0Clusterrdbms ipc reply82,075,60926.3K07.1Otherdb file sequential read652,67822.8K356.1User I/Odb file scattered read828,11520.9K255.6User I/ODB CPU 20K 5.4 read by other session946,40218.5K205.0User I/Olog file sync103,43616.3K1574.4Commitgc cr block busy73,69513.1K1783.5Cluster

Segments by Physical Reads

Total Physical Reads: 167,906,170
Captured Segments account for 98.3% of Total

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%TotalL_SYSL_SYS_DATARU_DONE_TASK_MISS TABLE71,893,92142.82L_SYSL_SYS_DATAUSER_VISIT TABLE36,013,36721.45L_SYSL_SYS_DATARU_DONE_TASK_MRTN TABLE25,627,23515.26L_ZL_Z_DATAPURCHASE_ITEM_MV TABLE16,697,7199.94L_ZL_Z_DATAOPERATION_HISTORY TABLE12,571,3657.49

0 0