By Shailendra Tripathi, Fellow, Filesystem Development Engineering, and Ganesh Balabharathi, Sr. Technologist, Performance Engineering

As I shared in my previous blog, NVMe protocol, devices and arrays are bringing very low latency and extreme performance to the data center. We wanted to test our IntelliFlash™ NVMe array to assess if and how real-world applications can exploit these performance levels. Although application performance naturally benefit from such improvements, the advantages gained vary significantly for each application. It is largely driven by the application’s own characteristics and its inherent scalability.

Oracle, a very popular enterprise database, has been selected to evaluate application level performance. We measured the online transaction performance earlier and it shows commensurate performance. This blog shares our testing results and insights gained looking at analytical processing (OLAP) performance which focuses is largely throughput oriented capability.

The IntelliFlash N5800 Array

The IntelliFlash N5800 NVMe array provides fantastic performance from all three typical counts of performance – higher random read/write IOPS, higher aggregate throughput, and low latency. It delivers up to 2x improvement in random I/O performance and as much as 67% lower latency over SAS SSD arrays in typical I/O benchmarking tools like FIO, IOMeter or VDBench.

Storage performance is one of the key criterial factors that contributes directly to OLAP performance. The IntelliFlash N5800 has the ability to handle the variety of database I/O requests including full table scans (FTS) that reflect the sequential reads performance, and index lookups, index range scans and parallel executions, that reflect the random I/O performance.  The outstanding random I/O performance is already established and exhibited on in my previous OLAP blog.

Oracle Analytical Processing Test Configuration

To demonstrate the N5800 Online Analytical Processing (OLAP) capabilities with Oracle Database, the popular benchmark tool HammerDB has been used to measure the performance of TPC-H type database transactions. The TPC Benchmark™ H (TPC-H) was created as a decision support benchmark. It consists of a suite of business-oriented ad-hoc queries and concurrent data modifications that were particularly chosen to have broad industry-wide relevance. This benchmark looks at decision support systems that require large data, executing queries with a high degree of complexity, and respond to business- critical workloads and questions.[1]

See how Oracle and SQL Server databases have developed scale-out and scale-up approaches

The goal of our test was not to get the actual TPC-H numbers, as its performance is combination of client infrastructure, networking, storage and the database software. The focus instead was to use TPC-H’s standard customer-centric ad-hoc SQL queries to measure the capabilities storage in real-world application.

Testbed Configuration

The test was conducted on an IntelliFlash N5800 array connected to two physical clients running individual database instances. The client is a dual-socket system with Intel® Xeon® Gold 6130, 2.1 GHz, 16 core (with hyperthreading enabled) CPUs with total 512GB physical memory.

HammerDB TPC-H Setup

The TPC-H schema was built with a scale factor of 1000 on each host separately, as shown below:

After the full schema creation, which includes the data load, indexing, and constraints creation, the row count in each database for the TPC-H schema is shown as below:

SQL Ad-Hoc Queries

A total of 22 Ad-Hoc Queries were created. Theses queries were run randomly during the test. One sample query is shown below. All the queries used in the tests are listed in Appendix A.

sql(1): “select l_returnflag, l_linestatus, sum(cast(l_quantity as NUMBER)) as sum_qty, cast(sum(l_extendedprice) as NUMBER) as sum_base_price, cast(sum((l_extendedprice) * (1 – l_discount)) as NUMBER) as sum_disc_price, cast(sum(l_extendedprice * (1 – l_discount) * (1 + l_tax)) as NUMBER) as sum_charge, avg(cast(l_quantity as NUMBER)) as avg_qty, avg(cast(l_extendedprice as NUMBER)) as avg_price, avg(cast(l_discount as NUMBER)) as avg_disc, cast(count(*) as NUMBER) as count_order from lineitem where l_shipdate <= date ‘1998-12-01’ – interval ‘:1’ day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus”

One of the hosts appears on the TPC-H console as seen below, representing the virtual users running the 22 queries in parallel. The second host is also running a similar query. These queries were chosen randomly.

Oracle Analytical Processing on NVMe – Test Results

The AWR report generated at end of the result in each host is shown below:

Host 1 – AWR

Host 2 – AWR

Each host was able to reach the line rate on their networking interface (each host is connected via 4 x 16 Gbps ports). This is very close to the line rate (6.4 GiB/s).

The storage array view during the test run is shown below. The average read size is about 128KB and the array was serving 12.4 GiB/s. Hence, in this testbed, both the hosts are close to the line rate!

Conclusion – NVMe Array Delivers Outstanding Analytical Performance

NVMe arrays have higher performance and provide very low latency operations. We wanted to test if this is only reflected in the standard performance benchmarking tools, and how well real-world applications are able to exploit the performance from the IntelliFlash NVMe array.

We’re excited to see such outstanding performance results. The test results show that not only is the transaction performance excellent, even analytical performance is. In the 2-host setup, the array performance lets the host utilize the throughput performance and is reflected in near line rate from performance on both the hosts.

The array was not the scaling bottlenecks. The FIO based benchmarks show that the array performance can be as high as 19 GiB/s to 22 GiB/s for throughput-oriented workloads. Based on this, it is very likely that 3 host also will be able hit line rate on from the single array. The sequential throughout is largely dependent on the scalability of the page cache, metadata index and drive performance. The results indicate that the array is able to scale the performance matching the hardware capabilities.

More Analysis

In this test, the analytical throughput performance is the aggregate performance from multiple client streams (22 virtual uses on each host). It will be very interesting to assess what is the single stream throughput performance on the IntelliFlash N5800 array, and we will follow up to assess the single stream performance using a real-life application.

Follow this blog (you can subscribe below) to read our subsequent testing of single stream performance use case, evaluated in identical testbed.

To learn more about the NVMe protocol – read our technical guide

Visit our website to learn about the IntelliFlash all-NVMe array

Read the solution brief: Accelerating Oracle Applications with IntelliFlash Arrays

Learn how to manage data growth with Oracle or SQL Server®

 

[1] http://www.tpc.org/tpch/

 

Appendix A

All the SQL queries used in the test are defined below.

sql(1): “select l_returnflag, l_linestatus, sum(cast(l_quantity as NUMBER)) as sum_qty, cast(sum(l_extendedprice) as NUMBER) as sum_base_price, cast(sum((l_extendedprice) * (1 – l_discount)) as NUMBER) as sum_disc_price, cast(sum(l_extendedprice * (1 – l_discount) * (1 + l_tax)) as NUMBER) as sum_charge, avg(cast(l_quantity as NUMBER)) as avg_qty, avg(cast(l_extendedprice as NUMBER)) as avg_price, avg(cast(l_discount as NUMBER)) as avg_disc, cast(count(*) as NUMBER) as count_order from lineitem where l_shipdate <= date ‘1998-12-01’ – interval ‘:1’ day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus”

sql(2): “select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = :1 and p_type like ‘%:2’ and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = ‘:3’ and ps_supplycost = ( select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = ‘:3’) order by s_acctbal desc, n_name, s_name, p_partkey”

sql(3): “select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = ‘:1’ and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date ‘:2’ and l_shipdate > date ‘:2’ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate”

sql(4): “select o_orderpriority, cast(count(*) as NUMBER) as order_count from orders where o_orderdate >= date ‘:1’ and o_orderdate < date ‘:1’ + interval ‘3’ month and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group by o_orderpriority order by o_orderpriority”

sql(5): “select n_name, sum(l_extendedprice * (1 – l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = ‘:1’ and o_orderdate >= date ‘:2’ and o_orderdate < date ‘:2’ + interval ‘1’ year group by n_name order by revenue desc”

if { !$timesten } {

sql(6): “select cast(sum(l_extendedprice * l_discount) as NUMBER) as revenue from lineitem where l_shipdate >= date ‘:1’ and l_shipdate < date ‘:1’ + interval ‘1’ year and l_discount between :2 – 0.01 and :2 + 0.01 and l_quantity < :3”

sql(7): “select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 – l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = ‘:1’ and n2.n_name = ‘:2’) or (n1.n_name = ‘:2’ and n2.n_name = ‘:1’)) and l_shipdate between date ‘1995-01-01’ and date ‘1996-12-31’) shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year”

sql(8): “select o_year, sum(case when nation = ‘:1’ then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 – l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = ‘:2’ and s_nationkey = n2.n_nationkey and o_orderdate between date ‘1995-01-01’ and date ‘1996-12-31’ and p_type = ‘:3’) all_nations group by o_year order by o_year”

sql(9): “select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 – l_discount) – ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like ‘%:1%’) profit group by nation, o_year order by nation, o_year desc”

sql(10): “select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date ‘:1’ and o_orderdate < date ‘:1’ + interval ‘3’ month and l_returnflag = ‘R’ and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc”

sql(11): “select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = ‘:1’ group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * :2 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = ‘:1’) order by value desc”

sql(12): “select l_shipmode, sum(case when o_orderpriority = ‘1-URGENT’ or o_orderpriority = ‘2-HIGH’ then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> ‘1-URGENT’ and o_orderpriority <> ‘2-HIGH’ then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in (‘:1’, ‘:2’) and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date ‘:3’ and l_receiptdate < date ‘:3’ + interval ‘1’ year group by l_shipmode order by l_shipmode”

sql(13): “select c_count, cast(count(*) as NUMBER) as custdist from ( select c_custkey, count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey and o_comment not like ‘%:1%:2%’ group by c_custkey) c_orders group by c_count order by custdist desc, c_count desc”

sql(14): “select 100.00 * sum(case when p_type like ‘PROMO%’ then l_extendedprice * (1 – l_discount) else 0 end) / sum(l_extendedprice * (1 – l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= date ‘:1’ and l_shipdate < date ‘:1’ + interval ‘1’ month”

sql(15): “create view revenue$myposition (supplier_no, total_revenue) as select l_suppkey, sum(l_extendedprice * (1 – l_discount)) from lineitem where l_shipdate >= to_date( ‘:1’, ‘YYYY-MM-DD’) and l_shipdate < add_months( to_date (‘:1’, ‘YYYY-MM-DD’), 3) group by l_suppkey; select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue$myposition where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue$myposition) order by s_suppkey; drop view revenue$myposition”

sql(16): “select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand <> ‘:1’ and p_type not like ‘:2%’ and p_size in (:3, :4, :5, :6, :7, :8, :9, :10) and ps_suppkey not in ( select s_suppkey from supplier where s_comment like ‘%Customer%Complaints%’) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size”

sql(17): “select cast(sum(l_extendedprice) as NUMBER) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = ‘:1’ and p_container = ‘:2’ and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)”

sql(18): “select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > :1) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate”

sql(19): “select sum(l_extendedprice* (1 – l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = ‘:1’ and p_container in (‘SM CASE’, ‘SM BOX’, ‘SM PACK’, ‘SM PKG’) and l_quantity >= :4 and l_quantity <= :4 + 10 and p_size between 1 and 5 and l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN PERSON’) or ( p_partkey = l_partkey and p_brand = ‘:2’ and p_container in (‘MED BAG’, ‘MED BOX’, ‘MED PKG’, ‘MED PACK’) and l_quantity >= :5 and l_quantity <= :5 + 10 and p_size between 1 and 10 and l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN PERSON’) or ( p_partkey = l_partkey and p_brand = ‘:3’ and p_container in (‘LG CASE’, ‘LG BOX’, ‘LG PACK’, ‘LG PKG’) and l_quantity >= :6 and l_quantity <= :6 + 10 and p_size between 1 and 15 and l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN PERSON’)”

sql(20): “select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like ‘:1%’) and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date ‘:2’ and l_shipdate < date ‘:2’ + interval ‘1’ year)) and s_nationkey = n_nationkey and n_name = ‘:3’ order by s_name”

sql(21): “select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = ‘F’ and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = ‘:1’ group by s_name order by numwait desc, s_name”

sql(22): “select cntrycode, cast(count(*) as NUMBER) as numcust, cast(sum(c_acctbal) as NUMBER) as totacctbal from ( select substr(c_phone, 1, 2) as cntrycode, c_acctbal from customer where substr(c_phone, 1, 2) in (‘:1’, ‘:2’, ‘:3’, ‘:4’, ‘:5’, ‘:6’, ‘:7’) and c_acctbal > ( select cast(avg(c_acctbal) as NUMBER) from customer where c_acctbal > 0.00 and substr(c_phone, 1, 2) in (‘:1’, ‘:2’, ‘:3’, ‘:4’, ‘:5’, ‘:6’, ‘:7’)) and not exists ( select * from orders where o_custkey = c_custkey)) custsale group by cntrycode order by cntrycode”

 

Shailendra has over 18 years of experience in file and storage systems development. He leads storage system file system development for WDC.