GDC 2008
SQL Considered Harmful
Shannon PosniewskiCryptic Studios
GDC 2008
Current Relational DBMSs are Inappropriate for Low-
Latency, Controlled Working-Set Databases
and what we did trying to circumvent thatand how that didn’t work out very well
and what we’re doing now
Shannon Posniewski
GDC 2008
Database Fundamentals
Structured collection of data
“ACID” rules
GDC 2008
Atomicity
Consistency
Isolation
Durability
GDC 2008
Database Types
Relational
Object
Object-Relational
Hierarchical
etc.
GDC 2008
Relational Model
Defined mathematically
Based onset theory
predicate logic
GDC 2008
Relational Database
TablesRows
aka records
aka entities
Columnsvalues for that entity
GDC 2008
SQL
computer language for manipulating relational databases
GDC 2008
Unique and Foreign Keys
Unique key in one table refers to other rows in another table.
Used to represent hierarchical data
GDC 2008
Player ID PlayerName … AttribN
818 Warthog … foo
237 John … bar
Player ID Item Count
818 Hat 1
818 Brooch 1
818 Pteradactyl 1
237 Monkey Paw 1
GDC 2008
Player ID Name … Class ID
818 Warthog … 2
237 John … 3
Class ID Name
1 Elf
2 Orc
3 Monkey
GDC 2008
SQL DBMSs
Provide all this and more
Have object extensions, BLOBs, etc
Used by practically everyone
Safe, tested
Lots of experience
GDC 2008
Why not use it?
GDC 2008
The Good
SQL servers optimized for general case
Usually good at arbitrary queries
Will always “work”
GDC 2008
The Bad
Long query set up time
Self-optimizes on the fly
Unpredictable latency
GDC 2008
The Ugly
Inserts are SLOW
Per row overhead is high on updates
GDC 2008
Object-Relational Impedance Mismatch
Hierarchy and references are typical
Joins and normalization are often slow
GDC 2008
The Evils of Normalization
Doing it the “the right way” can backfire
GDC 2008
Player ID Stat ID Value
818 Time Online 2,123
818 Fights Won 700
818 Fights Lost 3
…
818 Stat N Value N
This was Bad
GDC 2008
Player ID Stat 0 Value Stat 1 Value … Stat N Value
818 2,123 200 … Value N
237 5,235 60 … Value N
54 675 23 … Value N
This was Good
Abandons relational DB concepts
GDC 2008
Solution: Caching
Built a write-through cache
Reads come from the cache
Writes go to cache and then to SQL
All active players in memory (fast)
GDC 2008
Not Enough
Still too slow
Cache smoothes out latency issues
Throughput still a problem
GDC 2008
Reduce Throughput Needs
Don’t save HP, location, etc
Snapshot instead
ACID is pretty much shot
GDC 2008
Data Ownership
A process “owns” an entity’s data
Vast majority of changes are local
Changes are periodically flushed to DB
GDC 2008
Cross-Owner Transactions
Flush both characters
Still a window of failure
We just did our best
GDC 2008
Optimized and played tricks on the database…
…and stopped getting the benefits of using one
GDC 2008
It’s just not the right tool
GDC 2008
Cryptic DB
GDC 2008
Requirements
Regain ACID (as much as possible)
Low, predictable latency
high read/write throughput
minimize code-database impedance
friendly to schema changes
easy backup and restore
GDC 2008
Relaxations
Offline character access can be slower
Domain integrity responsibility of programmer
General ad hoc queries can be slower
SQL does not need to be supported
GDC 2008
StructParser
Structured text definition of data
Generated from C structs
Used for serializing static data
Unifies static and DB data
GDC 2008
Example
AUTO_STRUCT AST_CONTAINER;typedef struct Entity{
EntityRef myRef; NO_AST const ContainerID myContainerID; AST(PERSIST, KEY) Vec3 pos; AST(PERSIST, SERVER_ONLY, NO_TRANSACT)
…
} Entity;
GDC 2008
Struct Metadata
Generates metadata on structsmemory layout
data types
Generic mutators are possibleCryptic DB
GDC 2008
Basic Operations
Cryptic DB supports only a few basic operations
Simple transactions built-in
More complicated things need code
GDC 2008
Data and Logic Ownership
A server “owns” the data for an entity
Knows how to modify these entities
GDC 2008
Map ServerMap Server
Architecture
Map ServerTeam Server
Cryptic DBTransaction
Server
GDC 2008
Cryptic Engine Transactions
Local if possible
Dispatched by Transaction Server if not
AUTO_TRANSACTIONS in C
Field-level locking
GDC 2008
AUTO_TRANSACTION
AUTO_TRANSACTION; enumTransactionOutcome trGiveGold(ATR_ARGS,
NOCONST(Entity) *pEnt1,NOCONST(Entity) *pEnt2,int amount)
{ if (pEnt1->gold < amount) TRANSACTION_RETURN(TRANSACTION_OUTCOME_FAILURE,
”NotEnoughGold”); pEnt1->gold -= amount; pEnt2->gold += amount;
return TRANSACTION_OUTCOME_SUCCESS;}
C code plus special macros
GDC 2008
Step 1Transaction Server
Server 1
A
Server 2
BDoTrade
Trans(“DoTrade”, A, B)
GDC 2008
Step 2Transaction Server
Server 1
Trans(“DoTrade”, A, B)
A
Server 2
BDoTrade
1234…
1234…
GDC 2008
Step 3Transaction Server
Server 1
A
Server 2
BDoTrade
1234…
B
GDC 2008
Step 4Transaction Server
Server 1
A
Server 2
B
DoTrade
1234…
B
A’
B’
GDC 2008
Step 4Transaction Server
Server 1
A
Server 2
B
1234…
B
A’
dB
B’
dB
B’
GDC 2008
Step 4Transaction Server
Server 1 Server 2
1234…
A’ B’
GDC 2008
Abstract Cryptic DB
Get transaction
Log it to journal
Apply change to in-memory DB
Occasionally write out updated DB file
Mark the journal resolved to that point
We’re hiring! Apply Online!Copyright 2008 Cryptic Studios, Inc.
Top Related