TPC-H in MongoDB

Aung Thu Rha Hein(g5536871)

Agenda• Introduction to MongoDB• TPC-H Data Setup• Schema• Advantages and Disadvantages of New Schema• Queries

o Pricing Summary Recordo National Market Share Queryo Total Supplier Queryo Potential Part Promotion Queryo Suppliers who kept orders waiting queryo Global Sales Opportunity Query

• Benchmark result• Discussion • Demonstration

Introduction to MongoDB

• Open source, document-oriented and schema-free• Store data in BSON format• Easy to understand• Flexible, Scalable & lightweight• Ease of use• No ‘join’ operation

• SQL to MongoDB Sample Query• Select * from users where status = “A” ORDER BY USER_ID DESC

• db.users.find( { status: "A" } ).sort( { user_id: -1 } )

TPC-H Data Setup• Import data into MongoDB

o Use MongoVue to import from MySQLo Time consuming and difficult

• To achieve flexibility:o Embedded all tables into single collectiono Replace all foreign keys with objects from lineitem tableo Choose lineitem table because of

• No primary keys

Schema• Final Schema of TPC-H in MongoDB

lineitemOrder CustomerNation Region PartsuppPart supplier N R

Advantages and Disadvantages of New

Schema• Advantages

o Easier to understand than SQL schemao One document: one recordo No need to join tables

• Disadvantageso Higher memory usageo Update operation becomes more demandingo Converting to BSON takes time o Require lot of computational powero Only around 300,000(5%) count of lineitem able to

convert

Queries• Select 6 queries to run on MongoDB with Map-

Reduce & Aggregation Framework• Compare the result with MySQL

PROBLEMS• Outputs are not the same because of failure

during converting data• Aggregation framework is still in development

Q1: Pricing SummaryRecord Query

Q8:National Market Share Query

Q15:Top Supplier Query

Q20:Potential part Promotion Query

Q21:Supplier who kept order waiting

Q22:Global Sales Opportunity

Benchmark result • All benchmarks run on Intel Core i7-3610QM 2.30GHz 6MB

cache,4GB DDR3,750GB 7200 RPM,Win64 system

• Query1

• Query 8

• Query15

6.1 sec

0.2 sec

MongoDB

MySQL 0.1 sec

1.6 sec

0.4 sec

0.7 sec

MySQL

MongoDB

MongoDB

MySQL

Benchmark result(cont.)

• Query 20

• Query 21

• Query 22

MongoDB

MySQL

1.1 sec

174.4 sec

MongoDB

MySQL

6.2 sec

5.5 sec

MongoDB

MySQL

7.6 sec

0.8 sec

Discussion & Conclusion

• MongoDB left behind in all querieso Design problemo Aggregation framework problemo No standard Query Languageo Server side query processing is not the nature of NoSQLo Complex SQL cannot convert easily

• Only suitable for Applications:o Business card databaseo Web Blogo Applications without complex transactions

Demonstration