Initial Postgres comparison

As a first comparison with Postgres, we present 2 results showing performance of a count and a point query to get a baseline for performance targets. We compare performance of a single node Postgres 9.6 database with local SSD storage defined as our tablespace, and we ensure that Postgres parallel query feature is on and active. The experimental setup mimic our cluster scalability experiment performed previously. For SkyhookDB, this experiment does not use Postgres, it uses a simple client driver program for querying our Ceph data until our FDW is developed.

Experimental Setup

We use the TPC-H lineitem table with 1 billion rows (~140GB). In Postgres, the data is stored in a tablespace defined on the SSD, and stats (ANALYZE) are computed after loading. In Ceph, the data is stored across 10,000 objects in which are distributed over all of the server machines. Each query is executed 3 times with a cold cache and the average execution time is reported. For our system, we use parallelism of 12 (IO dispatch) per server machine since previous experiments showed that most of the concurrency gain is realized with a factor of 12.

Qa: Range query with 10% selectivity.

SELECT count(*) FROM lineitem1B WHERE l_extendedprice > 71000.0

Qf: Regex query with 10% selectivity.

SELECT count(*) FROM lineitem1B WHERE l_comment iLIKE '%uriously%'

Qd: Point query (unique row) with and without index.

EXPLAIN SELECT * FROM :tname WHERE l_orderkey=5 AND l_linenumber=3;

Hardware

1 Client machine (Postgres or our simple client app)

1--16 Server machines (Ceph OSDs)

All machines are Cloudlab c220g1 (16 cores, 128GB ram, 1xHDD (OS), 1xSSD (data storage).

The Postgres explain plans are below, showing that parallel query determines 9 parallel workers for each query. In each case, our approach is comparable to the Postgres single node result for 1 server, and then performance improves significantly as we scale out the number of storage servers. This shows the benefits of our approach that scale out IO and CPU.

Qa:

EXPLAIN VERBOSE SELECT COUNT(*) FROM :tname WHERE l_extendedprice > 71000;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------

Finalize Aggregate (cost=21170468.30..21170468.31 rows=1 width=8)

Output: count(*)

-> Gather (cost=21170467.37..21170468.28 rows=9 width=8)

Output: (PARTIAL count(*))

Workers Planned: 9

-> Partial Aggregate (cost=21169467.37..21169467.38 rows=1 width=8)

Output: PARTIAL count(*)

-> Parallel Seq Scan on public.lineitem1bssd (cost=0.00..21141395.56 rows=11228725 width=0)

Filter: (lineitem1bssd.l_extendedprice > '71000'::numeric)

Qf:

EXPLAIN VERBOSE SELECT count(*) FROM :tname WHERE l_comment ilike '%uriously%';

QUERY PLAN

------------------------------------------------------------------------------------------------------

Finalize Aggregate (cost=21173364.16..21173364.17 rows=1 width=8)

-> Gather (cost=21173363.23..21173364.14 rows=9 width=8)

Workers Planned: 9

-> Partial Aggregate (cost=21172363.23..21172363.24 rows=1 width=8)

-> Parallel Seq Scan on lineitem1bssd (cost=0.00..21141628.53 rows=12293877 width=0)

Filter: ((l_comment)::text ~~* '%uriously%'::text)

Qd:

EXPLAIN SELECT * FROM :tname WHERE l_orderkey=5 AND l_linenumber=3;

QUERY PLAN

------------------------------------------------------------------------------------

Gather (cost=1000.00..21420167.15 rows=439 width=8)

Workers Planned: 9

-> Parallel Seq Scan on lineitem1bssd (cost=0.00..21419123.25 rows=49 width=8)

Filter: ((l_orderkey = 5) AND (l_linenumber = 3))

The Postgres+Index query time is very fast at 0.1 seconds, this is because for our approach we have to query the index for each of 10,000 objects but since this is done in parallel it gets faster as we scale out the number of servers although still not as fast as Postgres' index lookup.