Die InnoDB Storage Engine (Teil 1)

Click here to load reader

  • date post

    15-Apr-2017
  • Category

    Internet

  • view

    557
  • download

    2

Embed Size (px)

Transcript of Die InnoDB Storage Engine (Teil 1)

  • Die InnoDB Storage EngineDie wunderbare Welt von Isotopp

    Mittwoch,30.Januar2008DieInnoDBStorageEngineDie Storage Engine InnoDB ist eine Storage Engine, die ACIDkonform betrieben werden kann, Transaktionen beherrscht undForeign Key Constraints prfen kann. Sie ist geeignet fr alleAnwendungen,dieOnlineTransactionProcessingmachenoderausanderenGrndeneinehoheRatevonparalellenSchreibzugriffenhaben.HatmeinMySQLInnoDBSupportundistdieserbetriebsbereit?

    Mit dem Kommano SHOW ENGINES kann man sich die von einer InstanzuntersttztenEnginesanzeigen lassen.Wenn InnoDBenthaltenundbetriebsbereitist,wirddieEnginemitYESangezeigt.Wennsieenthaltenundnichtbetriebsbereitist,wirdDISABLEDgezeigt.Wennsienichteinmal imCodedesServersenthaltenist,wirdNOgezeigt.CODE:rootonmysql.sock[(none)]>showengines

    ++++|Engine|Support|Comment|++++|MyISAM|DEFAULT|DefaultengineasofMySQL3.23withgreatperformance||MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables||InnoDB|YES|Supportstransactions,rowlevellocking,andforeignkeys||BerkeleyDB|NO|Supportstransactionsandpagelevellocking||BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)||EXAMPLE|YES|Examplestorageengine||ARCHIVE|YES|Archivestorageengine||CSV|YES|CSVstorageengine||ndbcluster|NO|Clustered,faulttolerant,memorybasedtables||FEDERATED|YES|FederatedMySQLstorageengine||MRG_MYISAM|YES|CollectionofidenticalMyISAMtables||ISAM|NO|Obsoletestorageengine|++++12rowsinset(0.00sec)

    EineminimaleKonfigurationfrInnoDB

    Fr die folgenden Beispiele ist es notwendig, da der MySQLServer mit einemfunktionierenden InnoDB ausgestattet ist. Die folgende MinimalKonfiguration istnichtfrdieProduktiongeeignet,sollteaberausreichenumvonDISABLEDaufYESzukommen.

    Mit SHOWGLOBAL VARIABLES LIKE 'datadir' ist das Datenverzeichnis frdieseInstanzvonMySQLzulokalisieren.IndiesemVerzeichnisbefindensichunter Umstnden ein Datenfile ibdata1 und zwei Logfiles, ib_logfile0 undib_logfile1.CODE:rootonmysql.sock[(none)]>showglobalvariableslike'datadir'+++|Variable_name|Value|+++|datadir|/export/data/rootforum/data/|+++ 1 row in set (0.00 sec)root on mysql.sock [(none)]> quit Bye linux:/export/data/rootforum # ls

  • lh /export/data/rootforum/data/ib* rwrw 1 mysql mysql 5M Jan 9 17:51 /export/data/rootforum/data/ib_logfile0 rwrw 1 mysql mysql 5M Jan 9 17:51 /export/data/rootforum/data/ib_logfile1 rwrw1mysqlmysql10MDec1314:34/export/data/rootforum/data/ibdata1

    DerDatenbankserveristzustoppen.Dieo.a.dreiFilessind,wennvorhanden,zulschen.WARNUNG!Dieslschtalle evtl. bereits vorhandenen Daten in InnoDB. Diesen Schritt nur danndurchfhren,wenndieEnginevorherDISABLEDwar.In der Sektion [mysqld] der my.cnf ist dieAnweisung skipinnodb zu finden,wennvorhanden,undzuentfernen.Die folgenden Konfigurationsanweisungen sind stattdessen einzufgen. IhreBedeutungwirdweiteruntenerlutert.CODE:innodbinnodb_file_per_table=1innodb_flush_log_at_trx_commit=2

    Dies ist ein minimales Setup, das zum Testen geeignet ist, aber keinePerformance bringen wird. Spter gehen wir auch auf InnoDB PerformanceTuningein.Der Server ist zu starten. Er legt die o.a. drei Files neu an, und hinterltentsprechende Nachrichten im Log. Die Engine InnoDB wird in SHOWENGINESjetztmitYESangezeigt.

    InnoDBTabellenanlegen

    Beim Anlegen einer Tabellenspezifikation kann durch das Nachstellen einerENGINEKlauselfestgelegtwerden,mitwelcherStorageEnginedieTabelleerzeugtwird. Dies kann innerhalb eines Schemas fr jede Tabelle getrennt festgelegtwerden, und mit Hilfe von ALTER TABLE auch nachtrglich ohne Datenverlustgendertwerden.CODE:rootonmysql.sock[(none)]>createdatabaseinnodemo

    QueryOK,1rowaffected(0.32sec)

    rootonmysql.sock[(none)]>useinnodemoDatabasechangedrootonmysql.sock[innodemo]>createtablekris(idintegerunsignednotnullprimarykeyauto_increment,dvarchar(20)notnull)engine=innodbQueryOK,0rowsaffected(0.36sec)

    rootonmysql.sock[innodemo]>insertintokris(d)values("eins"),("zwei"),("drei")QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:0

    rootonmysql.sock[innodemo]>select*fromkris+++|id|d|+++|1|eins||2|zwei||3|drei|+++3rowsinset(0.01sec)

    MitHilfedesKommandoSHOWCREATETABLEodermitSHOWTABLESTATUSknnenwirsehen,welcheStorageEngineeineTabelleverwendet.CODE:rootonmysql.sock[innodemo]>showcreatetablekris\G

    ***************************1.row***************************Table:krisCreateTable:CREATETABLE`kris`(`id`int(10)unsignedNOTNULLauto_increment,`d`varchar(20)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf81rowinset(0.00sec)

  • rootonmysql.sock[innodemo]>showtablestatuslike"kris"\G

    ***************************1.row***************************

    Name:kris

    Engine:InnoDB

    Version:10

    Row_format:Compact

    Rows:3

    Avg_row_length:5461

    Data_length:16384

    Max_data_length:0

    Index_length:0

    Data_free:0

    Auto_increment:4

    Create_time:2008010918:04:00

    Update_time:NULL

    Check_time:NULL

    Collation:utf8_general_ci

    Checksum:NULL

    Create_options:

    Comment:InnoDBfree:148480kB

    1rowinset(0.00sec)

    InderAusgabevonSHOWTABLESTATUSsehenwireinigeBesonderheiten:Ersteinmal ist die angezeigte Average Row Length von InnoDBTabellen mit sehrwenigen Datenstzen ungltig. Die Average Row Length unserer Tabelle ist, daswissenwir,ungefhr4(integer)+5(1LngenbyteundvierZeichenbytes)pluseinwenigVerwaltungsoverhead(ca.1012ByteproRow).InnoDBvergibtSpeicherplatzjedochinSeitenzu16KB,unddaheristdieData_length=16384Bytes.DieAverageRowLengthwird von InnoDBalsStatistik nicht gepflegt und anMySQLexportiertundwirddaheralsData_length/Rowsgeschtzt.FrkleineAnzahlenvonZeilenistdieserWertfalsch.

    Auerdemknnenwirsehen,dadieIndex_lengthfrdieseTabelle0ist,obwohleinPrimaryKeydefiniert ist.Daswirdunsspternoch imDetailbeschftigen.Frdaserstegengteszuwissen,dasderPrimaryKeyinInnoDBzudenDatengerechnetwirdundauchsonstmagischist.Data_freeist,andersalsinMyISAM,immer0.

    ExistierendeTabellenimTypndern

    MitdenKommandoALTERTABLEknnenwireineexistierendeTabellevonInnoDBnach MyISAM umwandeln oder zurck. Daten gehen dabei nicht verloren. ZumVergleichhiereinmaldieAusgabevonSHOWTABLESTATUSfrdieselbeTabelle,wennsiealsMyISAMTabelleexistiert.Mankannsehen,daMyISAMdenPrimaryKeyNICHTzudenDatenrechnetunddaMyISAMimIndexebenfallsseitenbasiertarbeitet,nursinddieSeitenvielkleiner:Siesindnur1KBgro.CODE:rootonmysql.sock[innodemo]>altertablekrisengine=myisam

    QueryOK,3rowsaffected(0.00sec)

    Records:3Duplicates:0Warnings:0

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    rootonmysql.sock[innodemo]>showtablestatuslike"kris"\G

    ***************************1.row***************************

    Name:kris

    Engine:MyISAM

    Version:10

    Row_format:Dynamic

    Rows:3

    Avg_row_length:20

    Data_length:60

    Max_data_length:281474976710655

    Index_length:2048

  • Data_free:0

    Auto_increment:4

    Create_time:2008010918:11:57

    Update_time:2008010918:11:57

    Check_time:NULL

    Collation:utf8_general_ci

    Checksum:NULL

    Create_options:

    Comment:

    1rowinset(0.00sec)

    WirknnenunsereTabelleauchwiederzurcknach InnoDBwandeln.Wirknnensogar eine existierende InnoDBTabelle nach InnoDB wandeln. Das macht sogarSinnesistgenaudas,wasOPTIMIZETABLEinInnoDBmacht.CODE:rootonmysql.sock[innodemo]>altertablekrisengine=innodb

    QueryOK,3rowsaffected(0.01sec)

    Records:3Duplicates:0Warnings:0

    rootonmysql.sock[innodemo]>altertablekrisengine=innodb

    QueryOK,3rowsaffected(0.02sec)

    Records:3Duplicates:0Warnings:0

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    Ein ALTER TABLE t ENGINE=... erzeugt eine Kopie der Tabelle mit der neuenEngine als temporre Tabelle. Danach wird die Originaltabelle gelscht und dietemporre Tabelle als permanente Tabelle installiert. Das hat eine Reihe vonKonsequenzen:

    DieOperationkannkeineDatenverlieren.WenndieOperationscheitertoderdurchBenutzerabgebrochenwird,wirddietemporreTabellegelschtunddieOriginaltabellebleibtunverndertstehen.Vorbergehend wird sehr viel mehr Speicher gebraucht, denn esmssen jabeideTabellenparallelexistieren.Dabeiistzubeachten,daInnoDBdieDatenwenigerdichtpacktalsMyISAM.EineInnoDBTabellebrauchtetwa1.6bis2.2malsovielPlatzwieeineMyISAMTabelle.WhrenddesALTERTABLEsindbeideTabellengelockt.Wenndasstrt,kannmansichmitderSequenz

    CREATETABLEbLIKEaALTERTABLEbENGINE=InnoDBINSERTINTObSELECT*FROMa

    behelfen.Hier ist a dannwhrenddesKopiervorgangesnurmit einemReadLock gesperrt. Auerdem kann man mit einer LIMITClause am Select dieDatenmengebegrenzen,etwaumzuexperimentieren.

    Transaktionen

    InnoDB beherrscht Transaktionen. Das bedeutet, das Anweisungen an InnoDBTabellengesammeltwerdenunderstdurcheinCOMMITgesammeltaufalleTabellenangewendet werden. Entweder gelingen alle Anweisungen, oder keine von denAnweisungenwirdausgefhrt.

    Per Default befindet sich dieDatenbank imAUTOCOMMITModus. Das bedeutet,

  • nachjederAnweisung"denkt"sichderServerautomatischeinCOMMITdazu.Mankann entweder Autocommit abschalten, oder mit der Anweisung BEGIN auch imAutocommiteinelngereTransaktionbeginnen.CODE:rootonmysql.sock[innodemo]>begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>insertintokris(d)values("vier")

    QueryOK,1rowaffected(0.00sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    |4|vier|

    +++

    4rowsinset(0.00sec)

    rootonmysql.sock[innodemo]>rollback

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    Hier wird mit BEGIN eine Transaktion bei aktiviertemAutocommit begonnen, dasAutocommit also vorbergehend unterbrochen. Dies ist die empfohleneVorgehensweise. Eine Zeilewird in dieTabelle eingefgt. Fr uns selber ist dieseZeile auch erst einmal sichtbar. Die Transaktion wird jedoch nicht mit COMMITbeendet,sondernmitROLLBACKrckgngiggemacht.DadurchistnachdemEndederTransaktiondieZeilewiederweg.

    WieInnoDBaufderPlatteaussieht

    CODE:linux:/export/data/rootforum/data#lslhib*innodemo

    rwrw1mysqlmysql5MJan918:27ib_logfile0

    rwrw1mysqlmysql5MJan918:28ib_logfile1

    rwrw1mysqlmysql10MJan918:27ibdata1

    innodemo:

    total112K

    rwrw1mysqlmysql61Jan918:03db.opt

    rwrw1mysqlmysql8.4KJan918:28kris.frm

    rwrw1mysqlmysql96KJan918:28kris.ibd

    JedeInnoDBInstallationhatmindestenseinDatenfileibdata1undmindestenszweiRedoLogilesib_logfile0undib_logfile1.Position,Gre,AnzahlundNamendieserDateienknnenjedochrelativfreibestimmtwerden.

    Unser InnoDB arbeitetmit innodb_file_per_table = 1. Das bedeutet, unsereDatenliegen in einer .ibdDatei neben der .frmDatei im SchemaDirectory, hier also in$datadir/innodemo/kris.ibd.DieEndung IBDstehtDatei fr InnoBaseData.ObwohldieeigentlichenDaten indieserDatei liegen,braucht InnoDBzwingendein ibdataFile und mindestens zwei RedoLogs. Im ibdataFile legt Innobase das DataDictionaryab,alsoeineSchattenkopiederTabellendefinitionenausdem.frmDateienineinerInnobaseinternenCodierungunddasUndoLog.ImRedoLogloggtInnoDballedatenvernderndenOperationen.

  • AndersalsbeiMyISAMistesNICHTmglich,.ibdDateienaufDateisystemebenezukopieren, verschieben, umzubenennen oder einer anderen Instanz von MySQLeinfach so unterzuschieben. JededieserOperationenwird imgnstigstenFall vonInnoDBerkanntundzurckgewiesenundzerstrtimschlimmstenFalldieInstanz.

    Wenn wir unser InnoDBmit innodb_file_per_table = 0 betreiben, liegen auch alleTabellen in der zentralen ibdataDatei, und es existieren im SchemaDirectory nur.frmDateien.

    ibdataDateienknneninternnichtgenutztenPlatzfreigebenundwiederverwenden,aber sie schrumpfen niemals. Wenn man eine Instanz nachtrglich voninnodb_file_per_table=0auf1umstellt,werdenexistierendeInnoDBTabellendurchein "ALTERTABLE tENGINE=InnoDB" in einemexterne .ibdDatei umkopiert undderPlatz inder ibdataDatei freigegeben,aberdieDateiwirdniemalsschrumpfen.Die einzige Methode, von der alten groen ibdataDatei weg zu kommen ist einDumpderDatenbankundeinNeuladenderDatenineineandereInstanz.

    Wieviel Platz in einer ibdata oder .ibdDatei frei ist wird im CommentFeld derAusgabevonSHOWTABLESTATUSfr jedeTabelleangezeigt.DieAngabensindnatrlichimmerVielfachevon16K,derInnoDBSeitengre.

    Es ist mglich, mehr als eine ibdataDatei zu haben, aber es ist vollkommenunmglichzukontrollieren,welcheTabelleoderwelcherTeileinerTabelleinwelcheribdataDatei liegt. Bei innodb_file_per_table liegt jedeTabelle immer vollstndig inihrereigenen.ibdDatei.

    DasUndoLog

    JedeTabellehat inInnoDBzweiversteckteSpalten,eineTransaktionsnummerundeinenZeigeraufdie vorhergehendeVersionderZeile imUndoLog,denRollbackPointer.

    Wenn wir eine Zeile in einer InnoDBTabelle verndern, wird die alte Version derZeilemitderaltenTransaktionsnummerausderOriginaltabelleinsUndoLogkopiertunddieneueVersionmiteinerneuenTransaktionsnummerindie.IBDDateikopiert.DerRollbackPointerderneuenVersionderZeilezeigtdabeiaufdiealteVersionderZeileimUndoLog.

    CODE:rootonmysql.sock[innodemo]>begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>updatekrissetd="one"whereid=1

    QueryOK,1rowaffected(0.01sec)

    Rowsmatched:1Changed:1Warnings:0

    rootonmysql.sock[innodemo]>select*fromkris

    ++++++++

    |id|d|txn#||id|d|txn#|

    ++++++++

    |1|one|2|>|1|eins|1|

    |2|zwei|1|++++

    |3|drei|1|

    ++++

    3rowsinset(0.00sec)

    IndemBeispielobenhabeichdieseverborgenenZeilenunddenUndoLogEintragzurVerdeutlichungmanuellvonHandhereingeflscht.

    Diese Verzeigerung von Zeilenversionen kann auch ber mehr als eine Version

  • gehen,d.h.einEintrag imUndoLogkannaufeinenochltereVersioneinerZeile

    zeigenundsoweiter. LschtmandasUndoLognie, bekommtman fr jedeZeile

    jederTabelle eine lineare Liste, die die vollstndigeAbfolgeallerVersionendieser

    Zeile darstellt. Irgendwann einmal jedoch wird der UndoEintrag, der jetzt ja nicht

    mehr gebraucht wird, von einer InnoDBAufrumkomponente, dem PurgeThread,

    gelschtundderEintragimUndoLoggelscht,sodadasUndoLoginderRealitt

    nichtinsUnendlichewchst.

    Das Kopieren von Versionen von Zeilen aus der Tabelle ins UndoLog und die

    Verzeigerung von Zeilenversionen untereinander nennt man MVCC, Multiversion

    ConcurrencyControl.

    MVCC ist, sowie InnoDBes implementiert, frdasCOMMIToptimiert.Wennman

    als Anwender ein COMMIT ausfhrt, ist nichts zu tun: Die Transaktion wird als

    comittedmarkiert,dieVerzeigerungderEintrgebleibtbestehenundallesistgut.

    CODE:

    rootonmysql.sock[innodemo]>rollback

    QueryOK,0rowsaffected(0.01sec)

    ++++++++

    |id|d|txn#||id|d|txn#|

    ++++ROLLBACK++++

    |1|eins|1|begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>updatekrissetd="one"whereid=1

    QueryOK,1rowaffected(0.00sec)

    Rowsmatched:1Changed:1Warnings:0

    undlassendiesnunhngen,ohnedieTransaktionmitCOMMIToderROLLBACKzu

    beenden.IneinemzweitenFensterffnenwireinezweiteVerbindungzurDatenbank

    undschaueneinmal,waswirsehen:

    CODE:

    rootonmysql.sock[(none)]>useinnodemo

    Databasechanged

    rootonmysql.sock[innodemo]>select*fromkris

  • +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    Wirsehenersteinmal,daunserLesezugriffnichthngtoderwartet,obwohlgerade

    eine Transaktion im Gange ist. In MVCC knnen lesende und schreibende

    Operationen einander niemals blockieren das ist ein groer Unterschied zu

    MyISAMundeiner derGrndewarum InnoDBbeiSzenarienmit hoherParallelitt

    schnelleristalsMyISAM,obwohlesstndigKopienvondenDatenmachenmu.

    WirsehenhierdenaltenWertderZeilemitderid=1.Wirwissenvonweiteroben,

    dasinderibdDateischondieneueVersionsteht.AlsomudieDatenbankhierfr

    dieZeileid=1frunsereVerbindungdemRollbackPointergefolgtseinundfruns

    diealteVersionderZeilegefischthaben.DieersteVerbindungdagegensieht,wie

    nochweiter oben schon demonstriert, ihre eigenennderungen sofort, auch ohne

    COMMIT.

    Wir knnen auch unsere externe, zweite Verbindung diese uncomitteten Daten

    sehen lassen.Dasgeht, indemwirdenTRANSACTIONISOLATIONLEVELdieser

    VerbindungaufREADUNCOMITTEDstellen.

    CODE:

    rootonmysql.sock[innodemo]>settransactionisolationlevelreaduncommitted

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|one|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    Wirmerken uns:Eine schreibendeVerbindungmacht immer dasselbe:Sie kopiert

    die zu verndernden Daten vor der nderung ins UndoLog und verzeigert sie

    korrekt.Dasmusietun,damitsieeinRollbackmachenkann.

    Eine lesendeVerbindungkannsichnunaussuchen,welcheVersionderDaten sie

    sehen will. Jede einzelne lesende Verbindung kann das unabhngig von allen

    anderenVerbindungentunundsichauchumentscheiden,dennessindja in jedem

    FallalleVersionenderDatenimmerda.

    In READUNCOMITTED sehen wir immer die Daten aus dem ibdFile und folgen

    demRollbackPointer nie. Dadurch sehenwir Daten, die noch nicht comitted sind

    undesauchvielleichtnieseinwerden.WirseheneineVersionderRealitt,dienoch

    nicht existiert und es vielleicht nie tun wird. Fr eine Anwendung ist das in den

    meistenFllennichtdasgewnschteVerhalten.

    TransactionLevelReadComitted

    SetzenwirdagegendenTRANSACTIONISOLATIONLEVELaufREADCOMITTED,

    dannbekommenwir fralleZeilen,dienicht voneinerTransaktion inBearbeitung

    sind,dieDatenausdem.ibdFileundfralleZeilen, indenennochnichtcomittete

    Daten stehen folgt dieDatenbank demRollbackPointer genau eineEbene in das

    UndoLogund liefertunsdieDatenvondort.Dadurchbekommenwir immerDaten

  • zu sehen, die "wirklich da sind" und sie hypothetischenVersionen derWirklichkeit

    vonREADUNCOMITTEDwerdenfrunsausgefiltert.

    JedochbestehtimmernochdieMglichkeit,dawirsehenwirsichDatenverndern,

    wenn wir sie zweimal lesen. Gegeben sei etwa eine Anwendung, die mit der

    Anweisungssequenz"beginupdatekrissetd=d+1whereid=2commit"einen

    Zhler fr die Zeile id = 2 hochzhlt. Wenn wir in einer zweiten Verbindung

    wiederholt die Tabelle mit "READ COMMITTED" betrachten, bekommen wir

    vernderlicheWerte von kris.d fr kris.id = 2 zurck. Das ist auch dann der Fall,

    wenn unsere zweite Verbindung selbst eine Transaktion durchfhrt die Sequenz

    BEGINSELECTSELECTCOMMIT, eine ReadOnlyTransaktion, hat in READ

    COMMITTEDkeinebesondereBedeutung.

    CODE:

    rootonmysql.sock[innodemo]>settransactionisolationlevelreadcommitted

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|zwei|

    |3|drei|

    +++

    3rowsinset(0.01sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|1|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    rootonmysql.sock[innodemo]>select*fromkris

    +++

    |id|d|

    +++

    |1|eins|

    |2|2|

    |3|drei|

    +++

    3rowsinset(0.00sec)

    rootonmysql.sock[innodemo]>commit

    QueryOK,0rowsaffected(0.00sec)

    TransactionIsolationLevelRepeatableRead

    Genau dies ndert sich, wenn wir im Transaction Isolation Level REPEATABLE

    READ arbeiten: In demMoment, in dem wir mit BEGIN eine Transaktion starten,

    sieht diese Verbindung einen Snapshot der Datenbank, der sich nicht mehr

    verndert,bisdieTransaktionbeendetwird. InREPEATABLEREADhatalsoauch

    eineReadOnlyTransaktioneineBedeutung.

    Internwirddiessorealisiert,dabeimLeseneinerZeilenichtnureinenSchrittindie

    Vergangenheit der Zeile im UndoLog gegangen wird, sondern den untereinander

    verbundenen Zeigern im UndoLog so lange gefolgt wird bis die neuste Zeile

    gefundenwird,diefrdiesenLesernochsichtbarist.Whrendalsodieschreibenden

    Verbindungen die Daten in der Tabelle immer weiter und weiter ndern, wandern

    mehr undmehr alte Versionen dieser Zeile insUndoLog,wo sie bis auf weiteres

    archiviertwerden.

  • WhrendbeiREADCOMITTEDalsoimmernureinSchrittvonderTabelleinsUndoLog gemacht wird, kann es bei REPEATABLE READ vorkommen, da fr einenbestimmtenLeservieleSchritteimUndoLogindieVergangenheitderZeilegemachtwerdenmssen.DadurchistdieLebensdauereinzelnerEintrgeimUndoLogabernichtmehreinheitlich,sonderneskannsein,daeinmalmehroderwenigerEintrgeim UndoLog aufbewahrt werden mssen. InnoDB hat einen globalen (fr SHOWPROCESSLIST nicht sichtbaren) PurgeThread, der schaut, was die ltesteTransaktion im System fr eine Transaktionsnummer noch brauchen wrde. DerPurgeThread lschtdannalleEintrge imUndoLog,dienochltersindalsdieseTransaktion.

    Das bedeutet aber anders herum auch, da eine lange laufendeTransaktion denPurgeThread effektiv still legt. Wenn zum Beispiel ein "mysqldump singletransaction"einegroeDatenbankexportiert,dannkannessein,dadiese"singletransaction"vieleMinutenoder jenachDatenmengegarstundenlangstehenbleibtunddamit auchderPurgeThreadnichts lschenkann.Finden indieserZeit vieleSchreibzugriffestatt,wirddasUndoLogunterUmstndenbetrchtlichanwachsen.Wieweiterobenerklrt, liegtdasUndoLog immer im ibdataFile,auchdann,wenninnodb_file_per_tableaktiviertist.Dasbedeutet,dadasibdataFileauchbeieinemServer, der innodb_file_per_table aktiviert hat, grerwerden kann 256Mbis 1GsindunterUmstndenvollkommennormal.

    Wenn ein ibdataFile zu klein ist und nicht wachsen kann, weil in derinnodb_data_file_path ohne "autoextend" definiert ist oder die Platte voll ist, kanndies unter Umstnden zu schwer verstndlichen Fehlermeldungen ("Table full",obwohlnochmchtigPlatzdaist)oderTransaktionsabbrchenfhren.

    TransactionIsolationLevelSERIALIZEABLEundSELECT...FORUPDATE

    Whrend REPEATABLEREAD also alle unsere Leseprobleme lst, fehlt uns jetztnoch ein Mechanismus, mit dem wir READMODIFYWRITE Zyklen korrekthandhaben knnen. Ein ReadModifyWriteZyklus ist ein Zugriff, bei dem eineAnwendung Daten liest, in der Anwendung verarbeitet und dann die gendertenDaten zurck schreibt. Damit dies konsistent geschehen kann, mu sichergestelltsein,dadieDatenimDatenbanksystemnichtmehrgendertwerdennachdemdieAnwendung sie gelesen hat, ansonsten bekommen wir eine RaceCondition, weilzwei Verbindungen zeitgleich dieselben Daten lesen und sich gegenseitig dienderungenberschreiben.

    Wir erreichendiesdurcheineTransaktion, inderdieDatenmit einemSELECT ...FOR UPDATEStatement gelesen werden. Dies ist eine SelectAnweisung, dienormal lesend zugreift, aber dabei Locks erzeugt wie ein UpdateStatement. InunseremFallbedeutetdies,daexclusiveLocksaufallenZeilenerzeugtwerden,dievomSelectStatementberden Indexzugegriffenwird.DieLocksbleibenbis zumEndederTransaktionstehen.DasFORUPDATEbewirktalsodurchdieLocks,dazweinderungenandenselbenZeilennacheinander,alsoserialisiert,erfolgen.

    DabeigilteseinpaartrickreicheDingezubeachten:

    Zunchst einmal werden die Locks ber den Index erzeugt.Wenn wir also einenEXPLAINPlansehen, indemein "usingwhere"steht,dannheitdas inderRegel,da mehr Zeilen ber den Index selektiert werden als nachher im Result Set zusehensindesgibtweitereeinschrnkendeBedingungen,diedenberden IndexgeneriertenResultSetweiterverkleinern.FrdieLocksbedeutetesaber,daunterUmstndenmehrZeilengelocktwerdenalswirmchtenoder imResultSetsehen

  • knnen.

    Wir knnen das demonstrieren, indem wir eine Tabelle mit zwei Spalten a und berzeugen.aseiPrimaryKeyundbseinichtindiziert.WennwirjetzteinSELECT...FORUPDATEausfhren,dasWHEREa=...ANDb=...enthlt,werdenalleinderaBedingunggefundenenZeilengelockt,auchjene,beidenendiebBedingungnichtzutrifft.CODE:

    Tabelleanlegen

    rootonmysql.sock[kris]>createtablet(

    aintegernotnull,

    bintegernotnull

    )engine=innodb

    QueryOK,0rowsaffected(0.16sec)

    Datengenerieren

    rootonmysql.sock[kris]>insertintotvalues(rand()*100000,rand()*10)

    QueryOK,1rowaffected(0.01sec)

    rootonmysql.sock[kris]>insertintotselectrand()*100000,rand()*10fromt

    QueryOK,1rowaffected(0.01sec)

    Records:1Duplicates:0Warnings:0

    ...

    rootonmysql.sock[kris]>insertintotselectrand()*100000,rand()*10fromt

    QueryOK,4096rowsaffected(0.26sec)

    Records:4096Duplicates:0Warnings:0

    DasindnochDuplikatedrin,diedas

    AnlegeneinesPrimaryKeyverhindern

    rootonmysql.sock[kris]>createtabledupasselectafromtgroupbyahavingcount(a)>1

    QueryOK,300rowsaffected(0.08sec)

    Records:300Duplicates:0Warnings:0

    rootonmysql.sock[kris]>deletefromtwhereain(selectafromdup)

    QueryOK,608rowsaffected(5.65sec)

    rootonmysql.sock[kris]>altertabletaddprimarykey(a)

    QueryOK,7584rowsaffected(0.40sec)

    Records:7584Duplicates:0Warnings:0

    rootonmysql.sock[kris]>droptabledup

    QueryOK,0rowsaffected(0.00sec)

    68Zeilenberaselektiert

    rootonmysql.sock[kris]>selecta,bfromtwherea>99000

    ...

    |99490|8|

    ...

    62rowsinset(0.00sec)

    JetztdieDemo:IneinerTransaktion

    einSELECT...FORUPDATEfahren

    rootonmysql.sock[kris]>begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[kris]>selecta,bfromtwherea>99000andb=10

    +++

    |a|b|

    +++

    |99839|10|

    |99970|10|

    +++

    2rowsinset(0.00sec)

    IneineranderenVerbindungknnenwirnunversuchen,etwadasPaar(99490,8)zundern.Wirsehen:DasStatementhngtwegeneinesXLocksaufderZeile.CODE:rootonmysql.sock[kris]>updatetsetb=502wherea=99490

    ...hang...

    Wenn wir jedoch einen weiteren INDEX (a,b) definieren und seine Benutzungerzwingen,werdennurdiebeidenRecords(99839,10)und(99970,10)gelocktund

  • unserparallelesUpdateauf(99490)gehtohneWartendurch:

    CODE:

    rootonmysql.sock[kris]>begin

    QueryOK,0rowsaffected(0.00sec)

    rootonmysql.sock[kris]>select*fromtforceindex(a)wherea>99000andb=10

    +++

    |a|b|

    +++

    |99839|10|

    |99970|10|

    +++

    2rowsinset(0.00sec)

    rootonmysql.sock[kris]>explainselect*fromtforceindex(a)wherea>99000andb=10\G

    ***************************1.row***************************

    id:1

    select_type:SIMPLE

    table:t

    type:range

    possible_keys:a

    key:a

    key_len:4

    ref:NULL

    rows:63

    Extra:UsingwhereUsingindex

    1rowinset(0.00sec)

    InderanderenVerbindung:

    CODE:

    rootonmysql.sock[kris]>updatetsetb=503wherea=99490

    QueryOK,1rowaffected(0.09sec)

    Rowsmatched:1Changed:1Warnings:0

    Dieses Lockingverhalten hat also weitreichende Auswirkungen: Wir mssen beim

    Schreiben vonSQLunbedingt darauf achten, dadieQueryplne vonSELECT ...

    FORUPDATEStatementsdie korrekten Indizesbenutzen.EinALLoder INDEX in

    derTypeSpaltedesEXPLAINwrdehierzumBeispieleinenIndexScanandeuten

    daderganzeIndexberstrichenwird,bekommenwirsoeffektiveinsehrteures,aus

    ZeilenlockszusammengesetztesTableLock.

    Dasandere trickreicheundunerwarteteVerhalten ist, da InnoDBnichtnurZeilen

    lockt, sondern auch die Lcke hinter den Zeilen. Dieses NextKey Locking

    vereinfacht die Implementierung von REPEATABLEREAD. Dieses Verhalten ist

    abschaltbar, der Schalter hat den unerwarteten Namen

    "innodb_locks_unsafe_for_binlog" stellt man ihn auf "ON", werden von InnoDB

    einfachZeilenlockserzeugtohnedieLckehinterderZeileauchzusperren.

    AufdenTransactionIsolationLevelSERIALIZEABLEverhltsichdasSystemgenau

    wieaufdemLevelREPEATABLEREAD,fhrtaberjedeseinzelneSELECTsoaus,

    alsseiesalsSELECT...FORUPDATEgeschriebenworden.Das fhrtdazu,da

    jedesSELECTLockserzeugtalswrees inUPDATEStatement, jederLesezugriff

    locktalsowieeinSchreibzugriff.Diesfhrteffektivdazu,dasichselbstLesezugriffe

    (diejaSchreiblockserzeugen)gegenseitigindieQuerekommen,wennsiezugleich

    dieselbenDatenlesenwollen.DiesistnochschlechteresVerhaltenalsinMyISAM!

    DerTransactionIsolationLevelSERIALIZEABLEistunntig:Erwirdniegebraucht,

    wenn der SQLCode in der Anwendung korrekt mit ... FOR UPDATE lockt. Nur

    Anwendungen, die dies nicht korrekt tunund bei denen auerdemdasSQLnichtkorrigierbarist,brauchendenIsolationLevelSERIALIZEABLE.

    GeschriebenvonKristianKhntoppinMySQLum10:17|Kommentare(8)|Trackbacks(4)

    Trackbacks

  • TrackbackURLfrdiesenEintrag

    linksfor20080131DieInnoDBStorageEngineDiewunderbareWeltvonIsotopp(tags:KrisabouttheInnoDBEngineofMysql)Weblog:c0t0d0s0.orgAufgenommen:Jan31,11:18

    DieInnoDBStorageEngine:KonfigurationLinks:StrukturenimSpeicher,Rechts:StrukturenaufDisk.Oben:LogStrukturen,Unten:TablespaceStrukturen.WieeineTransaktionphysikalischorganisiertwirdWenninInnoDBeineneueTransaktionbegonnenunderzeugtwird,istsiejanochWeblog:DiewunderbareWeltvonIsotoppAufgenommen:Feb03,11:54

    AnInnoDBtutorialMySQLoffersavarietyofstorageenginesgivingyoualotofflexibilityinmanagingyourstorageanddataaccessneeds.StillIencountercustomerswhoarenotusingthisflexibilitywhentheyshould,becausetheylackinformationabouttheadvantagesoWeblog:MySQLdumpAufgenommen:Feb07,08:48

    TransactionsAnInnoDBTutorialThisisanenglishtranslationofthesecondpartofanarticleinmygermanlanguageblog.TransactionsAnInnoDBtutorialInnoDBdoestransactions.Meaning:ItcollectsstatementsworkingonInnoDBtablesandappliesthemonCOMMITtoalltablesWeblog:MySQLdumpAufgenommen:Feb07,08:53

    KommentareAnsichtderKommentare:(Linear|Verschachtelt)

    "aseiPrimaryKeyundbseinichtindiziert.WennwirjetzteinSELECT...FORUPDATEausfhren,dasWHEREa=...ANDb=...enthlt,werdenalleinderaBedingunggefundenenZeilengelockt,auchjene,beidenendiebBedingungnichtzutrifft."WennaeinPrimaryKeyist,isterdannnichtauchUniqued.h.gibtesnichteinfachnurgenaueinenDatensatz,frden"WHEREa=..."gilt?ImweiterenbenutztDujaauchnicht"="sondern">"imWHERE...#1ChristianKirscham30.01.200811:36

    Ja,DuhastRecht,esmuauchimerstenBeispielINodersonsteineRangeBedingungsein.

    Andererseitsistesfrdas,waszuzeigenist,unwesentlich.Punktistlediglich,dadieLocksaufdenIndexgelegtwerdenundzwarbeimberstreichendesselben.WennderQueryPlanschlechtist,istesdasLockschemaauch.DaheristesvonzentralerBedeutungbeimEntwurfderAnwendung,alleQueryplnevonSELECT...FORUPDATEnocheinmalgesondertzuchecken,weilmansonstbeimDeploymentKrieghat.#1.1KristianKhntopp(Link)am30.01.200813:05

  • IstesgenerellvonVorteilinnodb_file_per_table=1zusetzen?Wennja,wasfreinVorteil?IstesnichtegalobdieTabellenineinerzentralenibdataDateiliegenodereinzelnverteiltsind?Wennichsieehnichtkopierenkann...

    Und,dadueserwhnst,wasbringtmyisam_recoverFORCE,BACKUP?

    PrimaArtikel!#1.1.1StephanWentzam30.01.200819:41

    GibteseigentlicheinenGrund,waruminnodb_file_per_tablenichtperDefaultdenWert1hat?#2Guidoam30.01.200812:02

    AUsdemselbenGrund,warumsql_mode=""istundweshalbmyisam_recovernichtaufFORCE,BACKUPstehtdaswarfrhernichtsounddieDefaultszundernbringtunterUmstndenunerwnschteKompatibilittsprobleme.#2.1KristianKhntopp(Link)am30.01.200813:06

    Dankeschn,wiedereinmaleinTopBeitrag!IchhabeaktuellzwarkeineInnodbDatenbank,habeesabertrotzdemsehraufmerksamgelesen.#3Peteram30.01.200814:48

    MichwrdeinteressierenfrwelcheAnwendungsgebietesichInnoDBbessereignetalsMyISAM.DasmitdenTransaktionenhabeichverstanden.WiesiehtesmitderPerformanceaus?EinAnwendungsgebietwasmichbesondersinteressiertsindgroeDatenbankenvonOnlineforen(PHPbasiert)?#4Manuelam30.01.200816:03

    MyISAMeignetsicheigentlichimmerdann,wennman(fast)ausschlielichLeseoder(fast)ausschlielichSchreiboperationenhatundmanzustzlichaufACIDverzichtenkann.

    InnoDBbietetsichan,wennmaneinausgeglichen(er)esVerhltnisvonLesezuSchreiboperationenhatbzw.wennmanACIDbraucht.#4.1SebastianBergmann(Link)am05.02.200812:46

    KommentarschreibenName

    EMail

    Homepage

    Antwortzu

    UmschlieendeSternehebeneinWorthervor(*wort*),per_wort_kanneinWortunterstrichenwerden.

  • Kommentar UmmaschinelleundautomatischebertragungvonSpamkommentaren

    zuverhindern,bittedieZeichenfolgeimdargestelltenBildinder

    Eingabemaskeeintragen.NurwenndieZeichenfolgerichtigeingegeben

    wurde,kannderKommentarangenommenwerden.BittebeachtenSie,

    dassIhrBrowserCookiesuntersttzenmuss,umdiesesVerfahren

    anzuwenden.

    HierdieZeichenfolgederSpamschutzGrafikeintragen:

    BBCodeFormatierungerlaubt

    Datenmerken?