Phoenix SNAPSHOT Performance

Phoenix Performance Result :: Thu Jul 11 16:43:27 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.17s CURRENT-SNAPSHOT

7.15s LAST-SNAPSHOT

7.01s PHOENIX-1.1

6.9s PHOENIX-1.2.0

select count(1) from AGGREGATION_TEST_TABLE group by HOST

7.48s CURRENT-SNAPSHOT

7.46s LAST-SNAPSHOT

7.13s PHOENIX-1.1

7.14s PHOENIX-1.2.0

select MIN(CORE) from AGGREGATION_TEST_TABLE group by DOMAIN

8.12s CURRENT-SNAPSHOT

8.02s LAST-SNAPSHOT

7.95s PHOENIX-1.1

7.86s PHOENIX-1.2.0

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

8.14s CURRENT-SNAPSHOT

7.84s LAST-SNAPSHOT

7.83s PHOENIX-1.1

7.8s PHOENIX-1.2.0

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

12.92s CURRENT-SNAPSHOT

12.56s LAST-SNAPSHOT

12.63s PHOENIX-1.1

12.63s PHOENIX-1.2.0

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

17.58s CURRENT-SNAPSHOT

17.12s LAST-SNAPSHOT

16.79s PHOENIX-1.1

16.01s 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.96s CURRENT-SNAPSHOT

5.65s LAST-SNAPSHOT

5.63s PHOENIX-1.1

5.5s 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.52s CURRENT-SNAPSHOT

7.62s LAST-SNAPSHOT

11.2s PHOENIX-1.1

7.64s PHOENIX-1.2.0

select count(active_visitor) count from AGGREGATION_TEST_TABLE

11.86s CURRENT-SNAPSHOT

11.4s LAST-SNAPSHOT

11.69s PHOENIX-1.1

11.14s PHOENIX-1.2.0

select count(distinct active_visitor) count from AGGREGATION_TEST_TABLE

12.99s CURRENT-SNAPSHOT

12.6s 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

12.89s CURRENT-SNAPSHOT

12.64s 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

12.88s CURRENT-SNAPSHOT

12.62s 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.18s CURRENT-SNAPSHOT

15.63s LAST-SNAPSHOT

15.61s PHOENIX-1.1

15.46s PHOENIX-1.2.0

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

18.23s CURRENT-SNAPSHOT

17.79s 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

18.23s CURRENT-SNAPSHOT

17.76s 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

11.63s CURRENT-SNAPSHOT

11.72s LAST-SNAPSHOT

11.73s PHOENIX-1.1

11.36s PHOENIX-1.2.0

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

14.78s CURRENT-SNAPSHOT

14.64s 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

14.91s CURRENT-SNAPSHOT

14.94s LAST-SNAPSHOT

N/A for PHOENIX-1.1

N/A for PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

317.13s CURRENT-SNAPSHOT

312.93s LAST-SNAPSHOT

291.9s PHOENIX-1.1

355.46s 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.55s CURRENT-SNAPSHOT

3.48s LAST-SNAPSHOT

3.85s PHOENIX-1.1

3.5s 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.51s 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.01s CURRENT-SNAPSHOT

0.01s LAST-SNAPSHOT

25.13s 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.38s CURRENT-SNAPSHOT

5.11s LAST-SNAPSHOT

4.91s PHOENIX-1.1

5.39s 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%')

5.04s CURRENT-SNAPSHOT

4.99s LAST-SNAPSHOT

4.89s PHOENIX-1.1

5.48s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

260.28s CURRENT-SNAPSHOT

258.51s LAST-SNAPSHOT

234.52s PHOENIX-1.1

314.59s 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.19s CURRENT-SNAPSHOT

8.42s LAST-SNAPSHOT

9.99s PHOENIX-1.1

8.71s PHOENIX-1.2.0

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

8.24s CURRENT-SNAPSHOT

7.76s LAST-SNAPSHOT

9.31s PHOENIX-1.1

8.11s PHOENIX-1.2.0

select count(*) from CF_TABLE_14CF where N < 1000

3.96s CURRENT-SNAPSHOT

4.23s LAST-SNAPSHOT

13.9s PHOENIX-1.1

4.39s 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.81s CURRENT-SNAPSHOT

5.68s LAST-SNAPSHOT

360.25s PHOENIX-1.1

6.1s PHOENIX-1.2.0

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

25.8s CURRENT-SNAPSHOT

25.31s LAST-SNAPSHOT

27.42s PHOENIX-1.1

26.74s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

512.13s CURRENT-SNAPSHOT

517.69s LAST-SNAPSHOT

466.9s PHOENIX-1.1

626.5s 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')

5.69s CURRENT-SNAPSHOT

5.65s LAST-SNAPSHOT

17.49s PHOENIX-1.1

6.02s PHOENIX-1.2.0

select count(*) from SALTED_TABLE where D < 1000

2.07s CURRENT-SNAPSHOT

2.19s LAST-SNAPSHOT

22.26s PHOENIX-1.1

2.3s PHOENIX-1.2.0

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

12.3s CURRENT-SNAPSHOT

11.77s LAST-SNAPSHOT

30.86s PHOENIX-1.1

12.07s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

337.46s CURRENT-SNAPSHOT

330.26s LAST-SNAPSHOT

440.79s PHOENIX-1.1

393.76s 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')

8.56s CURRENT-SNAPSHOT

8.34s LAST-SNAPSHOT

8.68s PHOENIX-1.1

8.65s PHOENIX-1.2.0

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

8.49s CURRENT-SNAPSHOT

7.89s LAST-SNAPSHOT

8.62s PHOENIX-1.1

8.39s PHOENIX-1.2.0

select count(*) from CF_TABLE_1CF where N < 1000

11.38s CURRENT-SNAPSHOT

11.18s LAST-SNAPSHOT

11.35s PHOENIX-1.1

12s 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.44s CURRENT-SNAPSHOT

13.06s LAST-SNAPSHOT

62.43s PHOENIX-1.1

14.18s PHOENIX-1.2.0

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

24.5s CURRENT-SNAPSHOT

24.06s LAST-SNAPSHOT

24.46s PHOENIX-1.1

25.84s PHOENIX-1.2.0

LOAD DATA [10000000 ROWS]

442.69s CURRENT-SNAPSHOT

437.84s LAST-SNAPSHOT

390.75s PHOENIX-1.1

546.05s PHOENIX-1.2.0