Lately I have been drawn into to a fare number of discussions about IO characteristics while helping customers run benchmarks. I have been working with a mix of developers, DBAs, sysadmin, and storage admins. As I have learned, every group has there own perspective – certainly when it comes to IO and performance.
- Most DBA’s want to see data from the DB point of view so AWR’s or EM works just fine.
- Most System Admin’s look at storage from the Filesystem or ASM disk level.
- Storage Admins want to see what is going on within the array.
- Performance geeks like myself, like to see all up and down the stack
As part of pulling back the covers, I came up with a simple little tool for show IOPS at the cell level.
Mining IO statistics from cellcli
The cellsrv process collects data about various events and performance metrics in an Exadata storage cell. I certainly am a huge fan of the table and index usage data gathered using the ”pythian_cell_cache_extract.pl” written by Christo Kutrovsky. It is really provides a great look inside the Exadata Smart Flash Cache. So, this got me to thinking. What about IOPS data?
With the introduction of the Write Back Flash cache in X3, there is much more analysis about what is going to flash vs disk – and how what is written to flash is flushed to disk.
To look at all the current metrics gathered from the storage cells in your Exadata or SuperCluster you can run “cellcli -e list metriccurrent” on all the storage cells. The “metriccurrent” parameters are updated every minute by cellsrv to store performance data. There are a few convient parameters that can be used to sum up all the IOPS.
- CD_IO_RQ_R_LG_SEC + CD_IO_RQ_R_SM_SEC
- CD_IO_RQ_W_LG_SEC + CD_IO_RQ_W_SM_SEC
These parameters shore the number of IO/sec for reads and writes. By mining this data and breaking it down by “FD” vs “CD” you can see hit ratios for reads from an overall cell point of view, but now you can also see how many writes are going to FLASH vs DISK.
The “ciops-all.sh” script will look at all the cells and sum up all the IOPS and report the findings. This is very useful to get a quick look at the IO profile in the cells.
[oracle@exa6db01 WB]$ ./ciops-all.sh FLASH_READ_IOPS: 6305 DISK_READ_IOPS: 213 FLASH_WRITE_IOPS: 488203 DISK_WRITE_IOPS: 6814 TOTAL_NUMBER_OF_DRIVES: 84 WRITE_PCT_to_FLASH: 98 READ_PCT_from_FLASH: 96 IOPS_PER_DISK: 83
This can be very helpful when trying to figure out if you need to go with high performance or high capacity disks. This case shows most IO going to the flash and only 83 IOPS are spilled to each disk. So, with this case HC disks would be a fine choice. With a simple modification, I made the “ciops-mon.sh” script to print out the throughput every few minutes to graph the results over time.
This has been helpful as I have been investigating and explaining the inter-workings of the Exadata smart flash cache. Hopefully, you will find this useful when trying to analyze and understand Exadata Cell level IO with your workload.