Pickleball spielen

With this posting we will look at the Flash Cache, built into the Exadata Database Machine. This feature is one major reason why Exadata is not only beneficial for Data Warehouse but also for OLTP. In a Full Rack, we have 8 Database Servers and 14 Storage Servers. Each Storage Server (brief: Cell) contains 4 PCIe Flash Cards:

This Flash Card * (s. Addendum at the bottom) delivers 96 GB Flash Storage, devided into 4 Flash Drives. This summarizes to 5 TB Flash Storage for a Full Rack – many Databases will probably fit completely into it. In an OLTP Database, the number of IOs per second deliverable is one of the most critical factors. With the above configuration, we can deliver up to 1 Million IOs per second.

The default way to deal with the Flash Storage is to use it completely as Flash Cache. You may think of Flash Cache as a prolongation of the Database Buffer Cache. It is populated automatically by the system with objects deemed useful to cache them. Without any intervention it is used that way:

CellCLI> list flashcache detail
 name:                   exa5cel02_FLASHCACHE
 cellDisk:                [... list of all 16 flashdisks]
 creationTime:           2011-02-02T01:14:04-08:00
 degradedCelldisks:
 effectiveCacheSize:     365.25G
 id:                     880d826b-47cc-4cf5-95fc-c36d6d315ba8
 size:                   365.25G
 status:                 normal

That was from one of the cells as the celladmin user connected using the command line interface. The whole Flash Storage is in use for Flash Cache ** on that cell. On the Database Layer, we may see the effect like this:

SQL> select name,value  from v$sysstat where name in
('physical read total IO requests','cell flash cache read hits');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                    51572139
cell flash cache read hits                                         32344851

That is already a good ratio: The majority of IO requests is resolved from the Flash Cache. We can specify storage attributes on the segment layer to influence the caching behavior of that segment:

SQL> select segment_name,cell_flash_cache from user_segments;
SEGMENT_NA CELL_FL
---------- -------
SALES      KEEP

We have 3 possible values here: DEFAULT (the default), KEEP and NONE. Keep means that the sales table will be stored in the Flash Cache „more aggressively“ than the default. It will in other words increase the chance to read it from there. The table is the same as in the previous posting. Because of this setting and previous selects on the table that populated the Flash Cache with it, I am now able to read it from there. I reconnect to initialize v$mystat:

SQL> connect adam/adam
Connected.
SQL> set timing on
SQL>  select count(*) from sales;

 COUNT(*)
----------
 20000000

Elapsed: 00:00:00.50
SQL> select name,value  from v$mystat natural join v$statname where name in
('physical read total IO requests','cell flash cache read hits');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                       10265
cell flash cache read hits                                            10265

The second possibility to deal with the Flash Storage is to take a part of it for building ASM diskgroups upon. All files on these ASM diskgroups will then reside permanently on Flash Storage:

CellCLI> drop flashcache
Flash cache exa5cel01_FLASHCACHE successfully dropped

CellCLI> create flashcache all size=100g
Flash cache exa5cel01_FLASHCACHE successfully created

CellCLI> create griddisk all flashdisk prefix=flashdrive
GridDisk flashdrive_FD_00_exa5cel01 successfully created
GridDisk flashdrive_FD_01_exa5cel01 successfully created
GridDisk flashdrive_FD_02_exa5cel01 successfully created
GridDisk flashdrive_FD_03_exa5cel01 successfully created
GridDisk flashdrive_FD_04_exa5cel01 successfully created
GridDisk flashdrive_FD_05_exa5cel01 successfully created
GridDisk flashdrive_FD_06_exa5cel01 successfully created
GridDisk flashdrive_FD_07_exa5cel01 successfully created
GridDisk flashdrive_FD_08_exa5cel01 successfully created
GridDisk flashdrive_FD_09_exa5cel01 successfully created
GridDisk flashdrive_FD_10_exa5cel01 successfully created
GridDisk flashdrive_FD_11_exa5cel01 successfully created
GridDisk flashdrive_FD_12_exa5cel01 successfully created
GridDisk flashdrive_FD_13_exa5cel01 successfully created
GridDisk flashdrive_FD_14_exa5cel01 successfully created
GridDisk flashdrive_FD_15_exa5cel01 successfully created

The Flash Cache for this cell is now reduced to 100 GB; all means „upon all 16 Flash Drives“ here. I am doing the same on the second cell – my Database Machine is limited to only one Server Node and two Cells. That gives me 32 Grid Disks based on Flash Drives to create ASM diskgroups upon:

CellCLI> drop flashcache
Flash cache exa5cel02_FLASHCACHE successfully dropped

CellCLI> create flashcache all size=100g
Flash cache exa5cel02_FLASHCACHE successfully created

CellCLI> create griddisk all flashdisk prefix=flashdrive
GridDisk flashdrive_FD_00_exa5cel02 successfully created
GridDisk flashdrive_FD_01_exa5cel02 successfully created
GridDisk flashdrive_FD_02_exa5cel02 successfully created
GridDisk flashdrive_FD_03_exa5cel02 successfully created
GridDisk flashdrive_FD_04_exa5cel02 successfully created
GridDisk flashdrive_FD_05_exa5cel02 successfully created
GridDisk flashdrive_FD_06_exa5cel02 successfully created
GridDisk flashdrive_FD_07_exa5cel02 successfully created
GridDisk flashdrive_FD_08_exa5cel02 successfully created
GridDisk flashdrive_FD_09_exa5cel02 successfully created
GridDisk flashdrive_FD_10_exa5cel02 successfully created
GridDisk flashdrive_FD_11_exa5cel02 successfully created
GridDisk flashdrive_FD_12_exa5cel02 successfully created
GridDisk flashdrive_FD_13_exa5cel02 successfully created
GridDisk flashdrive_FD_14_exa5cel02 successfully created
GridDisk flashdrive_FD_15_exa5cel02 successfully created

CellCLI> list griddisk flashdrive_FD_10_exa5cel02 detail
 name:                   flashdrive_FD_10_exa5cel02
 availableTo:
 cellDisk:               FD_10_exa5cel02
 comment:
 creationTime:           2011-02-02T02:56:52-08:00
 diskType:               FlashDisk
 errorCount:             0
 id:                     0000012d-e604-87f2-0000-000000000000
 offset:                 6.28125G
 size:                   16.578125G
 status:                 active

Changing to the Database Server Node to create the ASM diskgroup as sysasm:

SQL> select path,header_status, os_mb,free_mb from v$asm_disk where path like '%flash%'

PATH                                               HEADER_STATU      OS_MB    FREE_MB
-------------------------------------------------- ------------ ---------- ----------
o/192.168.14.10/flashdrive_FD_14_exa5cel02         CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_12_exa5cel01          CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_05_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_11_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_08_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_15_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_00_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_03_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_06_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_12_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_09_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_01_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_04_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_13_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_07_exa5cel02         CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_10_exa5cel02         CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_07_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_04_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_10_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_01_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_13_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_08_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_05_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_02_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_14_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_11_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_00_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_09_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_03_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_15_exa5cel01          CANDIDATE         16976          0
o/192.168.14.9/flashdrive_FD_06_exa5cel01          CANDIDATE         16976          0
o/192.168.14.10/flashdrive_FD_02_exa5cel02         CANDIDATE         16976          0

32 rows selected.
SQL> create diskgroup flashdrive normal redundancy
disk 'o/*/flashdrive*'
attribute 'compatible.rdbms'='11.2.0.0.0',
'compatible.asm'='11.2.0.0.0',
'cell.smart_scan_capable'='TRUE',
'au_size'='4M';  

Diskgroup created.

Please notice the Allocation Unit size of 4MB, necessary for Exadata. Normal Redundancy is strongly recommended – automatically each cell is also a Failure Group. In other words: Should one Storage Server crash, no loss of Data will happen, regardless whether we use Flash Drives or spinning drives to build the diskgroups upon. After the creation, we use the diskgroup like any other:

SQL>  create tablespace veryfast datafile '+flashdrive' size 10g;

Tablespace created.

Any segment created in this tablespace will reside on Flash Drives permanently.

Let’s take the opportunity to give an example for Information Lifecycle Management (ILM):

SQL> create tablespace compahigh datafile size 1g;
Tablespace created.
SQL> alter tablespace compahigh default compress for archive high;
Tablespace altered.

The tablespace got created on spinning drives, because my DB_CREATE_FILE_DEST parameter points to such a diskgroup. Same for the next two:

SQL> create tablespace querylow datafile size 1g;
Tablespace created.
SQL> alter tablespace querylow default compress for query low;
Tablespace altered.
SQL> create tablespace ordinary datafile size 1g;
Tablespace created.

My plan is to store one large partitioned table partly compressed, partly uncompressed on spinning drives and partly on Flash Drives – the newest and most volatile part.

SQL> create table sales_part
(id number, flag number, product char(25),channel_id number,cust_id number,
amount_sold number, order_date date, ship_date date)
partition by range (order_date)
interval (numtoyminterval(1,'year'))
store in (veryfast)
(
partition archhigh values less than (to_date('01.01.1991','dd.mm.yyyy')) tablespace compahigh,
partition querylow values less than (to_date('01.01.1998','dd.mm.yyyy')) tablespace querylow,
partition ordi1998 values less than (to_date('01.01.1999','dd.mm.yyyy')) tablespace ordinary,
partition ordi1999 values less than (to_date('01.01.2000','dd.mm.yyyy')) tablespace ordinary,
partition ordi2000 values less than (to_date('01.01.2001','dd.mm.yyyy')) tablespace ordinary,
partition ordi2001 values less than (to_date('01.01.2002','dd.mm.yyyy')) tablespace ordinary,
partition ordi2002 values less than (to_date('01.01.2003','dd.mm.yyyy')) tablespace ordinary,
partition ordi2003 values less than (to_date('01.01.2004','dd.mm.yyyy')) tablespace ordinary,
partition ordi2004 values less than (to_date('01.01.2005','dd.mm.yyyy')) tablespace ordinary,
partition ordi2005 values less than (to_date('01.01.2006','dd.mm.yyyy')) tablespace ordinary
)
;  

Table created.

This uses the 11g New Feature Interval Partitioning to create new partitions automatically on Flash Drives. Now loading the table from the old sales table:

SQL> alter table sales_part nologging;

Table altered.

SQL> insert /*+ append */ into sales_part select * from sales order by order_date;

20000000 rows created.

Notice the order by above. It makes it later on possible not only to do partition pruning but also to use Storage Indexes if we query after ORDER_DATE or even SHIP_DATE. The single partitions now look like this:

SQL> select partition_name,tablespace_name,bytes/1024/1024 as mb
from user_segments where segment_name='SALES_PART';  

PARTITION_NAME                 TABLESPACE_NAME                        MB
------------------------------ ------------------------------ ----------
ARCHHIGH                       COMPAHIGH                               8
ORDI1998                       ORDINARY                               56
ORDI1999                       ORDINARY                               56
ORDI2000                       ORDINARY                               56
ORDI2001                       ORDINARY                               56
ORDI2002                       ORDINARY                               56
ORDI2003                       ORDINARY                               56
ORDI2004                       ORDINARY                               56
ORDI2005                       ORDINARY                               56
QUERYLOW                       QUERYLOW                               40
SYS_P101                       VERYFAST                               56
SYS_P102                       VERYFAST                               56
SYS_P103                       VERYFAST                               56
SYS_P104                       VERYFAST                               56
SYS_P105                       VERYFAST                               56
SYS_P106                       VERYFAST                                8

16 rows selected.

SQL> select count(*) from sales_part partition (archhigh);

 COUNT(*)
----------
 5330000

SQL>  select count(*) from sales_part partition (querylow);

 COUNT(*)
----------
 5114000

SQL> select count(*) from sales_part partition (ordi1998);

 COUNT(*)
----------
 730000

SQL>  select count(*) from sales_part partition (sys_p101);

 COUNT(*)
----------
 730000

During the life cycle of the data, partitions may no longer be highly volatile and can be moved to spinning drives or even get compressed:

SQL> alter table sales_part move partition sys_p101 tablespace ordinary;

Table altered.

SQL> alter table sales_part move partition ordi1998 compress for query low tablespace querylow;

Table altered.

SQL> select partition_name,tablespace_name,bytes/1024/1024 as mb
from user_segments where segment_name='SALES_PART';  

PARTITION_NAME                 TABLESPACE_NAME                        MB
------------------------------ ------------------------------ ----------
ARCHHIGH                       COMPAHIGH                               8
ORDI1998                       QUERYLOW                                8
ORDI1999                       ORDINARY                               56
ORDI2000                       ORDINARY                               56
ORDI2001                       ORDINARY                               56
ORDI2002                       ORDINARY                               56
ORDI2003                       ORDINARY                               56
ORDI2004                       ORDINARY                               56
ORDI2005                       ORDINARY                               56
QUERYLOW                       QUERYLOW                               40
SYS_P101                       ORDINARY                               56
SYS_P102                       VERYFAST                               56
SYS_P103                       VERYFAST                               56
SYS_P104                       VERYFAST                               56
SYS_P105                       VERYFAST                               56
SYS_P106                       VERYFAST                                8

16 rows selected.

We have no indexes in place – so there is no rebuild needed 🙂

Summary: Flash Storage inside the Oracle Exadata Database Machine is used completely as Flash Cache by default, effectively working as an extension of the Database Buffer Cache  and delivering faster Access together with a very high IO per Second rate which is especially important for OLTP. Additionally, we may take a part of the Flash Storage to build ASM diskgroups upon it. Files placed on these diskgroups will reside permanently on Flash Storage – no Caching needed.

* Addendum: The posting reflects the state of X2. With X3, Cells come with 4 x F40 Flashcards that deliver  each 400 GB Flash capacity, now to a total of 1600 GB Flash capacity for each Storage Server. Given that the F40 is also faster than the old F20 and together with the new Write Back Flash Cache technology, there will be even less likely a need to build ASM diskgroups upon Flash storage than with X2.

** Second Addendum: Already with newer versions of X2, we introduced Flash Logging, which takes 512 MB Flash Storage from each cell (regardless whether it is X2 or X3). This relatively small amount reduces the capacity of the Flash Cache accordingly. See here for a more detailed explanation (Page 6).