使用rrdtool自定义绘图监控Oracle数据库


结合这篇关于PHP连接Oracle数据库，本次结合rrdtool绘图工具，绘制一些自定义的监控图，虽然Oracle的EM已经很完美了，但是往往根据业务需求对一些自定义或者说业务环境关心的问题无法监控，此时我们就需要自己来搞定了。

首先用php写一个rrdtool数据库文件的生产 脚本

  1. #!/usr/bin/php -f
  2. <?php
  3. define('WD','/u01/app/oracle/awr');
  4. $cs = $_SERVER['argv'][1];
  5. $user = 'system';
  6. $pass = 'oracle';
  7. /* open a new connection */
  8. $ds = oci_connect($user, $pass, $cs)
  9. or die ("Cannot connect to Oracle Database ".$cs."\n");
  10. /* setting client nls environment */
  11. $sql = "alter session set nls_timestamp_format='MM/DD/YY HH24:MI'";
  12. $stmt = oci_parse($ds, $sql);
  13. oci_execute($stmt);
  14. oci_free_statement($stmt);
  15. /* create directory that will contain rrds (if not exists) */
  16. if(!file_exists(WD.'/'.$cs))
  17. mkdir(WD.'/'.$cs);
  18. if(!file_exists(WD.'/'.$cs.'/wait'))
  19. mkdir(WD.'/'.$cs.'/wait');
  20. /* function to create new RRDs */
  21. function createRRD($name, $interval, $cs) {
  22. $hb = $interval*5; //heartbeat
  23. $cmd="rrdtool create ".WD."/".$cs."/wait/${name}.rrd -s ".$interval." \
  24. -b \"now -3month\" DS:waits:DERIVE:$hb:0:U \
  25. DS:mswaited:DERIVE:$hb:0:U \
  26. RRA:AVERAGE:0.5:1:1440 RRA:AVERAGE:0.5:30:336 \
  27. RRA:AVERAGE:0.5:120:372 RRA:AVERAGE:0.5:720:730 \
  28. RRA:MIN:0.5:1:1440 RRA:MIN:0.5:30:336 \
  29. RRA:MIN:0.5:120:372 RRA:MIN:0.5:720:730 \
  30. RRA:MAX:0.5:1:1440 RRA:MAX:0.5:30:336 \
  31. RRA:MAX:0.5:120:372 RRA:MAX:0.5:720:730 \
  32. RRA:LAST:0.5:1:1440";
  33. //print $cmd."\n";
  34. return passthru($cmd);
  35. }
  36. /* take the snapshot frequency from dba_hist_wr_control
  37. to create the RDD with correct heartbeat value */
  38. $sql = 'select extract(hour from snap_interval)*3600 +
  39. extract(minute from snap_interval)*60 as SEED from DBA_HIST_WR_CONTROL';
  40. $stmt = oci_parse($ds, $sql);
  41. oci_execute($stmt);
  42. $row = oci_fetch_assoc($stmt);
  43. $interval = $row['SEED'];
  44. unset($row);
  45. oci_free_statement($stmt);
  46. /* statement definition that will collect
  47. all snapshots for a certain wait event with more than
  48. a certain amonut of time waited.
  49. Gathering ALL EVENTS could be time consuming and useless.
  50. I fetch rows ordered by event_name rather
  51. then by date because I can update many values
  52. into the same rrd with very few rrdupdate commands
  53. */
  54. $sql = 'select s.END_INTERVAL_TIME END_INTERVAL_TIME,
  55. g.EVENT_NAME, g.WAIT_CLASS, g.TOTAL_WAITS,
  56. round(g.TIME_WAITED_MICRO/1000) MS
  57. from DBA_HIST_SNAPSHOT s,
  58. dba_hist_bg_event_summary g,
  59. v$instance i
  60. where s.SNAP_ID=g.SNAP_ID and g.wait_class!=\'Idle\'
  61. and g.TIME_WAITED_MICRO&gt;100000
  62. and s.instance_number=i.instance_number
  63. and s.instance_number=g.instance_number
  64. order by 2,1';
  65. /* default prefetch size (148) matches default snapshot retention (24hx7dd) */
  66. $stmt = oci_parse($ds, $sql);
  67. oci_set_prefetch($stmt, 148);
  68. oci_execute($stmt);
  69. $i=0;
  70. $oldevent="";
  71. while ($row = oci_fetch_assoc($stmt)) {
  72. if ($oldevent != $row['EVENT_NAME']) {
  73. //NEW EVENT DETECTED: WILL START A NEW UPDATE CMD
  74. if ($i != 0 &amp;&amp; !empty($cmd)) {
  75. /* not the first occurrence,
  76. I bet there's something in my buffer */
  77. passthru($cmd);
  78. }
  79. $cleanName = preg_replace ("([^[:alnum:]_-])","_",$row['EVENT_NAME']);
  80. // if there is no rrd for this event, I create a new one
  81. if (!file_exists(WD."/".$cs."/wait/${cleanName}.rrd")) {
  82. createRRD($cleanName, $interval, $cs);
  83. }
  84. /*
  85. * I initialize a new update command. This string act as a buffer: I append many
  86. * values to be updated so I'll update many values in a single command line:
  87. * less forks of rrdtool and less file opens: the whole update process has an
  88. * enormous improvement.
  89. */
  90. $precmd="rrdtool update ".WD."/".$cs."/wait/${cleanName}.rrd ";
  91. $lastcmd="rrdtool info ".WD."/".$cs."/wait/${cleanName}.rrd".
  92. "| grep last_update | awk '{print \$NF}'";
  93. $last=trim(`$lastcmd`);
  94. printf ("%s - %s - last: %d\n", $row['EVENT_NAME'], $cleanName, $last);
  95. $i=0;
  96. $cmd=$precmd;
  97. $oldevent=$row['EVENT_NAME'];
  98. }
  99. $time=strtotime($row['END_INTERVAL_TIME']);
  100. //print "time: ".$time." last: ".$last."\n";
  101. if ( $time &gt; $last ) {
  102. $cmd.=" ".$time.":".$row['TOTAL_WAITS'].":".$row['MS'];
  103. $i++;
  104. }
  105. if ($i &gt;= 40) {
  106. // when I reach 40 values per commandline I force
  107. // the update: next loop will reinitialize a new commandline.
  108. passthru($cmd);
  109. $cmd=$precmd;
  110. $i=0;
  111. }
  112. unset($row);
  113. }
  114. if ($i != 0) {
  115. /* one more update pending in my buffer */
  116. passthru($cmd);
  117. }
  118. oci_free_statement($stmt);
  119. oci_close($ds);
  120. ?>

通过运行以上脚本可以生成相关rrd文件 

  1. # ll  
  2. total 3864  
  3. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 Streams_AQ__enqueue_blocked_on_low_memory.rrd  
  4. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 buffer_busy_waits.rrd  
  5. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_parallel_write.rrd  
  6. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_sequential_read.rrd  
  7. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 cursor__pin_S_wait_on_X.rrd  
  8. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_scattered_read.rrd  
  9. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_sequential_read.rrd  
  10. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 events_in_waitclass_Other.rrd  
  11. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__cache_buffers_chains.rrd  
  12. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache.rrd  
  13. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache_lock.rrd  
  14. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__redo_writing.rrd  
  15. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__row_cache_objects.rrd  
  16. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__shared_pool.rrd  
  17. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_load_lock.rrd  
  18. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_lock.rrd  
  19. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_buffer_space.rrd  
  20. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_parallel_write.rrd  
  21. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sequential_read.rrd  
  22. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_single_write.rrd  
  23. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_switch_completion.rrd  
  24. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sync.rrd  
  25. -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 os_thread_startup.rrd 

使用rrdtool命令绘图如下

相关内容