Phoenix SNAPSHOT Performance

Phoenix Performance Result :: Wed Jul 03 20:46:41 PDT 2013

AGGREGATION_TEST_TABLE
Various server side grouped/un-grouped aggregation functions. Data is pre-split in 9 regions over a 4 region server cluster.

ROWS 10000000 [10M]

DDL CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN VARCHAR NOT NULL,FEATURE VARCHAR NOT NULL,DATE DATE NOT NULL,USAGE.CORE BIGINT,USAGE.DB BIGINT,STATS.ACTIVE_VISITOR INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)) SPLIT ON ('CSGoogle','CSSalesforce','EUApple','EUGoogle','EUSalesforce','NAApple','NAGoogle','NASalesforce')

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Values: Salesforce.com|Apple.com|Google.com|Yelp.com
STRING :: Values: Login|Report|Dashboard|Sales|UI
DATE :: Values: SEQUENTIAL
INTEGER :: Max. Value: 100 Values: RANDOM
INTEGER :: Max. Value: 2000 Values: RANDOM
INTEGER :: Max. Value: 10000 Values: RANDOM

QUERIES

select count(*) from AGGREGATION_TEST_TABLE

7.44s CURRENT-SNAPSHOT

7.24s LAST-SNAPSHOT

7.47s PHOENIX-1.1

7.2s PHOENIX-1.2.0

select count(1) from AGGREGATION_TEST_TABLE group by HOST

7.6s CURRENT-SNAPSHOT

7.45s LAST-SNAPSHOT

7.68s PHOENIX-1.1

7.3s PHOENIX-1.2.0

select MIN(CORE) from AGGREGATION_TEST_TABLE group by DOMAIN

8.54s CURRENT-SNAPSHOT

8.27s LAST-SNAPSHOT

8.56s PHOENIX-1.1

8.05s PHOENIX-1.2.0

select core, count(1) from AGGREGATION_TEST_TABLE group by CORE

8.41s CURRENT-SNAPSHOT

8.07s LAST-SNAPSHOT

8.34s PHOENIX-1.1

7.77s PHOENIX-1.2.0

select core, host, sum(ACTIVE_VISITOR) from AGGREGATION_TEST_TABLE group by core, host

13.54s CURRENT-SNAPSHOT

12.82s LAST-SNAPSHOT

13.54s PHOENIX-1.1

12.65s PHOENIX-1.2.0

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from AGGREGATION_TEST_TABLE where host='CS' and DOMAIN<>'Google.com'

18.99s CURRENT-SNAPSHOT

17.45s LAST-SNAPSHOT

17.56s PHOENIX-1.1

17.33s PHOENIX-1.2.0

select round(date, 'day',50), sum(core)+2 from AGGREGATION_TEST_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host

5.69s CURRENT-SNAPSHOT

5.76s LAST-SNAPSHOT

5.74s PHOENIX-1.1

5.39s PHOENIX-1.2.0

select trunc(date, 'hour', 1000), count(DB), count(ACTIVE_VISITOR) from AGGREGATION_TEST_TABLE where feature='Login' and (core<50 or db<20 or active_visitor > 200) group by trunc(date, 'hour', 1000)

7.59s CURRENT-SNAPSHOT

7.84s LAST-SNAPSHOT

11.3s PHOENIX-1.1

7.51s PHOENIX-1.2.0

select count(active_visitor) count from AGGREGATION_TEST_TABLE

11.86s CURRENT-SNAPSHOT

11.75s LAST-SNAPSHOT

11.89s PHOENIX-1.1

11.14s PHOENIX-1.2.0

select count(distinct active_visitor) count from AGGREGATION_TEST_TABLE

13.33s CURRENT-SNAPSHOT

13.33s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select percentile_cont(0.9) within group (order by active_visitor asc) from AGGREGATION_TEST_TABLE

13.33s CURRENT-SNAPSHOT

13.44s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select percentile_cont(0.1) within group (order by active_visitor desc) from AGGREGATION_TEST_TABLE

13.28s CURRENT-SNAPSHOT

13.32s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select count(core), count(db), count(active_visitor) from AGGREGATION_TEST_TABLE

16.41s CURRENT-SNAPSHOT

16.12s LAST-SNAPSHOT

16.16s PHOENIX-1.1

15.37s PHOENIX-1.2.0

select count(distinct core), count(distinct db), count(distinct active_visitor) from AGGREGATION_TEST_TABLE

20.05s CURRENT-SNAPSHOT

19.69s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select percentile_cont(0.5) within group (order by core asc), percentile_cont(0.5) within group (order by db asc), percentile_cont(0.5) within group (order by active_visitor asc) from AGGREGATION_TEST_TABLE

19.98s CURRENT-SNAPSHOT

19.73s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select core, count(db) count from AGGREGATION_TEST_TABLE group by CORE

12.3s CURRENT-SNAPSHOT

11.76s LAST-SNAPSHOT

12.16s PHOENIX-1.1

11.56s PHOENIX-1.2.0

select core, count(distinct db) count from AGGREGATION_TEST_TABLE group by CORE

15.54s CURRENT-SNAPSHOT

15.25s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

select core, percentile_cont(0.9999) within group (order by db asc) from AGGREGATION_TEST_TABLE group by CORE

15.58s CURRENT-SNAPSHOT

15.27s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

365.14s CURRENT-SNAPSHOT

366.64s LAST-SNAPSHOT

311.1s PHOENIX-1.1

362.25s PHOENIX-1.2.0

IN_LIKE_TABLE
Phoenix 1.2+ uses skip scan that gives significant performance improvement for IN/LIKE queries.

ROWS 10000000 [10M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF.A INTEGER, CF.B VARCHAR, CF.C VARCHAR CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D','E','F','G','H','I')

DATA GENERATOR
STRING :: Values: A|B|C|D|E|F|G|H|I
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
STRING :: Length: 50 Values: RANDOM
STRING :: Length: 50 Values: RANDOM

QUERIES

select count(1) from IN_LIKE_TABLE where K1 in ('Z', 'D', 'F', 'X')

3.68s CURRENT-SNAPSHOT

3.5s LAST-SNAPSHOT

4.15s PHOENIX-1.1

3.45s PHOENIX-1.2.0

select * from IN_LIKE_TABLE where K1 in ('A','B') and K2 in ('xxx1000000','xxx2000000','xxx2000001')

0.01s CURRENT-SNAPSHOT

0.01s LAST-SNAPSHOT

5.3s PHOENIX-1.1

0.01s PHOENIX-1.2.0

select * from IN_LIKE_TABLE where K1 in ('A', 'C', 'E', 'F', 'H') and K2 like 'xxxxxx1%'

0.02s CURRENT-SNAPSHOT

0.02s LAST-SNAPSHOT

23.29s PHOENIX-1.1

0.02s PHOENIX-1.2.0

select count(*) from IN_LIKE_TABLE where K1 in ('X','A','B','C','D','E','F','G','H','I','Z') and K2 like '%1%'

5.1s CURRENT-SNAPSHOT

5.25s LAST-SNAPSHOT

5.02s PHOENIX-1.1

5.36s PHOENIX-1.2.0

select count(*) from IN_LIKE_TABLE where (K1 in ('A','E','F','G','H','I','Z') or K1 like 'E%') and (K2 like '%xx1%' or K2 like '%xxxxxx2%')

4.96s CURRENT-SNAPSHOT

5.11s LAST-SNAPSHOT

4.92s PHOENIX-1.1

5.28s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

313.7s CURRENT-SNAPSHOT

320.76s LAST-SNAPSHOT

240.34s PHOENIX-1.1

320.46s PHOENIX-1.2.0

CF_TABLE_14CF
Data is split in 14 column families. Phoenix 1.2+ uses Essential Column Family filter feature which fetches only required columns when filtering rows.

ROWS 10000000 [10M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF1.A INTEGER, CF2.B INTEGER, CF3.C INTEGER, CF4.D INTEGER, CF5.E INTEGER, CF6.F INTEGER, CF7.G INTEGER, CF8.H INTEGER, CF9.I INTEGER, CF10.J INTEGER, CF11.K INTEGER, CF12.L INTEGER, CF13.M INTEGER, CF14.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D')

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from CF_TABLE_14CF where K1 in ('A', 'B', 'C', 'D')

8.46s CURRENT-SNAPSHOT

8.32s LAST-SNAPSHOT

9.64s PHOENIX-1.1

8s PHOENIX-1.2.0

select count(1) from CF_TABLE_14CF where K1 in ('A', 'C')

8.52s CURRENT-SNAPSHOT

8.21s LAST-SNAPSHOT

8.56s PHOENIX-1.1

7.97s PHOENIX-1.2.0

select count(*) from CF_TABLE_14CF where N < 1000

4.29s CURRENT-SNAPSHOT

4.09s LAST-SNAPSHOT

14.14s PHOENIX-1.1

4.18s PHOENIX-1.2.0

select a,b,c,d,e,f,g,h,i,j,k,l,m,n from CF_TABLE_14CF where (B>1000 and B<2000) and (M>1000 and M<2000)

5.95s CURRENT-SNAPSHOT

6.1s LAST-SNAPSHOT

360.67s PHOENIX-1.1

5.8s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_14CF group by K1

26.35s CURRENT-SNAPSHOT

25.36s LAST-SNAPSHOT

28.18s PHOENIX-1.1

25.83s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

638.88s CURRENT-SNAPSHOT

637.26s LAST-SNAPSHOT

475.88s PHOENIX-1.1

639.07s PHOENIX-1.2.0

SALTED_TABLE
This is Phoenix 1.2+ feature. Data is split in 4 regions equal to number of region servers in test cluster by adding salted byte to each key.

ROWS 10000000 [10M]

DDL CREATE TABLE $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CFA.A VARCHAR, CFA.B CHAR(100), CFA.C INTEGER, CFB.D INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SALT_BUCKETS=4

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
STRING :: Length: 100 Values: RANDOM
STRING :: Length: 100 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from SALTED_TABLE where K1 in ('A', 'B', 'C', 'D')

7.28s CURRENT-SNAPSHOT

5.99s LAST-SNAPSHOT

16.04s PHOENIX-1.1

5.88s PHOENIX-1.2.0

select count(*) from SALTED_TABLE where D < 1000

2.28s CURRENT-SNAPSHOT

2.16s LAST-SNAPSHOT

20.02s PHOENIX-1.1

2.16s PHOENIX-1.2.0

select avg(c),avg(d) from SALTED_TABLE group by K1

12.72s CURRENT-SNAPSHOT

12.14s LAST-SNAPSHOT

28.57s PHOENIX-1.1

12.13s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

399.7s CURRENT-SNAPSHOT

399.67s LAST-SNAPSHOT

445.09s PHOENIX-1.1

400.59s PHOENIX-1.2.0

CF_TABLE_1CF
Same data as in 14 column family table but this one only has a single column family. Performance should be worse in most cases.

ROWS 10000000 [10M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF.A INTEGER, CF.B INTEGER, CF.C INTEGER, CF.D INTEGER, CF.E INTEGER, CF.F INTEGER, CF.G INTEGER, CF.H INTEGER, CF.I INTEGER, CF.J INTEGER, CF.K INTEGER, CF.L INTEGER, CF.M INTEGER, CF.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D')

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from CF_TABLE_1CF where K1 in ('A', 'B', 'C', 'D')

9.15s CURRENT-SNAPSHOT

8.6s LAST-SNAPSHOT

9.08s PHOENIX-1.1

8.42s PHOENIX-1.2.0

select count(1) from CF_TABLE_1CF where K1 in ('A', 'C')

8.92s CURRENT-SNAPSHOT

8s LAST-SNAPSHOT

9.07s PHOENIX-1.1

8.23s PHOENIX-1.2.0

select count(*) from CF_TABLE_1CF where N < 1000

11.89s CURRENT-SNAPSHOT

11.62s LAST-SNAPSHOT

11.41s PHOENIX-1.1

11.53s PHOENIX-1.2.0

select a,b,c,d,e,f,g,h,i,j,k,l,m,n from CF_TABLE_1CF where B>1000 and B<2000 and M>1000 and M<2000

13.85s CURRENT-SNAPSHOT

14.05s LAST-SNAPSHOT

60.97s PHOENIX-1.1

13.47s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_1CF group by K1

26.51s CURRENT-SNAPSHOT

24.89s LAST-SNAPSHOT

24.84s PHOENIX-1.1

24.01s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

561.2s CURRENT-SNAPSHOT

555.33s LAST-SNAPSHOT

390.39s PHOENIX-1.1

555.01s PHOENIX-1.2.0