.
\n![\"cloud\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/cloud.png\")
Having a database in the cloud is very nice. You add storage with a few clicks. You don’t have to think about the storage type (DAS, SAN, NAS), size of disks, stripe size, SSD, etc. But do you have the performance you expect? I’ve tested I\/O in the Cloud that just opened for Europe, so that I get an image of performance when nobody’s there.<\/p>\n
If you want to test IOPS, the tool is Kevin Closson SLOB of course. It’s easy to setup, and I detailed that in the SLOB in the Cloud<\/a>.<\/p>\n I have a VM with a 100GB storage attached to it. The storage was defined as ‘latency’ which definition is recommended for low latency and high IOPS, such as for storing database files<\/em> according to documentation<\/a>. As of now, I’ve no (official) idea about the storage that is behind.<\/p>\n I run SLOB with default options except the scale that fit the maximum I have on that VM:<\/p>\n So here is the result for the storage optimized for latency:<\/p>\n On average, this is what we can call ‘low latency’. Let’s look at the detail.<\/p>\n Most of I\/O come between 256µs and 512µs. That may suggest data was cached in the array behind Fiber Channel (roundtrip on FC being around 240µs). Nothing below shows that nothing is cached on the server.<\/p>\n That may be an effect of my small amount of data (reading within 9000MB in 5 minutes) and the fact that there is not a lot of activity on that Cloud yet. We will see in the future how it evolves. A small part of I\/O is within the mechanical disks latency (> 4ms for 15000 RPM)<\/p>\n I’ve run the same workload with a ‘default’ storage witch is recommended by documentation for all other than datafiles.<\/p>\n From there I don’t see a big difference, except that the I\/O that are in the range of mechanical disks seem to take longer. Do we hit there a storage that is more busy and have I\/O calls in queue?<\/p>\n While I’m on these tests, and following Stefan Koehler comment (https:\/\/twitter.com\/OracleSK\/status\/636904162802958336<\/a>) I tested the same with the ext4 filesystem (filesystemio_options=none) and here it is:<\/p>\n We clearly see two groups here. Filesystem cache hits around 16 microseconds and array head cache at the additional Fiber Channel transfer time.<\/p>\n I’ve put that in Excel. The maximum number of IO done in 5 minutes are with the ‘latency’ storage (remember, latency here means ‘low latency’).UPDATE_PCT=25\nRUN_TIME=300\nWORK_LOOP=0\n#SCALE=500M\nSCALE=90000M\nWORK_UNIT=64\nREDO_STRESS=LITE\nLOAD_PARALLEL_DEGREE=4<\/code><\/pre>\nOptimized for latency<\/h1>\n
\ncolumn event format a30 \nset linesize 120 pagesize 1000\nselect event,wait_time_micro,wait_count,wait_time_format from v$event_histogram_micro \n where event like 'db file sequential read' order by event,wait_time_micro;\n<\/code><\/pre>\n\nEVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT\n------------------------------ --------------- ---------- ------------------------------\ndb file sequential read 1 0 1 microsecond\ndb file sequential read 2 0 2 microseconds\ndb file sequential read 4 0 4 microseconds\ndb file sequential read 8 0 8 microseconds\ndb file sequential read 16 0 16 microseconds\ndb file sequential read 32 0 32 microseconds\ndb file sequential read 64 0 64 microseconds\ndb file sequential read 128 0 128 microseconds\ndb file sequential read 256 27 256 microseconds\ndb file sequential read 512 390619 512 microseconds\ndb file sequential read 1024 172914 1 millisecond\ndb file sequential read 2048 3387 2 milliseconds\ndb file sequential read 4096 1709 4 milliseconds\ndb file sequential read 8192 1237 8 milliseconds\ndb file sequential read 16384 96 16 milliseconds\ndb file sequential read 32768 56 32 milliseconds\ndb file sequential read 65536 4 65 milliseconds\ndb file sequential read 131072 0 131 milliseconds\ndb file sequential read 262144 1 262 milliseconds\n<\/code><\/pre>\nDefault storage<\/h1>\n
\nEVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT\n------------------------------ --------------- ---------- ------------------------------\ndb file sequential read 1 0 1 microsecond\ndb file sequential read 2 0 2 microseconds\ndb file sequential read 4 0 4 microseconds\ndb file sequential read 8 0 8 microseconds\ndb file sequential read 16 0 16 microseconds\ndb file sequential read 32 0 32 microseconds\ndb file sequential read 64 0 64 microseconds\ndb file sequential read 128 0 128 microseconds\ndb file sequential read 256 22 256 microseconds\ndb file sequential read 512 285811 512 microseconds\ndb file sequential read 1024 188620 1 millisecond\ndb file sequential read 2048 2776 2 milliseconds\ndb file sequential read 4096 1533 4 milliseconds\ndb file sequential read 8192 1424 8 milliseconds\ndb file sequential read 16384 1552 16 milliseconds\ndb file sequential read 32768 606 32 milliseconds\ndb file sequential read 65536 182 65 milliseconds\ndb file sequential read 131072 52 131 milliseconds\ndb file sequential read 262144 10 262 milliseconds\n<\/code><\/pre>\nFilesystem cache<\/h1>\n
\nEVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT\n------------------------------ --------------- ---------- ------------------------------\ndb file sequential read 1 0 1 microsecond\ndb file sequential read 2 0 2 microseconds\ndb file sequential read 4 0 4 microseconds\ndb file sequential read 8 562 8 microseconds\ndb file sequential read 16 10458 16 microseconds\ndb file sequential read 32 6460 32 microseconds\ndb file sequential read 64 1053 64 microseconds\ndb file sequential read 128 451 128 microseconds\ndb file sequential read 256 41 256 microseconds\ndb file sequential read 512 126705 512 microseconds\ndb file sequential read 1024 338407 1 millisecond\ndb file sequential read 2048 14425 2 milliseconds\ndb file sequential read 4096 863 4 milliseconds\ndb file sequential read 8192 187 8 milliseconds\ndb file sequential read 16384 80 16 milliseconds\ndb file sequential read 32768 87 32 milliseconds\ndb file sequential read 65536 19 65 milliseconds\ndb file sequential read 131072 2 131 milliseconds\ndb file sequential read 262144 1 262 milliseconds\ndb file sequential read 524288 1 524 milliseconds\n<\/code><\/pre>\nComparison and guesses<\/h1>\n
\nWhen using the filesystem cache, a few IO are much quicker with no need to go to the array, but the remaining ones are a bit longer. It looks like the most read blocks that were serve the faster by the array are now served from the server (I’ve about 10GB for filesystem cache).<\/p>\n
![\"CaptureDBCSIO1\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureDBCSIO1.jpg\")
<\/a><\/p>\n![\"CaptureDBCSIO2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureDBCSIO2.jpg\")
<\/a><\/p>\n