…l…b…g…‘†[…N…v…“…O…›…~…ﬁ…OﬁÁŸ_

Size: px

Start display at page:

Download "…l…b…g…‘†[…N…v…“…O…›…~…ﬁ…OﬁÁŸ_"

あきひろすわ
6 years ago
Views:

1 13 : Web : RDB (MySQL ) DB (memcached ) 1: MySQL ( ) 2: : /, 3: : Google, 1 / 23

2 testmysql.rb: mysql ruby testmem.rb: memcached ruby 2 / 23

3 ? Web / 3 ( ) Web s ( ) MySQL PostgreSQL SQLite MariaDB (MySQL ) 3 / 23

4 MySQL Relational Data Base (SQL) Oracle Web ( ) InnoDB ( MyISAM) 4 / 23

5 Web RDB MySQL/Ruby testmysql.rb 5 / 23

6 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

7 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

8 SQL injection (SQL injection ) ( ) ( Cookie ) (htmlspecialchars) (HTML DB DB ) 8 / 23

9 MySQL START TRANSACTION; COMMIT; ROLLBACK; : ACID Atomicity: all-or-nothing Consistency: DB Isolation: 2 Durability: Commit Isolation TRANSACTION ISOLATION LEVEL 9 / 23

10 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

11 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

12 CAP E.Brewer : Consistency: Availability: ( ) Partition tolerance: Partition Tolerance Consistency Availability (*) E.Brewer: CAP Twelve Years Later, Computer / 23

13 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 / 23

14 BASE : (eventual consistency soft sate ) : (persistent message) : ID (idempotent update) BASE ACID Serializable Snapshot Isolation BASE BASE 14 / 23

15 memcached : write read read memcached: ( Web DB ) 15 / 23

16 $ telnet XXXX memcached set name name 9 testvalue 9 STORED memcached get name name VALUE name memcached testvalue memcached END 16 / 23

17 : set : get (CAS ): gets : delete get set check set: cas cas 1 1 : incr decr 17 / 23

18 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

19 testmem.rb "foo s value" 1234 "this is a pen" "value" "this is a pen" 19 / 23

20 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; (I=1..1 ) (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

21 Twitter : 21 / 23

22 1: NoSQL? : = RDBMS ( SQL) ( ) SQL NoSQL (Not Only SQL) NoSQL + Key-Value Hadoop BigTable Cassandra : MongoDB, CouchDB 22 / 23

23 2: DB ( GB ) ( ) ( ) Oracle TimesTen DB IBM PowerPC A2, Intel Haswell Serializable Snapshot Isolation SI Serializable ( false-positive ) Google Spanner (= ) GPS 23 / 23

Microsoft PowerPoint - shudo-NoSQL-data-model ppt

Microsoft PowerPoint - shudo-NoSQL-data-model ppt 28 2009 12 17 NoSQL 1 NoSQL, key-value store, documentoriented DB, graph DB, memcached, Bigtable, Dynamo, Amazon SimpleDB, Cassandra, Voldemort, Ringo, VPork, MongoDB, CouchDB, Tokyo Cabinet/Tokyo Tyrant,