Pocket Data The Case for TPC-MOBILE Oliver Kennedy, Jerry Ajay, - PowerPoint PPT Presentation

Pocket Data The Case for TPC-MOBILE Oliver Kennedy, Jerry Ajay, Geoff Challen, Lukasz Ziarek http://odin.cse.buffalo.edu/research/astral 1 The ODIn Lab @

Big Data! 2 The ODIn Lab @

Big Data! • GB, TB or PB of data! • Hundreds of thousands of updates per second • Thousands of nodes computing together! • “Virtually” infinite resources! 3 The ODIn Lab @

Big Data! 4 The ODIn Lab @

Big Data! TPC-H SSB TPC-C TPC-E TPC-DI YCSB TPC-DS 4 The ODIn Lab @

Big Data! TPC-H SSB TPC-C TPC-E TPC-DI YCSB TPC-DS What about other types of databases? 4 The ODIn Lab @

The average smartphone processes almost 180 thousand queries per day That’s about 2 queries per second 5 The ODIn Lab @

2 Queries per Second 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! • Is this Important? 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! • Is this Important? • Multi-Tenancy : The phone is more than just a DB. 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! • Is this Important? • Multi-Tenancy : The phone is more than just a DB. • Power : 1-2 days of battery life under ideal circumstances. 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! • Is this Important? • Multi-Tenancy : The phone is more than just a DB. • Power : 1-2 days of battery life under ideal circumstances. • It’s Everywhere : Odds are that your phone is running some queries right now! 6 The ODIn Lab @

2 Queries per Second • Is this Big Data? No! YES! • Is this Important? • Multi-Tenancy : The phone is more than just a DB. • Power : 1-2 days of battery life under ideal circumstances. • It’s Everywhere : Odds are that your phone is running some queries right now! 6 The ODIn Lab @

We need to better understand pocket-scale data 7 The ODIn Lab @

SQLite • Embedded : SQLite is a library Client Application • Un-shared : SQLite DBs are specific to one client “app”. • Lightweight : Entire SQLite DB is backed to one file. SQLite Library • Universal : SQLite client Android OS library is available by default in nearly all major OSes. • “Easy” : Duck Typing, Relaxed SQL Syntax, One Big Lock (file) 8 The ODIn Lab @

How do developers and users use Pocket Scale Data? 9 The ODIn Lab @

PhoneLab A Smartphone Platform Testbed ~200 UB students, faculty, and staff using instrumented LG Nexus 5 smartphones in exchange for discounted service. 10 The ODIn Lab @

PhoneLab A Smartphone Platform Testbed • Preliminary Trial : 11 phones for ~1 month (254 phone/days) • Instrumented SQLite logs all statements (~45 mil statements) • ~33.5 million SELECT statements • ~9.4 million INSERT statements • ~1 million UPDATE statements • ~1.2 million DELETE statements • 179 distinct ‘apps’ issuing statements https://phone-lab.org/experiment/request 11 The ODIn Lab @

• SELECT Complexity • ORM Effects • Function Usage • Read/Write Ratios • Query Periodicity 12 The ODIn Lab @

SELECT Complexity 1x10 8 1x10 8 Number of SELECT Queries 1x10 7 1x10 7 1x10 6 1x10 6 100000 100000 10000 10000 1000 1000 100 100 10 10 1 0.1 1 1 2 3 4 5 6 7 8 1 2 3 4 Number of Tables Accessed Maximum Nesting Depth 13 The ODIn Lab @

SELECT Complexity 30 million simple “SPA” queries 1x10 8 1x10 8 Number of SELECT Queries 1x10 7 1x10 7 1x10 6 1x10 6 100000 100000 10000 10000 1000 1000 100 100 10 10 1 0.1 1 1 2 3 4 5 6 7 8 1 2 3 4 Number of Tables Accessed Maximum Nesting Depth 13 The ODIn Lab @

SELECT Complexity 30 million simple “SPA” queries 1x10 8 1x10 8 Number of SELECT Queries 1x10 7 1x10 7 1x10 6 1x10 6 100000 100000 10000 10000 1000 1000 100 100 10 10 1 0.1 1 1 2 3 4 5 6 7 8 1 2 3 4 Number of Tables Accessed Maximum Nesting Depth Infrequent, but extremely complex queries 13 The ODIn Lab @

SELECT Complexity (by app) 1.0 0.9 0.8 0.7 0.6 CDF 0.5 0.4 0.3 0.2 0.1 0.0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% % of SELECT Queries That Are Key-Value Queries 14 The ODIn Lab @

SELECT Complexity (by app) 1.0 0.9 24 / 179 apps using SQLite 0.8 only as a K/V Store 0.7 0.6 CDF 0.5 0.4 0.3 0.2 0.1 0.0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% % of SELECT Queries That Are Key-Value Queries 14 The ODIn Lab @

SELECT Complexity INSERT OR REPLACE INTO properties(property_key,property_value) VALUES (?,?); SELECT property_value FROM properties WHERE property_key=?; (These are actual real queries from the trace) 15 The ODIn Lab @

SELECT Complexity 1 . 0 0 . 9 0 . 8 0 . 7 0 . 6 CDF 0 . 5 0 . 4 0 . 3 0 . 2 0 . 1 0 . 0 10 0 10 1 10 2 10 3 10 4 Returned Row Count 16 The ODIn Lab @

SELECT Complexity 1 . 0 0 . 9 0 . 8 0 . 7 0 . 6 CDF 0 . 5 0 . 4 0 . 3 0 . 2 0 . 1 0 . 0 10 0 10 1 10 2 10 3 10 4 80% of SELECTs Returned Row Count return one row 16 The ODIn Lab @

SELECT Complexity 1 . 0 0 . 9 0 . 8 0 . 7 0 . 6 CDF 0 . 5 0 . 4 0 . 3 0 . 2 0 . 1 0 . 0 10 0 10 1 10 2 10 3 10 4 80% of SELECTs Small % of SELECTs Returned Row Count return one row return 100s of rows 16 The ODIn Lab @

SELECT Complexity (by app) 1 . 0 0 . 9 0 . 8 0 . 7 0 . 6 CDF 0 . 5 0 . 4 0 . 3 0 . 2 0 . 1 0 . 0 10 0 10 1 10 2 10 3 10 4 Returned Row Count 17 The ODIn Lab @

SELECT Complexity (by app) 1 . 0 0 . 9 0 . 8 0 . 7 0 . 6 CDF 0 . 5 Google+, 0 . 4 GMail, 0 . 3 Facebook, 0 . 2 0 . 1 Contacts 0 . 0 10 0 10 1 10 2 10 3 10 4 Returned Row Count 17 The ODIn Lab @

Object-Relational Mapper 19 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) name = pers.firstName() Object-Relational Mapper 19 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) name = pers.firstName() Object-Relational Mapper SELECT first_name FROM Persons WHERE id = 10; 19 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) name = pers.firstName() Object-Relational Mapper SELECT first_name FROM Persons WHERE id = 10; SQL DB used for persisting objects 19 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) org = pers.employer() name = org.name() Object-Relational Mapper 20 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) org = pers.employer() name = org.name() Object-Relational SELECT employer_id Mapper FROM Persons WHERE id = 10; SELECT name FROM Organizations WHERE id = ?; 20 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) org = pers.employer() name = org.name() Object-Relational SELECT employer_id Mapper FROM Persons WHERE id = 10; SELECT name FROM Organizations WHERE id = ?; ORMs are not always efficient 20 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) pers.setSalary( pers.salary() * 1.1 ) Object-Relational Mapper 21 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) pers.setSalary( pers.salary() * 1.1 ) Object-Relational SELECT salary Mapper FROM Persons WHERE id = 10; UPDATE Persons SET salary = ? WHERE id = 10; 21 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) pers.setSalary( pers.salary() * 1.1 ) Object-Relational SELECT salary Mapper FROM Persons WHERE id = 10; UPDATE Persons SET salary = ? WHERE id = 10; We saw NO update value computations in SQL 21 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) pers.setSalary( pers.salary() * 1.1 ) Object-Relational SELECT salary Mapper FROM Persons WHERE id = 10; INSERT OR REPLACE INTO Persons(id, salary) VALUES (?, 10); 22 The ODIn Lab @ Image courtesy of http://openclipart.org

pers = Persons.get(10) pers.setSalary( pers.salary() * 1.1 ) Object-Relational SELECT salary Mapper FROM Persons WHERE id = 10; INSERT OR REPLACE INTO Persons(id, salary) VALUES (?, 10); Insert or Replace used very frequently 22 The ODIn Lab @ Image courtesy of http://openclipart.org

Aggregates Function Call Sites GROUP_CONCAT 583,474 SUM 321,387 MAX 314,970 COUNT 173,031 MIN 19,566 AVG 15 24 The ODIn Lab @

Aggregates Function Call Sites GROUP_CONCAT 583,474 SUM 321,387 MAX 314,970 COUNT 173,031 MIN 19,566 AVG 15 Aggregates most common function type 24 The ODIn Lab @

Aggregates Function Call Sites GROUP_CONCAT 583,474 SUM 321,387 MAX 314,970 COUNT 173,031 MIN 19,566 AVG 15 Concatenate all strings in a column: Non-algebraic 25 The ODIn Lab @

Other Functions 26 The ODIn Lab @

Pocket Data The Case for TPC-MOBILE Oliver Kennedy, Jerry Ajay, - PowerPoint PPT Presentation

Pocket Data The Case for TPC-MOBILE Oliver Kennedy, Jerry Ajay, Geoff Challen, Lukasz Ziarek http://odin.cse.buffalo.edu/research/astral 1 The ODIn Lab @ Big Data! 2 The ODIn Lab @ Big Data! 2 The ODIn Lab @ Big Data! GB, TB or PB of

l O O O pra de pra de P P oche P l l pra de oche oche The Pocket Opera The

PicoPix PPX2055/F7 KP-101-01 P4-X 55-Lumen Pocket Projector 55-Lumen Pocket Projector LED Pico

Back to the future: sockets and relational data in your (Windows) pocket Dragos Manolescu

MHCLG Pocket Park Plus - Mayes Road Linear Pocket Park design interventions Supplier Description

Pocket Lecture Pocket Lecture Pocket Lecture Pocket Lecture Listen Audio Notes Progress

New in 2013 v Out of Pocket Maximums v Pharmacy Co-Pays v Drug Management Programs v

Gstreamer Editing Services Video Editing in your pocket (size of pocket not specified) Edward

Estimates of Medicare Beneficiaries Out-of-Pocket Drug Spending in 2006 M odeling the Impact of

Commission: Out of touch, out of date, out of pocket April 2017 Commission: Out of touch, out of

Pocket door slides Guas de puertas correderas giratorias Guide per ante rientranti For

A/V PRESENTATION 282 Multimedia Projectors www.BandH.com MP180 Pocket Projectors MPro 150 Pocket

From something that fits in your pocket ... ... to, well, this. The future? ... Energy A look

WHAT THE HELL DID YOU JUST DO IN MY POCKET? April 2016 Hello IAB. Im Rik. MATTERING TO

The Risk you carry in your Pocket Nils Black Hat Abu Dhabi 2010 MWR InfoSecurity Who Am I?

Plasma Sneaks Into Your Pocket... Plasma for Phones... Artur Duque de Souza and Alexis Menard

Launch of the new Pocket Guide to NDCs Requirements of the Paris rulebook on the

0 min 0-1 An analysis of Internet chat systems by Arne Wichmann (TU M unchen), Christian

2017 Hymn to Christ the Lord Colossians 1:15-20 Dr. John Mahony What think ye of Christ?

Ultra-High Energy Cosmic Rays (Very short) reminder on Cosmic Ray experimental situation and

UI command UI command Makoto Asai (SLAC Computing Services) Makoto Asai (SLAC Computing

DISPLACED PHYSICS AT THE LHC Eric Kuflik Cornell University with Csaba Csaki (Cornell) Salvator

RCB: A Simple and Practical Framework for Real-time Collaborative Browsing Chuan Yue, Zi Chu, and

Crafting Your Census Campaign Plan 1 Hi, Im Christina! Senior Project Manager 270

Learning to Extract Entities from Labeled and Unlabeled Text Rosie Jones Language Technologies