MySQL UC solid DB xact

Information about MySQL UC solid DB xact

Published on June 18, 2007

Author: Techy_Guy

Source: authorstream.com

Content

Transaction Processing and Durability with solidDB for MySQLJan LindströmSenior R&D Engineer, Solid:  Transaction Processing and Durability with solidDB for MySQL Jan Lindström Senior Randamp;D Engineer, Solid Contents:  Contents Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Solid At A Glance:  Solid At A Glance Business Category: Leading provider of fast, always-on and affordable database solutions for both OEM and Enterprise customers Technology Adoption: More than 3,000,000 Solid databases are deployed worldwide in communications networks, electronic devices, and enterprise applications Company Background: Founded 1992, Helsinki, Finland Technology enhanced over 12 years with €35M invested in Randamp;D Privately held Global Presence: Headquartered in Cupertino, CA USA Randamp;D in Helsinki, Finland, and Russia Sales offices across the USA, EMEA, China and Japan solidDB for MySQL:  solidDB for MySQL Built on MySQL, behaves like MySQL Designed for transactional applications Scales in multi-core/multiprocessor environments Includes online backup at no extra charge First product certified under MySQL Storage Engine Partner Program Slide5:  Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Transaction processing:  Transaction processing A transaction is a logical unit of work It begins with the execution of a BEGIN transaction End with execution of a COMMIT or ROLLBACK Transaction processing schedules concurrent access to data so that each user can safely ignore the fact that others are accessing the data concurrently Transaction processing architecture:  Transaction processing architecture Transaction manager performs any required pre-processing of operations it receives from the transactions. Scheduler controls the relative order in which these operations are executed. Data manager operates directly on the database. Scheduler Transaction Manager Data Manager User thread DB Scheduler:  Scheduler A scheduler controls the concurrent execution of the transactions. It makes use of this control by deciding the order in which the data manager executes the reads, writes, commits, and aborts of different transactions. Scheduler is responsible for maintaining desired isolation level. Scheduler actions:  Scheduler actions Execute: scheduler passes the operation to the data manager (DM) and wait for a result. Reject: scheduler refuses to process the operation, in which case it tells the transaction that its operation has been rejected and transaction is aborted. Delay: scheduler delays the operation by placing it in a queue internal to the scheduler. Later, it can remove the operation from the queue and either execute it or reject it. Schedulers:  Schedulers Traditionally there are two kind of schedulers The pessimistic view that many transactions will conflict with each other The optimistic view that not too many transactions will conflict with each other. Pessimistic concurrency control is also known as ‘locking’ Locks are placed before any piece of the row is accessed. This is safe, conceptually simple approach. Optimistic concurrency control Synchronization of transactions is done in transaction termination Instead of locking every record the software looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then one user’s updates are discarded and transaction is rolled back. Transaction processing and MySQL:  Handler Transaction processing and MySQL Handlerton contains transaction interface Handler contains table interface Transaction processing is shared between handlerton and storage engine Handlerton Storage engine Slide12:  Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Transaction processing on solidDB for MySQL:  Transaction processing on solidDB for MySQL solidDB for MySQL offers two different types of concurrency control mechanisms Pessimistic Optimistic Concurrency control can be selected per-table using a table type. Table type used when user does not explicitly define table type is based on configuration variable soliddb_pessimistic. Pessimistic tables are default by April release (earlier optimistic tables were default). Features for both table types:  Features for both table types Non-blocking consistent reads No locks taken for read-only selects Lock taken in pessimistic mode for select ... for update; Current release of solidDB forMySQL does not support select ... in share mode. Deferred constraint checking Unique constraint check Foreign key constraint check Check constraint check Disadvantages of pessimistic tables:  Disadvantages of pessimistic tables Unpredictable lock wait time Possibility of deadlocks Requires overhead taking a lock for every database operation whether or not two or more users are actually trying to access the same record and this has a penalty on performance. Disadvantages of optimistic tables:  Disadvantages of optimistic tables Incorrect execution order leads transaction rollback (because execution order does not obey isolation level). This rollback has penalty to performance because updates done by the transaction must be rolled back. When to use Optimistic/Pessimistic:  When to use Optimistic/Pessimistic Shortly, if you expect having many simultaneous conflicting writes (inserts/deletes/updates), pessimistic gives better performance. This is because optimistic allows those conflicting transactions to proceed 'too far'. Then, when transaction has done all what it was supposed to, it is aborted. With pessimistic, conflicting transactions are not allowed to produce conflicts. Instead, the operations they include, are ordered by the engine in a way that they will be executed successfully. This results in better transaction throughput. Small database andamp; lots of updates -andgt; lots of conflicts -andgt; choose pessimistic. Large database andamp; less updates -andgt; not many conflicting operations -andgt; choose optimistic. Implementing optimistic concurrency control:  Implementing optimistic concurrency control Each time that the server reads a record to try to update it, the server makes a copy of the version number of the record and stores that copy for later reference. When it's time to write the updated record back to the database, the server compares the original version number that it read with the version number that the database now contains. If the versions are the same then no one else has changed the record and we can write the updated record. If the versions differ then someone has changed the record and we must discard our version of the data and give user the error. Example of pessimistic table:  Example of pessimistic table create table t0(a int not null, primary key(a)) engine=soliddb comment=’MODE=PESSIMISTIC’; Insert into t0 values (0),(1),(2),(3),(4); commit; /* In session 1 */ begin; update t0 set a = 5 where a = 2; /* in session 2 */ begin; Update t0 set a = 7 where a = 2; // waits for session1 Example of optimistic table:  Example of optimistic table create table t0(a int not null, primary key(a)) engine=soliddb comment=’MODE=OPTIMISTIC’; Insert into t0 values (0),(1),(2),(3),(4); commit; /* In session 1 */ begin; update t0 set a = 5 where a = 2; /* in session 2 */ begin; update t0 set a = 7 where a = 2; ERROR 1205 (HY000): Lock wait timeout exceeded... Deferred unique constraint checking:  Deferred unique constraint checking create table t0(a int not null, primary key(a)) engine=soliddb; insert into t0 values (0),(1),(2); commit; update t0 set a = a + 1; /* success !*/ select * from t0; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ How update is implemented?:  How update is implemented? ::rnd_init(t0) ::rnd_next(t0) == (0) 0 +1 = 1 ::update_row(0, 1) ::rnd_next(t0) == (1) 1 +1 = 2 ::update_row(1,2) ::rnd_next(t0) == (2) 2 + 1 = 3 ::update_row(2,3) statement commit (at this stage there is no duplicate keys in the primary key, thus success ! ) More complicated example:  More complicated example create table t0(a int not null, b int, primary key(a)) engine=soliddb; insert into t0 values (1,1),(2,2),(3,3),(4,4); update t0 set a = 3 - a; select * from t0; +----+------+ | a | b | +----+------+ | -1 | 4 | | 0 | 3 | | 1 | 2 | | 2 | 1 | +----+------+ Slide24:  Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Multiversioning:  Multiversioning Implemented in solidDB for MySQL with a feature called Bonsai Tree Bonsai Tree is comprised of recently written data interrelated by the transactions structures that they were processed with Bonsai Tree is a part of a two-level internal structure, where Bonsai Tree is a 'differential index'. The other part is a traditional disk-optimized index called the Storage Tree. Both parts are based on the B+ tree concept Bonsai Tree and multiversioning:  Bonsai Tree and multiversioning Any time new data is written, a new version is created in the Bonsai Tree This is the cornerstone of Solid's multi-version concurrency control (MVCC) All new data written by a transaction is given a version number called 'read level'. The name has to do with the fact that, when other transactions start to read that data, they operate at a given read level and may be guaranteed a consistent data snapshot, regardless of what happens to that data later on Multiple versions are maintained in the Bonsai Tree only Older versions:  Older versions Older versions are maintained for as long as they are needed by other transactions. That depends on the isolation levels used. The outdated versions are purged by a background thread. Over time, hot spot data is efficiently stored in memory by collecting only the rows that are frequently written. If a transaction is rolled back, the given read level is simply discarded. Thus, the Bonsai Tree serves as an undo log, and the rollback operation is extremely light-weight. Committed versions:  Committed versions The committed versions are migrated to the Storage Tree when they are not needed any more. This activity is called 'Bonsai Tree merge' and is performed by a background thread. In terms of page management policy, this is often called No-Steal, as the data page migration to disk lags behind the transactions that are written to the log. It might happen that the Bonsai Tree grows too large because of some irregularity like long-running transactions of high isolation level. In that case, the Bonsai Tree is swapped (by pages) to the disk. Slide29:  Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Durability:  Durability Normally, when a transaction is committed, the database server writes data to two locations: the database file and the transaction log file However, the data are not necessarily written to those two locations concurrently (and in an atomic way) Thus, when a transaction is committed, the server writes the data to the transaction log file immediately. But, this data is not written to the database file immediately The server may wait until it is less busy, or until it has accumulated multiple changes, before writing data to the database file Durability levels:  Durability levels Strict durability Data is written to the disk drive before user is told that his data has been committed Relaxed durability User may be told that the data has been committed even before the data has been written to the transaction log on disk. Server may group several transactions to one log write This increases the performance but you risk losing some data if the server shuts down abnormally after it has committed some data but before it has written those data to the transaction log Thus, you should use relaxed durability only when you can afford to lose a small amount of recent data Relaxed durability:  Relaxed durability Can be used if you can afford to lose a small amount of recent data and if performance is crucial to you. Relaxed durability is appropriate when each individual transaction is not crucial. For example monitoring systems where you want to store data while monitoring the system. You may be interested in average values which will not be significantly affected if you are missing a few pieces of data. Database consistency is maintained also in relaxed durability. Slide33:  Solid at a glance Transaction processing Different table types inside solidDB for MySQL Pessimistic tables Optimistic tables Multiversioning Durability Conclusions Conclusions:  Conclusions solidDB for MySQL is the first storage engine offering both optimistic and pessimistic concurrency control methods. Transactions can access both pessimistic and optimistic tables. solidDB for MySQL supports deferred constraint checking. Multiversioning decreases effect of concurrent access to the same data. Bonsai Tree is useful because maintaining version information in main-memory is better for performance. Choice of durability allows you to take advantage of application semantics. How to Learn More:  How to Learn More Visit Solid in Booth # 401 3 different demos of solidDB for MySQL 'Meet the Experts' Sessions Job board sponsored by Proven Scaling Hear Solid in these Sessions Strengthening High Availability in MySQL Tuesday, April 24; 10:45AM - 11:45AM; Ballroom G Keynote: Battle of the Database Egos (panel session) Wednesday, April 25; 9:05AM - 9:50AM; Main Ballroom Transaction Processing and Durability with solidDB for MySQL Wednesday, April 25; 1:40PM - 2:35PM; Ballroom F Lightening Rounds: State of the Partner Engines (panel session) Thursday, April 26; 10:45AM - 11:45AM; Ballroom G solidDB Storage Engines Thursday, April 26; 2:35PM; - 3:20; Ballroom G www.solidDB.com Q & A:  Q andamp; A Thank You:  Thank You

Related presentations


Other presentations created by Techy_Guy

Character Analysis
04. 01. 2008
0 views

Character Analysis

Roosevelt and Latin America
22. 10. 2007
0 views

Roosevelt and Latin America

S10 Processor Performance
17. 09. 2007
0 views

S10 Processor Performance

Hawaiian Humpback Whale
17. 09. 2007
0 views

Hawaiian Humpback Whale

rainforest
02. 10. 2007
0 views

rainforest

Comvalid BGPsentinel
07. 10. 2007
0 views

Comvalid BGPsentinel

PETERPAN
10. 10. 2007
0 views

PETERPAN

across crocodile lake
11. 10. 2007
0 views

across crocodile lake

MLM basic info
12. 10. 2007
0 views

MLM basic info

VortragRichter
15. 10. 2007
0 views

VortragRichter

azerbaijan
15. 10. 2007
0 views

azerbaijan

ch02jjm
19. 10. 2007
0 views

ch02jjm

PRNAV Eurocontrol presentation
19. 10. 2007
0 views

PRNAV Eurocontrol presentation

Hakkarainen 091104
17. 09. 2007
0 views

Hakkarainen 091104

Extreme Ostrich2
17. 09. 2007
0 views

Extreme Ostrich2

Soy Protein in Baking
04. 10. 2007
0 views

Soy Protein in Baking

McMurrenTidbits
23. 10. 2007
0 views

McMurrenTidbits

Larijani stemcell ABA2007 Final
24. 10. 2007
0 views

Larijani stemcell ABA2007 Final

F Gauze
24. 10. 2007
0 views

F Gauze

TornadoSafetyAMS
07. 10. 2007
0 views

TornadoSafetyAMS

nii report
09. 10. 2007
0 views

nii report

NS102 3a S07 Fighting Sail
21. 10. 2007
0 views

NS102 3a S07 Fighting Sail

am0845 Khanna
16. 11. 2007
0 views

am0845 Khanna

culturechange
10. 12. 2007
0 views

culturechange

Jeopardy
29. 10. 2007
0 views

Jeopardy

masstheory
02. 11. 2007
0 views

masstheory

Finnish Chemicals information
21. 08. 2007
0 views

Finnish Chemicals information

zodiac
21. 08. 2007
0 views

zodiac

ICT Expo Presentation
21. 08. 2007
0 views

ICT Expo Presentation

words alive notes
21. 08. 2007
0 views

words alive notes

notes 13
21. 08. 2007
0 views

notes 13

200612011440150 ser mama
01. 10. 2007
0 views

200612011440150 ser mama

t5f2
07. 11. 2007
0 views

t5f2

PHYS 124 lt 2
13. 11. 2007
0 views

PHYS 124 lt 2

Localization days1 2
14. 11. 2007
0 views

Localization days1 2

Barlow
15. 11. 2007
0 views

Barlow

CEO breakfast Mar
16. 11. 2007
0 views

CEO breakfast Mar

SEVESO II 28 04 2003 d jansen
23. 11. 2007
0 views

SEVESO II 28 04 2003 d jansen

farawayplaces quiz
31. 10. 2007
0 views

farawayplaces quiz

lino hospitalstay 2005
28. 12. 2007
0 views

lino hospitalstay 2005

eno
05. 10. 2007
0 views

eno

Destinos Tradicionale
22. 10. 2007
0 views

Destinos Tradicionale

El Karib Hagmann 2001 HEKS ACORD
23. 10. 2007
0 views

El Karib Hagmann 2001 HEKS ACORD

Bioceramics
05. 01. 2008
0 views

Bioceramics

dennis
07. 01. 2008
0 views

dennis

DNR wetland benefits
07. 01. 2008
0 views

DNR wetland benefits

Norm Wright Presentation06
17. 09. 2007
0 views

Norm Wright Presentation06

Tudor Sports
21. 08. 2007
0 views

Tudor Sports

watson 2006
21. 08. 2007
0 views

watson 2006

IBM Presentation Roel Spee
24. 10. 2007
0 views

IBM Presentation Roel Spee

david simek
17. 09. 2007
0 views

david simek

75thWinter Silver
02. 08. 2007
0 views

75thWinter Silver

Revay Presentation
17. 09. 2007
0 views

Revay Presentation

week12 f03
17. 09. 2007
0 views

week12 f03

Ch12 ResolutionTheoremPro ving
17. 09. 2007
0 views

Ch12 ResolutionTheoremPro ving

INFOCOM99
05. 10. 2007
0 views

INFOCOM99

RoHS Presentation3 May
12. 10. 2007
0 views

RoHS Presentation3 May

Botany
07. 12. 2007
0 views

Botany

Week6February20 07
20. 02. 2008
0 views

Week6February20 07

Microcosmo Parte II
12. 10. 2007
0 views

Microcosmo Parte II

TSW
29. 02. 2008
0 views

TSW

HazMat Flow Study
26. 02. 2008
0 views

HazMat Flow Study

Vegetarian Nutrition 101
04. 03. 2008
0 views

Vegetarian Nutrition 101

White 10th Inter mountain
21. 08. 2007
0 views

White 10th Inter mountain

hondaimobil
02. 01. 2008
0 views

hondaimobil

Cfi
10. 03. 2008
0 views

Cfi

Timber Bridge Presentation
01. 01. 2008
0 views

Timber Bridge Presentation

carstenschymik
29. 12. 2007
0 views

carstenschymik

Ch 22 WB
07. 04. 2008
0 views

Ch 22 WB

Macroclean
10. 04. 2008
0 views

Macroclean

agingandwork
13. 04. 2008
0 views

agingandwork

nyBrazeau
14. 04. 2008
0 views

nyBrazeau

presentation total
16. 04. 2008
0 views

presentation total

3 Tufano2002
17. 04. 2008
0 views

3 Tufano2002

Chapter 18
18. 04. 2008
0 views

Chapter 18

Baltic states and Russia
12. 10. 2007
0 views

Baltic states and Russia

quotes
03. 10. 2007
0 views

quotes

WDR 2008
29. 11. 2007
0 views

WDR 2008

CHLA PSRS Overview
30. 04. 2008
0 views

CHLA PSRS Overview

15 UKernel
02. 05. 2008
0 views

15 UKernel

Mr Logan OCCAR
06. 03. 2008
0 views

Mr Logan OCCAR

shen
15. 10. 2007
0 views

shen

Industry Brief
22. 10. 2007
0 views

Industry Brief

sess 4 solano
18. 06. 2007
0 views

sess 4 solano

sess 2 vollmer
18. 06. 2007
0 views

sess 2 vollmer

NSDI05 poster
18. 06. 2007
0 views

NSDI05 poster

NLC talk
18. 06. 2007
0 views

NLC talk

My Proxy GW06
18. 06. 2007
0 views

My Proxy GW06

my Master 4
18. 06. 2007
0 views

my Master 4

More Mosaics
18. 06. 2007
0 views

More Mosaics

MEM SPI Jan00
18. 06. 2007
0 views

MEM SPI Jan00

VCA Org Charts
11. 12. 2007
0 views

VCA Org Charts

cjdim com Boudchiche
23. 10. 2007
0 views

cjdim com Boudchiche

GA Conf06China1
25. 03. 2008
0 views

GA Conf06China1

lecture 7 deadlock
17. 09. 2007
0 views

lecture 7 deadlock

Neptune Presentation
15. 06. 2007
0 views

Neptune Presentation

neptune
15. 06. 2007
0 views

neptune

Mehregan
18. 06. 2007
0 views

Mehregan

Plants are very useful
15. 06. 2007
0 views

Plants are very useful

Learning Phonics
15. 06. 2007
0 views

Learning Phonics

Learning Percent III
15. 06. 2007
0 views

Learning Percent III

Learning Percent I
15. 06. 2007
0 views

Learning Percent I

Physical Education Procedures
15. 06. 2007
0 views

Physical Education Procedures

Penguins
15. 06. 2007
0 views

Penguins

Olympic Wax Museum
15. 06. 2007
0 views

Olympic Wax Museum

howe9
17. 09. 2007
0 views

howe9

GSantin Siena 2 SpaceTools
03. 01. 2008
0 views

GSantin Siena 2 SpaceTools

gunderia powerpointlab
26. 11. 2007
0 views

gunderia powerpointlab

Civics Lecture
31. 12. 2007
0 views

Civics Lecture

Physics and psycho2
14. 02. 2008
0 views

Physics and psycho2

TOUREDIT
12. 03. 2008
0 views

TOUREDIT

harvard deas
03. 01. 2008
0 views

harvard deas

Angelology
01. 10. 2007
0 views

Angelology

HenryVIII wwtbam
21. 08. 2007
0 views

HenryVIII wwtbam

AGU 2002
03. 10. 2007
0 views

AGU 2002

RubÃn Blades
22. 10. 2007
0 views

RubÃn Blades

tran present
21. 08. 2007
0 views

tran present

BU01
17. 09. 2007
0 views

BU01

Thode
17. 09. 2007
0 views

Thode

PP R CAJAR
22. 10. 2007
0 views

PP R CAJAR

moore lightning uw05
17. 09. 2007
0 views

moore lightning uw05

Space- The Outside World
15. 06. 2007
0 views

Space- The Outside World

arts and humanities applications
22. 11. 2007
0 views

arts and humanities applications

Session9 CATHALAC UNDP
25. 10. 2007
0 views

Session9 CATHALAC UNDP

use sunscreen
17. 09. 2007
0 views

use sunscreen

aatom
20. 11. 2007
0 views

aatom

9681
02. 08. 2007
0 views

9681

HHDL
15. 10. 2007
0 views

HHDL