Innodb (Foreign key) vs myisam (index)

Hej,

ofte er der tradeoffs at tage hensyn til i et database miljø. Et index kan være med til at reducere søgetiden i en select, men gøre insert tiden større. Derfor må man lave test og se hvilket tradeoff der bedst kan betale sig.

F.eks. når du siger at inserts er om natten og måske ikke forstyrre andre, ville jeg mene at det var mere eftertragtet at have en mindre throughput, men forsøge sig med indexes af forskellig art til at få select throughout større i dagstimerne.

Dog er forskellen på InnoDB og MyISAM ret så karakteristisk: I korte veninder, så tilbyder InnoDB en større data sikkerhed i og med den benytter sig af row-lock fremfor table-lock (som er i MyISAM) og er transaction-sikker. Row-lock gør den dog noget hurtigere.

Men sammenlignet med MyISAM vil den de i de fleste tilfælde være langsommere, trods for at den benytter Row-locking.

InnoDB har, ud over row-locking, også den dejlige feature at den har foreign-key og relationship constraints, men dette er helt afhængig af din database og hvordan systemet er opbygget og hvordan det skal bruges.

Her er der lige et par sider om fordelene og ulemperne ved de to database motorer:

http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html

http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM

Men lav nogle tests og udskriv tiderne det tager for f.eks. 50x5 queries (random) og prøv med forskellige indexes. Så kan du plotte tallene i et excel ark og se hvilke giver bedst throughput.

Hej public2,

Tak for dit svar, som langt hen af vejen bekræfter det jeg har læst mig til.

Mht. row-lock så kommer dette først ind i spil når jeg skriver til tabellen samtidigt. Er det ikke korrekt forstået?

Som sagt, så i mit scenarie, vil der ikke blive skrevet og læst fra tabellerne samtidigt, og som du nævner tager skrive tiden lidt længere tid når der er index på, men i dette tilfælde er dette ikke et problem da scriptet kører om natten, og er færdigt når folk kommer på arbejde.

Mht. data sikkerhed så er det noget vores script styrer 100% og er derfor ikke noget databasen behøver at tage højde for.

Jeg læser lige dine links her senere når jeg har lidt mere tid

Jo row og table-locking er for inserts og updates, men med row-locking vil du det være muligt at have en form for concurrency mens du f.eks. opdatere en række.

Angående datasikkerheden, så vil jeg nu mene at det er en god idé at tænke databasen mekanismer med ind også og tror også at I bruger de mekanismer der er i databasen som logging og roll-back funktioner, de kan ikke helt afmeldes.

Nu bruger jeg kun MySQL til hjemmesider, hvor der foregår en daglig backup af dataen, så de rent sikkerhedsmæssige og låse mekanismer i der er jeg ikke så meget inde i, men når jeg f.eks. bruger IBMs DB2 bruger jeg en del tid på at tænke over hvordan sikkerheden kan implementeres. Det er klart at jeg benytter mig af sikkerhed i både script / program, men så sandelig også databasens.

Og hvad angår indexes, så vær sikker på at du bruger de rette indexes. Nu er MySQL ikke en særlig stærk index maskine i den forstand, at mulighederne for indexes er begrænset til B-trees (jeg kan tage fejl, men synes at have hørt at InnoDB kan benytte R-trees og/eller hashtables til indexes også), så alt efter hvordan dine queries og data er lavet, så kan man med et par gode tommelfinger regler og lidt testing lave et godt covering index på dine tabeller.

Nu skal vi passe på ikke at rode ting sammen.

MySQL understøtter foreign key i den betydning at man kan joine. Kun InnoDB tabeller understøtter foreign key constraint d.v.s. at en INSERT vil fejle hvis den ikke peger på en gyldig række i den anden tabel.

Index har stor betydning for søgning. Hverken primary key eller foreign key gør i sig selv noget. Men MySQL opretter automatisk et index for primary key. Og med InnoDB foreign key constraints skal man oprette index.

Ved skrivninger vil man normalt forvente at MyISAM er hurtigere end InnoDB, fordi MyISAM kun skal skrive data mens InnoDB skal skrive både log og data.

Ved læsninger vil man forvente at de er ca. lige hurtige. Det betyder ikke at de er præcis lige hurtige. Den ene kan godt være 25% hurtigere end den anden til noget.

Din forskel på en faktor 100 kan ikke kun skyldes forskellig tabel type. Den langsomme må have dårligere index eller dårligere hardware eller noget lignende.

Til alt bortset fra en typiske PHP hobby applikation vil jeg klart anbefale InnoDB.

public2 - Jeg er helt enig i at sikkerheden skal tænkes ind i alle kroge af et system, men grundet konstruktionen og sikkerhed andet steds så vil det ikke give meget mening i lige præcis denne situation.

Arne_v - min faktor 100 er jeg godt med på er helt hen i vejret, men det var for at understrege mit mønster.
Kan du komme lidt ord på hvorfor du "altid" vil anbefale innodb frem for myisam?

Har I nogen erfating med at blande de to tabeltyper?

Jeg leger med to servere og vil prøve at teste begge tabeltyper og så vende tilbage med nogle testtider :-)

Tak for hjælpen indtil videre gutter.

Primaert p.g.a. bedre recovery og transaktions support.

Jeg er med Arne_v angående valget af InnoDB (også til PHP hobby app.) da jeg er stor fan af både Row-locking, foreign key constraints og dens cascade funktion.

Men så igen ville jeg ikke benytte MySQL til større databaser systemer eller andet end web-application, og selv der kan jeg godt lide Postgresql, trods for at der ikke er så mange udbydere der understøtter det, så det er mest på mine egne webservere jeg bruger det. Men det er mest på grund af dens index-typer.

larsny?

??

jeg skylder et svar og en beskrivelse af hvad jeg kom frem til..

Jeg fik lavet et program der kunne lave en masse inserts i forskellige situationer.

Følgende opsætninger blev testet for både MyISAM og InnoDB:
1. Alm. insert cmd
2. Insert med transaction
3. Stored procedure med transaction
4. Alm. insert via stored procedure

Alle 8 test blev kørt med 1000000 insert af 2 kolonner med lidt data.

Testen blev kørt 2 gange og som I kan se neden for så blev det MyISAM der vandt testen med uden transaction el.

Husk på, at disse resultater kan være anderledes på netop din server opsætning. Så det anbefales at køre en test!

InnoDB:
Elapsed: 01:52:59.2229579
in minutes    :112.987049298333
in seconds    :6779.2229579
in milliseconds:6779222.9579
Elapsed: 00:04:27.1457119
in minutes    :4.45242853166667
in seconds    :267.1457119
in milliseconds:267145.7119
Elapsed: 00:04:56.0366007
in minutes    :4.933943345
in seconds    :296.0366007
in milliseconds:296036.6007
Elapsed: 00:03:48.3498327
in minutes    :3.805830545
in seconds    :228.3498327
in milliseconds:228349.8327

MyISAM:
Elapsed: 00:02:20.5610547
in minutes    :2.342684245
in seconds    :140.5610547
in milliseconds:140561.0547
Elapsed: 00:02:13.6213608
in minutes    :2.22702268
in seconds    :133.6213608
in milliseconds:133621.3608
Elapsed: 00:03:16.5536534
in minutes    :3.27589422333333
in seconds    :196.5536534
in milliseconds:196553.6534
Elapsed: 00:03:00.5930575
in minutes    :3.00988429166667
in seconds    :180.5930575
in milliseconds:180593.0575

InnoDB:
Elapsed: 01:20:55.0324547
in minutes    :80.9172075783333
in seconds    :4855.0324547
in milliseconds:4855032.4547
Elapsed: 00:05:06.0095979
in minutes    :5.100159965
in seconds    :306.0095979
in milliseconds:306009.5979
Elapsed: 00:06:53.9533912
in minutes    :6.89922318666667
in seconds    :413.9533912
in milliseconds:413953.3912
Elapsed: 00:06:08.5358499
in minutes    :6.142264165
in seconds    :368.5358499
in milliseconds:368535.8499

MyISAM:
Elapsed: 00:02:32.0452030
in minutes    :2.53408671666667
in seconds    :152.045203
in milliseconds:152045.203
Elapsed: 00:02:34.0143999
in minutes    :2.566906665
in seconds    :154.0143999
in milliseconds:154014.3999
Elapsed: 00:04:33.7453718
in minutes    :4.56242286333333
in seconds    :273.7453718
in milliseconds:273745.3718
Elapsed: 00:04:20.1580132
in minutes    :4.33596688666667
in seconds    :260.1580132
in milliseconds:260158.0132

MyISAM understoetter ikke transkationer saabrug af transaktioner ignoreres bare for denne.

Det kan ikke overraske at det er hurtigere at skrive en gang til disk (MyISAM) end at skrive to gange til disk (InnoDB).

SP kun interessant performance maessigt hvis et kald laver flere INSERT.

Med InnoDB bruger man altid transaktion. Default er en transaktion per statement.

Jo flere statements man bundter i en enkelt transaktion jo bedre performance faar man. Men det er sjaeldent saa interessant da transaktioner normalt defineres af business logic ikke af hvad der performer bedst.