Transcript LN5

CPT-S 580-06
Advanced Databases
Yinghui Wu
EME 49
1
2
newSQL
3
old OLTP and old SQL

An information system can be transactional (OLTP) or/and analytical (OLAP)

OLTP (On-line Transaction Processing): a large number of short on-line
transactions (INSERT, UPDATE, DELETE)
– very fast query processing
– data integrity in multi-access environments
– effectiveness measured by number of transactions per second.

In OLTP database there is detailed and current data, and schema used to store
transactional databases is the entity model (usually 3NF).

Old OLTP:
–
–
–
Collection of OLTP systems
Connected to ETL (Extract-Transform-and-Load)
Connected to one or more data warehouses
 Old SQL: techs, systems and vendors supporting old OLTP
4
New requirements (new OLTP)
 Web changes everything
 Large scale systems, with huge and growing data sets

9M messages per hour in Facebook
 50M messages per day in Twitter

Information is frequently generated by devices (PDAs, sensors…) -> “Online”

High concurrency requirements, high-throughput ACID write -> “Transaction”

High Availability + Durability: core database requirements
 Need for high throughput
 Need for real-time analytics
Challenge
 Ingest the firehose in real time
 Process, validate, enrich and respond in Real-time
 Real-time analytics
 Options:
– Old SQL - Legacy RDBMS vendors
– NoSQL: give up SQL and ACID
– NewSQL: SQL + ACID + new architecture
6
noSQL
 Give up SQL
 Give up ACID
–
–
–
–
Data accuracy
Funds transfer
Integrity constraints
Multi-record state
 noSQL fits
– Non-transactional systems
– Single record transactions that are commutative
 noSQL is not a good fit for
– New OLTP: gaming, purchasing, order management, realtime analytical, etc
7
NewSQL: definition
 SQL is good
 ACID is good
 Figure out a way to make oldSQL perform
 Make it scale like noSQL
 Make it available
 “A DBMS that delivers the scalability and flexibility promised by
NoSQL while retaining the support for SQL queries and/or ACID, or
to improve performance for appropriate workloads.”
-- 451 Group
NewSQL: definition
 SQL as the primary mechanism for application interaction
 ACID support for transactions
 A non-locking concurrency control mechanism so real-time
reads will not conflict with writes, and thereby cause them to
stall.
 An architecture providing much higher per-node performance
than available from the traditional "elephants"
 A scale-out, shared-nothing architecture, capable of running on
a large number of nodes without bottlenecking
– Michael Stonebraker
Traditional DBMS overheads
“Removing those overheads and running the database in main
memory would yield orders of magnitude improvements in
database performance”
NewSQL design principles
 SQL + ACID + performance and scalability through modern
innovative software architecture
 Principle 1: minimizing or stay away from locking
 Principle 2: rely on main memory
 Principle 3: try to avoid latching
 Principle 4: cheaper solutions for HA
11
NewSQL design principles
 new solution other than low-level record level
locking mechanism
– Transaction processed in timestamp order with no locking
(voltDB)
– multisession concurrency control (nuoDB)
– Optimistic concurrency control (Google)
– Principle: minimizing or stay away from locking
12
NewSQL design principles
 new solution for buffer pool overhead
– Main memory DBMS
– Moderate case is tilted towards main memory
– Principle 2: rely on main memory
 new solution to latching for shared data structures
– new way to manage B-trees
– Single-threading
– Principle 3: try to avoid latching
 new solution for write-ahead logging
– Built-in replication
– Principle 4: cheaper solutions for HA
13
newSQL databases
14
NewSQL: categories
 New approaches: VoltDB, Clustrix, NuoDB
 New Storage engines: TokuDB, ScaleDB
❖
❖
❖
 Transparent Clustering: ScaleBase, dbShards
voltDB
16
VoltDB
 VoltDB is an in-memory, horizontally scalable, ACID compliant,
fast RDBMS
 Backed and architected by Michael Stonebraker
 An open source project
 Java + C/++
 Available in community and commercial editions
17
Technical Overview
 VoltDB tries to avoid the overhead of traditional databases
 K-safety for fault tolerance
-no logging
 In memory operation for maximum throughput
- no buffer management
 Partitions operate autonomously and singlethreaded
- no latching or locking
 Built to horizontally scale
19
Technical Overview – Partitions (1/3)
 One partition per physical CPU core
• Each physical server has multiple VoltDB partitions
 Data - Two types of tables
• Partitioned
X
X
• Single column serves as partitioning key
• Rows are spread across all VoltDB partitions by partition column
• Transactional data (high frequency of modification)
• Replicated
• All rows exist within all VoltDB partitions
• Relatively static data (low frequency of modification)
X
 Code - Two types of work – both ACID
X
X
• Single-Partition
• All insert/update/delete operations within single partition
• Majority of transactional workload
• Multi-Partition
• CRUD against partitioned tables across multiple partitions
• Insert/update/delete on replicated tables
Technical Overview – Partitions (2/3)
 Single-partition vs. Multi-partition
select count(*) from orders where customer_id = 5
single-partition
select count(*) from orders where product_id = 3
multi-partition
insert into orders (customer_id, order_id, product_id) values (3,303,2)
single-partition
update products set product_name = ‘spork’ where product_id = 3
multi-partition
Partition 1
1
1
4
101
101
401
1
2
3
knife
spoon
fork
Partition 2
2
3
2
2
5
5
201
501
502
1
2
3
knife
spoon
fork
Partition 3
1
3
2
3
6
6
201
601
601
1
2
3
knife
spoon
fork
1
1
2
table orders :
(partitioned)
customer_id (partition key)
order_id
product_id
table products : product_id
(replicated)
product_name
Technical Overview – Partitions (3/3)
 Looking inside a VoltDB
partition…
– Each partition contains
data and an execution
engine.
– The execution engine
contains a queue for
transaction requests.
– Requests are executed
sequentially (single
threaded).
Work
Queue
execution engine
- Complete copy of all replicated tables
- Portion of rows (about 1/partitions) of
all partitioned tables
Table Data
Index Data
Technical Overview – Compiling
 The database is constructed
from
– The schema (DDL)
– The work load (Java stored
procedures)
– The Project (users, groups,
partitioning)
 VoltCompiler creates
application catalog
– Copy to servers along with
1 .jar and 1 .so
– Start servers
Stored Procedures
Schema
CREATE TABLE HELLOWORLD (
HELLO CHAR(15),
WORLD CHAR(15),
DIALECT CHAR(15),
PRIMARY KEY (DIALECT)
);
import org.voltdb. * ;
import org.voltdb. * ;
import org.voltdb. * ;
@ProcInfo(
@ProcInfo(
partitionInfo = "HELLOWORLD.DIA
partitionInfo
= "HE
@ProcInfo(
singlePartition
= true
partitionInfo = "HELLOWORLD.DIA
) singlePartition
= t
singlePartition = true
)
public
class Insert extends VoltPr
public final
SQLStmt
sql =
public
final
SQLStmt
public
class Insert extends
VoltPr
new SQLStmt("INSERT
INTO HELLO
public
VoltTable[]
run
public
final SQLStmt sql
=
new SQLStmt("INSERT
INTOhel
HELLO
public VoltTable[]
run( String
public VoltTable[] run( String hel
Project.xml
<?xml version="1.0"?>
<project>
<database name='data
<schema path='ddl.
<partition table=‘
</database>
</project>
Technical Overview – Clusters/Durability
 Scalability
• Increase RAM in servers to add capacity
• Add servers to increase performance / capacity
• Consistently measuring 90% of single-node performance
increase per additional node
 High availability
• K-safety for redundancy
 Duability
• Scheduled, continuous, on demand
Lack of concurrency
 Single-threaded execution within partitions (single-partition) or
across partitions (multi-partition)
 No need to worry about locking/dead-locks
– great for “inventory” type applications
• checking inventory levels
• creating line items for customers
 Because of this, transactions execute in microseconds.
 However, single-threaded comes at a price
– Other transactions wait for running transaction to complete
– Useful for OLTP, not OLAP
25
Schema Changes
 Traditional OLTP
• add table…
• alter table…
 VoltDB
• modify schema and stored procedures
• build catalog
• deploy catalog
 V1.0: Add/drop users, stored procedures
 V1.1: Add/drop tables
 Future: Add/drop column, …
nuoDB
27
nuoDB
 nuoDB is an elastically scalable, ACID compliant, 100% SQL
newSQL Database
 Backed and architected by Jim Starkley
 Runs on JVM
 Proprietary source project
28
NuoDB: Architecture
 Multi-tier Architecture
– Transaction tier
– Storage tier
– Management tier
❖
 Multi-Tenant
❖
❖
 Heavy use of memory
– hot data stays in memory
– Cold data in persistent store
 Object Oriented
❖
– Objects are atoms
 Asynchronous Messaging
 Partial, On-Demand replication
 MVCC - Concurrency
Technical Overview – Tiered Architecture
 Tiered Architecture
– Transactions: Transaction Engine
•
•
•
•
Parse, compile, optimize and execute SQL commands
Stores some information in memory locally
Map to locate the information
Any transaction engine can service any piece of information regardless
of where it resides
• Adding transaction engines -> More throughput
– Storage: Storage manager
• Can run on HDFS or Amazon S3
• Adding storage manager -> more resistance to failure
– Management: Agents and Brokers
• (Re)starting transaction engines and storage managers
• Collect statistics from them
• Clients connect to TE via Brokers
30
Multi-tier architecture
Management
brokers/agents
Transaction Engine
Transaction Engine
Transaction
Storage Manager
Storage Manager
Storage
Database Archives
31
32
Conclusions
 NewSQL is an established trend with a number of
options
❖
 Hard to pick one because they're not on a common scale
No silver bullet
❖
❖
❖
 Growing data volume requires ever more efficient ways to
store and process it
oldSQL, NoSQL and NewSQL: pros/cons

OldSQL
–
–
–
–
–
+: proven techs and standard SQL, ACID
+: rich client-side transaction
+: established market
-: not a scale-out architecture: single machine bottleneck
-: complex management, tuning for performance
 NoSQL
–
–
–
–

+: higher availability and scalability
+: support non-relational data, and OLAP
-: fundamentally no ACID
-: require application code; lack support for declarative queries
NewSQL
–
–
–
–
–
–
–
+: stronger consistency and often full transaction support
+: familiar SQL and standard tooling
+: richer analytics using SQL and extensions
+: leverages noSQL-style clustering
-: no NewSQL is as general-purpose as traditional SQL systems
-: in-memory architecture may hinder the application for large dataset
-: partial access to the rich tooling of traditional SQL
34