Testing Exadata with Different ASM AU Sizes

Oracle recommends a 4MB ASM allocation unit (AU) size for its ASM disk groups. In this post, we'll explore test cases with different ASM disk group AU sizes. Let's create some tablespaces to match our testing requirements:

Using a 1MB AU Size

In this section we'll perform tests for full-scanning a table when it's stored in an ASM disk group with a 1MB ASM allocation unit size and compare with the same table stored in a tablespace residing on an ASM disk group with a 4MB AU size. The control-case done with SYSTEM.MYOBJ, which has the following characteristics:

• Table: SYSTEM.MYOBJ

• Size (Gb): 14.8

• Blocks: 1,940,352

• Tablespace Name: USERS

• ASM Disk Group: DATA_CM01

• ASM AU Size: 4MB

Let's create a tablespace in the AU1MDATA_CM01 ASM disk group:

Now we'll create the copy of MYOBJ:

Let's run our test:

As we can see from the above test:

• The query execution time went from 4.32 seconds to 7.58 seconds with a 1MB AU size.

• Roughly the same number of bytes where received from the cluster interconnect, which was to be expected.

• The cell scan efficiency remained the same, so Smart Scan was definitely saving time.

• A 1MB AU size will required more physical IOs and be slower than a 4MB AU size in this test.

Using a 8MB AU Size

In this section we'll perform tests for full-scanning a table when it's stored in an ASM disk group with a 8MB ASM allocation unit size and compare with the same table stored in a tablespace residing on an ASM disk group with a 4MB AU size. The control-case done with SYSTEM.MYOBJ, which has the following characteristics:

• Table: SYSTEM.MYOBJ

• Size (Gb): 14.8

• Blocks: 1,940,352

• Tablespace Name: USERS

• ASM Disk Group: DATA_CM01

• ASM AU Size: 4MB

Let's create a tablespace in the AU8MDATA_CM01 ASM disk group:

Now we'll create our test table:

And finally, run our test:

As we can see from the above test:

• The query execution time went from 4.32 seconds to 7.92 seconds with an 8 MB AU size.

• Approximately the same number of bytes was returned.

• Using a larger AU size (8MB) didn't improve times in the scan; in fact, it reduced it to the same as when using a 1MB AU size. This would tend to conclude that a 4MB AU size is indeed ideal on Exadata.
   

Since this test and the previous one with the 1MB ASM allocation unit size showed relatively the same results, and considering the 8MB AU size would seem to yield better response, we're going to do some additional testing with multiple runs (5 each) to see if the tests are indeed what saw with just one sample:

We can see from this that the timings per test indeed indicate that things are more efficient (i.e., run faster) with a 4MB AU size, but let's try to figure out exactly why. Using V$SESSION_EVENT, we can see that the "cell smart table scan" wait event was the event responsible for most of the wait time, so let's compare test results:

As we can see above, the number and total time of "cell smart table scan" waits was larger with 1MB AU and 8MB AU sizes for the second two tests. They retrieved roughly the same number of bytes over the interconnect, and the number of bytes and blocks per segment is very nearly the same. If we do some math on the above table, we can see that the number of waits increased for 1MB and 8MB ASM AU sizes, but the total time didn't increase linearly with the number of waits, so we could suspect that alignment boundary issues are the cause of more waits.

Based on this, we can conclude that more work is required to satisfy cell IO requests when ASM disk groups don't use a 4MB AU size. So setting ASM disk group attributes will clearly yield a better overall result, in this case about 50% time-savings.

What about Extent Placement on the Physical Disk?

As you've probably noticed in the tests above, a 4MB AU size was optimal for performance. But was this solely because of the 4MB AU size or could it have been because the 4MB AU ASM disk group was created on a set of Grid Disks that resided our the outermost tracks of the physical disk? In this section, we'll again tear down the ASM disk groups and re-create them one at a time and do our testing.

The first thing we'll do to get a clean baseline is wipe out the DBM database. After this, we'll drop the ASM disk groups DATA_CM01 and RECO_CM01:

After this is completed, we'll delete our Grid Disks:

When complete, we'll create grid disks, 425Gb and remainder, for DATA_CM01 and RECO_CM01:

Next, let's create an ASM disk group on the "DATA" Grid Disks with a 1MB allocation unit and an ASM disk group with a 4MB AU called RECO_CM01:

When complete, we'll re-create our DBM RAC database:

Environment details:

Now we'll set to post-rebuild settings:

At this point, we can create our test table, MYOBJ:

Now we'll check the space used by MYOBJ:

Finally, let's run full-scan test:

We'll then repeat the tests above with an ASM disk group with a 8MB AU and a 4MB AU size. I'll leave out most of the details of the test case builds, as they mirror the above with the exception of the ASM disk group creation.

The 8MB AU test is below:

And finally, our 4MB storage test:

Summary:

Full-scans with cell offload (Smart Scan) perform better on Exadata with 4MB AU sizes

Summary

Configure ASM disk groups on Exadata with a 4MB allocation unit size.