This is so cool !
The above is so cool.
The graphic shows the latency heatmap of “log file sync” on Oracle displayed in SQL*Plus! SQL*Plus ?! Yes, the age old text interface to Oracle showing colored graphics.
How did I do this? All I did was type
sqlplus / as sysdba
@OraLatencyMap_event 3 "log file sync"The script OraLatencyMap_event was created by Luca Canali , see http://externaltable.blogspot.com/2013/05/latency-heat-map-in-sqlplus-with.htm
Now if we combine Luca’s monitoring, with the I/O throttling documeted by Frits Hoogland here https://fritshoogland.wordpress.com/2012/12/15/throttling-io-with-linux/ , we can really have some fun and even draw latency words:
The above words in latency heat maps are from http://dtrace.org/blogs/brendan/2009/03/17/heat-map-analytics/ where there is information on heat maps. In the graphic at the top of the page I put lgwr in an I/O write throttle group and played with the I/O throttle. I was running a swingbench load and at the same as throttling I/O such that latencies started off good then got worse and then back to normal. The full steps are:
Run an auto refresh color coded heatmap on “log file sync” in sqlplus by typing
sqlplus / as sysdba
@OraLatencyMap_event 3 "log file sync"where OraLatencyMap_event.sql and OraLatencyMap_internal.sql are your current directory or sqlpath
Now to play with LGWR latency with cgroup throttles see
# install cgroups on 2.6.24 LINUX or higher
yum intall cgroup
# setup /cgroup/blkio
grep blkio /proc/mounts || mkdir -p /cgroup/blkio ; mount -t cgroup -o blkio none /cgroup/blkio
cgcreate -g blkio:/iothrottle
# find the device you want
df -k
# my Oracle log file divice was
ls -l /dev/mapper/vg_source-lv_home
lrwxrwxrwx. 1 root root 7 May 1 21:42 /dev/mapper/vg_source-lv_home -> ../dm-2
# my device points to /dev/dm-2
ls -l /dev/dm-2
brw-rw----. 1 root disk 253, 2 May 1 21:42 /dev/dm-2
# my device major and minor numbers are "253, 2"
# create a write throtte on this device (for read just replace "write" with "read"
# this limits it to 10 writers per second
cgset -r blkio.throttle.write_iops_device="253:2 10" iothrottle
# look for lgwr
ps -ef | grep lgwr
oracle 23165 1 0 13:35 ? 00:00:19 ora_lgwr_o1123
# put lgwr pid into throttle group
echo 23165 > /cgroup/blkio/iothrottle/tasks
# now play with different throttles
cgset -r blkio.throttle.write_iops_device="253:2 1" iothrottle
cgset -r blkio.throttle.write_iops_device="253:2 10" iothrottle
cgset -r blkio.throttle.write_iops_device="253:2 100" iothrottle
cgset -r blkio.throttle.write_iops_device="253:2 1000" iothrottle
# if you are finished then delete the throttle control group
cgdelete blkio:/iothrottle
Comments