13 : Web : RDB (MySQL ) DB (memcached ) 1: MySQL ( ) 2: : /, 3: : Google, 1 / 23
testmysql.rb: mysql ruby testmem.rb: memcached ruby 2 / 23
? Web / 3 ( ) Web s ( ) MySQL PostgreSQL SQLite MariaDB (MySQL ) 3 / 23
MySQL Relational Data Base (SQL) Oracle Web ( ) InnoDB ( MyISAM) 4 / 23
Web RDB MySQL/Ruby testmysql.rb 5 / 23
testmysql.rb "5.1.53" ["information_schema", "demo_development", "demo_test", "mydb_development", "mydb_test", "mysql", "nchak_development", "nchak_test", "sampledb", "test"] ["engtable", "mathtable"] "id" "name" "score" ["1", "yamazaki", "50"] ["2", "shibaura", "70"] ---result of join--- ["yamazaki", "50", "60"] ["shibaura", "70", "80"] 6 / 23
SQL injection RDB SQL SQL SQL : q="select name, score from engtable where name="+username+";" result=ms.query(q) username yamazaki? username=" dummy OR true" 7 / 23
SQL injection (SQL injection ) ( ) ( Cookie ) (htmlspecialchars) (HTML DB DB ) 8 / 23
MySQL START TRANSACTION; COMMIT; ROLLBACK; : ACID Atomicity: all-or-nothing Consistency: DB Isolation: 2 Durability: Commit Isolation TRANSACTION ISOLATION LEVEL 9 / 23
MySQL Isolation Level 4 Serializable: Repeatable Read: phantom Read Committed: non-repeatable, phantom Read Uncommitted: dirty, non-repeatable, phantom read dirty read: commit non-repeatable read: Tx commit (2 ) phantom read: (2 SELECT ) 10 / 23
Snapshot Isolation H.Berenson: A critique of ANSI SQL isolation levels, SIGMOD 95 MVCC (Multi-Version Concurrency Control) : Tx read Tx read First-Committer-Wins : read write Read Skew: r1[x]...w2[x]...w2[y]...c2...r1[y]...(c1 or a1) Write Skew: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur) SI: Read Skew Write Skew RR: RC: RR > SI > RC Write Skew Serializable Snapshot Isolation (2008 ) 11 / 23
CAP E.Brewer 1999 2 : Consistency: Availability: ( ) Partition tolerance: Partition Tolerance Consistency Availability (*) E.Brewer: CAP Twelve Years Later, Computer 2012 12 / 23
ACID vs BASE Consistency first, Availability second: ACID Atomicity: all-or-nothing Consistency: DB Isolation: 2 Durability: Commit Availability first, Consistency second: BASE Basically Available: Soft-State: ( ) Eventual Consistency: (*) A. Fox: Cluster-Based Scalable Network Services, ACM SOSP 97. 13 / 23
BASE : (eventual consistency soft sate ) : (persistent message) : ID (idempotent update) BASE ACID Serializable Snapshot Isolation BASE BASE 14 / 23
memcached : write read read memcached: ( Web DB ) 15 / 23
$ telnet XXXX 11211 memcached set name 12345 0 9 name 9 testvalue 9 STORED memcached get name name VALUE name 12345 9 memcached testvalue memcached END 16 / 23
: set : get (CAS ): gets : delete get set check set: cas cas 1 1 : incr decr 17 / 23
memcache (ruby) memcache-client testmem.rb (memcache-client Dalli) : MemCache.new( 1, 2, 3) mc["foo"]="foo s value" p mc[ foo ] ruby : def []=(key, value) end def [](key) end 18 / 23
testmem.rb "foo s value" 1234 "this is a pen" "value" "this is a pen" 19 / 23
ruby 1 elapse time MySQL: insert into engtable values (I, y +I.to_s, I); 0.48 (I=1..10 ) select name, score from engtable where name= y0 ; 0.47 (I=1..10 ) select name, score from engtable where name= y +I.to_s; 1 13.4 (I=1..1 ) 2 0.47 (I=1..1 ) memcached: mc["y"+i.to_s]=i 0.5 (I=1..10 ) x = mc["y"+i.to_s] 0.49 (I=1..10 ) (+ ) MySQL 20 / 23
Twitter : http://www.infoq.com/news/2009/06/twitter-architecture 21 / 23
1: NoSQL? : = RDBMS ( SQL) ( ) SQL NoSQL (Not Only SQL) NoSQL + Key-Value Hadoop BigTable Cassandra : MongoDB, CouchDB 22 / 23
2: DB ( GB ) ( ) ( ) Oracle TimesTen DB IBM PowerPC A2, Intel Haswell Serializable Snapshot Isolation SI Serializable ( false-positive ) Google Spanner (= ) GPS 23 / 23