PowerMax and the Power of Data Reduction with Oracle Databases – Part II (Tests)

This post is a continuation from Part I.

Test environment

My test environment consisted of a single engine PowerMax 8000, two Dell servers PowerEdge R730 (28 cores each), RHEL 7.3, and Oracle database and ASM release 12.2.

To generate the test data I used Kevin Closson’s SLOB benchmark tool v2.3. However, I modified the data such as some of it was randomly generated. The outcome was storage compression ratio (CR) of about 3.0:1, which is what we typically expect of an Oracle database (though remember that CR always depends on the actual data).

Btw, loading the exact same data to VMAX All Flash produced 2.7:1 CR, confirming that the PowerMax hardware compression module can indeed provide stronger CR.

First test: basic utilization of compression and dedupe

Step 1: creating the SLOB test database

Per our best practices for high-performance Oracle databases, I created a few PowerMax storage groups with matching ASM disk groups:

  • crs_sg for Oracle GRID and clusterware (+GRID ASM disk group)
  • redo_sg for Oracle redo logs (+REDO ASM disk group)
  • data_sg for the Oracle data files (+DATA ASM disk group)
  • fra_sg (+FRA ASM disk group) for the archive logs (though I didn’t use archive logs in these tests).

All the ASM disk groups used External Redundancy (no ASM mirroring) except for the small +GRID disk group that used Normal Redundancy (two ASM mirrors to allow for 3 quorum files).

In all the following tests I focused on the data files (I didn’t even bother enabling compression on the redo logs, as those are relatively small and keep being overwritten – no reason to compress them). Therefore, the following graphs are based on the data files’ capacity alone.

And so, I enabled compression on data_sg (+DATA ASM disk group) and created the SLOB database with semi-random data as described above. Once the data was loaded, the Oracle data files’ extents consumed a total of 1,387.6 GB. However, due to PowerMax compression, only 448.5 GB capacity was added to the storage. That’s a 3.1:1 compression ratio (CR).

Note: in the graphs I used the term Data Reduction Ratio (DRR) to include the value of both compression and dedupe. Keep in mind that when Oracle creates its data files, each database block (typically 8 KB in size) has a unique header, which make dedupe within a single database impossible (whether the block is empty or full). Especially given PowerMax track granularity of 128 KB. However, compression in such large chunks has huge potential for efficiency. So, within a single database: compression has a strong potential for data reduction. When creating database copies: that’s where dedupe comes into play as we’re just about to see.

Step 2: creating database copies with storage snapshots

The recommended way to create database copies (for backups, test/dev, reporting instances, etc.) is to use PowerMax SnapVX storage snapshots. All SnapVX snapshots are created in seconds and very easy to use. The best part – they provide inherent dedupe value as the snapshot doesn’t consume any additional storage capacity. Capacity is only added based on changes to the data after the snapshot was established. In short – storage snapshots are the most efficient way of creating database copies.

Indeed, while I now had 3 copies of the database (original and two snapshots) worth 4,162.8 TB based on the database data capacity, when I checked the allocated storage capacity, it remained 448.5 GB! That’s 9.3:1 Data Reduction Ratio (DRR)!

Step 3: creating database copies with RMAN duplicate

Next I wanted to see what happens if the DBA doesn’t want to use SnapVX and insist on using Oracle tools such as RMAN duplicate. I terminated the two snapshots and instead created new ASM disk groups matching the source +DATA and +REDO disk groups. I mounted them on my second server and used RMAN to create a database copy:

DUPLICATE TARGET DATABASE TO slob2 FROM ACTIVE DATABASE NOFILENAMECHECK;

I repeated it twice to create two database copies: slob2 and slob3. Since RMAN utilizes the host and network resources, the copy generated lots of I/Os, and each took a few hours. However, at the end I had my two database copies ready! Ignoring the redo logs capacity (which was incremental with each copy as expected) PowerMax completely deduped the database data!

After the second RMAN duplicate I had 3 databases with a total of 4,035 TB Oracle data extents, consuming only 453.6 GB of storage (which was just about the same storage capacity as the original compressed database). That provided an overall DRR of 9.0:1. Not quite as good as SnapVX, and much slower to create, but almost just as efficient from storage capacity perspective.

To summarize, PowerMax compression reduced storage allocations when the original database was created, and PowerMax dedupe freed up the capacity of the database copies created by application tools (Oracle RMAN in this case).

Second test: Oracle encryption

I often see mission critical Oracle databases with Oracle Transparent Database Encryption (TDE) enabled. TDE can be set on specific columns, or a tablespace, and in such cases isn’t likely to affect storage compression by much. However, what happens if the whole database is encrypted? That was the motivation for my next test.

Step 1: creating the SLOB test database

Again I created a SLOB database with semi-random data. However, this time I didn’t start with a compressed data_sg storage group. As expected, both Oracle and PowerMax reported similar allocated capacity of 1,391 GB. Only then I enabled PowerMax compression on data_sg and waited for the background compression. At the end, while the database reported capacity didn’t change, the PowerMax allocated capacity dropped to 465 GB, for a 3.0:1 CR. So far – all is well.

Step 2: encrypting the database

As I mentioned earlier, the DBA can be selective in what components of the database they encrypt. For the sake of this test however, I went ahead and encrypted all the data:

sqlplus "/ as sysdba" << EOF
set echo on feedback on heading on termout on
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA' identified by <pwd>;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <pwd>;
ADMINISTER KEY MANAGEMENT SET KEY identified by <pwd> with backup;
quit;
EOF

# OFFLINE Encryption of a database:
###################################
srvctl stop database -d slob
time sqlplus "/ as sysdba" << EOF
shutdown immediate;
startup mount;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <pwd>;
alter tablespace SYSTEM encryption offline encrypt;
alter tablespace SYSAUX encryption offline encrypt;
alter tablespace UNDOTBS1 encryption offline encrypt;
alter tablespace UNDOTBS2 encryption offline encrypt;
alter tablespace SLOB encryption offline encrypt;
quit;
EOF

Since encryption masks the data and essentially makes it look random, or unique, the outcome was that storage compression lost its value and both database and storage capacity allocation matched again (at least very close match, as can be seen in the graph). This made me think that when Oracle encryption is used on the whole database, perhaps it should be joint by Oracle Advanced Compression to reduce the database footprint. Comparing Oracle Advanced Compression and PowerMax storage compression is a topic in itself as they are completely different in architecture and how they operate. Perhaps material for another blog… Moving on.

Step 3: creating database copies with RMAN duplicate

This time I skipped the creation of database copies with SnapVX, as we already know that storage snapshots don’t consume additional capacity besides changes to data after the snapshot was taken. So I moved right away to RMAN duplicate test to see if PowerMax dedupe will be just as efficient with the encrypted database.

Again, I started without enabling PowerMax compression on the target storage group to which RMAN duplicated the database. As such, when RMAN finished the copy, the data files allocations doubled (original database + copied database), and also the storage allocations doubled (as the target storage group was not enabled for compression yet). Storage allocations jumped from 1,367 GB to 2,690 GB. At that point I enabled PowerMax compression on the target storage group and waited for the background compression (and dedupe) to finish.

The result was magnificent! Storage allocations dropped back to 1,373 GB for a 100% dedupe rate of the copied data.

To summarize, PowerMax compression value was reduced due to Oracle database encryption (and disappeared all together when the database was fully encrypted). However, database copies, whether they were created using SnapVX or RMAN duplicate, fully benefited from PowerMax dedupe capability.

Of course if the database copies are modified (regardless if they were created by SnapVX or RMAN) the dedupe value will be reduced, however, often when there is one database copy – there are many… If, for example, the first copy is modified (e.g. Production database data is masked on the copy before it is handed to developers or tests), chances are that the rest of the copies will be created from the masked copy, and benefit from dedupe. Otherwise, normal changes to database copies (such as during test, development, and reporting instances) tend to be small compared to the overall size of the database, and unlikely to reduce the value of dedupe by much.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: